How to get a prepared MYSQLI report under action_rows, PHP

Question

It seems I have problems getting affected_rows when I am INSERT and SELECT, for some reason, it just returns -1? I use a database class that I use all the time for my projects that use MYSQLI statements to avoid sql injections.

Does anyone know why it returns -1 all the time? from what I read, it should be able to return the affected rows in both INSERT and SELECT.

Database class

$class database { protected $_mysqli; protected $_debug; public function __construct($host, $username, $password, $database, $debug) { $this->_mysqli = new mysqli($host, $username, $password, $database); $this->_debug = (bool) $debug; if (mysqli_connect_errno()) { if ($this->_debug) { echo mysqli_connect_error(); debug_print_backtrace(); } return false; } return true; } public function q($query) { if ($query = $this->_mysqli->prepare($query)) { if (func_num_args() > 1) { $x = func_get_args(); $args = array_merge(array(func_get_arg(1)), array_slice($x, 2)); $args_ref = array(); foreach($args as $k => &$arg) { $args_ref[$k] = &$arg; } call_user_func_array(array($query, 'bind_param'), $args_ref); } $query->execute(); if ($query->errno) { if ($this->_debug) { echo mysqli_error($this->_mysqli); debug_print_backtrace(); } return false; } if ($query->affected_rows > -1) { return $query->affected_rows; } $params = array(); $meta = $query->result_metadata(); while ($field = $meta->fetch_field()) { $params[] = &$row[$field->name]; } call_user_func_array(array($query, 'bind_result'), $params); $result = array(); while ($query->fetch()) { $r = array(); foreach ($row as $key => $val) { $r[$key] = $val; } $result[] = $r; } $query->close(); return $result; } else { if ($this->_debug) { echo $this->_mysqli->error; debug_print_backtrace(); } return false; } } public function handle() { return $this->_mysqli; } public function last_insert_id() { return $this->_mysqli->insert_id; } public function found_rowss() { return $this->_mysqli->affected_rows; } 

}

Answer 1

for Select-statements created during preparation, you should use $query->num_rows() or mysqli_stmt_num_rows($query) .

An insert statement can give you suppressed errors if you execute "INSERT IGNORE" , which can lead to -1 in $query->affected_rows() .

A comment on php.net (second link) suggests that you use $query->sqlstate=="00000" to check for errors.

---

see php.net (manual / en / mysqli-stmt.affected-rows) :

"This function only works with queries that update the table. To get the number of rows from a SELECT query, use mysqli_stmt_num_rows() instead."

and php.net (manual / en / mysqli.affected-rows) :

"Checking whether mysqli-> affected_rows is -1 or not is not a good method for determining the success of "INSERT IGNORE" . Example: Ignoring repeated key errors when inserting some rows containing user-supplied data only if they match the specified unique restriction causes a -1 value to be returned to mysqli->affected_rows even if the rows were inserted (tested on MySQL 5.0.85 linux and php 5.2.9-2 windows). However, mysqli->sqlstate does not return an error if the statement was successful done. "