select distinct problem

PHP/MySQL

[Receptional Andy]

I'm having some problems with a mysql query and try as I might I can't work out what the problem is.

My code is like this:

$query = "SELECT DISTINCT name FROM `atable` WHERE name LIKE '%john%'";
$result = mysql_query($query) or die (mysql_error());
$row = mysql_fetch_array($result);
while ($row = mysql_fetch_array($result)) {
echo $row["name"];
}

This should all be fine I think, however for certain records no data is returned - i.e. selecting records for '%john%' works but doesn't return anything at all for '%jim%'. Also if I remove DISTINCT then I do get a list of records...

If I run the query code directly with sql queries it always returns results so I guess the problem must be with my PHP somewhere.

Can anyone offer any suggestions as I've just about run out of ideas with this one.

Andy

[coopster]

You are hitting your result set twice without resetting the internal row pointer. Why not just get rid of the extra hit?

$query = "SELECT DISTINCT name FROM `atable` WHERE name LIKE '%john%'"; 
$result = mysql_query($query) or die (mysql_error()); 
//$row = mysql_fetch_array($result); 
while ($row = mysql_fetch_array($result)) { 
echo $row["name"]; 
}

[Receptional Andy]

Thanks coopster - helps me clean up my code at least!

Unfortunately I still have the problem of no results returned for certain queries.

If I add echo $row["name"]; before the while section then I do get a result, but I can't work out why I then get nothing after "while ($row = mysql_fetch_array($result))"

[coopster]

How about checking to see if there was indeed any rows returned in the result set first? Have a look at mysql_num_rows() [php.net]. Then, enter your loop if the number of rows is greater than zero, othewise return a message that no names like 'john' were found.

[Receptional Andy]

Thanks again coopster - I think your original suggestion must've fixed it as it's all working now. Perhaps I was seeing a cached version or something when I checked (and then posted) earlier.

In any case, many thanks!