PHP/MySql JOIN query problem...

ahajdar

Hello,

I don't know if anyone experienced a problem when using SELECT...JOIN queries with NULL values. The problem I'm having is this. Here is the problem...my php code uses mysql_fetch_object() API, therefore I'm accessing the contents of a $row variable such as $row->UserId...etc. However, when I use a JOIN on another table with UserId, and if in that second table UserId column is populated with a NULL, then when I try to print $row->UserId, what gets printed is nothing. It appears that MySql takes the 2nd UserId and prints that value and not the 1st from my main table. This problem is happening on my HOST, however, on my local test environment running the same code, this is not happening. So, the guess would be that the problem is with MySql configuration, but I don't know what option could I ask my HOST to set so that I don't experience this problem.

Thanks!

sarahk

check out the sql tutorials but your query should be something like

select table1.userid, table1.name, table2.details, table2.password
from table1, table2
where table1.id = table2.id

This way it's the userid from table1 which is returned.

ahajdar

sarahk,

Thanks for your suggestion but it doesn't work in my case since I want all the rows from table1 and all the rows from table2 where "id" field is matching, but the fact is that table1 is much bigger than table2, so there are many entries in table1 that don't have matching rows in table2. So, that's why I'm using JOIN.

The problem is in MySql configuration because at home, I have no problems, but when I upload data to my host, this is when things are happening. I could solve easily problems if I use arrays and not objects, but I am not sure if anyone here knows of a configuration line for MySql that will solve my problem.

Thanks again and if you have any other suggestions, please let me know.

MrAlaska

If the UserID values are null in the second table, I assume UserID is not your primary key to join them on. Are all the null values in the second table? It seems a LEFT JOIN might do the trick, instead of JOIN. I haven't used JOIN that much, I'm just thinking out load (such as it is)

And while I am busy thinking out loud anyway, it seems the select portion of the statement Sarahk made might sitll be applicable. Don't you still explicitly address the table to avoid an ambiguous reference even on a join?

sluderitz

try to give different names to the user ids using "AS", like this:

select table1.userid AS userid1, table2.userid AS userid2, table1.name, table2.details, table2.password
from table1, table2
where table1.id = table2.id

then in your result set you would get 2 columns for your user ids, one called userid1 and the other called userid2

Good luck.

sluderitz

also if you want to keep all rows of the first table no matter if there are matching rows in the second table then you have to use LEFT JOIN preferable with an ON condition:

SELECT ... table1 LEFT JOIN table2 ON table1.id = table2.id