John Davidson

Message:

I want to select the firstName and lastName from a different table, but im having some issues,
I'll paste the code down below.

The table orders does not contain the firstName and lastName, so I want to call firstName and last name from from table users, but I don't know how. I want help with the query!

<?php $sql = "SELECT * from orders";



    //In this line above i also want to select firstName and lastName from table users



    $result = mysqli_query($conn, $sql);

    $counter = 0;

    while($row = mysqli_fetch_assoc($result)){

        $Id = $row['userId'];

        $orderId = $row['orderId'];

        $firstName = $row['firstName'];

        $lastName = $row['lastName'];

        $address = $row['address'];

        $zipCode = $row['zipCode'];

        $phoneNo = $row['phoneNo'];

        $amount = $row['amount'];

        $orderDate = $row['orderDate'];

        $paymentMode = $row['paymentMode'];



        if ($paymentMode == 0) {

            $paymentMode = "Cash on Delivery";

        } else {

            $paymentMode = "Online";

        }

        $orderStatus = $row['orderStatus'];

        $counter++;

                    

        echo '<tr>

            <td>' . $orderId . '</td>

            <td>' . $Id . '</td>

            <td>' . $firstName . '</td>

            <td>' . $lastName . '</td>

            <td data-toggle="tooltip" title="' .$address. '">' . substr($address, 0, 20) . '...</td>

            <td>' . $phoneNo . '</td>

            <td>' . $amount . '</td>

            <td>' . $paymentMode . '</td>

            <td>' . $orderDate . '</td>

            <td><a href="#" data-toggle="modal" data-target="#orderStatus' . $orderId . '" class="view"><i class="material-icons">&#xE5C8;</i></a></td>

            <td><a href="#" data-toggle="modal" data-target="#orderItem' . $orderId . '" class="view" title="View Details"><i class="material-icons">&#xE5C8;</i></a></td>

        </tr>';

    }

    if ($counter==0) {

?>

<script>

document.getElementById("NoOrder").innerHTML = '<div class="alert alert-info alert-dismissible fade show" role="alert" style="width:100%"> You have not Recieve any Order!  </div>';</script>



<?php`

    } 

?> 

I've tried may query methods but none of them worked so I could need some help from professionals

---

My Answer:
You can achieve this by using a JOIN in your SQL query. Here is an example query that selects the firstName and lastName from the users table and the orderNumber from the orders table:

php
$sql = "SELECT users.firstName, users.lastName, orders.orderNumber
FROM users
JOIN orders ON users.userId = orders.userId";


In this query, we are selecting the firstName and lastName from the users table and the orderNumber from the orders table. We are using a JOIN to connect the two tables based on the userId column, which is assumed to be a common column between the two tables.

Make sure to replace "userId" with the actual column name that links the two tables together. This query will return the firstName and lastName from the users table and the orderNumber from the orders table in a single result set.