Page 1 of 2
Associative arrays and looping through mysql query
Posted: Tue Jun 24, 2003 4:57 pm
by mikusan
This may seem to be a stupid question but i cannot get my head around to this... I queried my database with something of the form...
Code: Select all
$SQL = mysql_query("SELECT * FROM cd_users");
$Data = mysql_fetch_array($SQL);
And i now want to display all of the results, my problem is the next thing i usually do when i work with this is i take the result of the query and run mysql_fetch_array() ...now then since the result is in an associative array, now then i would like to loop through each row and display all of the values, but i cannot see how to do this, $k and $v are out of bounds so i figure that i have to make a for statement... then let's say that i have the following information in my table
uid name email other
1 mike hello ...
2 jane abc ....
to loop through the result that i get would i say $Data['name'][0] and that will diplay the first value for name...then $Data['name'][1] would diplay second value in this case jane...
Clearly the output should be
uid name email other
1 mike hello ...
2 jane abc ....
Thanks a bunch, maybe i need sleep but i really can't think this one out...
Posted: Tue Jun 24, 2003 5:10 pm
by delorian
Live from php.net :
Code: Select all
foreach($Data as $row) {
foreach ($row as $column) {
print $column."<br>";
}
}
Posted: Tue Jun 24, 2003 5:31 pm
by mikusan
Invalid argument supplied for foreach()
That's how you access normal 2 dimensional arrays, is that the case for associative arrays too? It doesn't work, is my logic flawed, why?
Thanks again
Posted: Tue Jun 24, 2003 6:32 pm
by volka
mysql_fetch_
XYZ() fetches only one row from the resultset, so you have to do this for each record in there one time.
mysql_fetch_array() returns an array containing both numerical and associative keys, your data would be echo'ed twice if used with foreach(), try
mysql_fetch_assoc () instead.
Code: Select all
$SQL = mysql_query("SELECT * FROM cd_users");
while($data = mysql_fetch_assoc($SQL))
{
foreach($data as $key=>$value)
echo $key,'=>', $value, ', ';
echo "\n";
}
Posted: Wed Jun 25, 2003 9:27 am
by mikusan
That's almost perfect thanks, but say for one minute i didn't want to output avery single key and value in a row, then we are going back to the associative properties again...
The example fiven in the manual is something like this:
Code: Select all
while ($row = mysql_fetch_assoc($result)) {
echo $row["userid"];
echo $row["fullname"];
echo $row["userstatus"];
}
But that displays only one row...I was thinking i could do the following,
Code: Select all
$SQL = mysql_query("SELECT * FROM am_users");
$nrows = mysql_num_rows($SQL);
for ($i =0; $i<$nrows;$i++){
$SQL = mysql_query("SELECT * FROM am_users WHERE uid=' " . $i . " ' ");
$Data = mysql_fetch_row($SQL);
echo $Data['name'] . ' ' . $Data['email'];
}
Frankly i think that is the poorest code a monkey with no brains could ever do, and i am ashamed of myself for writing it, but i would appretiate a pointer of how to appropriately do this... since i really can't get my head around my treating arrays with numbers double a[][]; and then outputting a[3][1]; like in Java and such, associative arrays are more powerful...but cannot budge!!
Thanks everyone...
Posted: Wed Jun 25, 2003 9:54 am
by Gleeb
Code: Select all
SELECT `column1`, `column2`, `columnN` FROM `table`
Posted: Wed Jun 25, 2003 9:58 am
by volka
for ($i =0; $i<$nrows;$i++){
$SQL = mysql_query("SELECT * FROM am_users WHERE uid=' " . $i . " ' ");
$Data = mysql_fetch_row($SQL);
echo $Data['name'] . ' ' . $Data['email'];
}
in each iteration a new resultset is querried (mysql_query).
the following mysql_fetch_row() will always fetch the first record of this result set - always the same.
Posted: Wed Jun 25, 2003 10:14 am
by mikusan
OOps that line should have read
Code: Select all
$SQL = mysql_query("SELECT name,email FROM am_users WHERE uid=' " . $i . " ' ");
Then the code will only take the required row...but that's hundreds of calls to the databse... why can't i do it with one or a better way...
i am sorry Gleeb i did not understand what you meant to say... do you mean i should keep the code i have and then loop through the columns... i am sure there is a better way... thanks alot though, but volka you almost got me with your first post, that is almost what i am trying to do... i just want to adapt that so that i can make sure that i am getting the columns i need but still display all the rows...
Thanks guys, i hope i am not too picky...
Posted: Wed Jun 25, 2003 10:33 am
by volka
oops, I misread you code

nevertheless, of course it can be done with only one query
Code: Select all
$SQL = mysql_query("SELECT * FROM am_users");
while($Data = mysql_fetch_row($SQL))
echo $Data['name'] ,' ', $Data['email'];
Posted: Wed Jun 25, 2003 10:41 am
by mikusan
I have yet to try it out, but i think that your post not only is the solution to my gray hair, but also to my understanding of associative arrays...
I cannot thank you enough...Thanks

Posted: Wed Jun 25, 2003 3:40 pm
by xisle
here are a couple more ways to do this,
i prefer mysql_fetch_array for grabbing field names instead of keys.
this will create $variables (equal to that of each field name)
as you loop through each row:
Code: Select all
while($row=mysql_fetch_array($result)) {
extract($row);
echo $fieldname1;
}
To utilize only some values of the result set:
Code: Select all
while(list($whatever1,$whatever2)=mysql_fetch_array($result)) {
print $whatever1."and".$whatever2;
}
xisle
Another aspect of mysql_fetch_array...
Posted: Wed Jun 25, 2003 4:03 pm
by tal3323
I've used that particular command several times in conjunction with a while loop as demonstrated in some of the posts above. What I'd like to point out also though is that mysql_fetch_array returns an associative
and an enumerated array if you're using a PHP version 3.0.7 or higher. So whether you want associative or enumerated arrays, mysql_fetch_array will give you both.
More detail is given on this in the PHP reference downloadable at
http://www.php.net.
Posted: Wed Jun 25, 2003 4:28 pm
by mikusan
Volka i really like your way, but the result i get is there is no output... if i change the mysql_fetch_array i onlye get one row as usual... any thoughts?
Posted: Wed Jun 25, 2003 5:07 pm
by volka
right, I failed on copy&paste

of course mysql_fetch_array or _assoc
what does
Code: Select all
<html><body>
<?php
// <- mysql_connect code here ->
$SQL = mysql_query("SELECT * FROM am_users") or die(mysql_error());
echo '<fieldset><legend>', mysql_num_rows($SQL), ' results</legend><pre>';
while($Data = mysql_fetch_assoc($SQL))
echo $Data['name'] ,' ', $Data['email'], "\n";
echo '</pre></fieldset>';
?>
</body></html>
display?
Posted: Wed Jun 25, 2003 6:12 pm
by mikusan
For header purposes and cleaner coding i keep my output to the very end... hence the concatenated $main...
A modified version of the code you gave me :
Code: Select all
$SQL = mysql_query("SELECT * FROM am_users");
$nrows = mysql_num_rows($SQL);
while($Data = mysql_fetch_assoc($SQL)) {
$main .= $Data['name'] . ' ' . $Data['email'] . '<BR>';
}
$main .= $nrows;
Output 4 rows <-That's true...
and 4 Line breaks...but only one value...the first one...