alaising a foreign key in mysql

Question

Lets say I have two tables contacts and departments.

The contents of the table are as follows:

contacts:

id |  first_name | last_name | email            | department_id
10 |  Mani       | Raj       | raj@mail.com     | 2
11 |  Santhos    | Sam       | santhos@mail.com | 3

departments:

id | department_name
1  | HR
2  | Sales
3  | Finance

I'm trying to print the details of the tables using INNER JOIN

My query is:

$sql_query = "SELECT * FROM contacts INNER JOIN departments, departments.id as departments.department_id ON contacts.department_id=departments.department_id ORDE`R BY contacts.id";

I've referred the question below: SQL exclude a column using SELECT * [except columnA] FROM tableA?

but I don't want to create a temporary table.

I was thinking may be there was a way for alaising the id column from the department table.

Add

I could print the elements from the tables using the query:

$sql_query = "SELECT * FROM $table_name INNER JOIN departments ON $table_name.department_id=departments.department_id ORDER BY $table_name.id";

It works great but the problem is when I try to print the details on my web page, somehow the id from the contacts table is overwitten by the id from the departments table.

code that I use to print the table is:

if ($query_result->num_rows > 0) {
            ?>
            <table>
                <tr>
                    <th>ID</th>
                    <th> First Name </th>
                    <th> Last Name </th>
                    <th>E-mail</th>
                    <th>Department</th>
                </tr>
                <?php
                while($row = $query_result->fetch_assoc()) {
                    ?>
                    <tr>
                        <td> <?php echo $row["id"]; ?> </td>
                        <td> <?php echo $row["first_name"]; ?> </td>
                        <td> <?php echo $row["last_name"]; ?> </td>
                        <td> <?php echo $row["email"]; ?> </td>
                        <td> <?php echo $row["department_name"]; ?> </td>
                    </tr>
                    <?php
                }
                ?>
            </table>
            <?php           
        }

The output of this code is :

ID | First Name | Last Name     | E-mail            | Department
2  | Mani       | Raj           | mani@mail.com     | Sales
8  | Santhos    | Sam           | santhos@mail.com  | Finance

whereas my output should be:

ID | First Name | Last Name     | E-mail            | Department
10 | Mani       | Raj           | mani@mail.com     | Sales
11 | Santhos    | Sam           | santhos@mail.com  | Finance

Now how would I go about printing the values without being overwritten?

I'm a newbie. Please help me...

Edit

OK,cool this works

$sql_query = "SELECT *, contacts.id as id FROM contacts INNER JOIN departments 
ON contacts.department_id=departments.department_id ORDER BY contacts.id";

But I'm also trying to export the details to a .xls file. I'm getting an error there!!

This is my error

You have an error in your SQL syntax     check the manual that corresponds to your MariaDB server version for the right syntax to use near 'as id FROM  INNER JOIN departments ON .department_id=departments.id ORDER BY .id' at line 1<br />
<b>Warning</b>:  Invalid argument supplied for foreach() in <b>/opt/lampp/htdocs/contacts/export.php</b> on line <b>23</b><br />    

and my code is:

$flag = false;
foreach($query_result as $row) {
    if(!$flag) {
        // display field/column names as first row
        echo implode("\t", array_keys($row)) . "\r\n";
        $flag = true;
    }
    array_walk($row, __NAMESPACE__ . '\cleanData');
    echo implode("\t", array_values($row)) . "\r\n";
}

function cleanData(&$str) {
        $str = preg_replace("/\t/", "\\t", $str);
        $str = preg_replace("/\r?\n/", "\\n", $str);
        if(strstr($str, '"')) $str = '"' . str_replace('"', '""', $str) . '"';
    }

Answer 1

You need to tell SQL from which table you want the data:

$sql_query = "SELECT t1.id, t1.first_name, t1.last_name, t1.email, t2.department_name 
FROM contacts t1
INNER JOIN departments t2 ON t12.department_id=t1.id 
ORDER BY t1.id";

When you have same field in both tables (like ID in your case), SQL is confused which one to pick.

It's not good practice to use * as it might break your application once your table structure changes, however, if you must use it, following will work:

$sql_query = "SELECT t1.*, t2.department_name 
FROM contacts t1
INNER JOIN departments t2 ON t2.department_id=t1.id 
ORDER BY t1.id";

And here is the example with "dynamic" tables

$sql_query = "SELECT t1.*, t2.department_name 
FROM $table_name t1
INNER JOIN departments t2 ON t2.department_id=t1.id 
ORDER BY t1.id";

To make this fully dynamic, you might do something like following.

I'll use your two tables as a example (not tested but it should put you on the right track):

$table1_name = "contacts;
$table1_key = "id";
$table1_fields = "*";

$table2_name = "departments";
$table2_key = "id";
$table2_fields ="department_name";


$sql_query = "SELECT t1.$table1_fields, t2.$table2_fields
  FROM $table1_name t1
  INNER JOIN $table2_name t2 
    ON t2.$table2_key = t1.$table1_key
  ORDER BY t1.$table1_key";

Answer 2

Please try this query

$sql_query = "SELECT *, contacts.id as id FROM contacts INNER JOIN departments 
ON contacts.department_id=departments.department_id ORDER BY contacts.id";

Answer 3

There are two solutions to this and a third possible one. 1)

SELECT *, `department`.`id` as `depid`, `contacts`.`id` as `contactid`

Then, in your code simply ignore the spurious 'id' column and refer to whichever specific one you want

2) If you are using PHP (it looks like it) and using PDO to access the data, use

$stmt->fetchall(PDO::FETCH_NAMED)

and you will get both ids in an array instead of one overwriting the other.

You MIGHT get the result you want by flipping around the table references i.e. instead of

SELECT * FROM Contacts INNER JOIN Departments

use

SELECT * FROM Departments INNER JOIN Contacts