Foreach help

[dennismonsewicz]

$q = mysql_query("SHOW TABLES FROM *****");
	$row = mysql_fetch_object($q);

	foreach($row as $r) {
		print_r($r);
	}

 

for some reason the code above is only returning the first record it finds and not every record.. the code above is to echo out ALL of the table column names

[MatthewJ]

<?php
$q = mysql_query("SHOW TABLES FROM ****");

      $row = mysql_fetch_object($q);
      
      while($row = mysql_fetch_object($q)) {
 echo "<pre>";
         print_r($row);
         echo "</pre>";
      }
?>

 

That will list all of the tables if that helps

[dennismonsewicz]

hmmm... that worked but how come you can't do it in a foreach loop?

[gr1zzly]

Hey, sorry to hijack your post but I'm having a similar problem and urgently need help.

 

Posted here but no replies yet - link below:

 

http://www.phpfreaks.com/forums/index.php/topic,268388.0.html

 

Surely someone on here knows how to use foreach's with mysql results objects?

 

Thanks

[PFMaBiSmAd]

Each call to a mysql_fetch_xxxxx function, returns ONE ROW from the result set (please see the php.net documentation.) To iterate over all the rows in a result set, you must use a loop that continues until there are no more rows to fetch.

 

The foreach() loop that was posted in this thread just iterates over the columns in one row.

[dennismonsewicz]

ok... so now that I have the while loop going how do I echo out just one of the tables?

 

I can print_r($row) all day long, but how do I access just one of the DB tables?

[MatthewJ]

Not real familiar with mysql_fetch_object(), but this works

 

<?php
$q = mysql_query("SHOW TABLES FROM ****");

$row = mysql_fetch_assoc($q);

while($row = mysql_fetch_assoc($q)) {
   echo $row['Tables_in_****']."<br />";
}
?>

[jcombs_31]

ok... so now that I have the while loop going how do I echo out just one of the tables?

 

I can print_r($row) all day long, but how do I access just one of the DB tables?

 

That depends on your query and the method for retrieving the results.  If you are using mysql_fetch_assoc, you would do something like

 

while($row = mysql_fetch_assoc($result) {
  echo $row['fieldname'];
}

[jcombs_31]

What you are doing doesn't make sense.

 

http://us.php.net/manual/en/function.mysql-fetch-assoc.php

[gr1zzly]

okay, please forgive my ignorance / stupidity but which part exactly? I literally have tried all kinds of different while and foreach loops and combinations so if the code I have now has got a bit lost somewhere I apologize.

 

What I need is each whole row of results to be stored associatively in my $data array variable as one entry. So that each entry in $data is an associative array containing one row from my db.

 

[gr1zzly]

Ok I think I've got it working now. Sorry I wasn't fully understanding how to use the results with an array variable. I am now using the following:

 

$r = @mysqli_query($dbc, $q); // OR $errors = mysqli_error($dbc);

if ($r) { // If OK.

$i = 0;

while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC) ) {
	$data[$i] = $row;
	$i++;
};

return array(true, $data);

}

 

Using the extra $i var to reference the $data array allowed me to create an array entry for each row as a sub array containing all the rows cell data.

 

Thanks to all your helps.

[PrinceOfDragons]

I made a function for this type of thing.

function name()
{
$sql = "SELECT * FROM table_name";
$results = mysql_query($sql, $dCon);
$row = mysql_fetch_assoc($results);
while($row = mysql_fetch_assoc($results))
        {
  echo '<tr>'; //Begins Table Row
foreach($row as $column=>value)
                {
   echo '<td>'; //Begins Table Column
   echo $value;
   echo '</td>'; //Ends Table Column
                 }
         }
echo '</tr>'; //Ends Table Row
}

 

It seems this is not grabbing the first row out of the database though :'( 

[Philip]

I made a function for this type of thing.

function name()
{
$sql = "SELECT * FROM table_name";
$results = mysql_query($sql, $dCon);
$row = mysql_fetch_assoc($results);
while($row = mysql_fetch_assoc($results))
        {
  echo '<tr>'; //Begins Table Row
foreach($row as $column=>value)
                {
   echo '<td>'; //Begins Table Column
   echo $value;
   echo '</td>'; //Ends Table Column
                 }
         }
echo '</tr>'; //Ends Table Row
}

 

It seems this is not grabbing the first row out of the database though :'( 

 

Because you called the first row ($row = mysql_fetch_assoc($results) and then overwrote it (while($row = mysql_fetch_assoc($results)))

[PrinceOfDragons]

Thank You ! Still learning the PHP. Ok I took out the first $row = mysql_fetch_assoc($results) all works 4.0 .

[Philip]

If I take out the first $row = msyql_fetch_assoc it wont work

Did you try it?

[PrinceOfDragons]

Ya I got it. Something was wrong in the database  :-\

[gr1zzly]

Sorry I made a mistake before in addressing the array, the $i is not required -> just use the square brackets to assign a row...

 

while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC) ) {
    $data[] = $row;
};

 

Like so! Then when you come to address the variable later a simple

 

foreach ($data AS $row) {
    // Something to do with the data...
}

 

in this foreach() the $row is the whole row from the database as an associative array (depending on the query and results fetch method of course) and so can be addressed as $row[column_name], where 'column_name' is the name of the column you want the contents of.

 

Hope this helps.