How Do I SELECT These Two Tables Using A Single PDO Query

[mexabet]

I have two MySQL tables, which I want to SELECT using a single PDO query and positional placeholders.

I've been going through similar questions here to find a solution, but none seems to match the issues I'm having.

The following code is the section of my script:

<?php
// query users table to retrieve its contents   
if (isset($_SESSION["user_id"]["0"]))
{               
    // select a particular user by user_id
    $user_id = isset($_POST["user_id"]) ? $_POST["user_id"] : '';

    $stmt = $pdo->prepare("SELECT * FROM users WHERE user_id=?",$_SESSION["user_id"]["0"]);
    $stmt->execute([$user_id]); 
    $user = $stmt->fetch(); # get user data

}

    // query courses table to retrieve its contents            
        $cid = $_POST["cid"] ?? NULL;
        if (is_null($cid))
    {
           $stmt = $pdo->query("SELECT * FROM courses");
        }
        else
    {
           $stmt = $pdo->prepare("SELECT * FROM courses WHERE cid = ?");
           $stmt->execute([$cid]);
    }

        $results = $stmt->fetchAll(PDO::FETCH_ASSOC);

        echo '<option value="">'. "Select a course to proceed" .'</option>';

        foreach ($results as $row) {
        echo '<option value=" '. $row["cid"] .' ">'. $row["c_name"] .'</option>';                
    }

Apart from echoing $row["cid"] (course ID) and $row["c_name"] (course name) from the courses table, I also want to echo the following from the same courses table: $row["code"], $row["duration"], $row["start"]

In the users table, I have the logged in user's "user_id", "firstname", "lastname", "username", "email", which I also want to echo in the above foreach loop. That means the user must be logged in.

Thank you in advance for your time and help.

[Dani]

I have no experience with PDO but I'm pretty good at basic MySQL. Here is what I am understanding so far:

users table:
user_id
firstname
lastname
username
email

courses table:
cid
c_name
code
duration
start

However, I'm a bit confused what you're asking. When you have SELECT * you are retrieving all possible columns from that table. Therefore, each $row in $results already has access, not just to $row['cid'] and $row['c_name'], but to $row['duration'] etc. as well. The loop seems to be various options in a dropdown list. Can you explain where / how you want the user information to be printed within the loop?

[Dani]

Also, your question asks how you can select from these two tables using a single query. When selecting from multiple tables at once, the tables need to relate to each other somehow ... in other words, there needs to be a column in one table that matches up to a column in the other table. What do you envision the query result looking like in tabular format, that includes both users and courses? Are users taking courses? Is there a table of what courses each user is taking? Otherwise, what do users and courses have in common?

[mexabet]

@Dani, thanks for your time and input. I'm creating a course registration form, whereby a registered user can select a course and register for it. I want to extract the course registrant's user_id, firstname, lastname, username,email from the users table and load them in the form fields via a foreach loop.

How do I include include items from the users table in that foreach loop? That's the challenge I'm having.

I want the logged in user's information to be printed in the same course registration form, where courses information is being printed like this $row["user_id"], $row["firstname"],$row["lastname"],$row["username"],$row["email"]`.

By doing so, when the user submits the form, both the registered course's details, as well as the particular user's details would be inserted into the third database table, course_registration.