Mysqli_query not returning false on fail

I have a php script - see below.
And a mysql database with a table with one row in it with 4 fields.
Database = ftest
table = ftable1
urn f1 f2 f3
1 new F1 this is f2 this is f3

if I execute this query
update ftable1 SET f1= ‘new F1b’ WHERE urn = ‘2’
then it should fail because there is no row with urn=2
It does fail in that no line is added and the only existing row where urn = 1 is not changed but the
if ($result==FALSE) statement is never executed

<?php

// Remember use searh [^\x00-\x7F]+ and replace with spaces
// Key things to note
// in MYSQLI when doing a query, one has to include the connection
// in MYSQL if you only had one connection then the connection was implied and not specifically required.
// 
//https://prittytimes.com/difference-between-mysql-vs-mysqli-in-php/
//  
//  mysqli Function                         mysql Function
//  mysqli_real_escape_string($conn,$data) 	mysql_real_escape_string($data)
//  mysqli_errno($con) 	                    mysql_errno() or mysql_errno($cxn)
//* mysqli_select_db($con,$dbname) 	        mysql_select_db($dbname)
//  mysqli_error($con) 	                    mysql_error() or mysql_error($con)
//*  mysqli_query($con,$sql) 	            mysql_query($sql) or mysql_query($sql,$con)
//  mysqli_fetch_array($result) 	        mysql_fetch_array($result)
//  mysqli_num_rows($result) 	            mysql_num_rows($result)
//  mysqli_fetch_assoc($result) 	        mysql_fetch_assoc($result)
//  mysqli_fetch_row($result) 	            mysql_fetch_row($result)
//  mysqli_insert_id($con) 	                mysql_insert_id($con)
// 
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
echo "Hello";
$link = db_connect();
$row = get_row_from_table($link, 'ftable1', 'urn', '1');
echo "\n<BR>Before";
print_array_recursive($row);

$var_array = ['f1' => 'new F1b'];
update_table($link, 'ftable1', 'urn', '2',$var_array);
echo "\n<BR>After";
$row = get_row_from_table($link, 'ftable1', 'urn', '2');
print_array_recursive($row);

function update_table($link, $table_name,$ufn,$ud,$var_array)
{
	// This is a generalised update table function
	// The name of the unique column is contained in the variable $ufn.
	//    so normally $ufn="urn" and
	//    $ud is the unique record data and so normally $ud=$urn
	// $var_array is an associative array of key value pairs where
	// the keys are the field names and the values is the data to be stored.
	
	// 1 GET EXISTING DATA
	$row = get_row_from_table($link, $table_name, $ufn, $ud);
	// 2 BUILD THE QUERY
	$query = "update $table_name SET";  // first bit of the query string
	foreach($var_array as $key => $value)
		{
		$query = $query." ".$key."= '".$value."'";   
		};
	
	$query = $query." WHERE $ufn = '$ud'";
	
	echo $query;
	$result = mysqli_query($link, $query);
// https://www.php.net/manual/en/mysqli.query.php
// Returns false on failure. 
// For successful queries which produce a result set, such as SELECT, SHOW, DESCRIBE or EXPLAIN, mysqli_query() 
// will return a mysqli_result object. 
// For other successful queries, mysqli_query() will return true. 

	if ($result==FALSE)
		{
		echo "<br>query did not happen result is false<br>";
		echo "<BR>query_update= $query_update";
		echo "<BR>result_update= $result_update";
		echo "<BR>mysql error no= ".mysqli_errno().": ".mysqli_error()."<BR>";
		};
	
	
	
	
	
};




function get_row_from_table($link, $table_name, $ufn, $ud)
{
// This function searches $table_name 
// The where clause is "WHERE $ufn = '$ud'";
// The row is returned as an associative array of fieldname => data
$query = "SELECT *
			FROM $table_name
			WHERE $ufn = '$ud'
			";
			
$result = mysqli_query($link, $query);
// Returns false on failure. 
// For successful queries which produce a result set, such as SELECT, SHOW, DESCRIBE or EXPLAIN, mysqli_query() 
// will return a mysqli_result object. 
// For other successful queries, mysqli_query() will return true. 
if ($result==FALSE)
		{
		echo "<br>query did not happen result is false<br>";
		echo "<BR>query_update= $query_update";
		echo "<BR>result_update= $result_update";
		echo "<BR>mysql error no= ".mysqli_errno().": ".mysqli_error()."<BR>";
		};
$row = mysqli_fetch_assoc($result);
return $row;
};

function db_connect()
{
//https://www.php.net/manual/en/mysqli.construct.php
/* You should enable error reporting for mysqli before attempting to make a connection */
//mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);



$mysqli = mysqli_connect('localhost', 'root', '', 'ftest');


if (mysqli_connect_errno()) {
 throw new RuntimeException('mysqli connection error: ' . mysqli_connect_error());
}

/* Set the desired charset after establishing a connection */
mysqli_set_charset($mysqli, 'utf8mb4');
if (mysqli_errno($mysqli)) {
 throw new RuntimeException('mysqli error: ' . mysqli_error($mysqli));
}
return $mysqli;
};



function print_array_recursive($myarray)
{
if (is_array($myarray))
	{
	echo "<TABLE border=1>";
	foreach($myarray as $key => $value)
		{
		echo "\n<TR>";
		echo "<TD> $key </TD><TD>";
		if (is_array($value))
			{
			echo "<B>Array</B>";
			print_array_recursive($value);  // call recursively
			}
		else
			{
			echo "$value";
			};
		echo "</TD>";
		echo "\n</TR>";
		};
	echo "</TABLE>";
	}
else
	{
	echo "$myarray is not an array $myarray";
	};
};


?>

A query() call that executes without any sql error, but doesn’t match a row, e.g. a false WHERE statement, isn’t an error and returns a true value.

You will only get a false value returned for sql errors and since you are using exceptions for database statement errors, due to the mysqli_report(…) setting, the in-line code testing if ($result==FALSE) will never be true since php code execution will transfer to the nearest correct type of exception handler, or to php’s uncaught exception handler, if there’s none in your code.

If you want to test if a successfully executed insert/update query actually changed the row of data, you can check the number of affected rows (if you update the data to the exact same values, the data isn’t actually written back to the database and the number of affected rows will be zero .)

I’m not sure what learning resource you are using, but this code is not secure (any sql special character in a data value will break the sql query syntax, which is how sql injection is accomplished, you should use prepared queries when supplying external, unknown, dynamic values to a query when it gets executed), is not actually general-purpose (the table, and column names could be anything at the point of dynamically building the sql query statements and should be enclosed in back-ticks), and the throwing of your own exception in the connection code is unnecessary, won’t actually get executed due to the mysqli_report(…) setting, and if it did get executed, by only included the error message, you are loosing the the other error related information, such as the file name and line number.

As stated in your other recent thread, when using exceptions for database statement errors, the only time you should have any error handling logic in your code is for user recoverable errors. In this code, that would only be for the UPDATE query IF any of the columns are defined as being unique indexes and a duplicate value in any of them is an application error.

Also, the code dynamically building the update query isn’t inserting a coma between the SET col=‘value’ ,col=‘value’ terms. I recommend that you add each term into an array, then implode the array with the ‘,’ (coma) between the terms. This will work for any number of terms.

Commas issues - Ahh thank you for that.

WRT SQL injection etc, this is cutdown code to highlight my problem. I am intending to rewrite a large php program I wrote years ago that has database interaction spread throughtout it. My intention is to funnel all the database accesses through a small number of functions so that I can do all the safety stuff in one place.

With the old method using mysql_query, I used the code below to ascertain if a query had executed OK or not.

$result_update = mysql_query($query_update);
	if (!($result_update))
	{
		echo "<br>query did not happen result is false<br>";
		echo "<BR>query_update= $query_update";
		echo "<BR>result_update= $result_update";
		echo "<BR>mysql error no= ".mysql_errno().": ".mysql_error()."<BR>";
	};

The problem now seems to be that mysqli_query pretty much always returns true (ie 1).
mysqli_affected_rows($link) returns 0 if the row is not affected which is handy BUT if one writes the same data back into a row as was there before, the row is correctly not affected and so mysqli_affected_rows returns zero. Now writing the same data back is perhaps not efficient but it isn’t in my view the same as trying to update a row that doesn’t exist.

So I am still looking for a way to flag when I have a sql statement that didn’t work in the same way it was highlighted in the pre “improved” days.

Interestingly trying to update a non existant column produces this
Fatal error: Uncaught mysqli_sql_exception: Unknown column ‘f7’ in ‘field list’ in C:\xampp\htdocs\Test.php:100 Stack trace: #0 C:\xampp\htdocs\Test.php(100): mysqli_query(Object(mysqli), ‘update ftable1 …’) #1 C:\xampp\htdocs\Test.php(30): update_table(Object(mysqli), ‘ftable1’, ‘urn’, ‘5’, Array) #2 {main} thrown in C:\xampp\htdocs\Test.php on line 100

BUT updating a non existent row does not produce a similar error.

The result returned by both the mysql_query() and mysqli_query() statements operate the same. They will only be a false value if the query produced an sql error. They will both be a true value if the query ran successfully, regardless of if a row was inserted/updated. They both have a way of checking the number of affected rows, for this same reason.

The mysqli extension is not a good choice, even if all you are doing is updating old mysql_ based code, due to the removal of magic_quotes from php. You must now provide security against sql special characters, in all data types, from breaking the sql query syntax, which is how sql injection is accomplished.

The simplest, fool-proof way of doing this is to use prepared queries, with place-holders in the sql query statement for each value, then supply the actual values when the query gets executed. This actually simplifies the sql query syntax (by removing all the php variables, single-quotes, {}'s, concatenation dots, and extra quotes from the sql query statement), works for all data types, and simplifies the php code (removal of any casting and _escape_string() functions), letting you eliminate, rather than needing to update all the code.

The mysqli prepared query programming interface is almost completely different from the non-prepared query programming interface, requiring you to learn essentially two sets of statements. The much simpler and more modern PDO extension treats a non-prepared and a prepared query the same, so, you are only learning and using one set of statements.

This would have produced an sql query error in any case and is the result of a programming mistake, not a recoverable application run-time error.

Thank phdr,
I just ran almost identical code on my existing PHP5 system and my new PHP8 system. It does seem like updating a non existent row produces equally silent result. I am not sure how I missed that.

<?php
// PHP 5.4.16
// Linux, Apache, Mysql 5.5.68-MariaDB
// Test database is called ftest with one table with one row
// urn f1  f2  f3 <--Field names urn is the id colum. auto increment
//  1  t1  t2  t3 <--row 1 data.  

$link = mysql_pconnect("localhost", "root", "hidden");  // linux
mysql_select_db("ftest");

$query = "update ftable1 SET f1= 'new F1g' WHERE urn = '7'";
$result = mysql_query($query);
if (!($result))
	{
		echo "<br>query did not happen result is false<br>";
		echo "<BR>query_update= $query";
		echo "<BR>result_update= $result";
		echo "<BR>mysql error no= ".mysql_errno().": ".mysql_error()."<BR>";
	};

//Note that this is silent.
// so updating a urn that does not exist is silent.  Who'd have thought.
?>
<?php
// test8.php
// PHP 8.1.12
// WAMP Win11,
$link = $mysqli = mysqli_connect('localhost', 'root', '', 'ftest');  //Win 11 laptop

$query = "update ftable1 SET f1= 'new F1g' WHERE urn = '7'";
$result = mysqli_query($link, $query);
if (!($result))
	{
		echo "<br>query did not happen result is false<br>";
		echo "<BR>query_update= $query";
		echo "<BR>result_update= $result";
		echo "<BR>mysql error no= ".mysqli_errno().": ".mysqli_error()."<BR>";
	};

//Note that this is silent.
// so updating a urn that does not exist is silent
// AND this is the same as on PHP 5
?>
Sponsor our Newsletter | Privacy Policy | Terms of Service