MySQL using PHP join a table to another-CodePudding

I need a little bit of help, because I do not know how to join a table to another. I want to output the owner's name from clients table, in the machines table instead of outputting the clients ID. In the clients table owner's name if full_name.

<table  id="clients">
        <tr>
            <th>ID</th>
            <th>VIN</th>
            <th>Model</th>
            <th>Client ID</th>
            <th>Desc</th>
            <th>Make</th>
            <th>Year</th>
            <th>Actions</th>
        </tr>
        <?php
        $sql = "SELECT * FROM machines ORDER BY year DESC";
        $res = mysqli_query($conn, $sql);

        if ($res == TRUE) {
            $count = mysqli_num_rows($res);
            $sn = 1;

            if ($count > 0) {
                while ($rows = mysqli_fetch_assoc($res)) {
                    $id = $rows['id'];
                    $vin = $rows['vin'];
                    $client_id = $rows['client_id'];
                    $model = $rows['model'];
                    $description = $rows['description'];
                    $make = $rows['make'];
                    $year = $rows['year'];
        ?>
                    <tr>
                        <td><?php echo $sn  ; ?></td>
                        <td><?php echo $vin; ?></td>
                        <td><?php echo $model; ?></td>
                        <td><?php echo $client_id; ?></td>
                        <td><?php echo $description; ?></td>
                        <td><?php echo $make; ?></td>
                        <td><?php echo $year; ?></td>
                        <td>
                            <a href="<?php echo SITEURL; ?>/update-machine.php?id=<?php echo $id; ?>" >Update Machine</a>
                            <a href="<?php echo SITEURL; ?>/delete-machine.php?id=<?php echo $id; ?>" >Delete Machine</a>
                        </td>
                    </tr>

CodePudding user response：

$sql = "SELECT m.*, c.full_name AS 'client_full_name'
FROM machines m
LEFT JOIN clients c ON c.id = m.client_id
ORDER BY m.year DESC";

then

$client_full_name = $rows['client_full_name'];

and finally

<td><?php echo $client_full_name; ?></td>

CodePudding user response：

Your query should be like this

SELECT M.*,C.full_name 
FROM machines as m 
INNER JOIN clients as c ON m.id = c.client_id 
ORDER By m.year DESC

and then use

$client_name = $row['client_name']

and then echo client name

<td><?php echo $client_name; ?></td>