$mysqli->affected_rows returns 0 multi_query

Question

I have simple code. This query works fine. It works directly through sql and from php too (changed $mysqli->query to $mysqli->multi_query to make it work from php) but it $mysqli->affected_rows return 0. There are actually inserted rows and when I run it the query through heidisql it reports:

Affected rows: 2 Found rows: 0 Warnings: 0 Duration for 2 queries: 0.000 sec.

How am I supposed to count affected rows? What am I doing wrong?

I tried also rowCount() and didn't really helped.

include ("../connect.php");

$sql = "SELECT balance INTO @b
            FROM managerstock
            WHERE ID = LAST_INSERT_ID();
            INSERT INTO managerstock (`inOut`, quantity, balance)
            VALUES (1, 1, 3);";

if($result = $mysqli->multi_query($sql)){
  echo $mysqli->affected_rows;
}

maybe interesting? http://stackoverflow.com/a/22396503/3184785. It appears you need to accumulate the `affected_rows` when checking the `next_results`. — Ryan Vincent, Apr 19 '16 at 08:05

score 1 · Answer 1 · edited Dec 27 '16 at 22:36

Here CALL user('param1','param2','param3','param4'); is a stored procedure in which you can write any number of queries.

You can use ROW_COUNT() function to get number of rows affected, and use select statement to fetch it as output.

Procedure

CREATE DEFINER=`root`@`localhost` PROCEDURE `user`(IN `p_mode` VARCHAR(100), IN `p_id` INT(3), IN `p_name` VARCHAR(100), IN `p_contact_no` VARCHAR(100))
    NO SQL
begin
    DECLARE
        row_count int(11);

    if p_mode='update' then
        UPDATE `user` SET `name`=p_name, `contact_no`=p_contact_no WHERE `id`=p_id;
        SET row_count = ROW_COUNT();
        select row_affected as row_count;   
    end if;
end

PHP Script:

<?php
function execute_qry($sql){
    $fire=mysqli_multi_query($this->conn,$sql);
    $array_result= array();
    do{
        $res = array();
        if($result = $this->conn->store_result())
        {
            while($row = mysqli_fetch_assoc($result))
            {
                array_push($res,$row);
            }
            array_push($array_result,$res);
        }
    }while($this->conn->more_results() && $this->conn->next_result());
    return  $array_result;
}
$query  = "CALL user('update','1','hiren','1234');";
$exe_qry=execute_qry($query);
?>

OK, but heidi sql somehow gets that affected rows were 2, why i cant get it the same way? — M.G., Apr 19 '16 at 06:52

Brijal Savaliya · Answer 2 · 2016-04-15T12:45:40.147

-1

First insert record then make select query

 include ("../connect.php");

$sql = "INSERT INTO managerstock (`inOut`, quantity, balance)
            VALUES (1, 1, 3);SELECT balance INTO @b
            FROM managerstock
            WHERE ID = LAST_INSERT_ID();";

    if($result = $mysqli->multi_query($sql)){
      echo $mysqli->affected_rows;
    }

edited Apr 15 '16 at 12:45

answered Apr 15 '16 at 12:42

Brijal Savaliya

1,101
9
19

Where did you get the name, password and username colums from? – Phiter Apr 15 '16 at 12:43
I have tried in my system so please check updated @PhiterFernandes , have you DW my answer ? – Brijal Savaliya Apr 15 '16 at 12:46
I think OP is using multiple query because he will use the value of `@b` in the `INSERT` query afterwards, so the `INSERT` must come after the `SELECT`. – Phiter Apr 15 '16 at 12:51
I have checked above query , its actually inserting record and giving 1 affected row as result, – Brijal Savaliya Apr 15 '16 at 12:53
@b has to be used in insert statement, so select has to be first – M.G. Apr 19 '16 at 06:40

$mysqli->affected_rows returns 0 multi_query

2 Answers2