PHP using PDO with two data tables-CodePudding

I want the status column to show the value "free" when there is no corresponding record in the device_transactions table, or there is a corresponding record in the device_transactions table with the returned_date blank field

My desired return result is like this

That's all I can do right now

How can I print row #4 while the device_transactions table has no corresponding records in the devices table?

The 2 data tables

Table devices:

id	name
1	Laptop01
2	Laptop02
3	Laptop03
4	Laptop04

Table device_transactions:

id	device_id	start_transaction_plan	end_transaction_plan	returned_date
1	1	2021-12-10 14:20:43	2021-12-12 07:00:00	2021-12-12 9:30:23
2	2	2021-12-11 10:10:20	2021-12-15 15:30:00	2021-12-16 7:30:45
3	3	2021-12-12 19:03:00	2021-12-21 08:00:00	NULL

<table id="myTable">
        <thead>
            <tr>
                <th>No</th>
                <th>Name</th>
                <th>Status</th>
            </tr>
        </thead>
        <tbody> 
            <?php
                    $sql = $conn->prepare("SELECT devices.id, devices.name, device_transactions.returned_date 
                         FROM devices, device_transactions WHERE devices.id = device_transactions.device_id ");
                    $sql->execute();
                    $result = $sql->setFetchMode(PDO::FETCH_ASSOC);
                    foreach ($sql->fetchAll() as $row) { ?>
                    <tr>
                            <td><?php echo $row['id']; ?></td>
                            <td><?php echo $row['name']; ?></td>
                            <td><?php                             
                                if($row['returned_date'] !== null ){
                                    echo "free";
                                }
                                else{
                                    echo "borrowed";
                                }
                            ?></td>
                    </tr>
                    <?php } 
            ?>
        </tbody>    
                
    </table>

CodePudding user response：

You want to use LEFT JOIN, instead of FROM devices, device_transactions (which is an INNER JOIN).

SELECT devices.id, devices.name, device_transactions.returned_date 
FROM devices
LEFT JOIN device_transactions ON devices.id = device_transactions.device_id

CodePudding user response：

Just use a simple LEFT JOIN and you'll get all the record:

OK I've made a little edit so my answer is not the same as already givven and this will help you to avoid checkinh if return date is null or not

SELECT d.id as deviceid, d.name as devicename, c.returned_date as returndate
FROM device d
LEFT JOIN device_transactions c ON c.device_id = d.id;

Edit of the query - so now if there is no record or return date is null you'll get 'free' as returntype else you get 'borrowed'(you can put whatever you want of course...) So now no need in php to check if $row['return_date'] == null just output the result from the query:

SELECT d.id as deviceid, d.name as devicename,
CASE WHEN c.returned_date IS NULL THEN 'free' ELSE 'borrowed' END as returntype
FROM device d
LEFT JOIN device_transactions c ON c.device_id = d.id;

See it working here

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=32db15fbb2b00d780196ea879e6f7d20

Edited result:

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=4edc860aa39461f2c5e6655bbca77d66