Query works in PHPmyadmin, but not in PHP script

Question

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

It's worked that way in other queries I've done, where I need to SET a value first — Just Lucky Really
– Just Lucky Really, Commented Feb 26, 2014 at 22:00
Maybe try separating it into two queries and see if that helps? — muttley91
– muttley91, Commented Feb 26, 2014 at 22:00
Also, PDO doesn't deal with duplicate ":user1id" tokens (edit: this might only be in some versions bugs.php.net/bug.php?id=40417) — Scuzzy
– Scuzzy, Commented Feb 26, 2014 at 22:01

Community · Accepted Answer · 2017-05-23 11:57:48Z

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.

edited May 23, 2017 at 11:57

CommunityBot

11 silver badge

answered Feb 26, 2014 at 22:02

scrowler

24.4k10 gold badges67 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Just Lucky Really Over a year ago

Thanks a lot dude :D ... Didn't know about nextRowset() ... My head is hurting a little less now :D

scrowler Over a year ago

Cool - check the second link in the bullet points too, it's got another example for PDO

Collectives™ on Stack Overflow

Query works in PHPmyadmin, but not in PHP script

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related