Page 1 of 1

mysqli affected_rows = -1, but error and errno are blank

Posted: Thu Jun 03, 2010 3:44 am
by elgranjeff
Hello,

I'm trying to prepare a bound-parameter statement, but this problem has been holding me back for quite some time. (I'm teaching myself how to use mysqli, so my theory probably isn't perfect :wink: )

I have a query that works properly from phpmyadmin (with actual values substituted)
but when i try to prepare the query string, my mysqli object's affected_rows property gets set to -1, but error and errno are both set to 0 and '' (empty string) respectively.

The documentation for mysqli -> affected_rows says that a value of -1 means the query produced and error, but as I am given no error message and my syntax works with phpmyadmin, i'm not sure what's going on :?

EDIT 06.29.2010: THIS CODE IS NOT QUITE UP TO DATE; SEE LATER POSTS!

This is my prepare() method:

Code: Select all

[php=db_class.php|557]
	protected function prepare () {
		$this -> debugClassObj -> head(__METHOD__);
		
		$this -> debugClassObj -> print_var($this -> mysqliObj,'$this -> mysqliObj',__LINE__);
		$this -> debugClassObj -> print_var($this -> mysqliStmtObj,'$this -> mysqliStmtObj',__LINE__);

		$this -> query['prepare'] = "SELECT {$this -> query['columns']} FROM `{$this -> query ['table']}` WHERE ";
		
		$count = 0;
		foreach ($this -> query['where'] as $k => $v) {
			if ($count ==0) {
				$this -> query['prepare'] .= "'$k'=?";
			} else {
				$this -> query['prepare'] .= "AND '$k'=?";
			}
			$count++;
		}
		
		$this -> debugClassObj -> print_var($this -> query['prepare'],'$this -> query[\'prepare\']',__LINE__);

		$this -> debugClassObj -> message('$this -> mysqliStmtObj -> prepare ("'. $this -> query['prepare'] .'")',__CLASS__,__LINE__);
		
		$this -> mysqliStmtObj = $this -> mysqliObj -> prepare ($this -> query['prepare']);
		
		$this -> debugClassObj -> print_var($this -> mysqliObj,'$this -> mysqliObj',__LINE__);
		$this -> debugClassObj -> print_var($this -> mysqliStmtObj,'$this -> mysqliStmtObj',__LINE__);
		
		if ($this -> mysqliObj -> affected_rows == -1) {
			die (__LINE__.': '.__CLASS__.'::mysqliObj -> affected_rows == -1!  why is this??');
		} else {
			$this -> bindParams ();
		}
		
		$this -> debugClassObj -> foot();
	}
[/php]
Here's my debugClass:

Code: Select all

[php=debug_class.php]
/* 
 * This class is used to expedite the process of displaying debug information
 */
class debugClass {
	
	private $mode = 0;
	
	function __construct($mode) {
		$this -> mode = $mode;
		
		if ($this -> mode) {
			$this -> head(__METHOD__);
			$this -> foot();
		}
	}
	
	function print_var ($var,$name,$line=NULL) {
		if ($this -> mode) {
			echo "<pre>$name: $line</p><blockquote><pre>";
			print_r ($var);
			echo "</pre></blockquote>";
		}
	}
	
	///////////////////////////////////////////////////////////////////////////////////////
	
	function head($method,$params=NULL) {
		if ($this -> mode) {
			$output = "<p><pre>$method (";
			
			if (is_array($params)) {
				$i = 0;
				$count = count($params);
				foreach ($params as $v) {
					if ($i == 0) {
						$output .= "'$v'";
					} else {
						$output .= ", '$v'";
					}
					$i++;
				}
				
				if (substr_count($output,',') == 1) {
					str_replace(',','',$output);
				}
				
			} else {
				$output .= $params;
			}
			
			$output .= ") {</pre></p><blockquote>";
			
			echo $output;
		}
		
		return true;
	}
	
	///////////////////////////////////////////////////////////////////////////////////////

	function foot() {
		if ($this -> mode) {
			echo "</blockquote><p><pre>}</pre></p>";
		}

	}
	
	///////////////////////////////////////////////////////////////////////////////////////
	
	function message ($message,$class,$line=null) {
		if ($this -> mode) {
			echo "<pre><p><b>$class::$line</b>: $message</p></pre>";
		}
			
	}
	
}
[/php]
and here's the debug info i have the program output:

Code: Select all

dbClass::prepare () {
560: $this -> mysqliObj
mysqli Object
(
    [affected_rows] => 9
    [client_info] => 5.0.27
    [client_version] => 50027
    [connect_errno] => 0
    [connect_error] => 
    [errno] => 0
    [error] => 
    [field_count] => 6
    [host_info] => Localhost via UNIX socket
    [info] => 
    [insert_id] => 0
    [server_info] => 5.1.41-3ubuntu12.1
    [server_version] => 50141
    [sqlstate] => 00000
    [protocol_version] => 10
    [thread_id] => 153
    [warning_count] => 0
)
561: $this -> mysqliStmtObj
NULL
575: $this -> query['prepare']
SELECT `id`, `name`, `website`, `dateCreate`, `frozen` FROM `accounts` WHERE 'id'=?

579 - dbClass:: "$this -> mysqliStmtObj -> prepare ("SELECT `id`, `name`, `website`, `dateCreate`, `frozen` FROM `accounts` WHERE 'id'=?")"

584: $this -> mysqliObj
mysqli Object
(
    [affected_rows] => -1
    [client_info] => 5.0.27
    [client_version] => 50027
    [connect_errno] => 0
    [connect_error] => 
    [errno] => 0
    [error] => 
    [field_count] => 6
    [host_info] => Localhost via UNIX socket
    [info] => 
    [insert_id] => 0
    [server_info] => 5.1.41-3ubuntu12.1
    [server_version] => 50141
    [sqlstate] => 00000
    [protocol_version] => 10
    [thread_id] => 153
    [warning_count] => 0
)
585: $this -> mysqliStmtObj
mysqli_stmt Object
(
    [affected_rows] => 0
    [insert_id] => 0
    [num_rows] => 0
    [param_count] => 1
    [field_count] => 5
    [errno] => 0
    [error] => 
    [sqlstate] => 00000
    [id] => 1
)
586: $this -> mysqliStmtObj2
mysqli_stmt Object
OH SNAP

Warning:  print_r() [function.print-r.html]: Property access is not allowed yet in /home/jeff/www/techjeff/include/classes/debug_class.php on line 29

OH SNAP

Warning:  print_r() [function.print-r.html]: Property access is not allowed yet in /home/jeff/www/techjeff/include/classes/debug_class.php on line 29

OH SNAP

Warning:  print_r() [function.print-r.html]: Property access is not allowed yet in /home/jeff/www/techjeff/include/classes/debug_class.php on line 29

OH SNAP

Warning:  print_r() [function.print-r.html]: Property access is not allowed yet in /home/jeff/www/techjeff/include/classes/debug_class.php on line 29

OH SNAP

Warning:  print_r() [function.print-r.html]: Property access is not allowed yet in /home/jeff/www/techjeff/include/classes/debug_class.php on line 29

OH SNAP

Warning:  print_r() [function.print-r.html]: Property access is not allowed yet in /home/jeff/www/techjeff/include/classes/debug_class.php on line 29

(
    [affected_rows] => 
    [insert_id] => 
    [num_rows] => 
    [param_count] => 
    [field_count] => 
    [errno] => 0
    [error] => 
    [sqlstate] => 00000
    [id] => 
)
589: die ("dbClass::mysqliObj -> affected_rows == -1!  why is this??")
accountClass::__destruct () {
}
dbClass::__destruct () {
dbClass::disconnect () {
}
}
I sure hope it doesn't have to do with my setting 'error_prepend_string' to "OH SNAP"... :lol:

Any help is greatly appreciated! :D

Re: mysqli affected_rows = -1, but error and errno are blank

Posted: Sat Jun 26, 2010 4:54 am
by elgranjeff
okay, I've reorganized things, added comments and just cleaned things up, BUT none of this cleaning affected my problem whatsoever. when I try to prepare my query string using mysqliObj, its affected_rows property is set to -1; mysqliStmtObj is set, but has no rows or valuable information. i don't think its a syntax thing, i don't think its a problem with my php or mysqli.

does anyone have any idea where to look for more information? I have zendserver CE installed and there are no relevant log entries, i have error reporting on E_ALL... any ideas? Anything is apprecaited :)

anyhoo... here's my select() method, then initialize(), prepare(), my debugClass, and finally the output.

Code: Select all

///////////////////////////////////////////////////////////////////////////////////////

public function select($columns, $table, $where = NULL) {
/* 
 * a smiplified SELECT statement 
 *  
 * $columns is a CSL of columns to be selected.
 * $table is the table from which the fields will be selected
 * $fields and $values are an optional pair of CSLs to be used
 * with WHERE clause.
 *  
 */
	
	// see debugClass for more info
	$this -> debugClassObj -> set_mode(1);
	$this -> debugClassObj -> head(__METHOD__,__LINE__,array($columns,$table,$where));
	
	// initialization
	$this -> initialize();
	
	// create "local" storage of parameters 
	$this -> columns = $columns;
	$this -> table = $table;
	$this -> where = $where; 
	
	// initialize $query
	$this -> query = "SELECT {$this->columns} FROM {$this->table}"; 
			
	// output $query to verification 
	$this -> debugClassObj -> print_var($this -> query,'query',__CLASS__,__LINE__);
	
	if ($this -> where === NULL) {
		// if there is no where clause, just run the query
		$this -> query ($this -> query);
		
	} else { 
		// if where is set, prepare and execute
		$this -> prepare ();
		$this -> execute ();
	}
	
	// fetch the data
	$this -> fetchData ();
	
	// see DEBUG in the readme
	$this -> debugClassObj -> foot(__METHOD__,'rows');
	
	// return $this -> rows 
	return $this -> rows;
}
initialize()

Code: Select all

protected function initialize() {
/*
 * This function resets all request-specific variable information
 */
	
	// see DEBUG in the readme
	$this -> debugClassObj -> set_mode(0);
	$this -> debugClassObj -> head(__METHOD__,__LINE__);
		
	$this -> debugClassObj -> print_var ($this,'$this',__CLASS__,__LINE__);
		
	foreach ($this as $k => &$v) {			
		switch ($k) {
			case 'mysqliStmtObj':
				if (is_object($this -> mysqliStmtObj)) {
					$this -> mysqliStmtObj -> close();
					$this -> mysqliStmtObj = NULL;
				}
				break;
			case 'mysqliResultObj':
				if (is_object($this -> mysqliResultObj)) {
					$this -> mysqliResultObj -> close();
					$this -> mysqliResultObj = NULL;
				}
				break;
			case 'query':
			case 'columns':
			case 'table':
				$v = '';
				break;

			case 'where':
			case 'rows':
				$v = array();
				break;
			case 'tmp':
				$v = NULL;
				break;
					
		}
	}
	
	$this -> debugClassObj -> message ('initialized',__CLASS__,__LINE__);
	
	$this -> debugClassObj -> print_var ($this,'$this',__CLASS__,__LINE__);
		
	$this -> debugClassObj -> foot(__METHOD__,true);
}
prepare()

Code: Select all

protected function prepare () {
/*
 * this function prepares a statement from a query containing a WHERE clause
 * 
 * logical flow:
 * 1) validate the where input
 * 2) add WHERE to $query
 * 3) add columns to $query
 * 4) prepare the query and return a prepared statement object to mysqliStmtObj
 * 5) bind parameters to mysqliStmtObj
 */
	
	// see debugClass for more info
	$this -> debugClassObj -> set_mode(1);
	$this -> debugClassObj -> head(__METHOD__,__LINE__);
	
	// validate WHERE input (this will be more scruitinous when the time comes)
	foreach ($this -> where as $k => $v) {
		// if either k or v are boolean false
		if (!($k) xor !($v)) {
			die ('$k or $v == false. '.__LINE__);					
		}
	}
	
	// add WHERE to $query
	$this -> query .= " WHERE ";
			
	// add columns to query		
	$i = 0;
	foreach ($this -> where as $k => $v) {
		if ($i > 0) {
			$this -> query .= "AND ";
		} 
		$this -> query .= "$k = ?";
		$i++;
	}
	
	// display $query for verification
	$this -> debugClassObj -> print_var($this -> query,'query',__CLASS__,__LINE__);
	
	// notification of prepare()
	$this -> debugClassObj -> message("mysqliStmtObj -> prepare ({$this -> query})",__CLASS__,__LINE__);

	// prepare the query and return a prepared statement object to mysqliStmtObj
	$this -> mysqliStmtObj = $this -> mysqliObj -> prepare ($this -> query) or $this -> debugClassObj -> message("prepare() failed.",__CLASS__,__LINE__);
	
	// I don't understand why I'm getting a -1 with no error or errno.
	// this bock checks for that scenario and displays a notification if detected.
	if ($this -> mysqliObj -> affected_rows == -1) {
		$this -> debugClassObj -> message("mysqliObj -> affected_rows == -1!  why is this??",__CLASS__,__LINE__);
	
		// display mysqliObj and mysqliStmtObj
		$this -> debugClassObj -> print_var($this -> mysqliObj,'mysqliObj',__CLASS__,__LINE__);
		$this -> debugClassObj -> print_var($this -> mysqliStmtObj,'mysqliStmtObj',__CLASS__,__LINE__);
			
		$this -> debugClassObj -> message ('affected_rows == -1',__CLASS__,__LINE__);
		
		die();
	}
	
	// bind parameters to mysqliStmtObj
	$this -> bindParams ();
		
	// see DEBUG in the readme
	$this -> debugClassObj -> foot(__METHOD__,true);
}
debugClass

Code: Select all

class debugClass {
	
	private $mode = 0;
	
	function __construct($mode) {
		$this -> mode = $mode;

		$this -> head(__METHOD__,__LINE__,$mode);
		$this -> foot(__METHOD__,true);
	}
	
	function set_mode($mode) {
		$this -> mode = $mode;
	}
	
	function print_var ($var,$name,$class,$line=NULL) {
		if ($this -> mode) {
			echo "<pre><b>$line: $class -> $name:</b><blockquote>";
			if ($var) {
				print_r ($var);
			} else {
				echo "false";
			}
			echo "</blockquote></pre>";
		}
	}
	
	///////////////////////////////////////////////////////////////////////////////////////
	
	function head($method,$line,$params=NULL) {
		if ($this -> mode) {
			$output = "<pre><b>$line: $method (";
			
			if (is_array($params)) {
				$i = 0;
				$count = count($params);
				foreach ($params as $v) {
					if ($i == 0) {
						$output .= "'$v'";
					} else {
						$output .= ", '$v'";
					}
					$i++;
				}
				
				if (substr_count($output,',') == 1) {
					str_replace(',','',$output);
				}
				
			} else {
				$output .= $params;
			}
			
			$output .= ") {</b></pre><blockquote>";
			
			echo $output;
		}
		
		return true;
	}
	
	///////////////////////////////////////////////////////////////////////////////////////

	function foot($method,$return) {
		if ($this -> mode) {
			echo "<pre><b>$method -> return $return</b></pre></blockquote><pre><b>}</b></pre>";
		}

	}
	
	///////////////////////////////////////////////////////////////////////////////////////
	
	function message ($message,$class,$line=null) {
		if ($this -> mode) {
			echo "<pre><b>$line: $class::message:</b><blockquote>$message</blockquote></pre>";
		}
			
	}
	
}
and the output.

Code: Select all

Accounts

194: dbClass::select ('*', 'accounts', 'Array') {
551: dbClass::prepare () {
575: dbClass -> query:
SELECT * FROM accounts WHERE id = ?
578: dbClass::message:
mysqliStmtObj -> prepare (SELECT * FROM accounts WHERE id = ?)
586: dbClass::message:
mysqliObj -> affected_rows == -1!  why is this??
589: dbClass -> mysqliObj:
mysqli Object
(
    [affected_rows] => -1
    [client_info] => 5.0.27
    [client_version] => 50027
    [connect_errno] => 0
    [connect_error] => 
    [errno] => 0
    [error] => 
    [field_count] => 6
    [host_info] => Localhost via UNIX socket
    [info] => 
    [insert_id] => 0
    [server_info] => 5.1.41-3ubuntu12.3
    [server_version] => 50141
    [sqlstate] => 00000
    [protocol_version] => 10
    [thread_id] => 77
    [warning_count] => 0
)
590: dbClass -> mysqliStmtObj:
mysqli_stmt Object
(
    [affected_rows] => 0
    [insert_id] => 0
    [num_rows] => 0
    [param_count] => 1
    [field_count] => 5
    [errno] => 0
    [error] => 
    [sqlstate] => 00000
    [id] => 1
)
592: dbClass::message:
affected_rows == -1
57: dbClass::__destruct () {

Re: mysqli affected_rows = -1, but error and errno are blank

Posted: Sat Jun 26, 2010 5:07 am
by Benjamin
Select queries don't affect any rows.

Re: mysqli affected_rows = -1, but error and errno are blank

Posted: Sat Jun 26, 2010 3:48 pm
by elgranjeff
Benjamin wrote:Select queries don't affect any rows.
True. by the definition of "affect", select queries do not actually "change" any data, but then why would that change affected_rows from 0 to -1?

http://www.php.net/manual/en/mysqli.affected-rows.php
php manual wrote:
mysqli->affected_rows -- mysqli_affected_rows — Gets the number of affected rows in a previous MySQL operation

--Object oriented style--
mysqli
int $affected_rows;

--Procedural style--
int mysqli_affected_rows ( mysqli $link )

Returns the number of rows affected by the last INSERT, UPDATE, REPLACE or DELETE query.
For SELECT statements mysqli_affected_rows() works like mysqli_num_rows().
...
--Return Values--
An integer greater than zero indicates the number of rows affected or retrieved. Zero indicates that no records where updated for an UPDATE statement, no rows matched the WHERE clause in the query or that no query has yet been executed. -1 indicates that the query returned an error.
so if the query returned an error, there should be some kind of error and errno produced by mysql right? The syntax should be fine, it works in phpmyadmin if i replace the ? with a valid number..

Re: mysqli affected_rows = -1, but error and errno are blank

Posted: Sat Jun 26, 2010 4:36 pm
by Benjamin

Code: Select all

$this -> mysqliStmtObj  = $this ->  mysqliObj -> prepare ($this -> query) or $this ->  debugClassObj -> message("prepare() failed.",__CLASS__,__LINE__);
I think this line is not behaving as you expect. Check the value of $this->mysqliStmtObj and verify it is correct.

Other than that I'm not sure. I wrote database wrappers years ago and have used them ever since so I don't know the ins and outs of these particular methods.

Re: mysqli affected_rows = -1, but error and errno are blank

Posted: Sun Jun 27, 2010 8:18 pm
by elgranjeff
Benjamin wrote:

Code: Select all

$this -> mysqliStmtObj  = $this ->  mysqliObj -> prepare ($this -> query) or $this ->  debugClassObj -> message("prepare() failed.",__CLASS__,__LINE__);

I think this line is not behaving as you expect. Check the value of $this->mysqliStmtObj and verify it is correct.

Other than that I'm not sure. I wrote database wrappers years ago and have used them ever since so I don't know the ins and outs of these particular methods.
thanks benjamin,

prior to running mysqliObj -> prepare(), mysqliStmtObj is "closed" (mysqliStmtObj -> close()) and set to null by my protected method initialize():

Code: Select all

foreach ($this as $k => &$v) {			
	switch ($k) {
		case 'mysqliStmtObj':
			if (is_object($this -> mysqliStmtObj)) {
				$this -> mysqliStmtObj -> close();
			}
			$this -> mysqliStmtObj = NULL;
			break;
		case 'mysqliResultObj':
			if (is_object($this -> mysqliResultObj)) {
				$this -> mysqliResultObj -> close();
			}
			$this -> mysqliResultObj = NULL;
			break;
		case 'query':
		case 'columns':
		case 'table':
			$v = '';
			break;
		case 'where':
		case 'rows':
			$v = array();
			break;
		case 'tmp':
			$v = NULL;
			break;
			
	}
}
so it should just be a matter of filling in the empty variable $this -> mysqliStmtObj.

here's the output when I turn on debug messages for initialize()

Code: Select all

Accounts

156: accountClass::viewAct (1) {
230: accountClass::getAct (1) {
194: dbClass::select ('*', 'accounts', 'Array') {
495: dbClass::initialize () {
497: dbClass -> $this:
dbClass Object
(
    [debugClassObj:dbClass:private] => debugClass Object
        (
            [mode:debugClass:private] => 1
        )

    [mysqliObj:dbClass:private] => mysqli Object
        (
            [affected_rows] => 9
            [client_info] => 5.0.27
            [client_version] => 50027
            [connect_errno] => 0
            [connect_error] => 
            [errno] => 0
            [error] => 
            [field_count] => 6
            [host_info] => Localhost via UNIX socket
            [info] => 
            [insert_id] => 0
            [server_info] => 5.1.41-3ubuntu12.3
            [server_version] => 50141
            [sqlstate] => 00000
            [protocol_version] => 10
            [thread_id] => 55
            [warning_count] => 0
        )

    [mysqliStmtObj:dbClass:private] => 
    [mysqliResultObj:dbClass:private] => mysqli_result Object
        (
            [current_field] => 0
            [field_count] => 6
            [lengths] => Array
                (
                    [0] => 4
                    [1] => 6
                    [2] => 2
                    [3] => 0
                    [4] => 0
                    [5] => 0
                )

            [num_rows] => 9
            [type] => 0
        )

    [query:dbClass:private] => SHOW COLUMNS FROM `account-locations`
    [columns:dbClass:private] => Array
        (
            [0] => id
            [1] => accountId
            [2] => name
            [3] => contactIds
            [4] => address
            [5] => city
            [6] => state
            [7] => zip
            [8] => zip4
        )

    [table:dbClass:private] => 
    [where:dbClass:private] => Array
        (
        )

    [rows:dbClass:private] => Array
        (
            [0] => Array
                (
                    [0] => id
                    [1] => int(5)
                    [2] => NO
                    [3] => PRI
                    [4] => 
                    [5] => auto_increment
                )

            [1] => Array
                (
                    [0] => accountId
                    [1] => int(5)
                    [2] => NO
                    [3] => 
                    [4] => 
                    [5] => 
                )

            [2] => Array
                (
                    [0] => name
                    [1] => varchar(16)
                    [2] => NO
                    [3] => 
                    [4] => 
                    [5] => 
                )

            [3] => Array
                (
                    [0] => contactIds
                    [1] => varchar(30)
                    [2] => NO
                    [3] => 
                    [4] => 
                    [5] => 
                )

            [4] => Array
                (
                    [0] => address
                    [1] => varchar(32)
                    [2] => NO
                    [3] => 
                    [4] => 
                    [5] => 
                )

            [5] => Array
                (
                    [0] => city
                    [1] => varchar(24)
                    [2] => NO
                    [3] => 
                    [4] => 
                    [5] => 
                )

            [6] => Array
                (
                    [0] => state
                    [1] => char(2)
                    [2] => NO
                    [3] => 
                    [4] => 
                    [5] => 
                )

            [7] => Array
                (
                    [0] => zip
                    [1] => int(5)
                    [2] => NO
                    [3] => 
                    [4] => 
                    [5] => 
                )

            [8] => Array
                (
                    [0] => zip4
                    [1] => int(4)
                    [2] => NO
                    [3] => 
                    [4] => 
                    [5] => 
                )

            [fields] => Array
                (
                    [0] => id
                    [1] => accountId
                    [2] => name
                    [3] => contactIds
                    [4] => address
                    [5] => city
                    [6] => state
                    [7] => zip
                    [8] => zip4
                )

        )

    [tmp:dbClass:private] => 
)
530: dbClass::message:
initialized
532: dbClass -> $this:
dbClass Object
(
    [debugClassObj:dbClass:private] => debugClass Object
        (
            [mode:debugClass:private] => 1
        )

    [mysqliObj:dbClass:private] => mysqli Object
        (
            [affected_rows] => 9
            [client_info] => 5.0.27
            [client_version] => 50027
            [connect_errno] => 0
            [connect_error] => 
            [errno] => 0
            [error] => 
            [field_count] => 6
            [host_info] => Localhost via UNIX socket
            [info] => 
            [insert_id] => 0
            [server_info] => 5.1.41-3ubuntu12.3
            [server_version] => 50141
            [sqlstate] => 00000
            [protocol_version] => 10
            [thread_id] => 55
            [warning_count] => 0
        )

    [mysqliStmtObj:dbClass:private] => 
    [mysqliResultObj:dbClass:private] => 
    [query:dbClass:private] => 
    [columns:dbClass:private] => 
    [table:dbClass:private] => 
    [where:dbClass:private] => Array
        (
        )

    [rows:dbClass:private] => Array
        (
        )

    [tmp:dbClass:private] => 
)
dbClass::initialize -> return 1
}
208: dbClass -> query:
SELECT * FROM accounts
551: dbClass::prepare () {
575: dbClass -> query:
SELECT * FROM accounts WHERE id=?
578: dbClass::message:
mysqliStmtObj = mysqliObj -> prepare (SELECT * FROM accounts WHERE id=?)
586: dbClass::message:
mysqliObj -> affected_rows == -1!  why is this??
589: dbClass -> mysqliObj:
mysqli Object
(
    [affected_rows] => -1
    [client_info] => 5.0.27
    [client_version] => 50027
    [connect_errno] => 0
    [connect_error] => 
    [errno] => 0
    [error] => 
    [field_count] => 6
    [host_info] => Localhost via UNIX socket
    [info] => 
    [insert_id] => 0
    [server_info] => 5.1.41-3ubuntu12.3
    [server_version] => 50141
    [sqlstate] => 00000
    [protocol_version] => 10
    [thread_id] => 55
    [warning_count] => 0
)
590: dbClass -> mysqliStmtObj:
mysqli_stmt Object
(
    [affected_rows] => 0
    [insert_id] => 0
    [num_rows] => 0
    [param_count] => 1
    [field_count] => 5
    [errno] => 0
    [error] => 
    [sqlstate] => 00000
    [id] => 1
)
I Have no idea and it seems like not many other people do either. Perhaps I should report a bug..? (I never thought I would manage to find a bug in php :wink: )

Re: mysqli affected_rows = -1, but error and errno are blank

Posted: Tue Jun 29, 2010 1:35 pm
by elgranjeff
mmmk... i've rearranged things again trying to perhaps use a real_connect() with the MYSQLI_CLIENT_FOUND_ROWS flag to alleviate my problem.. but still no go..

same problem, different code. here's the stuff:

select()

Code: Select all

	public function select($columns, $table, $where = NULL) {
	/* 
	 * a smiplified SELECT statement 
	 *  
	 * $columns is a CSL of columns to be selected.
	 * $table is the table from which the fields will be selected
	 * $fields and $values are an optional pair of CSLs to be used
	 * with WHERE clause.
	 *  
	 */
		
		// see DEBUG in the readme
		$this -> debugClassObj -> set_mode(true);
		$this -> debugClassObj -> head(__METHOD__,__LINE__,array($columns,$table,$where)); // 206
		
		// initialize containers for request-data
		$this -> initialize(); // 209
		
		// create "local" storage of parameters 
		$this -> columns = $columns;
		$this -> table = $table;
		$this -> where = $where; 
		
		// initialize $query
		$this -> query = "SELECT {$this->columns} FROM `{$this->table}`"; 
				
		// output $query for verification 
		$this -> debugClassObj -> print_var($this -> query,'query',__CLASS__,__LINE__); // 220
		
		$this -> connect(MYSQLI_CLIENT_FOUND_ROWS); // 222
		
		if ($this -> where === NULL) {
			// if there is no where clause, just run the query
			$this -> query ($this -> query);  // 226
			
		} else { 
			// if where is set, prepare and execute
			$this -> prepare (); // 230
			$this -> execute (); // 231
		}
		
		// fetch the data
		$this -> fetchData (); // 235
		
		// disconnect from database
		$this -> disconnect(); // 238
		
		// see DEBUG in the readme
		$this -> debugClassObj -> foot(__METHOD__,'rows'); // 241
		
		// return $this -> rows 
		return $this -> rows;
	}
initialize()

Code: Select all

	protected function initialize() {
	/*
	 * This function resets all request-specific variable information
	 */
		
		// see DEBUG in the readme
		$this -> debugClassObj -> set_mode(true);
		$this -> debugClassObj -> head(__METHOD__,__LINE__); // 512
		
		$this -> debugClassObj -> print_var ($this,'$this',__CLASS__,__LINE__); // 514
		
		foreach ($this as $k => &$v) {			
			switch ($k) {
				case 'mysqliStmtObj':
					if (is_object($this -> mysqliStmtObj)) {
						$this -> mysqliStmtObj -> close();
					}
					$this -> mysqliStmtObj = NULL;
					break;
				case 'mysqliResultObj':
					if (is_object($this -> mysqliResultObj)) {
						$this -> mysqliResultObj -> close();
					}
					$this -> mysqliResultObj = NULL;
					break;
				case 'query':
				case 'columns':
				case 'table':
					$v = '';
					break;
	
				case 'where':
				case 'rows':
					$v = array();
					break;
				case 'tmp':
					$v = NULL;
					break;
					
			}
		}
		
		$this -> debugClassObj -> message ('initialized',__CLASS__,__LINE__); // 547
		
		$this -> debugClassObj -> print_var ($this,'$this',__CLASS__,__LINE__); // 549
		
		$this -> debugClassObj -> foot(__METHOD__,true);
	}
connect()

Code: Select all

	public function connect($flag=NULL) {
	/*
	 * this function is used to establish a database connection
	 * 
	 * Logical flow:
	 * 	1) establish database connection / populate mysqliObj
	 * 	2) check for connection errors
	 * 	3) return true on success / false on failure
	 */	
		
		// see DEBUG in the readme
		$this -> debugClassObj -> set_mode(true);
		$this -> debugClassObj -> head(__METHOD__,__LINE__); // 84
		
		if (!$flag) {
			// estabish database connection / populate mysqliObj
			$this -> mysqliObj = new mysqli (MYSQL_HOST, MYSQL_USER, MYSQL_PASS, MYSQL_DB)
			or $this -> debugClassObj -> message('failed to connect to database',__CLASS__,__LINE__); // 89
		} else {
			// initialize mysqliObj for real_connect()
			$this -> mysqliObj = mysqli_init();
		
			// really connect (haha)
			$this -> mysqliObj -> real_connect(
				MYSQL_HOST,
				MYSQL_USER,
				MYSQL_PASS,
				MYSQL_DB,
				MYSQL_PORT,
				MYSQL_SOCKET,
				$flag
			)
			or $this -> debugClassObj -> message('failed to connect to database',__CLASS__,__LINE__);  // 96
		}
		
		// display mysqliObj for verification
		$this -> debugClassObj -> print_var($this -> mysqliObj,'mysqliObj',__CLASS__,__LINE__); // 100
		
		// check for connection errors
		if ($this -> mysqliObj -> connect_errno) {
			// set error in $_SESSION to be handled at a later time.
			$_SESSION ['error'] ['db_connect'] = 
				"MySQL error " . $this -> mysqliObjObj -> errno . ": " . $this -> mysqliObj -> error . "<br>";

			$this -> debugClassObj -> message('return false',__CLASS__,__LINE__); // 107
			
			// set return value to false indicating a connection error
			$return = false;

		} else {
			// display notification of successfull connection			
			$this -> debugClassObj -> message('connected successfully',__CLASS__,__LINE__); // 114
			
			$this -> debugClassObj -> message('return true',__CLASS__,__LINE__); // 116

			// set return value to true indicating successful connection
			$return = true;
		
		}
		
		// see DEBUG in the readme
		$this -> debugClassObj -> foot(__METHOD__,$return);
		return $return;
	}
prepare()

Code: Select all

	protected function prepare () {
	/*
	 * this function prepares a statement from a query containing a WHERE clause
	 * 
	 * logical flow:
	 * 1) validate the where input
	 * 2) add WHERE to $query
	 * 3) add columns to $query
	 * 4) prepare the query and return a prepared statement object to mysqliStmtObj
	 * 5) bind parameters to mysqliStmtObj
	 */
		
		// see DEBUG in the readme
		$this -> debugClassObj -> set_mode(true);
		$this -> debugClassObj -> head(__METHOD__,__LINE__); // 568
		
		// validate WHERE input (this will be more scruitinous when the time comes)
		foreach ($this -> where as $k => $v) {
			$this -> debugClassObj -> print_var ($k,'$k',__CLASS__,__LINE__); // 572
			$this -> debugClassObj -> print_var ($v,'$v',__CLASS__,__LINE__); // 573
			
			// if either k or v are boolean false
			if (!($k) xor !($v)) {
				die ('$k or $v == false. '.__LINE__); // 577
			}
		}
		
		// add WHERE to $query
		$this -> query .= " WHERE ";
			
		// add columns to query		
		$i = 0;
		foreach ($this -> where as $k => $v) {
			if ($i > 0) {
				$this -> query .= "AND ";
			} 
			$this -> query .= "$k=?";
			$i++;
		}
		
		// display $query for verification
		$this -> debugClassObj -> print_var($this -> query,'query',__CLASS__,__LINE__); // 595
		
		// notification of prepare()
		$this -> debugClassObj -> message("mysqliStmtObj = mysqliObj -> prepare ({$this -> query})",__CLASS__,__LINE__); // 598

		// prepare the query and return a prepared statement object to mysqliStmtObj
		$this -> mysqliStmtObj = $this -> mysqliObj -> prepare ($this -> query) 
		or $this -> debugClassObj -> message("prepare() failed.",__CLASS__,__LINE__); // 602
		
		// I don't understand why I'm getting a -1 with no error or errno.
		// this bock checks for that scenario and displays a notification if detected.
		if ($this -> mysqliObj -> affected_rows == -1) {
			$this -> debugClassObj -> message("mysqliObj -> affected_rows == -1!  why is this??",__CLASS__,__LINE__); // 606
			
			// display mysqliObj and mysqliStmtObj
			$this -> debugClassObj -> print_var($this -> mysqliObj,'mysqliObj',__CLASS__,__LINE__); // 609
			$this -> debugClassObj -> print_var($this -> mysqliStmtObj,'mysqliStmtObj',__CLASS__,__LINE__); // 610
			
			$this -> debugClassObj -> message ('affected_rows == -1',__CLASS__,__LINE__); // 612
			
   		}
		
		// bind parameters to mysqliStmtObj
		$this -> bindParams ();
		
		// see DEBUG in the readme
		$this -> debugClassObj -> foot(__METHOD__,true);
	}
execute()

Code: Select all

	protected function execute () {
	/*
	 * This method executes the prepared statement
	 * 
	 * Logical flow:
	 * 1) execute the prepared statement
	 * 2) check for errors
	 * 3) store useable date into mysqliResultObj
	 * 4) frees mysqliStmtObj to allow for future queries
	 * 
	 */
		
		// see DEBUG in the readme
		$this -> debugClassObj -> set_mode(true);
		$this -> debugClassObj -> head(__METHOD__,__LINE__); // 444
		
		// display mysqliObj and mysqliStmtObj for verification
		$this -> debugClassObj -> print_var($this -> mysqliObj,'mysqliObj',__CLASS__,__LINE__); // 447
		$this -> debugClassObj -> print_var($this -> mysqliStmtObj,'mysqliStmtObj',__CLASS__,__LINE__); // 448
		$this -> debugClassObj -> message("execute()",__CLASS__,__LINE__); // 449
		
		// execute the prepared statement
		$this -> mysqliStmtObj -> execute(); // 452
		
		// display mysqliObj and mysqliStmtObj for verification
		$this -> debugClassObj -> print_var($this -> mysqliObj,'mysqliObj',__CLASS__,__LINE__); // 455
		$this -> debugClassObj -> print_var($this -> mysqliStmtObj,'mysqliStmtObj',__CLASS__,__LINE__); // 456
		
		// check for errors (work in progress)
		if ($this -> mysqliStmtObj -> affected_rows == -1) {
			$this -> debugClassObj -> message("mysqliStmtObj -> affected_rows == -1!  why is this??",__CLASS__,__LINE__); // 460
		}
		
		// store useable date into mysqliResultObj.
		// this may change, once i can finally get a non-NULL result set.
		$this -> mysqliResultObj = $this -> mysqliStmtObj -> result_metadata();
		
		// frees mysqliStmtObj to allow for future queries.
		$this -> mysqliStmtObj -> free_result();
		
		// see DEBUG in the readme
		$this -> debugClassObj -> foot(__METHOD__,true);
		
	}
my debugClass

Code: Select all

/* 
 * This class is used to expedite the process of displaying debug information
 */

class debugClass {
	
	private $mode = 0;
	
	function __construct($mode) {
		$this -> mode = $mode;

		$this -> head(__METHOD__,__LINE__,$mode);
		$this -> foot(__METHOD__,true);
	}
	
	function set_mode($mode){
		$this -> mode = $mode;

	}
	
	function print_var ($var,$name,$class,$line=NULL) {
		if ($this -> mode) {
			echo "<pre><b>$line: $class -> $name:</b><blockquote>";
			if ($var) {
				print_r ($var);
			} else {
				echo "false";
			}
			echo "</blockquote></pre>";
		}
	}
	
	///////////////////////////////////////////////////////////////////////////////////////
	
	function head($method,$line,$params=NULL) {
		if ($this -> mode) {
			$output = "<pre><b>$line: $method (";
			
			if (is_array($params)) {
				$i = 0;
				$count = count($params);
				foreach ($params as $v) {
					if ($i == 0) {
						$output .= "'$v'";
					} else {
						$output .= ", '$v'";
					}
					$i++;
				}
				
				if (substr_count($output,',') == 1) {
					str_replace(',','',$output);
				}
				
			} else {
				$output .= $params;
			}
			
			$output .= ") {</b></pre><blockquote>";
			
			echo $output;
		}
		
		return true;
	}
	
	///////////////////////////////////////////////////////////////////////////////////////

	function foot($method,$return) {
		if ($this -> mode) {
			echo "<pre><b>$method -> return $return</b></pre></blockquote><pre><b>}</b></pre>";
		}

	}
	
	///////////////////////////////////////////////////////////////////////////////////////
	
	function message ($message,$class,$line=null) {
		if ($this -> mode) {
			echo "<pre><b>$line: $class::message:</b><blockquote>$message</blockquote></pre>";
		}
			
	}
	
}
and here's my output that I get when I run the program in my browser:

Code: Select all

Accounts

156: accountClass::viewAct (1) {
230: accountClass::getAct (1) {
206: dbClass::select ('*', 'accounts', 'Array') {
512: dbClass::initialize () {
514: dbClass -> $this:
dbClass Object
(
    [debugClassObj:dbClass:private] => debugClass Object
        (
            [mode:debugClass:private] => 1
        )

    [mysqliObj:dbClass:private] => 
    [mysqliStmtObj:dbClass:private] => 
    [mysqliResultObj:dbClass:private] => mysqli_result Object
        (
            [current_field] => 0
            [field_count] => 6
            [lengths] => Array
                (
                    [0] => 4
                    [1] => 6
                    [2] => 2
                    [3] => 0
                    [4] => 0
                    [5] => 0
                )

            [num_rows] => 9
            [type] => 0
        )

    [query:dbClass:private] => SHOW COLUMNS FROM `account-locations`
    [columns:dbClass:private] => Array
        (
            [0] => id
            [1] => accountId
            [2] => name
            [3] => contactIds
            [4] => address
            [5] => city
            [6] => state
            [7] => zip
            [8] => zip4
        )

    [table:dbClass:private] => 
    [where:dbClass:private] => Array
        (
        )

    [rows:dbClass:private] => Array
        (
            [0] => Array
                (
                    [0] => id
                    [1] => int(5)
                    [2] => NO
                    [3] => PRI
                    [4] => 
                    [5] => auto_increment
                )

            [1] => Array
                (
                    [0] => accountId
                    [1] => int(5)
                    [2] => NO
                    [3] => 
                    [4] => 
                    [5] => 
                )

            [2] => Array
                (
                    [0] => name
                    [1] => varchar(16)
                    [2] => NO
                    [3] => 
                    [4] => 
                    [5] => 
                )

            [3] => Array
                (
                    [0] => contactIds
                    [1] => varchar(30)
                    [2] => NO
                    [3] => 
                    [4] => 
                    [5] => 
                )

            [4] => Array
                (
                    [0] => address
                    [1] => varchar(32)
                    [2] => NO
                    [3] => 
                    [4] => 
                    [5] => 
                )

            [5] => Array
                (
                    [0] => city
                    [1] => varchar(24)
                    [2] => NO
                    [3] => 
                    [4] => 
                    [5] => 
                )

            [6] => Array
                (
                    [0] => state
                    [1] => char(2)
                    [2] => NO
                    [3] => 
                    [4] => 
                    [5] => 
                )

            [7] => Array
                (
                    [0] => zip
                    [1] => int(5)
                    [2] => NO
                    [3] => 
                    [4] => 
                    [5] => 
                )

            [8] => Array
                (
                    [0] => zip4
                    [1] => int(4)
                    [2] => NO
                    [3] => 
                    [4] => 
                    [5] => 
                )

            [fields] => Array
                (
                    [0] => id
                    [1] => accountId
                    [2] => name
                    [3] => contactIds
                    [4] => address
                    [5] => city
                    [6] => state
                    [7] => zip
                    [8] => zip4
                )

        )

    [tmp:dbClass:private] => 
)
547: dbClass::message:
initialized
549: dbClass -> $this:
dbClass Object
(
    [debugClassObj:dbClass:private] => debugClass Object
        (
            [mode:debugClass:private] => 1
        )

    [mysqliObj:dbClass:private] => 
    [mysqliStmtObj:dbClass:private] => 
    [mysqliResultObj:dbClass:private] => 
    [query:dbClass:private] => 
    [columns:dbClass:private] => 
    [table:dbClass:private] => 
    [where:dbClass:private] => Array
        (
        )

    [rows:dbClass:private] => Array
        (
        )

    [tmp:dbClass:private] => 
)
dbClass::initialize -> return 1
}
220: dbClass -> query:
SELECT * FROM `accounts`
84: dbClass::connect () {
100: dbClass -> mysqliObj:
mysqli Object
(
    [affected_rows] => 0
    [client_info] => 5.0.27
    [client_version] => 50027
    [connect_errno] => 0
    [connect_error] => 
    [errno] => 0
    [error] => 
    [field_count] => 0
    [host_info] => Localhost via UNIX socket
    [info] => 
    [insert_id] => 0
    [server_info] => 5.1.41-3ubuntu12.3
    [server_version] => 50141
    [sqlstate] => 00000
    [protocol_version] => 10
    [thread_id] => 76
    [warning_count] => 0
)
114: dbClass::message:
connected successfully
116: dbClass::message:
return true
dbClass::connect -> return 1
}
568: dbClass::prepare () {
572: dbClass -> $k:
id
573: dbClass -> $v:
1
595: dbClass -> query:
SELECT * FROM `accounts` WHERE id=?
598: dbClass::message:
mysqliStmtObj = mysqliObj -> prepare (SELECT * FROM `accounts` WHERE id=?)
606: dbClass::message:
mysqliObj -> affected_rows == -1!  why is this??
609: dbClass -> mysqliObj:
mysqli Object
(
    [affected_rows] => -1
    [client_info] => 5.0.27
    [client_version] => 50027
    [connect_errno] => 0
    [connect_error] => 
    [errno] => 0
    [error] => 
    [field_count] => 0
    [host_info] => Localhost via UNIX socket
    [info] => 
    [insert_id] => 0
    [server_info] => 5.1.41-3ubuntu12.3
    [server_version] => 50141
    [sqlstate] => 00000
    [protocol_version] => 10
    [thread_id] => 76
    [warning_count] => 0
)
610: dbClass -> mysqliStmtObj:
mysqli_stmt Object
(
    [affected_rows] => 0
    [insert_id] => 0
    [num_rows] => 0
    [param_count] => 1
    [field_count] => 5
    [errno] => 0
    [error] => 
    [sqlstate] => 00000
    [id] => 1
)
612: dbClass::message:
affected_rows == -1
293: dbClass::bindParams () {
297: dbClass -> tmp ['params']:
Array
(
    [id] => 1
)
333: dbClass -> tmp['params']:
Array
(
    [id] => 1
)
355: dbClass -> mysqliObj:
mysqli Object
(
    [affected_rows] => -1
    [client_info] => 5.0.27
    [client_version] => 50027
    [connect_errno] => 0
    [connect_error] => 
    [errno] => 0
    [error] => 
    [field_count] => 0
    [host_info] => Localhost via UNIX socket
    [info] => 
    [insert_id] => 0
    [server_info] => 5.1.41-3ubuntu12.3
    [server_version] => 50141
    [sqlstate] => 00000
    [protocol_version] => 10
    [thread_id] => 76
    [warning_count] => 0
)
356: dbClass -> mysqlistmtObj:
mysqli_stmt Object
(
    [affected_rows] => 0
    [insert_id] => 0
    [num_rows] => 0
    [param_count] => 1
    [field_count] => 5
    [errno] => 0
    [error] => 
    [sqlstate] => 00000
    [id] => 1
)
dbClass::bindParams -> return 1
}
dbClass::prepare -> return 1
}
444: dbClass::execute () {
447: dbClass -> mysqliObj:
mysqli Object
(
    [affected_rows] => -1
    [client_info] => 5.0.27
    [client_version] => 50027
    [connect_errno] => 0
    [connect_error] => 
    [errno] => 0
    [error] => 
    [field_count] => 0
    [host_info] => Localhost via UNIX socket
    [info] => 
    [insert_id] => 0
    [server_info] => 5.1.41-3ubuntu12.3
    [server_version] => 50141
    [sqlstate] => 00000
    [protocol_version] => 10
    [thread_id] => 76
    [warning_count] => 0
)
448: dbClass -> mysqliStmtObj:
mysqli_stmt Object
(
    [affected_rows] => 0
    [insert_id] => 0
    [num_rows] => 0
    [param_count] => 1
    [field_count] => 5
    [errno] => 0
    [error] => 
    [sqlstate] => 00000
    [id] => 1
)
449: dbClass::message:
execute()
455: dbClass -> mysqliObj:
mysqli Object
(
    [affected_rows] => -1
    [client_info] => 5.0.27
    [client_version] => 50027
    [connect_errno] => 0
    [connect_error] => 
    [errno] => 0
    [error] => 
    [field_count] => 5
    [host_info] => Localhost via UNIX socket
    [info] => 
    [insert_id] => 0
    [server_info] => 5.1.41-3ubuntu12.3
    [server_version] => 50141
    [sqlstate] => 00000
    [protocol_version] => 10
    [thread_id] => 76
    [warning_count] => 0
)
456: dbClass -> mysqliStmtObj:
mysqli_stmt Object
(
    [affected_rows] => -1
    [insert_id] => 0
    [num_rows] => 0
    [param_count] => 1
    [field_count] => 5
    [errno] => 0
    [error] => 
    [sqlstate] => 00000
    [id] => 1
)
460: dbClass::message:
mysqliStmtObj -> affected_rows == -1!  why is this??
dbClass::execute -> return 1
}
486: dbClass::fetchData () {
494: dbClass -> rows:
false
496: dbClass -> mysqliObj:
mysqli Object
(
    [affected_rows] => -1
    [client_info] => 5.0.27
    [client_version] => 50027
    [connect_errno] => 0
    [connect_error] => 
    [errno] => 0
    [error] => 
    [field_count] => 5
    [host_info] => Localhost via UNIX socket
    [info] => 
    [insert_id] => 0
    [server_info] => 5.1.41-3ubuntu12.3
    [server_version] => 50141
    [sqlstate] => 00000
    [protocol_version] => 10
    [thread_id] => 76
    [warning_count] => 0
)
497: dbClass -> mysqliResultObj:
mysqli_result Object
(
    [current_field] => 0
    [field_count] => 5
    [lengths] => 
    [num_rows] => 0
    [type] => 0
)
dbClass::fetchData -> return 1
}
137: dbClass::disconnect () {
dbClass::disconnect -> return 1
}
dbClass::select -> return rows
}
accountClass::getAct -> return 1
}
174: accountClass::displayAct () {
View Account - Array

178: accountClass -> resultObj:
false
accountClass::displayAct -> return 1
}
accountClass::viewAct -> return 1
}
79: accountClass::__destruct () {
58: dbClass::__destruct () {
512: dbClass::initialize () {
514: dbClass -> $this:
dbClass Object
(
    [debugClassObj:dbClass:private] => debugClass Object
        (
            [mode:debugClass:private] => 1
        )

    [mysqliObj:dbClass:private] => 
    [mysqliStmtObj:dbClass:private] => mysqli_stmt Object
        (
            [affected_rows] => -1
            [insert_id] => 0
            [num_rows] => 0
            [param_count] => 1
            [field_count] => 5
            [errno] => 0
            [error] => 
            [sqlstate] => 00000
            [id] => 1
        )

    [mysqliResultObj:dbClass:private] => mysqli_result Object
        (
            [current_field] => 0
            [field_count] => 5
            [lengths] => 
            [num_rows] => 0
            [type] => 0
        )

    [query:dbClass:private] => SELECT * FROM `accounts` WHERE id=?
    [columns:dbClass:private] => *
    [table:dbClass:private] => accounts
    [where:dbClass:private] => Array
        (
            [id] => 1
        )

    [rows:dbClass:private] => Array
        (
        )

    [tmp:dbClass:private] => Array
        (
            [params] => Array
                (
                    [id] => 1
                )

        )

)
547: dbClass::message:
initialized
549: dbClass -> $this:
dbClass Object
(
    [debugClassObj:dbClass:private] => debugClass Object
        (
            [mode:debugClass:private] => 1
        )

    [mysqliObj:dbClass:private] => 
    [mysqliStmtObj:dbClass:private] => 
    [mysqliResultObj:dbClass:private] => 
    [query:dbClass:private] => 
    [columns:dbClass:private] => 
    [table:dbClass:private] => 
    [where:dbClass:private] => Array
        (
        )

    [rows:dbClass:private] => Array
        (
        )

    [tmp:dbClass:private] => 
)
dbClass::initialize -> return 1
}
137: dbClass::disconnect () {
dbClass::disconnect -> return 1
}
dbClass::__destruct -> return 1
}
accountClass::__destruct -> return 1
}

Re: mysqli affected_rows = -1, but error and errno are blank

Posted: Wed Jul 14, 2010 3:31 am
by elgranjeff
mmmk... here's some new code again (this problem has been a great excuse to simplify my code)

just like my last post: different code, same problem.

Select()

Code: Select all

	public function select($columns, $table, $where = NULL) { 
	/* 
	 * a smiplified SELECT query 
	 *  
	 * $columns is a string of columns to be selected separated by commas. 
	 * $table is the table from which the fields will be selected 
	 * 
	 * if the query includes a WHERE clause, $fields and $values are 
	 * strings of conditional fields and respective values to check.  
	 *  
	 */
		
		/* see debugClass */ 
		$this -> debugClassObj -> set_mode(true); 
		$this -> debugClassObj -> head(__METHOD__,__LINE__,array($columns,$table,$where)); /* 207 */
		
		/* initialize containers for request-data */ 
		$this -> initialize(); 
		
		/* create "local" storage of parameters */  
		$this -> columns = $columns; 
		$this -> table = $table; 
		$this -> where = $where; 
		
		/* initialize $query */ 
		$this -> query = 'SELECT '.$this -> columns.' FROM `'.$this -> table.'`';  
				
		/* output $query for verification */ 
		$this -> debugClassObj -> print_var($this -> query,'query',__CLASS__,__LINE__); /* 221 */
		
		$this -> connect(MYSQLI_CLIENT_FOUND_ROWS); /* 223 */
		
		if ($this -> where === NULL) {
			/* if there is no where clause, just run the query */ 
			$this -> query ($this -> query);  /* 227 */
			
		} else { 
			/* if where is set, prepare and execute */
			$this -> prepare (); /* 231 */
			$this -> execute (); /* 232 */
		}
		
		/* fetch the data */
		$this -> fetchData (); /* 236 */
		
		/* disconnect from database */
		$this -> disconnect(); /* 239 */
		
		/* see debugClass */
		$this -> debugClassObj -> foot(__METHOD__,'rows'); /* 242 */
		
		/* return $this -> rows */ 
		return $this -> rows;
	}
initialize()

Code: Select all

	protected function initialize() {
	/*
	 * This function resets all request-specific variable information
	 */
		
		/* see debugClass */ 
		$this -> debugClassObj -> set_mode(true);
		$this -> debugClassObj -> head(__METHOD__,__LINE__); /* 513 */
		
		$this -> debugClassObj -> print_var ($this,'$this',__CLASS__,__LINE__); /* 515 */
		
		foreach ($this as $k => &$v) {			
			switch ($k) {
				case 'mysqliStmtObj':
				case 'mysqliResultObj':
					if (is_object($v)) {
						$v -> close();
					}
					$v = NULL;
					break;
				case 'query':
				case 'columns':
				case 'table':
					$v = '';
					break;
	
				case 'where':
				case 'rows':
					$v = array();
					break;
				case 'tmp':
					$v = NULL;
					break;
					
			}
		}
		
		$this -> debugClassObj -> message ('initialized',__CLASS__,__LINE__); /* 543 */
		
		$this -> debugClassObj -> print_var ($this,'$this',__CLASS__,__LINE__); /* 545 */
		
		$this -> debugClassObj -> foot(__METHOD__,true);
	}
connect()

Code: Select all

	public function connect($flag=NULL) {
	/*
	 * this function is used to establish a database connection
	 * 
	 * $flag contains any flags to be used while connecting with real_connect()
	 * 
	 * Logical flow:
	 * 	1) establish database connection / populate mysqliObj
	 * 	2) check for connection errors
	 * 	3) return true on success / false on failure
	 */	
		
		/* see debugClass */ 
		$this -> debugClassObj -> set_mode(true);
		$this -> debugClassObj -> head(__METHOD__,__LINE__); /* 85 */ 
		
		if (!$flag) {
			/* estabish database connection and assign to mysqliObj */
			$this -> mysqliObj = new mysqli (MYSQL_HOST, MYSQL_USER, MYSQL_PASS, MYSQL_DB)
			or $this -> debugClassObj -> message('failed to connect to database',__CLASS__,__LINE__); /* 90 */ 
		} else {
			/* initialize mysqliObj for real_connect() */ 
			$this -> mysqliObj = mysqli_init();
		
			/* really connect (haha) */ 
			$this -> mysqliObj -> real_connect(MYSQL_HOST, MYSQL_USER, MYSQL_PASS, MYSQL_DB, MYSQL_PORT, MYSQL_SOCKET, $flag) 
			or $this -> debugClassObj -> message('failed to connect to database',__CLASS__,__LINE__);  /* 97 */ 
		}
		
		/* display mysqliObj for verification */ 
		$this -> debugClassObj -> print_var($this -> mysqliObj,'mysqliObj',__CLASS__,__LINE__); /* 103 */ 
		
		/* check for connection errors */ 
		if ($this -> mysqliObj -> connect_errno) {
			/* set error in $_SESSION to be handled at a later time. */ 
			$_SESSION ['error'] ['db_connect'] = 'MySQL error '.$this -> mysqliObjObj -> errno.': '.$this -> mysqliObj -> error.'<br>';

			$this -> debugClassObj -> message('return false',__CLASS__,__LINE__); /* 108 */ 
			
			/* set return value to false indicating a connection error */ 
			$return = false;

		} else {
			/* display notification of successfull connection */ 			
			$this -> debugClassObj -> message('connected successfully',__CLASS__,__LINE__); /* 115 */ 
			
			$this -> debugClassObj -> message('return true',__CLASS__,__LINE__); /* 117 */ 

			/* set return value to true indicating successful connection */ 
			$return = true;
		
		}
		
		/* see debugClass */ 
		$this -> debugClassObj -> foot(__METHOD__,$return);
		return $return;
	}
prepare()

Code: Select all

	protected function prepare () {
	/*
	 * this function prepares a statement from a query containing a WHERE clause
	 * 
	 * logical flow:
	 * 1) validate the where input
	 * 2) add WHERE to $query
	 * 3) add columns to $query
	 * 4) prepare the query and return a prepared statement object to mysqliStmtObj
	 * 5) bind parameters to mysqliStmtObj
	 */
		
		/* see debugClass */ 
		$this -> debugClassObj -> set_mode(true);
		$this -> debugClassObj -> head(__METHOD__,__LINE__); /* 566 */ 
		
		/* validate WHERE input (this will be more scruitinous when the time comes */ 
		foreach ($this -> where as $k => $v) {
			$this -> debugClassObj -> print_var ($k,'$k',__CLASS__,__LINE__); /* 570 */ 
			$this -> debugClassObj -> print_var ($v,'$v',__CLASS__,__LINE__); /* 571 */ 
			
			/* if either k or v are boolean false */ 
			if (!($k) xor !($v)) {
				die ('$k or $v == false. '.__LINE__); /* 575 */ 
			}
		}
		
		/* add WHERE to $query */ 
		$this -> query .= ' WHERE ';
			
		/* add columns to query */ 
		$i = 0;
		foreach ($this -> where as $k => $v) {
			if ($i > 0) {
				$this -> query .= 'AND ';
			} 
			$this -> query .= $k.' = ?';
			$i++;
		}
		
		/* display $query for verification */ 
		$this -> debugClassObj -> print_var($this -> query,'query',__CLASS__,__LINE__); /* 593 */ 
		
		$this -> debugClassObj -> message('mysqliStmtObj = mysqliObj -> stmt_init()',__CLASS__,__LINE__); /* 595 */ 
		$this -> mysqliStmtObj = $this -> mysqliObj -> stmt_init();
		
		/* display $mysqliStmtObj for verification */ 
		$this -> debugClassObj -> print_var($this -> mysqliStmtObj,'mysqliStmtObj',__CLASS__,__LINE__); /* 599 */ 
		
		/* notification of prepare() */ 
		$this -> debugClassObj -> message('mysqliStmtObj -> prepare ('.$this -> query.')',__CLASS__,__LINE__); /* 602 */ 
		
		/* prepare the query and return a prepared statement object to mysqliStmtObj */ 
		$this -> mysqliStmtObj -> prepare ($this -> query) or $this -> debugClassObj -> message('prepare() failed.',__CLASS__,__LINE__); /* 605 */ 
		
		/* I don't understand why I'm getting a -1 with no error or errno.
		 * this bock checks for that scenario and displays a notification if detected */ 
		if ($this -> mysqliObj -> affected_rows == -1) {
			$this -> debugClassObj -> message('mysqliObj -> affected_rows == -1!  why is this??',__CLASS__,__LINE__); /* 610 */ 
			
			/* display mysqliObj and mysqliStmtObj */ 
			$this -> debugClassObj -> print_var($this -> mysqliObj,'mysqliObj',__CLASS__,__LINE__); /* 613 */ 
			$this -> debugClassObj -> print_var($this -> mysqliStmtObj,'mysqliStmtObj',__CLASS__,__LINE__); /* 614 */ 
			
			$this -> debugClassObj -> message ('affected_rows == -1',__CLASS__,__LINE__); /* 616 */ 
			
   		}
		
		/* bind parameters to mysqliStmtObj */ 
		$this -> bindParams ();
		
		/* see debugClass */ 
		$this -> debugClassObj -> foot(__METHOD__,true);
	}
execute()

Code: Select all

	protected function execute () {
	/*
	 * This method executes the prepared statement
	 * 
	 * Logical flow:
	 * 1) execute the prepared statement
	 * 2) check for errors
	 * 3) store useable date into mysqliResultObj
	 * 4) frees mysqliStmtObj to allow for future queries
	 * 
	 */
		
		/* see debugClass */ 
		$this -> debugClassObj -> set_mode(true);
		$this -> debugClassObj -> head(__METHOD__,__LINE__); /* 445 */ 
		
		/* display mysqliObj and mysqliStmtObj for verification */ 
		$this -> debugClassObj -> print_var($this -> mysqliObj,'mysqliObj',__CLASS__,__LINE__); /* 448 */ 
		$this -> debugClassObj -> print_var($this -> mysqliStmtObj,'mysqliStmtObj',__CLASS__,__LINE__); /* 449 */ 
		$this -> debugClassObj -> message('execute()',__CLASS__,__LINE__); /* 450 */ 
		
		/* execute the prepared statement */
		$this -> mysqliStmtObj -> execute(); /* 453 */ 
		
		/* display mysqliObj and mysqliStmtObj for verification */ 
		$this -> debugClassObj -> print_var($this -> mysqliObj,'mysqliObj',__CLASS__,__LINE__); /* 456 */ 
		$this -> debugClassObj -> print_var($this -> mysqliStmtObj,'mysqliStmtObj',__CLASS__,__LINE__); /* 457 */ 
		
		/* check for errors (work in progress) */ 
		if ($this -> mysqliStmtObj -> affected_rows == -1) {
			$this -> debugClassObj -> message('mysqliStmtObj -> affected_rows == -1!  why is this??',__CLASS__,__LINE__); /* 461 */ 
		}
		
		/* store useable date into mysqliResultObj.
		 * this may change, once i can finally get a non-NULL result set. */ 
		$this -> mysqliResultObj = $this -> mysqliStmtObj -> result_metadata();
		
		/* frees mysqliStmtObj to allow for future queries. */ 
		$this -> mysqliStmtObj -> free_result();
		
		/* see debugClass */ 
		$this -> debugClassObj -> foot(__METHOD__,true);
		
	}
fetch_data()

Code: Select all

	protected function fetchData () {
	/*
	 * This method fetches data from mysqliResultObj
	 * 
	 * Logial flow:
	 *  1) 
	 */
		/* see debugClass */ 
		$this -> debugClassObj -> set_mode(1);
		$this -> debugClassObj -> head(__METHOD__,__LINE__); /* 487 */
		
		$this -> rows = array();
		
		for ($i=0; $i < $this->mysqliResultObj->num_rows; $i++) {
			$this -> rows[$i] = $this -> mysqliResultObj -> fetch_row();
		}
		
		$this -> debugClassObj -> print_var($this -> rows,'rows',__CLASS__,__LINE__); /* 495 */ 
		
		$this -> debugClassObj -> print_var($this -> mysqliObj,'mysqliObj',__CLASS__,__LINE__); /* 497 */
		$this -> debugClassObj -> print_var($this -> mysqliResultObj,'mysqliResultObj',__CLASS__,__LINE__); /* 498 */
		
		/* see debugClass */ 
		$this -> debugClassObj -> foot(__METHOD__,true);
	}
disconnect()

Code: Select all

	public function disconnect() {
	/*
	 * this function is used to terminate a database connection.
	 */
		/* see debugClass */ 
		$this -> debugClassObj -> set_mode(true);
		$this -> debugClassObj -> head(__METHOD__,__LINE__); /* 137 */
		
		if (is_object($this -> mysqliObj)) {
			$this -> mysqliObj -> close();
		} 
		
		$this -> mysqliObj = NULL;
		
		/* see debugClass */ 
		$this -> debugClassObj -> foot(__METHOD__,true);
		return true;
	}
debugClass

Code: Select all

class debugClass {
	
	private $mode = 0;
	
	function __construct($mode) {
		$this -> mode = $mode;

		$this -> head(__METHOD__,__LINE__,$mode);
		$this -> foot(__METHOD__,true);
	}
	
	function set_mode($mode){
		$this -> mode = $mode;

	}
	
	function print_var ($var,$name,$class,$line=NULL) {
		if ($this -> mode) {
			echo '<pre><b>',$line,': ',$class,' -> ',$name,':</b><blockquote>';
			if ($var) {
				print_r ($var);
			} else {
				echo 'false';
			}
			echo '</blockquote></pre>';
		}
	}
	
	///////////////////////////////////////////////////////////////////////////////////////
	
	function head($method,$line,$params=NULL) {
		if ($this -> mode) {
			echo '<pre><b>',$line,': ',$method,' (';
			
			if (is_array($params)) {
				$i = 0;
				$count = count($params);
				foreach ($params as $v) {
					if ($i == 0) {
						if (is_array($v)) {
							print_r($v);
						} else {
							echo '"',$v,'"';
						}
					} else {
						if (is_array($v)) {
							echo ",\n";
							print_r($v);
						} else {
							echo ', "',$v,'"';
						}
					}
					$i++;
				}
				
			} else {
				echo $params;
			}
			
			echo ') {</b></pre><blockquote>';
		}
		
		return true;
	}
	
	///////////////////////////////////////////////////////////////////////////////////////

	function foot($method,$return) {
		if ($this -> mode) {
			echo '<pre><b>',$method,' -> return ',$return,'</b></pre></blockquote><pre><b>}</b></pre>';
		}

	}
	
	///////////////////////////////////////////////////////////////////////////////////////
	
	function message ($message,$class,$line=null) {
		if ($this -> mode) {
			echo '<pre><b>',$line,': ',$class,'::message:</b><blockquote>',$message,'</blockquote></pre>';
		}
			
	}
	
}
And last, but not least, my output.

Code: Select all

207: dbClass::select ("*", "accounts",
Array
(
    [id] => 1
)
) {
513: dbClass::initialize () {
515: dbClass -> $this:
dbClass Object
(
    [debugClassObj:dbClass:private] => debugClass Object
        (
            [mode:debugClass:private] => 1
        )

    [mysqliObj:dbClass:private] => 
    [mysqliStmtObj:dbClass:private] => 
    [mysqliResultObj:dbClass:private] => mysqli_result Object
        (
            [current_field] => 0
            [field_count] => 6
            [lengths] => Array
                (
                    [0] => 4
                    [1] => 6
                    [2] => 2
                    [3] => 0
                    [4] => 0
                    [5] => 0
                )

            [num_rows] => 9
            [type] => 0
        )

    [query:dbClass:private] => SHOW COLUMNS FROM `account-locations`
    [columns:dbClass:private] => Array
        (
            [0] => id
            [1] => accountId
            [2] => name
            [3] => contactIds
            [4] => address
            [5] => city
            [6] => state
            [7] => zip
            [8] => zip4
        )

    [table:dbClass:private] => 
    [where:dbClass:private] => Array
        (
        )

    [rows:dbClass:private] => Array
        (
            [0] => Array
                (
                    [0] => id
                    [1] => int(5)
                    [2] => NO
                    [3] => PRI
                    [4] => 
                    [5] => auto_increment
                )

            [1] => Array
                (
                    [0] => accountId
                    [1] => int(5)
                    [2] => NO
                    [3] => 
                    [4] => 
                    [5] => 
                )

            [2] => Array
                (
                    [0] => name
                    [1] => varchar(16)
                    [2] => NO
                    [3] => 
                    [4] => 
                    [5] => 
                )

            [3] => Array
                (
                    [0] => contactIds
                    [1] => varchar(30)
                    [2] => NO
                    [3] => 
                    [4] => 
                    [5] => 
                )

            [4] => Array
                (
                    [0] => address
                    [1] => varchar(32)
                    [2] => NO
                    [3] => 
                    [4] => 
                    [5] => 
                )

            [5] => Array
                (
                    [0] => city
                    [1] => varchar(24)
                    [2] => NO
                    [3] => 
                    [4] => 
                    [5] => 
                )

            [6] => Array
                (
                    [0] => state
                    [1] => char(2)
                    [2] => NO
                    [3] => 
                    [4] => 
                    [5] => 
                )

            [7] => Array
                (
                    [0] => zip
                    [1] => int(5)
                    [2] => NO
                    [3] => 
                    [4] => 
                    [5] => 
                )

            [8] => Array
                (
                    [0] => zip4
                    [1] => int(4)
                    [2] => NO
                    [3] => 
                    [4] => 
                    [5] => 
                )

            [fields] => Array
                (
                    [0] => id
                    [1] => accountId
                    [2] => name
                    [3] => contactIds
                    [4] => address
                    [5] => city
                    [6] => state
                    [7] => zip
                    [8] => zip4
                )

        )

    [tmp:dbClass:private] => 
)
543: dbClass::message:
initialized
545: dbClass -> $this:
dbClass Object
(
    [debugClassObj:dbClass:private] => debugClass Object
        (
            [mode:debugClass:private] => 1
        )

    [mysqliObj:dbClass:private] => 
    [mysqliStmtObj:dbClass:private] => 
    [mysqliResultObj:dbClass:private] => 
    [query:dbClass:private] => 
    [columns:dbClass:private] => 
    [table:dbClass:private] => 
    [where:dbClass:private] => Array
        (
        )

    [rows:dbClass:private] => Array
        (
        )

    [tmp:dbClass:private] => 
)
dbClass::initialize -> return 1
}
221: dbClass -> query:
SELECT * FROM `accounts`
85: dbClass::connect () {
101: dbClass -> mysqliObj:
mysqli Object
(
    [affected_rows] => 0
    [client_info] => 5.0.27
    [client_version] => 50027
    [connect_errno] => 0
    [connect_error] => 
    [errno] => 0
    [error] => 
    [field_count] => 0
    [host_info] => Localhost via UNIX socket
    [info] => 
    [insert_id] => 0
    [server_info] => 5.1.41-3ubuntu12.3
    [server_version] => 50141
    [sqlstate] => 00000
    [protocol_version] => 10
    [thread_id] => 90
    [warning_count] => 0
)
115: dbClass::message:
connected successfully
117: dbClass::message:
return true
dbClass::connect -> return 1
}
566: dbClass::prepare () {
570: dbClass -> $k:
id
571: dbClass -> $v:
1
593: dbClass -> query:
SELECT * FROM `accounts` WHERE id = ?
595: dbClass::message:
mysqliStmtObj = mysqliObj -> stmt_init()
599: dbClass -> mysqliStmtObj:
mysqli_stmt Object
OH SNAP

Warning:  print_r() [function.print-r.html]: Property access is not allowed yet in /home/jeff/www/techjeff/include/classes/debug_class.php on line 32

OH SNAP

Warning:  print_r() [function.print-r.html]: Property access is not allowed yet in /home/jeff/www/techjeff/include/classes/debug_class.php on line 32

OH SNAP

Warning:  print_r() [function.print-r.html]: Property access is not allowed yet in /home/jeff/www/techjeff/include/classes/debug_class.php on line 32

OH SNAP

Warning:  print_r() [function.print-r.html]: Property access is not allowed yet in /home/jeff/www/techjeff/include/classes/debug_class.php on line 32

OH SNAP

Warning:  print_r() [function.print-r.html]: Property access is not allowed yet in /home/jeff/www/techjeff/include/classes/debug_class.php on line 32

OH SNAP

Warning:  print_r() [function.print-r.html]: Property access is not allowed yet in /home/jeff/www/techjeff/include/classes/debug_class.php on line 32

(
    [affected_rows] => 
    [insert_id] => 
    [num_rows] => 
    [param_count] => 
    [field_count] => 
    [errno] => 0
    [error] => 
    [sqlstate] => 00000
    [id] => 
)
602: dbClass::message:
mysqliStmtObj -> prepare (SELECT * FROM `accounts` WHERE id = ?)
610: dbClass::message:
mysqliObj -> affected_rows == -1!  why is this??
613: dbClass -> mysqliObj:
mysqli Object
(
    [affected_rows] => -1
    [client_info] => 5.0.27
    [client_version] => 50027
    [connect_errno] => 0
    [connect_error] => 
    [errno] => 0
    [error] => 
    [field_count] => 0
    [host_info] => Localhost via UNIX socket
    [info] => 
    [insert_id] => 0
    [server_info] => 5.1.41-3ubuntu12.3
    [server_version] => 50141
    [sqlstate] => 00000
    [protocol_version] => 10
    [thread_id] => 90
    [warning_count] => 0
)
614: dbClass -> mysqliStmtObj:
mysqli_stmt Object
(
    [affected_rows] => 0
    [insert_id] => 0
    [num_rows] => 0
    [param_count] => 1
    [field_count] => 5
    [errno] => 0
    [error] => 
    [sqlstate] => 00000
    [id] => 1
)
616: dbClass::message:
affected_rows == -1
356: dbClass -> mysqliObj:
mysqli Object
(
    [affected_rows] => -1
    [client_info] => 5.0.27
    [client_version] => 50027
    [connect_errno] => 0
    [connect_error] => 
    [errno] => 0
    [error] => 
    [field_count] => 0
    [host_info] => Localhost via UNIX socket
    [info] => 
    [insert_id] => 0
    [server_info] => 5.1.41-3ubuntu12.3
    [server_version] => 50141
    [sqlstate] => 00000
    [protocol_version] => 10
    [thread_id] => 90
    [warning_count] => 0
)
357: dbClass -> mysqlistmtObj:
mysqli_stmt Object
(
    [affected_rows] => 0
    [insert_id] => 0
    [num_rows] => 0
    [param_count] => 1
    [field_count] => 5
    [errno] => 0
    [error] => 
    [sqlstate] => 00000
    [id] => 1
)
dbClass::bindParams -> return 1
}
dbClass::prepare -> return 1
}
445: dbClass::execute () {
448: dbClass -> mysqliObj:
mysqli Object
(
    [affected_rows] => -1
    [client_info] => 5.0.27
    [client_version] => 50027
    [connect_errno] => 0
    [connect_error] => 
    [errno] => 0
    [error] => 
    [field_count] => 0
    [host_info] => Localhost via UNIX socket
    [info] => 
    [insert_id] => 0
    [server_info] => 5.1.41-3ubuntu12.3
    [server_version] => 50141
    [sqlstate] => 00000
    [protocol_version] => 10
    [thread_id] => 90
    [warning_count] => 0
)
449: dbClass -> mysqliStmtObj:
mysqli_stmt Object
(
    [affected_rows] => 0
    [insert_id] => 0
    [num_rows] => 0
    [param_count] => 1
    [field_count] => 5
    [errno] => 0
    [error] => 
    [sqlstate] => 00000
    [id] => 1
)
450: dbClass::message:
execute()
456: dbClass -> mysqliObj:
mysqli Object
(
    [affected_rows] => -1
    [client_info] => 5.0.27
    [client_version] => 50027
    [connect_errno] => 0
    [connect_error] => 
    [errno] => 0
    [error] => 
    [field_count] => 0
    [host_info] => Localhost via UNIX socket
    [info] => 
    [insert_id] => 0
    [server_info] => 5.1.41-3ubuntu12.3
    [server_version] => 50141
    [sqlstate] => 00000
    [protocol_version] => 10
    [thread_id] => 90
    [warning_count] => 0
)
457: dbClass -> mysqliStmtObj:
mysqli_stmt Object
(
    [affected_rows] => 0
    [insert_id] => 0
    [num_rows] => 0
    [param_count] => 1
    [field_count] => 5
    [errno] => 2031
    [error] => No data supplied for parameters in prepared statement
    [sqlstate] => HY000
    [id] => 1
)
dbClass::execute -> return 1
}
487: dbClass::fetchData () {
495: dbClass -> rows:
false
497: dbClass -> mysqliObj:
mysqli Object
(
    [affected_rows] => -1
    [client_info] => 5.0.27
    [client_version] => 50027
    [connect_errno] => 0
    [connect_error] => 
    [errno] => 0
    [error] => 
    [field_count] => 0
    [host_info] => Localhost via UNIX socket
    [info] => 
    [insert_id] => 0
    [server_info] => 5.1.41-3ubuntu12.3
    [server_version] => 50141
    [sqlstate] => 00000
    [protocol_version] => 10
    [thread_id] => 90
    [warning_count] => 0
)
498: dbClass -> mysqliResultObj:
mysqli_result Object
(
    [current_field] => 0
    [field_count] => 5
    [lengths] => 
    [num_rows] => 0
    [type] => 0
)
dbClass::fetchData -> return 1
}
137: dbClass::disconnect () {
dbClass::disconnect -> return 1
}
dbClass::select -> return rows
}

Re: mysqli affected_rows = -1, but error and errno are blank

Posted: Mon Jul 19, 2010 3:13 pm
by elgranjeff
In case anyone is curious, I finally decided to put this problem on hold and develop a non-prepared statement form of talking to the DB for ease of coding and to stop delaying the rest of my work for the project. I would prefer to get it working with prepared statements eventually as they're more secure and much more efficient when running multiple queries with varying conditions, so for the sake of education, logic, and intuitiveness, any ideas are still appreciated.

how about this: If you use prepared statements, would you please show me (brief pseudo-code) how you go about it? Perhaps I'll notice something I never thought of.

Thanks y'all :)

Re: mysqli affected_rows = -1

Posted: Sun Apr 24, 2011 2:30 pm
by elgranjeff
okay, so now after quite a long time of working around this problem, I recently decided to give it another whack.

I took all of the information out of the class-object context and created a separate file in which to test my code.

there query is built dynamically and works great when replacing the ?s with actual values, but something is keeping my statement from preparing correctly, though I do not know what. I have suspicions that it might be a version incompatibility between ZendServer CE, php 5.3, or perhaps a package dependancy is unmet? I am running all this on Ubuntu Desktop 10.10 64-bit...

here's the code sans the dynamic query building stuff (my debug output clearly displays the query string that is built) I hope someone has an idea or two.

Thanks again for reading!

Code: Select all

$debug -> printVar($sql,'$sql',__LINE__,__FILE__);
$debug -> printVar($stmt,'$stmt - about to prepare statement',__LINE__,__FILE__);
$debug -> printVar($mysqli,'$mysqli',__LINE__,__FILE__);

$stmt = $mysqli -> prepare ($sql) or die('lame sauce');

$debug -> printVar($stmt,'$stmt - prepared',__LINE__,__FILE__);
$debug -> printVar($mysqli,'$mysqli',__LINE__,__FILE__);


[text]
/home/jeff/www/pub/test.php - 141: sql
SELECT `id`, `name`, `website`, `dateCreate` FROM `techjeff`.`accounts` WHERE `id` = ?
/home/jeff/www/smallbusyness/pub/test.php - 140: $stmt - about to prepare statement
/home/jeff/www/pub/test.php - 142: $mysqli
mysqli Object
(
[affected_rows] => 7
[client_info] => 5.0.27
[client_version] => 50027
[connect_errno] => 0
[connect_error] =>
[errno] => 0
[error] =>
[field_count] => 6
[host_info] => Localhost via UNIX socket
[info] =>
[insert_id] => 0
[server_info] => 5.1.49-1ubuntu8.1
[server_version] => 50149
[sqlstate] => 00000
[protocol_version] => 10
[thread_id] => 231
[warning_count] => 0
)
/home/jeff/www/pub/test.php - 146: $stmt - prepared
mysqli_stmt Object
(
[affected_rows] => 0
[insert_id] => 0
[num_rows] => 0
[param_count] => 1
[field_count] => 7
[errno] => 0
[error] =>
[sqlstate] => 00000
[id] => 1
)
/home/jeff/www/pub/test.php - 147: $mysqli
mysqli Object
(
[affected_rows] => -1
[client_info] => 5.0.27
[client_version] => 50027
[connect_errno] => 0
[connect_error] =>
[errno] => 0
[error] =>
[field_count] => 6
[host_info] => Localhost via UNIX socket
[info] =>
[insert_id] => 0
[server_info] => 5.1.49-1ubuntu8.1
[server_version] => 50149
[sqlstate] => 00000
[protocol_version] => 10
[thread_id] => 231
[warning_count] => 0
)
[/text]

Re: mysqli affected_rows = -1, but error and errno are blank

Posted: Tue Apr 26, 2011 2:53 am
by elgranjeff
i would like to make my situation and my questions more terse.

I am a freelance coder who would like to understand how to use prepared statements for obvious reasons of security and efficiency.

when I attempt to prepare a simple SELECT statement, $mysqli -> affected_rows is set to -1 from 0. i have checked the php manual for MySQLi::affected_rows which says that SELECT statements will set affected_rows to -1 if there is a query error.

http://php.net/mysqli_affected_rows says:
"...
Returns the number of rows affected by the last INSERT, UPDATE, REPLACE or DELETE query.
For SELECT statements mysqli_affected_rows() works like mysqli_num_rows().

--Return Values--
An integer greater than zero indicates the number of rows affected or retrieved. Zero indicates that no records where updated for an UPDATE statement, no rows matched the WHERE clause in the query or that no query has yet been executed. -1 indicates that the query returned an error.
..."

I enabled mysql.trace in my php.ini file. does this have any effect when using mysli? i don't think its even necessary as the objects store their last errors..

are there any errors in my sql? syntax appears correct as I have run the query in phpmyadmin with real id values.
do you see any obvious errors in my logic or am I overlooking anything? i have checked the order and logical
do you suppose this is a configuration problem, and if so, which do you suppose is most likely? (php, mysql, apache, etc.)
perhaps there are other resources which may help me resolve this problem such as another forum, another site, or a reference i have overlooked.

I'm starting to feel desperate and I really would like to be able to both get this to work and understand how. frankly i feel stumped :banghead:

ANY help, advice, resources and/or constructive criticism is greatly appreciated. Thank you all for your time,

Jeff

here's the `accounts` table:
[text]
id smallint(5) unsigned NO PRI auto_increment
name varchar(40) NO
website varchar(64) NO
dateCreate timestamp NO DATETIME
[/text]
here's test.php:

Code: Select all

define ('MYSQL_HOST' 	, "localhost");
define ('MYSQL_USER' 	, "username");
define ('MYSQL_PASS' 	, "password");
define ('MYSQL_DB' 		, "techjeff");
	
error_reporting(E_ALL);

require '/var/www/classes/debug.class.php';
$debug = new Debug();

/* estabish database connection and assign to mysqli */
$mysqli = new mysqli (MYSQL_HOST, MYSQL_USER, MYSQL_PASS, MYSQL_DB)
	or die('failed to connect to database');
	 
$sql = "SELECT 'name' FROM `techjeff` . `accounts` WHERE 'id' = ?";

$debug -> printVar($mysqli, '$mysqli',__LINE__,__FILE__);
echo 'preparing...<br>';

$stmt = $mysqli -> prepare($sql);

$debug -> printVar($mysqli, '$mysqli',__LINE__,__FILE__);
$debug -> printVar($stmt, '$stmt',__LINE__,__FILE__);

echo 'binding parameters...<br>';

$id = 3;
$stmt -> bind_param('i', $id);

$debug -> printVar($mysqli, '$mysqli',__LINE__,__FILE__);
$debug -> printVar($stmt, '$stmt',__LINE__,__FILE__);

echo 'executing...<br>';

$stmt -> execute();

$debug -> printVar($mysqli, '$mysqli',__LINE__,__FILE__);
$debug -> printVar($stmt, '$stmt',__LINE__,__FILE__);

echo 'binding results...<br>';

$col1 = null;

$stmt -> bind_result($col1);

while($stmt->fetch()){
	$debug -> printVar ($col1,'$col1',__LINE__,__FILE__);	
}

$stmt->close();
here's Debug::printVar()

Code: Select all

class Debug {
...
	public function printVar ($var,$name,$line=false,$file=false) {
		echo '<pre>',$file,' - ',$line,': ',$name,'<blockquote>';
		if (is_string($var)) {
			echo "$var";
		} else {
			print_r ($var);
		}
		echo '</blockquote></pre>';
		
	}
...
}
and here's the output with the updated code:
[text]
/var/www/pub/test.php - 19: $mysqli
mysqli Object
(
[affected_rows] => 0
[client_info] => 5.1.49
[client_version] => 50149
[connect_errno] => 0
[connect_error] =>
[errno] => 0
[error] =>
[field_count] => 0
[host_info] => Localhost via UNIX socket
[info] =>
[insert_id] => 0
[server_info] => 5.1.49-1ubuntu8.1
[server_version] => 50149
[sqlstate] => 00000
[protocol_version] => 10
[thread_id] => 170
[warning_count] => 0
)

preparing...

/var/www/pub/test.php - 24: $mysqli
mysqli Object
(
[affected_rows] => -1
[client_info] => 5.1.49
[client_version] => 50149
[connect_errno] => 0
[connect_error] =>
[errno] => 0
[error] =>
[field_count] => 0
[host_info] => Localhost via UNIX socket
[info] =>
[insert_id] => 0
[server_info] => 5.1.49-1ubuntu8.1
[server_version] => 50149
[sqlstate] => 00000
[protocol_version] => 10
[thread_id] => 170
[warning_count] => 0
)

/var/www/pub/test.php - 25: $stmt
mysqli_stmt Object
(
[affected_rows] => 0
[insert_id] => 0
[num_rows] => 0
[param_count] => 1
[field_count] => 1
[errno] => 0
[error] =>
[sqlstate] => 00000
[id] => 1
)

binding parameters...

/var/www/pub/test.php - 32: $mysqli
mysqli Object
(
[affected_rows] => -1
[client_info] => 5.1.49
[client_version] => 50149
[connect_errno] => 0
[connect_error] =>
[errno] => 0
[error] =>
[field_count] => 0
[host_info] => Localhost via UNIX socket
[info] =>
[insert_id] => 0
[server_info] => 5.1.49-1ubuntu8.1
[server_version] => 50149
[sqlstate] => 00000
[protocol_version] => 10
[thread_id] => 170
[warning_count] => 0
)

/var/www/pub/test.php - 33: $stmt
mysqli_stmt Object
(
[affected_rows] => 0
[insert_id] => 0
[num_rows] => 0
[param_count] => 1
[field_count] => 1
[errno] => 0
[error] =>
[sqlstate] => 00000
[id] => 1
)

executing...

/var/www/pub/test.php - 39: $mysqli
mysqli Object
(
[affected_rows] => -1
[client_info] => 5.1.49
[client_version] => 50149
[connect_errno] => 0
[connect_error] =>
[errno] => 0
[error] =>
[field_count] => 1
[host_info] => Localhost via UNIX socket
[info] =>
[insert_id] => 0
[server_info] => 5.1.49-1ubuntu8.1
[server_version] => 50149
[sqlstate] => 00000
[protocol_version] => 10
[thread_id] => 170
[warning_count] => 1
)

/var/www/pub/test.php - 40: $stmt
mysqli_stmt Object
(
[affected_rows] => -1
[insert_id] => 0
[num_rows] => 0
[param_count] => 1
[field_count] => 1
[errno] => 0
[error] =>
[sqlstate] => 00000
[id] => 1
)

binding results...
[/text]

Re: mysqli affected_rows = -1, but error and errno are blank

Posted: Mon Aug 22, 2011 1:33 pm
by pdavila
Hi Jeff,

You need to use store_results(). For SELECT statements mysqli_affected_rows works just like mysqli_num_rows. If you use store_results(), you get the number of rows selected. If you don't, you get that -1.

best,
Pedro

// Connect to the database
$mysqli = new mysqli($host, $user, $pass, $db);

$sql = "SELECT * FROM table ";

$stmt = $mysqli->prepare($sql);
$stmt->execute();

$stmt->store_result();
$rows = $stmt->num_rows;

echo "Num Rows: $rows Affected Rows: " . $stmt->affected_rows . "<br>";