为什么我不能在mysql_fetch_array的select查询中使用where子句？ [重复]

Question

This question already has an answer here:

• What is the difference between single-quoted and double-quoted strings in PHP? 10 answers

It may sound like noob question but I think ive tried everything and ive been stuck for weeks with this not making any progress.

This is my working code:

//$connect holds my db login details so ill hide it
$query = '
SELECT *,UNIX_TIMESTAMP(CONCAT_WS(" ", ranking_data_date, ranking_data_time)) AS datetime
FROM tbl_ranking_data
ORDER BY ranking_data_date ASC, ranking_data_time ASC LIMIT 60
';

$result = mysqli_query($connect, $query);
$rows = array();
$table = array();
$table['cols'] = array(
 array(
  'label' => 'Date Time',
  'type' => 'datetime'
 ),
 array(
  'label' => 'Position',
  'type' => 'number'
 )
);

while($row = mysqli_fetch_array($result))

My problem is that it always selects same data (and all of it) from database.

To fix it, ideally it would be to insert where clause for example:

WHERE user_id=$id_of_current_user

But I can't enter that or anything or i will get error.

Basically code same as above but with this difference:

//$connect holds my db login details so ill hide it
$query = '
SELECT *,UNIX_TIMESTAMP(CONCAT_WS(" ", ranking_data_date, ranking_data_time)) AS datetime
FROM tbl_ranking_data
WHERE user_id=$id_of_current_user
ORDER BY ranking_data_date ASC, ranking_data_time ASC LIMIT 60
';

Shows this error:

Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in /files/phpfile.php on line 32

My understanding: query fails and it returns false (thats why error talks about boolean) instead of array ($result) but it shouldnt fail!

it only fails if I add WHERE condition.

Any help appreciated.

How can I insert where clause to this code?

</div>

Answer 1

The problem with your query isn't the where clause, it's the variable in your where clause. If you echo your query when having an issue like this, you'll be able to see that the query doesn't have the correct syntax.

PHP doesn't substitude variable values in string that use single quotes. Use double quotes instead.

Also, use backticks to quote column names and table names. This will avoid problems with reserved words.

Lastly, use parameterized queries to avoid data quoting issues as well as SQL Injection.

//  $connect holds my db login details so I'll hide it
$query = "
SELECT 
    *,
    UNIX_TIMESTAMP(CONCAT_WS(' ', `ranking_data_date`, `ranking_data_time`)) AS `datetime`
FROM `tbl_ranking_data`
WHERE `user_id` = $id_of_current_user
ORDER BY `ranking_data_date` ASC, `ranking_data_time` ASC 
LIMIT 60;
";

$result = mysqli_query($connect, $query);
$rows = array();
$table = array();
$table['cols'] = array(
    array(
        'label' => 'Date Time',
        'type' => 'datetime'
     ),
    array(
        'label' => 'Position',
        'type' => 'number'
     )
);