Mysql Question posted in
The official MySQL documentation can be found here.

mysql, getting results from two tables. Missing one row

I got two tables in my database..

Users and guestbook

UserTable:
userID, username, password, email

GuestbookTable:
gbID, userID, toID, text, date

I’m trying to get the userInfo from users with $_GET[‘userID’]

And then im trying to get all the entries in the guestbook by toID = $_GET[‘userID’]

I use this code right now

$sql = "SELECT u1.*, g.text AS entry_text, g.date AS guestbook_date, u2.userID AS from_userID, u2.username AS from_username
FROM users AS u1
LEFT JOIN guestbook AS g ON u1.userID = g.toID
LEFT JOIN users AS u2 ON g.userID = u2.userID
WHERE u1.userID = :id

";

And it kinda works.. The only problem i got with this right now is that the first message sent in the guestbook is missing on the website but still in the database.

Tags:

2

Answers


  1. 0

    Using a combination of LEFT JOIN and RIGHT JOIN might be causing issues with how the results are fetched.

    Consider using a straightforward LEFT JOIN.

    $sql = "SELECT u1.*, g.text AS entry_text, g.date AS guestbook_date, u2.userID AS from_userID, u2.username AS from_username
FROM users AS u1
LEFT JOIN guestbook AS g ON u1.userID = g.toID
LEFT JOIN users AS u2 ON g.userID = u2.userID
WHERE u1.userID = :id";
    Login or Signup to reply.
  2. 0

    to get the user information and all the entries in the guestbook with toID = $_GET[‘userID’], you can try using a SQL query that combines the two tables correctly. Also, you may want to sort the guestbook entries by date. Here is an example of how you might do this:

        // Assume that $_GET['userID'] contains the ID of the user you want to query

$userId = $_GET['userID'];

$sql = "
    SELECT
        u1.*,
        g.text AS entry_text,
        g.date AS guestbook_date,
        u2.userID AS from_userID,
        u2.username AS from_username
    FROM
        users AS u1
    LEFT JOIN
        guestbook AS g ON u1.userID = g.toID
    LEFT JOIN
        users AS u2 ON g.userID = u2.userID
    WHERE
        u1.userID = :userId
    ORDER BY
        g.date DESC;";

// Execute the SQL query
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':userId', $userId, PDO::PARAM_INT);
$stmt->execute();

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

// Now, $results will contain the user information and guestbook entries
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search