0

I tryed to perform this query:

    $q = "SET @userId := (SELECT user_id
                            FROM users
                            WHERE user_email='$userEmail'
                            AND user_token='$userToken' );
            SELECT  stock_name, stock_description, stocks.stock_id, stock_owner_id,
                    stock_creation_date, stock_last_mod_date, stock_num_item_to_buy,
                    stock_distance_to_buy, stock_num_allowed_items, tab.user_role 
            FROM stocks
            INNER JOIN
                (SELECT stock_id, user_role
                    FROM user_stock
                    WHERE user_id = @userID) tab
            ON tab.stock_id=stocks.stock_id
            WHERE stocks.stock_id='$stockId'
            UNION ALL
            SELECT * , 'owner' as user_role
            FROM stocks
            WHERE stock_owner_id=@userId AND stocks.stock_id='$stockId';";

using:

$mysqli->query ( $q );

but the result is this error:

You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax 
to use near 'SELECT stock_name, stock_description, stocks.stock_id, 
stock_owner_id, st' at line 5

If I try to use the same query by PhpMyAdmin or MySQLWorkbrench all works fine...

... But by PHP I still having that error. I tryed to use also:

$mysqli->multi_query ( $q );

but the result is the same (To be honest I can't get also the error message in this case).

If I delete the first part (the SET of the variable) the query works fine also by PHP, I can't undertand what's the probelm!!!

Please, help me!

======================= EDIT ================================================== Thanks to @suchit, I just saw the error in the var name, but is the same after correcting the typo!

I see that if I use a ; in the SQL statement PHP works different respect to PhpMyAdmin where the same query works fine, but also if I use mysqli_multi_query the problem still again..

The only solutions is perferoming two different queries how @Barmar said, thanks!

I would only add: What's the use of mysqli_multi_query ?! I checked the manual, but I saw that the use would be to do more queries, but why didn't work for me?

Luca122131
  • 115
  • 2
  • 9

2 Answers2

0

I've seen lots of questions about mysqli::multi_query(), I don't recommend using it (even if it can be made to work, it seems unnecessary in most cases). Just split your query into two separate calls.

$q1 = "SET @userId := (SELECT user_id
                        FROM users
                        WHERE user_email='$userEmail'
                        AND user_token='$userToken' );";
$q2 = "SELECT  stock_name, stock_description, stocks.stock_id, stock_owner_id,
                stock_creation_date, stock_last_mod_date, stock_num_item_to_buy,
                stock_distance_to_buy, stock_num_allowed_items, tab.user_role 
        FROM stocks
        INNER JOIN
            (SELECT stock_id, user_role
                FROM user_stock
                WHERE user_id = @userID) tab
        ON tab.stock_id=stocks.stock_id
        WHERE stocks.stock_id='$stockId'
        UNION ALL
        SELECT * , 'owner' as user_role
        FROM stocks
        WHERE stock_owner_id=@userId AND stocks.stock_id='$stockId';";
$mysqli->query($q1);
$mysqli->query($q2);

User-defined variables are associated with a database connection, not a specific query invocation, so you can access a variable set in a previous call on the same connection.

Barmar
  • 741,623
  • 53
  • 500
  • 612
0

Probably because you're trying to run two sql-queries at once (SET and SELECT).

swift
  • 1,108
  • 7
  • 9