PHP: SQL returns empty cells for non-empty fields

Question

I am calling a simple JOIN SQL on a mySQL database. The exact query called from the PHP script is:

SELECT * 
FROM weighteventtest 
LEFT JOIN commenttest ON weighteventtest.eventid=commenttest.eventid 
ORDER BY weighteventtest.eventid DESC

When I run the sql query directly in phpMyAdmin, I get a normal result set with all values. But when the script is called through PHP, the 'eventid' field is empty or null for nearly all rows. When I echo the values of each row, all fields have a value except the primary key 'eventid', which is empty, EXCEPT FOR the rows where there is a match on the joined table.

Any ideas? thanks! Will.

Larry Lustig · Accepted Answer · 2011-01-10 21:12:56Z

3

The culprit is combining SELECT * with PHP. Because there are two eventid fields in the output (one from each table) when you access the column by name (eventid) it's erroneously finding the second one (the NULL one from the missing right-side record).

To fix the problem, explicitly specify which columns you want after SELECT. Use of SELECT * in production code is generally considered a bad practice in any event.

edited Jan 10, 2011 at 21:12

answered Jan 10, 2011 at 21:07

Larry Lustig

51.2k16 gold badges119 silver badges173 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Sam Dufel Over a year ago

If you join with USING (eventid), it may clear up the problem. It's a cleaner syntax when you have 2 tables with the same key.

Larry Lustig Over a year ago

The syntax you use will probably not have any effect on the list of columns in the result set (even assuming the user's DB supports USING — he did not specify what product he is using).

Will Gill Over a year ago

that sounds like it might fix my problem... will modify fields now and let you know asap...

Will Gill Over a year ago

Yep, that solved it. Thank you so much, @Larry Lustig! Once again, Stack Overflow saves the day. You people are marvelous!

Collectives™ on Stack Overflow

PHP: SQL returns empty cells for non-empty fields

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related