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.

Recommended Answers

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 …

Jump to Post

All 3 Replies

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?

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?

commented: I have created a courses table - primary key is "cid" and added some courses. Users are taking courses based on which course a user registered for. +4

@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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.21 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.