PHP/MySQL: Passing values from one query to another in PHP multi queries

Question

I have to execute 3 queries inside one PHP file. I have used mysqli_multi_query(). The first 2 queries are SELECT queries which return values for third query (INSERT). So far I have done this;

<?php

$host = "localhost";
$user = "smartbusarrival_grouplog";
$password = "group10@10";
$db = "smartbusarrival_sbaDB";

$u_id = 51;
$b_id = 1;
$t_id = 1;
$date = '2017-06-30';
$startHalt = "Kaduwela";
$endHalt = "Nugegoda";

$seats = array(42,43);

$con = mysqli_connect($host,$user,$password,$db);

$query = "CALL getHaltTag('$t_id','$startHalt');";
$query .= "CALL getHaltTag('$t_id','$endHalt');";
$query .= "INSERT INTO reservation (user_id,bus_id,trip_id,date,start,end) values ('$u_id','$b_id','$t_id','$date','$startTag','$endTag')";

if(mysqli_multi_query($con, $query)){
    do{
        if($result = mysqli_store_result($con)){
            while ($row = mysqli_fetch_array($result)){
                $startTag = $row[0];

                $endTag = $row[1];  

            }
            mysqli_free_result($result);
        }           
    }
    while (mysqli_next_result($con));
}   

mysqli_close($con);
?>

First two queries work very well and give correct answers. Code works just fine when inserting a new record. But the value of start and end are zero.

[Little Bobby](http://bobby-tables.com/) says ***[your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)*** Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! — Jay Blanchard, Jun 27 '17 at 20:41
`$startTag` and `$endtag` aren't defined when you execute. Use error reporting. — chris85, Jun 27 '17 at 20:50

score 0 · Answer 1 · answered Jun 27 '17 at 20:54

You can't use multi-query for this, because you're substituting the variables into the third query before you perform the fetch that sets them. Just do them as separate queries.

$query = "CALL getHaltTag('$t_id','$startHalt');";
$result = mysqli_query($con, $query);
$row = mysqli_fetch_row($result);
$startTag = $row[0];

$query = "CALL getHaltTag('$t_id','$endHalt');";
$result = mysqli_query($con, $query);
$row = mysqli_fetch_row($result);
$endTag = $row[0];

$query = "INSERT INTO reservation (user_id,bus_id,trip_id,date,start,end) values ('$u_id','$b_id','$t_id','$date','$startTag','$endTag')";
mysqli_query($con, $query);

In general, there are very few situations where multi-query is really needed, and it usually complicates the code.

And if you change getHaltTag from a stored procedure to a stored function, you don't need 3 separate queries. You could do it in a single query where you call the function:

$query = "INSERT INTO reservation (user_id,bus_id,trip_id,date,start,end) 
        values ('$u_id','$b_id','$t_id','$date',getHaltTag('$startHalt'),getHaltTag('$endHalt'))";

PHP/MySQL: Passing values from one query to another in PHP multi queries

1 Answers1