MYSQL select problem

[Third_Degree]

Hi guys,

 

I've run into a seemingly strange problem.  I am coding a very simple search function looking in just one mysql table, but whenever I use the query "SELECT * FROM `table` WHERE `field`='value'"  I only get the last record in the table the WHERE statement applies, as opposed to all of them.  Does anyone know what could be causing this?

 

Simplified Code:

$query = mysql_query( "SELECT * FROM users WHERE `Country`='Australia'" );
print mysql_num_rows($query) . '<br />';
while ( $person = mysql_fetch_object( $query ) ) {
	print $person->id;
}

 

I consistently only get one record, although there are quite a few more than 1 in the db.

 

[Kryptix]

I'm not sure but try using mysql_fetch_assoc() instead of mysql_fetch_object() as well as $person['id'] rather than $person->id.

 

I can't think of any other reason in all honesty. :-\

 

EDIT: I've just seen you haven't declared the connection when querying. I believe you need to use:

 

$query = mysql_query("SELECT * FROM users WHERE `Country`='Australia'", $connection);

[Third_Degree]

No difference

[Third_Degree]

Yeah, I'm not sure, I feel like some MySQL setting on the server is messed up.

[Kryptix]

No difference

 

I edited the above post again. Sorry. Also wrap `tags` around users please.

[Third_Degree]

I'm not sure but try using mysql_fetch_assoc() instead of mysql_fetch_object() as well as $person['id'] rather than $person->id.

 

I can't think of any other reason in all honesty. :-\

 

EDIT: I've just seen you haven't declared the connection when querying. I believe you need to use:

 

$query = mysql_query("SELECT * FROM users WHERE `Country`='Australia'", $connection);

 

I know how to use both mysql_fetch_assoc and mysql_fetch_object, and regardless they should both work.  The query is pulling from the right database (there's only one anyway) so the connection isn't the issue.  Ugh.  Thanks for your help though.

[Kryptix]

Have you used PhpMyAdmin? Do they display OK on there? The code looks absolutely fine.

[Third_Degree]

Doing this with a client who won't let me use phpmyadmin, but when i simply do SELECT * FROM `users` everything shows up

[Kryptix]

Doing this with a client who won't let me use phpmyadmin, but when i simply do SELECT * FROM `users` everything shows up

Out of curiosity, have you tried LIMIT 0, 100 to see if it's defaulting at 1 or whatever?

[Third_Degree]

Yeah, tried all kinds of LIMIT statements, just one record still.

[Third_Degree]

bump

[Third_Degree]

figured it out.  Damn whitespace.

[Kryptix]

figured it out.  Damn whitespace.

Do you mean the whitespace in mysql_query()? If so, I removed that above as it was annoying me. I should have mentioned it but like you, I honestly didn't think it would make any difference. It was kind of at the back of my mind but I didn't want to mention it in case it was a stupid suggestion. Glad you got it sorted anyway.