php mysql multiquery does not return result

Question

I am trying to execute the following.

public function result_multi($sql) {

    try {
        mysqli_multi_query($this->db_connection,$sql);
        $result=mysqli_store_result($this->db_connection);
        $row=mysqli_fetch_row($result);
    }

$b=new DB;
$sql="INSERT INTO payout (mid, type, datetime) VALUES ('0','BC', 
NOW());SELECT LAST_INSERT_ID();";
$b->result_multi($sql);

$row returns NULL and $result return FALSE, however when I execute the SQL query in phpmyadmin it works.

Your code looks invalid. I don't think you can have a try block without a catch and/or finally block (what would be the point?) — GordonM, Jan 17 '19 at 14:30
Here's a comprehensive post: https://stackoverflow.com/a/22469722/2943403 but honestly why not only INSERT then use php to grab the last id? http://php.net/manual/en/mysqli.insert-id.php — mickmackusa, Jan 17 '19 at 14:50

GMB · Accepted Answer · 2019-01-17T14:41:30.600

3

When it comes to fetching the results of a multi query, the mysqli_multi_query documentation states :

To retrieve the resultset from the first query you can use mysqli_use_result() or mysqli_store_result(). All subsequent query results can be processed using mysqli_more_results() and mysqli_next_result().

So you would need to call mysqli_next_result once to access the results of the second query, and then mysqli_store_result/mysqli_fetch_row to fetch the id.

But in your use case, it seems like a simpler way to proceed is to use mysqli_insert_id method, like :

my $conn = $this->db_connection;
$sql = "INSERT INTO payout (mid, type, datetime) VALUES ('0','BC', NOW())";
if (mysqli_query($conn, $sql)) {
    $last_id = mysqli_insert_id($conn);
    echo "Record inserted with ID " . $last_id;
} else {
    echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}

PS : you should also check for errors after opening the connection to the database.

edited Jan 17 '19 at 14:41

answered Jan 17 '19 at 14:18

GMB

216,147
25
84
135

this worked but why would the sql option not work. – the_big_blackbox Jan 17 '19 at 14:29
@the_big_blackbox : I updated my answer with explanations – GMB Jan 17 '19 at 14:41
thanks both solutions now work – the_big_blackbox Jan 17 '19 at 15:08
is there any advantage / disadvantage to either approach? – the_big_blackbox Jan 17 '19 at 15:10
I would recommend using `mysqli_insert_id`, because it is specifically designed for this use case. `mysqli_multi_query` better fits the cases when random multiple SQL need to be executed. – GMB Jan 17 '19 at 15:13

score -2 · Answer 2 · answered Jan 17 '19 at 14:28

-2

I need more code for this, because you used the multi sql so must have more query.

Try this code if only one query sql :

public function result($sql) {

    try {
        mysqli_query($this->db_connection,$sql);
        $result=mysqli_store_result($this->db_connection);
        $row=mysqli_fetch_row($result);
    }

$b=new DB;
$sql="INSERT INTO payout (mid, type, datetime) VALUES ('0','BC', 
NOW());SELECT LAST_INSERT_ID();";
$b->result($sql);

answered Jan 17 '19 at 14:28

Baby Corp

1
3

What did you change? Why does your code still contain syntax errors? – Nico Haase Jan 17 '19 at 14:40
Where the error ? could you proof it ? – Baby Corp Jan 17 '19 at 16:06
Well, where is this function `result` contained in? Probably within the DB class which you initialize within that function? – Nico Haase Jan 17 '19 at 16:06
$b->result($sql); thats the db, we can't test because don't see the real code. – Baby Corp Jan 17 '19 at 17:07
and where your solution ? I give the solution with delete multi query....are you spam in comment ? – Baby Corp Jan 17 '19 at 19:30

php mysql multiquery does not return result

2 Answers2