0

Banging my head against a wall here ... Been stuck on this for a while now ...

I have this query:

    include 'dbconnect.php';
    $user1id=$_SESSION['userid'];
    $stmt=$db->prepare("SET @chatroom_ID=(SELECT ID FROM chatroom
                        JOIN chatroommembers ON chatroom_ID=chatroom.ID
                        WHERE chatroommembers.user_ID=:user1id
                        ORDER BY chatroom.ID
                        LIMIT 1);
                        SELECT user_ID FROM chatroommembers 
                        WHERE chatroom_ID=@chatroom_ID 
                        AND user_ID!=:user1id
                        LIMIT 1;");
    $stmt->bindValue(':user1id',$user1id,PDO::PARAM_INT);
    $stmt->execute();
    $user2id=$stmt->fetchColumn();
    echo $user2id;

What it is supposed to do, is find the chatroom ID that the current user is in, and echo out the user id of the second person that is in the room (There is always only 2 in the room)

The query works in PHPmyadmin ... But not in my PHP script

I can echo $user1id no problems

If I replace the query with a simple SELECT statement it works, for example:

SELECT user_ID FROM chatroommembers WHERE user_ID=:user1id

I've tried pretty much everything I can think of, and this statement just doesnt want to work ...

No errors are being outputted (I've even made it wrong to make sure my errors will output, which they did).

tshepang
  • 12,111
  • 21
  • 91
  • 136
Just Lucky Really
  • 1,341
  • 1
  • 15
  • 38

1 Answers1

1

As Dagon has mentioned in the comments, you've got two queries going on there. PHP's MySQL libraries will, by default, limit you to only being able to use one query at a time - mainly to prevent SQL injection from multiple queries.

Howevever, mysqli has a multi_query function which has support for this, and in your case PDO can support multiple queries with PDOStatement::newRowset.

There are other options for you too:

I've had to deal with this occasionally, especially when dealing with temporary tables. Using SQL transactions and/or simply splitting each query into a seperate PHP statement will do the trick.

Community
  • 1
  • 1
scrowler
  • 24,273
  • 9
  • 60
  • 92