Error in echoing mysql left outer join results

[Amaina]

I have two mysql tables namely tableA and tableB.

tableA
+-----------+--------------+---------+
| tableA_id | title| month|
+-----------+--------------+---------+
|1 | malaria| january |
|2 | pneumonia| january |
|3 | HIV| january |
|4 | tuberculosis | january |
|5 | dengue fever | january |
+-----------+--------------+---------+

tableB
+-----------+-----------+--------------------+---------------+-----------------+
| tableB_id | tableA_id | correspondence_ref | date_received | date_dispatched |
+-----------+-----------+--------------------+---------------+-----------------+
| 1 | 1 | KLF007 |2015-10-10| 2015-10-10|
| 2 | 1 | KLF011| 2015-10-15|2015-10-15|
|3 | 1 | KLF012| 2015-10-17| 2015-10-17|
|4 | 3 | KLF050 | 2015-10-17| 2015-10-17|
|5 | 5 | KLF036| 2015-10-17 |2015-10-17|
+-----------+-----------+--------------------+---------------+-----------------+

I have written a mysql query returns records in tableA that have no matching records in tableB. Here is the mysl query:

select * from tableA left outer join tableB on tableA.tableA_id = tableB.tableA_id where tableB.tableA_id is null;

The above statement returns this:

+-----------+--------------+---------+-----------+-----------+--------------------+---------------+-----------------+
| tableA_id | title | month| tableB_id | tableA_id | correspondence_ref | date_received | date_dispatched |
+-----------+--------------+---------+-----------+-----------+--------------------+---------------+-----------------+
| 2 | pneumonia | january |NULL |NULL | NULL| NULL| NULL|
|4 | tuberculosis | january |NULL |NULL | NULL| NULL| NULL|
+-----------+--------------+---------+-----------+-----------+--------------------+---------------+-----------------

My problem is that i want php to echo out tableA_id but this does not work. See the code snippet below

 <?php
    $con=dbConnect();
    $query=select * from tableA left outer join tableB on tableA.tableA_id = tableB.tableA_id where tableB.tableA_id is null;
    $sql=$con->prepare(query);
    $sql->execute();
    $sql->setFetchMode(PDO::FETCH_ASSOC);
        while ($row = $sql->fetch()){
                    $tableA_id = $row ['tableA_id'];
                    $month = $row ['month'];
        echo $tableA_id;
        }
    ?>

What can i be possibly be doing wrong? How comes iam able to echo out month and not tableA_id?

[cereal]

Hi,

it happens because both tables will return the same column name tableA_id, since you're fetching as assoc, it will create an array in which the first index will be rewritten by the second column value. You see how it works by playing this:

<?php

    $stmt = $conn->prepare("select * from tableA left outer join tableB on tableA.tableA_id = tableB.tableA_id where tableB.tableA_id is null");
    $stmt->execute();
    $result = $stmt->fetchAll();

    print_r($result);

It will return both numeric and assoc index keys, so tableA_id index key will be empty, the numeric [0] and [4], instead, will show the current values i.e. 2 and null. Here's an example:

Array
(
    [0] => Array
        (
            [tableA_id] => 
            [0] => 2
            [title] => pneumonia
            [1] => pneumonia
            [month] => jan
            [2] => jan
            [tableB_id] => 
            [3] => 
            [4] => 
            [correspondence_ref] => 
            [5] => 
            [date_received] => 
            [6] => 
            [date_dispatched] => 
            [7] => 
        )

You see how tableA_id index is appearing only once? If you define the columns you want to return and use a column alias, then you can solve this issue.

So instead of select * do:

select tableA.tableA_id as tableAID, tableA.month, tableA.month, tableB.*

Note that if you were going to define a different WHERE condition (IS NOT NULL instead of IS NULL), you would not notice this error, as also the other columns would return populated. Check the examples here:

• http://sqlfiddle.com/#!9/47a8c6/4

[Amaina]

Works like a charm. Thank you very much for the insightful comments