PHP/MySQL - SELECT various tables, with some row name

Question

Hi,

Which is the best aproach to select * from various tables but with same row name, cause normally i use $row['name'] but i have seven tables with the some name... how can i do this?

tkz Roberto

Answer 1

You'll need to explicitly specify column names instead of doing SELECT *. Here's how you'd use aliases to return results from two tables that have some conflicting names:

SELECT students.id AS student_id, students.name AS student_name, teachers.id AS teacher_id, teachers.name AS teacher_name FROM students, teachers

In general it's bad practice to use wildcards in SELECT statements. Sure, you can save yourself a few minutes when you're first writing some code, but you may be shooting yourself in the foot if/when you change the database schema in the future. You're also probably returning more data than you really need.

If you have several tables with the same field names and you'd like to automate the query, here's one possibility:

$tables = array('table1', 'table2', 'table3', 'table4');
$fields = array('field1', 'field2', 'field3', 'field4');
$select = '';
$from = '';
foreach ($tables as $table) {
  if ($from) $from .= ', ';
  $from .= $table;
  foreach ($fields as $field) {
    if ($select) $select .= ', ';
    $select .= "$table.$field as {$table}_{$field}";
  }
}
$sql = "SELECT $select FROM $from WHERE ...";

Answer 2

You can use query

SELECT t1.name AS t1_name, t2.name AS t2_name
FROM table1 t1
INNER JOIN table t2 ON ....

And then

$row['t1_name']
$row['t2_name']