Choosing the correct SQL query based off user input

Question

Lets say the user only entered in an artist to search by, how do I get PHP to recognize that the user is only looking for an artist and should therefore setup a query only for the artist. The way I have currently done it works, but it is very ineffective when the user wants a variety of search options.

$artist = $_POST["artistSearch"];
$topic= $_POST["topicSearch"];
$date = $_POST["dateSearch"];
$title = $_POST["titleSearch"];

$artistLength = strlen($artist);
$topicLength = strlen($topic);
$dateLength = strlen($date);
$titleLength = strlen($title);

if ($artistLength>2 && $topicLength<2 && $dateLength<2 && $titleLength<2) {
    $sql=("Select * FROM music WHERE artist = '$artist' ORDER BY date");
}

if ($artistLength>2 && $topicLength>2 && $dateLength<2 && $titleLength<2) {
    $sql=("Select * FROM music WHERE artist = '$artist' AND topic = '$topic' ORDER BY date");
}

if ($artistLength>2 && $topicLength>2 && $dateLength>2 && $titleLength<2) {
    $sql=("Select * FROM music WHERE artist = '$artist' AND topic = '$topic' AND date = '$date' ORDER BY date");
}

if ($artistLength>2 && $topicLength>2 && $dateLength>2 && $titleLength>2) {
    $sql=("Select * FROM music WHERE artist = '$artist' AND topic = '$topic' AND date = '$date' AND title = '$title' ORDER BY date");
}

$result = mysqli_query($con,$sql);

[Your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). — Jay Blanchard, Feb 10 '16 at 20:43

score 1 · Accepted Answer · answered Feb 10 '16 at 20:46

1

Simple, you build up the query string in stages. Ignoring your sql injection attack vulnerability, and assuming that all options should be ANDed together, you can do something like:

$options = array();
if ($artist) {
   $options[] = "artist = '$artist'";
}
if ($topic) {
   $options[] = "topic = '$topic'";
}
etc...

$where_clause = implode(' AND ', $options);
$sql = "SELECT ... WHERE $where_clause";

answered Feb 10 '16 at 20:46

Marc B

356,200
43
426
500

Thank you for the suggestion, works fine. For the 'title' SQL column, how do I make it a WHERE LIKE clause – SamG Feb 10 '16 at 20:56
Though alone - it is no difficult. I suggest you think about OR instead AND in WHERE clausule too. – LuckyLue Feb 10 '16 at 21:25
`if ($title() ... "title LIKE '%$title%'"`. all you're doing is generating little snippets of sql. if you want something else to be done, then generate the sql snippet necessary to accomplish that something else. – Marc B Feb 10 '16 at 21:26

Choosing the correct SQL query based off user input

1 Answers1