PHP MYSQL SELECT AND LEFT JOIN - not returning all results

Question

I have two tables customer and customer_transaction. Both have common customer_id, but customer_transaction also has a field description that I want to return with my result, based on the common customer_id. The result works correctly, but omits the customer_id for all records that don't have both customer_id and customer_transaction. description as per image. I am sure that I am missing something small here.

A portion of the relevant code. I seem to think the problem lies in the SELECT statement.

$sql = "SELECT * FROM customer LEFT OUTER JOIN customer_transaction ON customer. customer_id =customer_transaction. customer_id WHERE customer.customer_group_id = $input";

$sth = $pdo->prepare($sql);
$sth->bindParam(':start',$start,PDO::PARAM_INT);
$sth->bindParam(':length',$length,PDO::PARAM_INT);
$sth->bindParam(':query',$query,PDO::PARAM_STR);
$sth->execute();

foreach ($sth->fetchAll(PDO::FETCH_ASSOC) as $row) {
    $phpvar = "$row[description] "; //creat variable for descriptio so as to limit the length using substr
    echo '<tr>';
    echo '<td>'. $row['customer_id'] . '</td>';
    echo '<td>'. $row['company_name'] . '</td>';
    echo '<td>'. $row['firstname'] ." ". $row['lastname'] .'</td>';
    echo '<td>'. $row['email'] . '</td>';
    echo '<td>'. $row['telephone'] . '</td>';
    echo '<td>'. $row['customer_id'] . '</td>';
    echo '<td>'. $row['customer_group_id'] . '</td>';
    echo '<td>'. substr($phpvar,0) . '</td>'; //Limit the length of the transactions here
    echo '<td width=250>';

Whatever you did to fix the problem, the accepted answer wasn't it. — Strawberry, Aug 22 '17 at 10:59

score 1 · Accepted Answer · answered Aug 22 '17 at 11:00

The problem is that you have customer_id twice. The array however can have only one key with the name customer_id. The values are simply copied to the array field by field, using the field name as key. The value of the second occurrence, the one from the customer_transaction table, is overwriting the one from the customer table, and because not every customer has a transaction, you'll get empty fields there.

The best solution is to be more exact in the fields you need. It is good practise anyway to only fetch the fields you need instead of using *.

As a result, your query could look like this. A little more verbose, but with the flexibility of using aliases, calculated values and without the overhead of returning fields that you don't use.

SELECT
    c.customer_id,
    c.company_name,
    c.firstname,
    c.email,
    c.telephone,

    /* You could provide the field with an alias */
    t.customer_id as transaction_customer_id,

    /* Or use its value to return a more sensible value */
    CASE WHEN t.customer_id IS NULL 
      THEN 'N' 
      ELSE 'Y' END AS has_transaction,
    t.customer_group_id
FROM customer 
LEFT OUTER JOIN customer_transaction ON customer. customer_id = customer_transaction.customer_id 
WHERE customer.customer_group_id = $input

Good and thank you but I can't remove mine due to the acceptance — S4NDM4N, Aug 22 '17 at 11:04
[I know that feeling](https://stackoverflow.com/questions/6113262/how-to-determine-if-pdo-is-enabled-in-php/6113296#6113296). ;-) — GolezTrol, Aug 22 '17 at 11:22
Oh you made the same mistake well then it's time for me to the same :P. — S4NDM4N, Aug 22 '17 at 11:23

PHP MYSQL SELECT AND LEFT JOIN - not returning all results

1 Answers1