Get column with same name from multiple tables using PHP

Question

Ok, so here is what I have right now:

$stuff = mysql_query("SELECT * FROM Table1, Table2") or die(mysql_error());
if ($info = mysql_fetch_array($stuff)) {
    $table1ID = $info['Table1.ID'];
    $table2ID = $info['Table2.ID'];
}

My problem is this does not work. I get nothing. And when I do this:

$stuff = mysql_query("SELECT * FROM Table1, Table2") or die(mysql_error());
if ($info = mysql_fetch_array($stuff)) {
    $table1ID = $info['ID'];
    $table2ID = $info['ID'];
}

It is the same ID (of course). So how do I get the ID of the first table and the second table when they have the same name?

Please don't use the `mysql` functions - [they're deprecated](http://www.deprecatedphp.com/tag/mysql/). See also [this comparison of PDO and MySQLi](http://net.tutsplus.com/tutorials/php/pdo-vs-mysqli-which-should-you-use/) (those being the things you should use instead). — michaelb958--GoFundMonica, Aug 13 '13 at 00:04

spencer7593 · Answer 1 · 2013-08-13T06:48:40.773

If you want to reference the columns in the resultset by name, then each column in the resultset needs to have a unique name.

So, ditch the "*" and instead give a list of the expressions you want to retrieve, and assign aliases to some of the columns so that each column has a unique name:

SELECT t1.id
     , t2.id AS t2_id
     , t1.somecol            
  FROM Table1 t1
 CROSS
  JOIN Table2 t2

The comma operator is equivalent to a [CROSS] JOIN. Every row in Table1 will be matched with every row from Table2 (a Cartesian product.) And that's a bit odd. (It's not invalid, it's just not the normal pattern. (It's an easy way to make light dimmingly huge resultsets, and I suspect that this is not the actual resultset you want.)

Another option is to reference the columns by position, rather than by name. (But as Marc B will point out "positional field notation" is a bad idea.)

score 1 · Answer 2 · answered Aug 13 '13 at 00:02

Try aliasing the column names, this will however involve aliasing all the columns you need from the table, which if it is a big table will be a pain

$stuff = mysql_query("SELECT Table1.ID As ID1, Table2.ID As ID2 FROM
Table1, Table2") or die(mysql_error());

// don't need to use mysql_fetch_array, unless you are referencing
// data by col num as well as key name 
if ($info = mysql_fetch_assoc($stuff)) {
    $table1ID = $info['ID1'];
    $table2ID = $info['ID2']; 
}

score 0 · Accepted Answer · answered Aug 12 '13 at 23:58

0

mysql_fetch_array gives you back a list using both names and numeric values, the names going to be 'ID' for both, so the first one is lost unless you use alias to rename them in your sql. but as you already have a numeric index, just use $info[0] and $info[1]

using alias instead:

$query = "SELECT Table1.ID AS id1, Table2.ID as id2 FROM Table1, Table2";

and then use $info['id1'] and $info['id2']

(Notice: mysql_*-functions is deprecated)

answered Aug 12 '13 at 23:58

Puggan Se

5,738
2
22
48

This is what I need to do. There are 2 others answers the same, but this was the first so I will accept this one. Thanks!! – ageoff Aug 13 '13 at 00:06
To quote @Marc B: "using positional field notation is a bad idea". – spencer7593 Aug 13 '13 at 04:38
@spencer7593: why? where can i find the source of that qoute? – Puggan Se Aug 13 '13 at 07:52
@Puggan Se: **"bad idea to use positional field notation"** from Marc B activity page (currently page 2) [http://stackoverflow.com/users/118068/marc-b?tab=activity&sort=all&page=2](http://stackoverflow.com/users/118068/marc-b?tab=activity&sort=all&page=2) – spencer7593 Aug 13 '13 at 15:36
aha, http://stackoverflow.com/questions/18191638/sql-get-date-of-most-votes-cast/18191665#comment26658007_18191665 , there its about using positional field notation inside the query, useful for console work, bad practise in software, but in the case above, naming fields with number, or use the numbers directly is both bad, i prefer always prefix my id columns, and name the foriegen keys the same, so i can use `LEFT JOIN x USING (y_id)` – Puggan Se Aug 13 '13 at 15:51

Get column with same name from multiple tables using PHP

3 Answers3