Using MySQL variables in outside stored procedures

Question

I am trying to learn how to use variables in MySQL:

$query = "
    SET @myid = '1234';
    SELECT * FROM `table1` WHERE `id` = @myid;
";

But I get a syntax error on line 1.

I have looked at the manual and it states this is the correct syntax.
I have also scoured stack but most of the questions are based on store procedures.

Are MySQL variables limited to stored procedures only? If not what am I doing wrong?

you can't run multiple queries in a single `query()` call. that's all. issue `query("set...")` and `query("select ...")` instead. — Marc B, Sep 14 '15 at 20:21
can you show us the code you are using to execute this sql, are you using mysqli, pdo ? — cmorrissey, Sep 14 '15 at 20:24
Its just mysqli, looking at the manual again Marc's comment makes sense as the examples I was following are from a terminal https://dev.mysql.com/doc/refman/5.0/en/user-variables.html — Edward, Sep 14 '15 at 20:28
could be good for others to see it when you are done Edward, as an answer yourself — Drew, Sep 14 '15 at 20:33
You could use http://php.net/manual/en/mysqli.multi-query.php to execute both. http://stackoverflow.com/questions/24853684/set-mysql-variables-with-mysqli-not-php-variables — chris85, Sep 14 '15 at 20:33

score 0 · Answer 1 · edited May 23 '17 at 12:14

Using mysqli_multi_query()

Trying to use variables inline with queries throws a syntax error on line 1. Thank you Marc B.

SET @myid = '<?php echo $myid; ?>';
SELECT t.*,
    CASE WHEN m.`dob` BETWEEN @uDateStart AND @uDateEnd
    etc...

My solution was to use mysqli::multi_query or mysqli_multi_query like so:

$this -> db -> multi_query($sql);
$i = 0;
$querySelect = 1; //query 1
do{
    if($result = $this -> db -> store_result()) {
        if($i === $querySelect) {
            while($row = mysqli_fetch_array($result)) {
                $data[] = $row;
            }
            mysqli_free_result($result);
        }
    }
    $i++;
} while($this -> db -> more_results() && $this -> db -> next_result());

$querySelect prevents returning unwanted result sets.

Using MySQL variables in outside stored procedures

1 Answers1

Using mysqli_multi_query()