PHP PDO select with INNER JOIN

Question

I am trying to do a PHP PDO call to my database with an INNER JOIN.

The three joins I'm trying to make is as follows:

KC_Orders -- Database Table Name
 --orderNumber -- Relation Field

KC_Payments -- Database Table Name
 --orderNumber -- Relation Field 

KC_OrderStatus -- Database Table Name
 --OrderNumber -- Relation Field 

I'm wanting to get more info from the tables than just the order number though.

My call to the database is as follows:

$sql = "SELECT * from `KC_Orders` INNER JOIN `KC_Payments` INNER JOIN `KC_OrderStatus`";
$ThreeRecentOrders = $db->query($sql);

And then in my foreach statement I have the following to display the results.

<?php foreach($ThreeRecentOrders as $order): ?>
   <?php $orderNumber = $order['orderNumber']; ?>
       <tr>
         <th><?php echo $orderNumber; ?></th>
         <th><?php echo $order['billingFName']; ?> <?php echo $order['billingLName']; ?></th>
         <th>$<?php echo number_format($order['order_total']/100,2); ?></th>
         <th><?php echo $order['statusID']; ?></th>
       </tr>
<?php endforeach; ?>

Now, everything works BUT I only have two orders in the system, meaning two rows in each database as of now. 3 database joins with 2 rows in each field 3x2=6 I'm getting 6 results in my table in the foreach. It's taking every row and echoing out a result. This clearly was not what I intended. What do I need to add to the database call or sql to resolve this?

Answer 1

you need to actually specify which columns to join on

i.e.

select * from kc_orders 
    inner join kc_payments 
        on kc_orders.orderNumber = kc_payments.order_number 
    inner join kc_orderStatus 
        on kc_orders.orderNumber = kc_orderStatus.orderNumber