Craig Johnstone Craig Johnstone - 3 months ago 8
MySQL Question

How do I write an SQL query with a WHERE = 'all'?

I am writing a PHP script which takes a JSON request containing a parameter known as

bookTypeID
.

This
bookTypeID
parameter can have either an integer value (e.g. 1,2,3,4) or a string value of "all".

I use this parameter as an input to the WHERE clause of an SQL query.

I want my query to say
SELECT name FROM books WHERE $bookTypeID
, which would work fine if
$bookTypeID
is an integer, but I do not know a correct value to pass to the
WHERE
clause in the case where the JSON parameter has the value "all". I want something which essentially says "ignore the WHERE clause".

I hope this makes sense. I have posted a simplified version of my code below:

<?php

include 'db_conf.php';

$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_errno) {
printf("Connect failed: %s\n", $conn->connect_error);
exit();
}

$content = file_get_contents('php://input');
$json = json_decode($content, true);

$bookTypeIDString = $json["bookTypeID"];
$bookTypeID;
switch($bookTypeIDString){
case "all":
$bookTypeID = "all"; // IS THERE A VALUE HERE THAT I CAN PASS TO AN SQL QUERY TO RETURN EVERYTHING? i.e. IGNORE THE WHERE CLAUSE COMPLETELY?
break;
default:
$bookTypeID = $bookTypeIDString;
}

$query = "SELECT name FROM books WHERE booktype = $bookTypeID";
$result = $conn->query($query);

$data = array();
while ($row = $result->fetch_array(MYSQLI_ASSOC)) {
$data[] = $row;
}
echo json_encode($data);

$conn->close();
?>


Update: This is a simplified example for the purpose of asking my question. In practice, I have about 50 parameters passed via the JSON, so I would like to avoid building up an SQL statement using conditional PHP. I am hoping for an SQL value that I can pass which will return everything.

Answer

Removing the WHERE altogether would be best.

Just for fun, you could do

case "all":
  $bookTypeID = "booktype"; 
  break;

so that the SQL ends up as

WHERE booktype = booktype 

which is the same as

WHERE booktype IS NOT NULL

which may work for you.

Essentially, you are SQL-injecting yourself here (goes to show that this JSON data must not come from untrusted input).

Comments