Member Avatar for ElegantElephant

I am having trouble with a certain query. The problem is that I am using an INNER JOIN with two tables that both have a column called "id". I want the query to return ALL the column names but in addition I also want to be returned as id1 and table2.id to be returned as id2 like this:

$query = "SELECT table1.id AS id2, table2.id AS id2 WHERE column = 'blah'";

However, how do I combine that query with a "SELECT *" query to return all the other columns as they are but with the id columns as defined above too? - without having to list out all the columns individually?

  • 3 Contributors
  • 3 Replies
  • 191 Views
  • 8 Hours Discussion Span
  • Latest Post Latest Post by jmo

Dani AI

Generated

Short answer for : you can't rely on a plain SELECT * and secretly rename two identical id columns — SELECT * expands to the original column names. Either explicitly list/alias the columns you want, or build the SELECT list for you at runtime. 's UNION suggestion stacks rows (not what a JOIN does), and is correct that the usual, reliable fix is to alias the ids and control which columns are returned.

A practical, low-maintenance approach is to have PHP ask MySQL for each table's column names (via SHOW COLUMNS or INFORMATION_SCHEMA.COLUMNS) and then assemble a SELECT list that:

  • maps table1.id to id1 and table2.id to id2, and
  • includes every other column automatically (prefixed with the table name or aliased to avoid collisions).

This keeps the code future-proof (no manual retyping when schema changes) and still returns all columns.

Example (PDO-based outline):

$pdo = new PDO(...);

function getCols($pdo, $table){
  $stmt = $pdo->query("SHOW COLUMNS FROM `$table`");
  return $stmt->fetchAll(PDO::FETCH_COLUMN, 0);
}

$cols1 = getCols($pdo, 'table1');
$cols2 = getCols($pdo, 'table2');

$select = [];
foreach ($cols1 as $c) $select[] = ($c === 'id') ? "table1.id AS id1" : "table1.`$c`";
foreach ($cols2 as $c) $select[] = ($c === 'id') ? "table2.id AS id2" : "table2.`$c`";

$sql = 'SELECT ' . implode(', ', $select) . ' FROM table1 JOIN table2 ON table1.id = table2.field1 WHERE table1.fieldA = :val';
$stmt = $pdo->prepare($sql);
$stmt->execute([':val' => 'blah']);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

Notes and cautions: if other column names duplicate across tables you should alias or prefix them to avoid collisions. Always validate/whitelist table names (to prevent injection) and use prepared statements for values. If you truly want only one id column, use USING(id) or select only the one you need instead of returning both.

Recommended Answers

All 3 Replies

Member Avatar for jmo

It sounds like you might need to try a union. See if this gets what you need:

$query = "SELECT table1.id AS id2 WHERE column = 'blah'
UNION
SELECT table2.id AS id2 WHERE column = 'blah'";

You can check out the mysql documentation for UNION for more info.

Member Avatar for Member #120589

Which fields are joined - not your 'id' fields obviously as there would not be any point in retrieving identical data.

EXAMPLE:

table1
id (primary key)
fieldA
fieldB


table2
id (primary key)
field1 (this is the foreign key of 'id' in table1)
field2

To return all fields (but only id in table1 - not field1 in table2):

SELECT t1.id AS id1,t1.fieldA,t1.fieldB,t2.id AS id2,t2.field2 FROM table1 AS t1 INNER JOIN table2 AS t2 ON t1.id = t2.field1 WHERE t1.fieldA = 'blah'

I've used a filter on fieldA in table1. Haven't checked it, but think it's OK.

Your particular problem was that you tried to name both 'id' fields with the same alias (id2).

BTW: UNION will only work for tables with identical datatypes for chosen 'common' fields. This will 'stack up' or combine the results as opposed to join them in a relational way.

Edited by Member #120589 because: n/a
Member Avatar for jmo

BTW: UNION will only work for tables with identical datatypes for chosen 'common' fields. This will 'stack up' or combine the results as opposed to join them in a relational way.

Good call!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.