Selecting * from two tables using php and mySQL [duplicate]

Question

I have two tables, one is projects, and the other is users.

PROJECTS table
ID     |     USER_ID    |   NAME
-------------------------------------
80     |        1       |   ABC Co.
82     |        2       |   XYZ Inc.

USERS table
ID     |     FIRSTNAME  |   LASTNAME
-------------------------------------
1      |     Joe        |   Namath
2      |     Jimmy      |   Fallon

What I want is to write a query such as:

SELECT * FROM PROJECTS, USERS WHERE PROJECTS.USER_ID=USERS.ID AND FIRSTNAME = "Joe"

I can successfully run the query in php, but when I attempt to access the results, I don't get what I want. I understand why, but I can't figure out a way to correct it. For example:

$row = mysqli_fetch_query($awesomeDatabaseLink);
echo $row['ID];  //returns '1' and I really wanted '80'

I get it. The two tables have fields with the same name, but it's not the same data. MySQL returns its best guess at what I so ambiguously asked for. However, I have also tried:

echo $row['PROJECTS.ID']; //returns an empty string.

I should mention that I desperately need "*" from both tables. The Projects table has dozens and dozens of fields (not my design, and re-engineering the database is out-of-scope). The Users table is also very extensive, so listing each field individually is much more impractical than it would appear by looking at my example tables.

Any suggestions? SH

Answer 1

The quickest fix is to assign a unique column alias for the expression (in this case just a simple column reference).

When you do that, you will need to qualify the * with the table name or alias. If you want to return all of the columns from both tables, you will need to included a * for each table.

Also, ditch the old-school comma operator for the join operation, and use the JOIN keyword instead. And qualify all column references. For example:

SELECT PROJECTS.*
     , USERS.*
     , PROJECTS.ID AS MY_PROJECTS_ID
  FROM PROJECTS
  JOIN USERS
    ON USERS.ID=PROJECTS.USER_ID
   AND USERS.FIRSTNAME = "Joe"

The assigned alias MY_PROJECTS_ID will be the name of the column in the result set, so you reference that column by the assigned alias.

This assumes that there are no other columns being returned with the name MY_PROJECTS_ID.

Anytime there are two (or more) columns in the resultset that have the same name, you'll only get one of those columns referencing it by name.

Answer 2

I'd suggest you to use alias. That'd make things less ambiguous.

Try this:

SELECT 
PROJECTS.ID AS project_id, 
USER_ID, 
NAME, 
USERS.ID AS user_id, 
FIRSTNAME, 
LASTNAME 
FROM PROJECTS, USERS 
WHERE PROJECTS.USER_ID=USERS.ID AND FIRSTNAME = "Joe"

And then:

echo $row['project_id']; //returns Project id

Hope this helps.