mysql and mysql_fetch_array / mysql_fetch_assoc

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
itbegary
Forum Commoner
Posts: 34
Joined: Sun Jan 05, 2003 2:50 am

mysql and mysql_fetch_array / mysql_fetch_assoc

Post by itbegary »

I found a problem when converting my select statement. I not sure if this is a bug, feature or how it should work. For the sample here I used the user table in the default mysql database.

When I execute the code below it generates an array with what appears to be no field names. If I use a loop to generate

Code: Select all

foreach ($row as $index=>$value) 
        echo $index." = ".$value."<br>";
It display all of the field names... But the code below just returns nothing. What is weird is that when I change the SQL statement from * to user, host then it works perfectly. I know that the data is there it just doesn't work right when using *.

Any ideas? Am I doing something wrong (highest probability)?

BTW, the GetRecordset() is just a wrapper function that just checks for a connection prior to executing the SQL statement and returning the resultset.


//Code

Code: Select all

$i=1;

$sql = 'select * from user';
//a simple wrapper function
$rs = GetRecordset($context, $sql);
print "<table>\n";
while (($i <= 200) && ($row = mysql_fetch_array($rs, MYSQL_ASSOC))) &#123;
    print "\t<tr>\n";
    print "\t\t<td>User:&nbsp;</td>\n";
    print "\t\t<td>".$row&#1111;"user"]."&nbsp;</td>\n";
    print "\t\t<td>Host:&nbsp;</td>\n";
    print "\t\t<td>".$row&#1111;"host"]."&nbsp;</td>\n";
    print "\t</tr>\n";
    $i++;
&#125;
print "</table>\n";
//OUTPUT

User: Host:
User: Host:
User: Host:
User avatar
puckeye
Forum Contributor
Posts: 105
Joined: Fri Dec 06, 2002 7:26 pm
Location: Joliette, QC, CA
Contact:

Re: mysql and mysql_fetch_array / mysql_fetch_assoc

Post by puckeye »

I'm not sure what's the problem with your code... Have you tried without the MYSQL_ASSOC constant?

Also I was wondering why the $i <= 200? Do you want to display the first 200 rows max? If so you should make a change to your query by adding LIMIT 200 at the end this will force MySQL to return only the 200 first rows (if there are more then 200 of course).

Using LIMIT XXX you will be able to get rid of the $i <= 200 and $i++ in your while loop.
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

Further to what puckeye said, try the following:

Code: Select all

<?php

$sql = 'SELECT user, host FROM user LIMIT 200';
//a simple wrapper function 
$rs = GetRecordset($context, $sql); 
while ($row = mysql_fetch_assoc($rs)) { 
	echo '<pre>';
	print_r($row);
	echo '</pre>';
} 

?>
The print_r() function should show you exactly what is in each row of the result set by printing out the $row array.

Mac
Post Reply