PHP error Code on WHERE query

Question

Ok I have myself confused here.

I have this code it works perfect

$result = mysql_query("SELECT * FROM e_track_access_log WHERE member_id>0 ORDER BY     
datetime_accessed DESC LIMIT 0,20");
?>
            <?php
while($rows=mysql_fetch_array($result)){
?>

I fetches all in field member_id that is greater than 0

All i have done now is changed the following

$result = mysql_query("SELECT * FROM e_track_access_log WHERE dealer=Demo ORDER BY    
datetime_accessed DESC LIMIT 0,15");
?>
           <?php
while($rows=mysql_fetch_array($result)){
?>

All I want it to do is only display dealer info that has Demo in it.

This is the error I received

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in   
/home/www/controldemo.e-track.co.za/control_main.php on line 177

Alvaro · Answer 1 · 2013-03-15T15:56:40.073

You need quotes to compare strings:

$result = mysql_query("SELECT * FROM e_track_access_log WHERE dealer='Demo' ORDER BY datetime_accessed DESC LIMIT 0,20");

But be careful, don't use double quotes in in this case or you will close the current mysql_query string parameter.

Anyway, as it has been mentinoned, mysql_query is deprecated. I would recommend you to make use of PDO which will also offer you more security against SQL injections.

Here an example of its usage:

$mysqlString =  'mysql:' 
              .'host='.$yourHost.';' 
              .'dbname='.$yourDBName; 

$conn = new PDO( $mysqlString, $yourUser, $yourPassword);  

$stmt = $conn->prepare(("SELECT * FROM e_track_access_log WHERE dealer=:dealer ORDER BY datetime_accessed DESC LIMIT 0,20");

$stmt->bindParam(':dealer', 'demo');

$stmt->execute();

$data = $query->fetchAll(PDO::FETCH_ASSOC);

score 3 · Accepted Answer · edited May 23 '17 at 12:28

3

since demo is a string, you should wrap it with single quote.

SELECT * 
FROM e_track_access_log 
WHERE dealer = 'Demo' 
ORDER BY datetime_accessed DESC 
LIMIT 0,15

As a sidenote, the query is vulnerable with SQL Injection if the value(s) of the variables came from the outside. Please take a look at the article below to learn how to prevent from it. By using PreparedStatements you can get rid of using single quotes around values.

How to prevent SQL injection in PHP?

edited May 23 '17 at 12:28

Community

1
1

answered Mar 15 '13 at 15:41

John Woo

249,283
65
481
481

1

JW thanks yes missed the 'Demo'part thanks alot sometimes sitting and looking at things you miss basic's – Trevor Ackermann Mar 15 '13 at 15:44

score 0 · Answer 3 · answered Mar 15 '13 at 15:47

Actually the problem is that without quotes, mysql will evaluate the statement like a column, and the column Demo obviously does not exist in the current scope. The query fails, returning a error similar to this one: ERROR 1054 (42S22): Unknown column 'Demo' in 'where clause'. In order to avoid this, you need to use error checking.

Since we are at this chapter, let me remind you that mysql_* functions will be removed in the future and you should not use them anymore.

The result from your query is false because of the previous mysql error, therefore you do not have a resource there but a simple boolean, therefor you have a error in the php code, also because the checks lack.

The 2 possibilities to avoid this: check the mysql error, and check if the result returned by the query function is actually not false.

PHP error Code on WHERE query

3 Answers3