SQL Warnings in my PHP code

[ajoo]

Hi everybody,

 

Please can someone check the snippet of code below and say why it generates a warning as "Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in functions.php on line 6". 

 

Same warning is generated for the next line i.e. line 7. Ofcourse this is only due to the earlier line i.e. line 6.

 

1. $con =  mysqli_connect($db_host, $db_user, $db_pass, $db);
2.        if(mysqli_connect_errno($con)) die('Could not connect: '. mysqli_connect_error());
3.
4.        $query = "Select * FROM $table WHERE username = '$user' AND id = $id "; // AND id = '$id' AND
5.        $result=mysqli_query($con, $query);
6.        $row = mysqli_fetch_array($result,MYSQLI_ASSOC);
7.        mysqli_free_result($result);

I have checked the usage of mysqli_fetch_array($result,MYSQLI_ASSOC) on google and it seems to be almost the same.

 

The difference was only in the $query which on w3schools examples was as follows

 

$query ="SELECT Lastname,Age FROM Persons ORDER BY Lastname";

 

Can someone please help. Thanks all

 

 

 

[requinix]

99% of the time that error message means your query failed due to a syntax error. Since you haven't shown where most of the important variables are given values, something that we would have seen if you had posted all of your code rather than just those few lines you thought were relevant, what is the exact value of $query?

[ajoo]

Hi, thanks for the reply. 

 

Well the values are simply values in a table. The user name and ID coud be null for a new table with no entry or there could be name and id entries in the table like say:

 

id  user

1  Jack

2  John

3  David & so on.

 

I don't get an error when i run this code. I just get a warning.  Further ID is an integer and user is a varchar. My code runs ok but I wanna do away with the warning. Why is $result a boolean ?. I do have a check for an initial value when the table is empty. but when i got the above warnings my table was not empty and so the values for the user and id existed or exist. I hope this can help. The whole code is heaps long. Looking for anyone to take a longer look and see if something can be found. Thanks all.

Edited July 27, 2013 by ajoo

[trq]

Why is $result a boolean ?

Because your query is failing. Meaning $result is the boolean false.

[ajoo]

Hi thanks, 

 

Sorry if i am bit slow to catch on. But lemme see if i understand this. The warning that i get is 

 

Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in functions.php on line 6". 

 

So what you mean is that my query is returning a boolean to mysqli_fetch_array() and hence the warning. Is that a good or a bad thing then? Because the result that I get otherwise is correct. 

 

And I am getting this warning in the special case where the table would be NULL when no value has been inserted in it. 

 

If this is not good then how can i change this ? 

 

Ok here is some more of the code along with the previous one.  In fact I use the fact that (!row)  (that the row return a NULL or is uninitialized)

 

Any more discussion and any solution is welcome. The code:

$con =  mysqli_connect($db_host, $db_user, $db_pass, $db);
        if(mysqli_connect_errno($con)) die('Could not connect: '. mysqli_connect_error());

//        $query="Select * FROM $table WHERE username = '".$_POST['username']."' && status = A";
        $query = "Select * FROM $table WHERE username = '$user' AND id = $id "; // AND id = '$id' AND
        $result=mysqli_query($con, $query);
        $row = mysqli_fetch_array($result,MYSQLI_ASSOC);
        mysqli_free_result($result);
        
        if (!$row) {    /* Table is empty  - -   initialization condition ! v! */
        $existingId = "Valid";          // Making the Session Valid bcos at this point the login table is empty
        mysqli_close($con);
        return($existingId);   // and insert the first values in the table.

[jazzman1]

Could you echo the $query before to send the statement to DB?

$query = "Select * FROM $table WHERE username = '$user' AND id = $id ";

echo $query; exit;

[ajoo]

hmmm yea sure i can. i will. thanks. I'll revert. Thanks 

[ajoo]

ok I get the following:-

 

Select * FROM loginstatus WHERE username = 'yahoo4' AND id = 21 yahoo4 21 A

 

which is correct !! as can be seen by the last 4 entries of my table loginstatus.

 

 

18  yahoo4 X 1374925966

19  yahoo4 X 1374926013

20  yahoo4 X 1374926344

21  yahoo4 A 1374933359

 

So how can the warnings be removed? Thanks.

[trq]

Select * FROM loginstatus WHERE username = 'yahoo4' AND id = 21 yahoo4 21 A

Is not a valid SQL statement.

[jazzman1]

Also, make sure that the table "loginstatus" exists in the selected database "$db"

[mac_gyver]

the warning, at the point you are trying to use the result from the query, means that the query failed to run due to an error of some kind and it returned a false (boolean) value, as has already been mentioned. a query that runs, but matches no rows (or there are not rows at all in the table to match) is a successful query and would return a result resource/result object, not a false value. your query is not running because the sql syntax is not valid, as has already been mentioned. if your code had some error checking/reporting logic to 'capture' (display or log) the mysqli_error($con) output, it would be calling attention to the point in the query that something was found that mysql didn't understand. your error checking/reporting logic would also prevent the following code from trying to use the result from a query that failed so that you don't get follow-on errors. your id field is a string data type (based on the value shown when you echoed the query statement.) the value you compare it with needs to be a string, enclosed by single-quotes inside the query statement.

[ajoo]

ok my mistake, the actual echo of the query was

 

Select * FROM loginstatus WHERE username = 'yahoo4' AND id = 21. Now that is a valid sql statement i guess.   

The other 3, yahoo4 21 A are three echo statements displaying id, user & status values of the fields. 

 

Ofcourse the table 'loginstatus' exists and snapshot of the last four entries in my last reply was from the 'loginstatus' table. 

 

So now why am I getting the Warnings ? Any suggestion , help is welcome. Thanks lots again 

[mac_gyver]

believe it or not, you can get mysql to tell you why the query is failing. use the following in place of your mysqli_query() statement -

$result=mysqli_query($con, $query) or die(mysqli_error($con));

also, i meant to type this above. your return statement and the file name funcitons.php implies the posted code is inside of a function. do NOT make a database connection, run one query, then close the database connection. you need to make ONE database connection in your main code and pass that as a call time parameter into any function that needs to use it. creating a database connection, depending on where your database server is located at with respect to your web server, can take longer than actually running the query. repeatedly opening/closing a database connection each time you run a query will noticeably make your site run slower.

[ajoo]

Thanks Mac, I'll try this out and revert. Thanks also for the suggestions regarding the opening of databases. I'll keep that in mind. Will revert with the results of your suggestions. 

Thanks

[ajoo]

OK !! Thanks guys. You guys were right ! I found the error and the mistake. There were 2 calls to the function. One was called using the correct table name, while there was a typo mistake in the table name in the 2nd call to the function. Thank you all. Jazzman actually pointed out that I should check the table name and i did without realising that there was a typo in the table name in the 2nd call to it.

 

Then I changed as Mac suggested and there it showed me the typo to the table name. 

 

Thanks loads.  You guys are fantastic and i hope to learn loads from you. 

Thanks.

[mac_gyver]

one additional point, you shouldn't have multiple tables holding same-meaning data. this will create a database management nightmare in just keeping the table names straight, as you just found out with a typo error.

 

same-meaning data should be in one table.

[ajoo]

Thanks Mac for your valuable tips. I will keep that in mind as well as the one regarding connecting to the database time n again. I saw my code and indeed there are instances where i close the database connection and then reconnect at another place. Will change those too. 

 

Thanks.