skip to Main Content

I have two tables which I’m trying to run a query on.

If I run the below query in phpmyadmin I get the results I expect, just one result returned with id 9, but when running it on my webpage, its returning all 4 results. I can put what ever I want as the user id, even one that doesn’t exist, its ignoring it.

    $sql = "
    SELECT c.*
         , cu.userID 
      FROM ConversationUsers AS cu 
      LEFT 
      JOIN Conversations AS c 
        ON c.id = cu.convoID 
     WHERE cu.userID = userID
     ";
    $q = $pdo->prepare($sql);
    $q->bindValue(':userID', $vars['userID']);
    $q->execute();
    if($q->errorCode() != 0) {
        $errors = $q->errorInfo();
        echo($errors[2]);
    }
    foreach ($q->fetchAll() as $row) {
        $convos[] = $row;
    }

    echo "<pre>";
    print_r($convos);
    echo "</pre>";

    echo "
    SELECT c.*
         , cu.userID 
      FROM ConversationUsers AS cu 
      LEFT 
      JOIN Conversations AS c 
        ON c.id = cu.convoID 
     WHERE cu.userID = " . $vars["userID"];

The echo produces:

SELECT c.*
     , cu.userID 
  FROM ConversationUsers AS cu 
  LEFT 
  JOIN Conversations AS c 
    ON c.id = cu.convoID 
 WHERE cu.userID = 1

The print produces

Array
(
    [0] => Array
        (
            [id] => 9
            [0] => 9
            [lastUpdated] => 2019-12-29 00:00:00
            [1] => 2019-12-29 00:00:00
            [userID] => 1
            [2] => 1
        )

    [1] => Array
        (
            [id] => 9
            [0] => 9
            [lastUpdated] => 2019-12-29 00:00:00
            [1] => 2019-12-29 00:00:00
            [userID] => 2
            [2] => 2
        )

    [2] => Array
        (
            [id] => 10
            [0] => 10
            [lastUpdated] => 2019-12-29 00:00:00
            [1] => 2019-12-29 00:00:00
            [userID] => 2
            [2] => 2
        )

    [3] => Array
        (
            [id] => 10
            [0] => 10
            [lastUpdated] => 2019-12-29 00:00:00
            [1] => 2019-12-29 00:00:00
            [userID] => 3
            [2] => 3
        )

)

The tables are setup as follows

CREATE TABLE `Conversations` (
  `id` int(11) NOT NULL,
  `lastUpdated` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `Conversations` (`id`, `lastUpdated`) VALUES
(9, '2019-12-29 00:00:00'),
(10, '2019-12-29 00:00:00');

CREATE TABLE `ConversationUsers` (
  `convoID` int(11) NOT NULL,
  `userID` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `ConversationUsers` (`convoID`, `userID`) VALUES
(9, 1),
(9, 2),
(10, 2),
(10, 3);

2

Answers


  1. You miss the : before the parameter in your query:

     $sql = "SELECT c.*, cu.userID FROM ConversationUsers AS cu LEFT JOIN Conversations AS c ON c.id = cu.convoID 
    WHERE cu.userID = :userID";
    
    Login or Signup to reply.
  2. You’re missing the : in :userID. That makes the WHERE always be true:

    $sql = "SELECT c.*, cu.userID FROM ConversationUsers AS cu LEFT JOIN Conversations AS c ON c.id = cu.convoID WHERE cu.userID = :userID";
    

    Your echo line used $vars["userID"] which is why it showed the correct result.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search