PHP Error - mysqli_result, boolean given

[rking117]

I have been at this one issue for days, and I have searched and found many similar issues without any one practical solution that solves mine. I am currently migrating a site from PHP 5 to PHP 7 where the old site was using MySQL_Query() to MySQLi_Query(). The old site works just fine but since I'm setting up a new LAMP server I now have to port it to use the newer code instead of the obsolete tags as indicated above. From what I can see my code is clean. Let me frame the issue.

I am receiving a PHP error when connect using the new MySQLi query which says:

Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in /var/www/XXXXXX/test.php on line XX

Here is my table information as seen in PHPMyAdmin
Database Table Name: return (InnoDB)(latin1_swedish_ci)

Field name    Type    Allow nulls? Key    Default value   Extras   
nReturnID int(11) No Primary NULL Auto_increment
nInfoID int(11) Yes None NULL
nMemberID int(10) Yes None NULL
nLocationID int(11) Yes None NULL
dDate varchar(255) Yes None NULL
sReturnStatus varchar(255) Yes None NULL
sReturnMaterial varchar(255) Yes None NULL
nReturnMaterialNum tinyint(4) Yes None NULL
sReturnNotes blob Yes None NULL
nSerial varchar(255) Yes None NULL

Here is the code: (Some code not affecting the outcome has been removed for security purposes)
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
//removed content here
$sLink = new MySQLi(DB_HOST, DB_USER, DB_PASSWORD, DB_DATABASE);
if (MySQLi_Connect_Errno($sLink)) {
printf("Common :: Could not connect to MySQL database: %s\n", MySQLi_Connect_Error($sLink));
exit();
} else {
if ($sRflag == "Yes") {
print("Database connection established. <br />\n");
print("-----------------------------------------------------------------------<br />");
}
}
// removed content here
// resume content
$sQuerySelect = "SELECT * FROM return WHERE nMemberID='$nMemberID' AND nInfoID='$nInfoID' ORDER BY nReturnID DESC";
$sQueryExecute = MySQLi_Query($sLink, $sQuerySelect);

if (MySQLi_Num_Rows($sQueryExecute) > 0) {
while($nRow=MySQLi_Fetch_Assoc($sQueryExecute)) {
$sReturnStatus = $nRow['sReturnStatus'];
}
}

print($sReturnStatus);

**************************************************************************

So of course the above code give me the following output on the page.
Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in /var/www/XXXXXX/test.php on line XX (corresponds with the line --> if (MySQLi_Num_Rows($sQueryExecute) > 0){ )

and when I add the "or die(mysqli_error($sLink))" function to trap the error I get the following:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'return WHERE nMemberID='1' AND nInfoID='1' ORDER BY nReturnID DESC' at line 1

So with that said I have looked through the SQL and everything is correct that I can see. Here is that SQL again:
"SELECT * FROM return WHERE nMemberID='$nMemberID' AND nInfoID='$nInfoID' ORDER BY nReturnID DESC";

Does anyone know why I am getting this? What should I change? An example is appreciated as I have tried everything I know. It doesn't matter if the table is empty or if it has data in it I get the same error. I will say that I am using the exact same query (except table names etc.) to connect to all of the other tables and am not getting any errors like this and am returning data as expected. Please save my brain from exploding.

thanks for your help,

RK

[mac_gyver]

return, the point where the sql syntax error is occurring at is a reserved mysql (database server) keyword. are you sure the original query wasn't as follows, with back-ticks around the table name - 

"SELECT * FROM `return` WHERE nMemberID='$nMemberID' AND nInfoID='$nInfoID' ORDER BY nReturnID DESC";

next, just converting the mysql_ statements to mysqli equivalents is not enough. you must also safely supply data values to the sql query statement. if $nMemberID or $nInfoID are coming from external data or they could contain any sql special characters, they must either be properly escaped (see the mysqli real escape string function/method) or you need to use a prepared query with place-holders in the sql statement for the data values and then bind the actual data to the place-holders. a prepared query is actually the best choice since it is impossible for injected sql in the data to be treated as sql syntax, whereas using the escape string function must have the proper character encoding set for the database connection to match what the database tables are set up for.

[rking117]

return, the point where the sql syntax error is occurring at is a reserved mysql (database server) keyword. are you sure the original query wasn't as follows, with back-ticks around the table name - 

"SELECT * FROM `return` WHERE nMemberID='$nMemberID' AND nInfoID='$nInfoID' ORDER BY nReturnID DESC";

next, just converting the mysql_ statements to mysqli equivalents is not enough. you must also safely supply data values to the sql query statement. if $nMemberID or $nInfoID are coming from external data or they could contain any sql special characters, they must either be properly escaped (see the mysqli real escape string function/method) or you need to use a prepared query with place-holders in the sql statement for the data values and then bind the actual data to the place-holders. a prepared query is actually the best choice since it is impossible for injected sql in the data to be treated as sql syntax, whereas using the escape string function must have the proper character encoding set for the database connection to match what the database tables are set up for.

 

OK I get it now.  The database is reserving the name return (sucks right but I get it)  as for how I'm pulling those vars I have them elsewhere in the code.  It wasn't listed purposely as there is some cleaning functions listed there that I didn't not want to disclose.  (for inserting purposes)   I think I will just rename the table and see if that fixes me up.  Also I know how to convert from MySQL to MySQLi without a problem.  I didn't want to bore with some of the semantics of things.  It makes sense to me know since that name is reserved by MySQL>

 

Much appreciated.

[rking117]

BTW just changed the name of the table to returnvisit and voila. it works like a charm. Thanks for the tip.

[ginerjm]

Yes - "return" is a horrible name for a table.

 

As for your connect logic - whatever does $sRFlag have to do with it? You do a connect and check if it succeeded but using that variable makes no sense here.

 

PS - using such complicated names for your fields and variables is going to bite you in the a.. more times than you can imagine. I strongly suggest you adopt a consistent style for naming and use it. What you are doing now is going to kill you. You do know that PHP is a case-sensitive language?

[rking117]

Yes - "return" is a horrible name for a table.

 

As for your connect logic - whatever does $sRFlag have to do with it? You do a connect and check if it succeeded but using that variable makes no sense here.

 

PS - using such complicated names for your fields and variables is going to bite you in the a.. more times than you can imagine. I strongly suggest you adopt a consistent style for naming and use it. What you are doing now is going to kill you. You do know that PHP is a case-sensitive language?

sRflag is a routine I use to insert variables for development only. If I set the $sRflag to Yes or True then I get all variable data written to the page via print functions. This helps me find issues when I am troubleshooting.

 

And yes I know it seems complicated but it isn't. For instance if you see a var that starts with 'n' it is an interger (n for number) s is for string, d is for date etc. The use of capital lettering is a habit from asp days. It helps me see the value right away and brings my eye directly to the var type letter code.

 

Good catch though I like your keen eye. I admit I might not have caught that when I review code from someone unless I was familiar with their coding style. sRflag is actually a contribution I picked up from an old friend of mine, a genius I might add, who works for Google now I believe as a channel intelligence developer.

 

The down side to that it a hacker would instantly know what type of database value is accepted so externally it might not be the greatest of ideas but I do know of several platforms that do similar things.

[Barand]

Commonly known as "Hungarian Notation"

https://en.wikipedia.org/wiki/Hungarian_notation

[Jacques1]

Get a proper IDE which can analyze your queries, and you'll immediately know the type of any column just by pointing to it. No need for any prefixes.

 

The Hungarian notation may be useful for special cases, but it's still an oddity, hard to read and has been actively rejected by many projects – including PHP. I've never seen it in a database.

 

So you might want to adopt more common naming conventions, especially if there's a chance that somebody else might work on the project.