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
– Strawberry, Commented Aug 22, 2017 at 10:59

GolezTrol · Accepted Answer · 2017-08-22 11:00:41Z

1

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

answered Aug 22, 2017 at 11:00

GolezTrol

116k19 gold badges186 silver badges215 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

S4NDM4N Over a year ago

Good and thank you but I can't remove mine due to the acceptance

GolezTrol Over a year ago

I know that feeling. ;-)

S4NDM4N Over a year ago

Oh you made the same mistake well then it's time for me to the same :P.

Collectives™ on Stack Overflow

PHP MYSQL SELECT AND LEFT JOIN - not returning all results

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related