How to get ALL from 2 tables

[optikalefx]

I need to get * from 2 different tables left joined by a common ID.

The problem is that when i do that, since 2 (in this case 3) tables have the same field, when i use mysql_fetch_array the result is wrong

Im trying to get UserID from the database. (note, im trying to get UserID equal to 94 which is the userid)

 

This is the result of mysql_fetch_array()

array(75) {
  [0]=>
  string(2) "94"
  ["UserID"]=>
  NULL
  [1]=>

 

And here is the result if i do mysql_fetch_assoc

array(35) {
  ["UserID"]=>
  NULL
  ["Username"]=>
  string(9) "Billabong"

 

So as you can see, for some reason UserID is NULL but the index 0 holds my ID. Why is this happening?

I tried aliasing tables etc. The only solution i found so far is to name all the columns im expecting from the 2nd table, which is not the correct solution. There must be a way to tell it to ignore the field i joined on on the joined table. Or select * ignore UserID or something like that

 

here is the query. NOTE - it runs fine in the console, perfect. Its just these PHP functions. bug maybe?

SELECT
users.*,
subscriptions.*,
GROUP_CONCAT(userpermissions.TutorialID) as tutorials
FROM 
users
JOIN subscriptions ON (users.UserID = subscriptions.UserID)
JOIN userpermissions ON (subscriptions.UserID = userpermissions.UserID)
WHERE users.UserID = '24995'

[fenway]

I don't follow -- what's the problem?

[DavidAM]

I need to get * from 2 different tables left joined by a common ID.

 

You say you are using a LEFT JOIN, but your example is using JOIN. There is a difference. If you are using a LEFT JOIN, then there may be rows coming from the outer table that have a NULL UserID. Since you are not ALIASing the selected columns, I guess the last column named UserID in the SELECT list (probabaly coming from the outer table) is overwriting the column named UserID that comes earlier in the select list. I would have expected mySql to automatically qualify the column names with the table names, but I don't see that happening in my tests and it does not appear to be the case from your (partial) var_dumps().  I don't know if there is any way to exclude repeated columns. I really doubt that there is. You have explicitly asked for all columns and that is what you get.

 

A couple of rules I follow when selecting data from the database

[*]Never use * unless you absolutely need every column defined for the table;

[*]Never use * even if you think you need every column defined for the table;

[*]Never select multiple columns with the same name and data (i.e. UserID from more than one table);

[*]Always alias any columns with duplicate names

Why?

• If you use SELECT *, you don't really know what column names (array keys) are in the returned row. If you have some code that depends on only certain keys being present, and you later modify the table (add or remove columns) you will break your code.
  
• Using SELECT * causes all data from the selected rows to be transferred to your application. If you are not using every column, you are wasting resources. Especially when JOINing multiple tables, the amount of data retrieved and returned can be significant.
  
• Furthermore, if you select only the data you need, and all of the data you need is "covered" by an index, the server may not have to read the data rows which can save time during the query.
  
• Oh yeah, and you don't run into problems with outer join tables overwriting data from the inner tables.
  

 

One other thing I'll note. What is the effect of using GROUP_CONCAT() without a GROUP BY phrase? I have never tried this so I don't know if it would cause problems or not.

[EchoFool]

If there are matching field names i just rename them:

 

SELECT *,table1.UserID AS Table1UserID,table2.UserID AS Table2UserID
FROM table1
LEFT JOIN table2
ON table1.id=table2.id

 

All the uniquely named fields will be fine in the * symbol.

 

This is just an example