FRIHOST FORUMS SEARCH FAQ TOS BLOGS COMPETITIONS
You are invited to Log in or Register a free Frihost Account!


Fetch array from mysql





andyd34
I have a db table (t1) with a list of couteries, provinces and regions. When a user saves details to a another db table (t2) it only saves the region, is there a way I can retrieve the province and country without adding it to (t2)

I could do
Code:

$sql_t2 = mysql_fetch_assoc("SELECT * FROM table2"));

$sql_reg = mysql_fetch_assoc(mysql_query("SELECT * FROM table1 WHERE name = " . $sql_2));
$sql_prov.....
$sql_country....


But this seems a bit long winded
Fire Boar
I'm not entirely sure what you're asking; perhaps the schema of your existing database would help.

As for your problem, I'd consider looking into some of the other MySQL functions in PHP:

Code:
if (!$result = mysql_query("SELECT name, age FROM table1")) die('Database error');
while($row = mysql_fetch_array($result))
{
    // Do some stuff with $row; each iteration is a new row from table 1. For example...
    echo $row['name']." is ".$row['age']." years old.<br />\n";
}


Just a bit of jargon explanation. The function to query the database is mysql_query(). That returns a result set, so we store it in $result. The if statement checks to see if $result has something in it AFTER performing the SQL query... if not, there's been an error. Even if there is nothing, $result will still have something in it (an empty result set) so if (!$result) will return false.

Then the while statement loops through each instance of mysql_fetch_array. This picks up each row from the result set stored in $result in turn; $row will be empty if it's reached the last row, so the while loop will end. Then $row contains each field by name in an array, as well as each field by number. So $row[0] would be the same as $row['name'] and $row[1] would be the same as $row['age']. Be careful when using SELECT * though, because if the database structure changes then the order in which the rows will appear will be different. So use the names of the fields to be sure.

If you want something that relates two tables to each other via a foreign key, you could use...

Code:
if (!$result = mysql_query("SELECT field3 FROM table2 WHERE field4 = (SELECT field1 FROM table1 WHERE field2 = 'Something')")) die('Database error');


That's called a subquery. It's a little more complicated, but basically it nests a second query into the first. So whatever the result of SELECT field1 FROM table1 WHERE field2='Something' is, will be compared with field4 in the main part of the query.
andyd34
I think i've been a bit vague with my question so i'll ellaberate a bit, here is what i'm looking to do

DATABASE TABLE 1

Field 1 Id
Field 2 Name
Field 3 Address
Field 4 table2Id

DATEBASE 2

FIELD 1 ID
FIELD 2 PARENT (int)
FIELD 3 NAME
FIELD 4 CHILDREN (int)


Code:
<?
$result = mysql_query("SELECT * FROM TABLE 1") or die(mysql_error());

while($row=mysql_fetch_assoc($result)) {
   echo $row....
   echo $row....
   $result2 = mysql_query("SELECT * FROM TABLE 2 WHERE id = " . $row['FIELD 4']) or die(mysql_error());
   $rT2 = mysql_fetch_assoc($result2);
   if(isset($rT2['PARENT'])) {
      $rt3 = mysql_query("SELECT * FROM TABLE 2 WHERE id = " . $rT2['PARENT']) or die(mysql_error());
      foreach($rt3['parent'] as $name) {
         print_r $name // I WANT IT GIVE SOMETHING LIKE TOWN, COUNTY, COUNTRY   
      }
   }
   
}
?>


I know the above won't work, I'm not looking for the answer just to be pointed in the right direction
Peterssidan
I don't know exactly what your goal is but maybe you want to join two or more tables?
andyd34
Very nearly sorted it
Code:

$parent = $row['cat'];

      while(isset($parent)) {
         $parentExists = mysql_query ("SELECT * FROM table1 WHERE id = $parent ");
         if (mysql_num_rows($parentExists)>0)
            {
            $parentID = mysql_fetch_assoc ($parentExists);
            $parent = $parentID['parent'];
            echo $parentID['name'] . ', ';
            }
            else
            {
            unset ($parent);
            }      
         }


The only trouble is its bringing it in reverse, Country, State, Region instead of Region, State, Country
andyd34
As per my previous post here is what I now have

Code:

$getRes = mysql_query('SELECT * FROM teble1') or die(mysql_error());
while($res=mysql_fetch_assoc($getRes)) {
$parent = $res['name'];
      while(isset($parent)) {
         $parentExists = mysql_query ("SELECT * FROM table2 WHERE id = $parent");
         if (mysql_num_rows($parentExists)>0)
            {
            $parentID = mysql_fetch_array ($parentExists);
            $parent = $parentID['parent'];
            echo $parentID['name'] . '/';
            }
            else
            {
            unset ($parent);
            }      
         }
         
}


Its giving the results in reverse ie Country, State, Region instead of Region, State, Country. Does anyone have any ideas how I can get this sorted. One of you PHP guru's out htere must have an idea
Fire Boar
Ouch, that's a really complicated database structure. I'm afraid that'll be performing hundreds of queries - for the exact amount I suggest doing the following:

Code:
function db_query($query)
{
    global $num;
    $num++;
    return mysql_query($query);
}


Have that before your code, and replace all instances of mysql_query with db_query. Then echo $num.

As for your problem, here's what you can do.

Code:
$buffer = array();
$getRes = mysql_query('SELECT * FROM teble1') or die(mysql_error());
while($res=mysql_fetch_assoc($getRes)) {
$parent = $res['name'];
      while(isset($parent)) {
         $parentExists = mysql_query ("SELECT * FROM table2 WHERE id = $parent");
         if (mysql_num_rows($parentExists)>0)
            {
            $parentID = mysql_fetch_array ($parentExists);
            $parent = $parentID['parent'];
            $buffer[] = $parentID['name'];
            }
            else
            {
            // Dump the contents of the buffer
            echo $buffer[2]."/".$buffer[1]."/".$buffer[0]."\n";
            // Reset variables
            $buffer = array();
            unset ($parent);
            }     
         }
         
}
andyd34
Thanks for your suggestion they worked ok but have made the following change in case I want to add towns and/or districts at a later stage

Code:

$r=0;
$parent = $res['name'];
while(isset($parent)) {
      
$parentExists = mysql_query ("SELECT * FROM table2 WHERE id = $parent");
         
         if (mysql_num_rows($parentExists)>0)
            {
                  
            $parentID = mysql_fetch_array ($parentExists);
            $parent = $parentID['parent'];
            $buffer[] = $parentID['name'];
            $rec=$r;
            $r++;
            }
            else
            {
            // Dump the contents of the buffer

   for($results = $rec; $results >= 0 ; $results--)
               {
   echo $buffer[$results].'/';
   }
            $buffer = array();
                  
            unset ($parent);
            }     
                  
         }

?>


With regards to

Code:

function db_query($query)
{
    global $num;
    $num++;
    return mysql_query($query);
}


This isn't the actual code i am using, in an earlier reply I was asked to elaberate on the code so I came up with the above as an example to show what i was trying to do. I am using my own db functions to query and get results.

But thanks for your help and suggestions they are much appreciated.

Also if you put all the above code together it can be used as a simple breadcrumb link.

Code:

<a href="http://www.yourdomain.com/page.php?path=
<?
for($res = $rec; $res >= 0 ; $res--){
echo $buffer[$res].'/';
}
?>
">
<?
for($res = $rec; $res >= 0 ; $res--){
echo $buffer[$res].'/';
}
?>
</a>

Related topics
[php scripts ] phpweather&email
An Example to mySQL class
Embarrassing newbie php/SQL questions
Need help understanding MySQL "Resource" type retu
SELECTing from MySQL with PHP
Random Image
IP Banning...
AJAX tutorial [2nd part now updated]
how do I use pear pager with a mysql resultset
Need Help integrating php variable into mySQL SELECT query
changing Array format from result of MySQL
Formatting mySQL date
mysql - doubles
Form and PHP problem
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.