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. Learn about prepared statements for MySQLi. — Jay Blanchard
– Jay Blanchard, Commented Feb 10, 2016 at 20:43

Marc B · Accepted Answer · 2016-02-10 20:46:37Z

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, 2016 at 20:46

Marc B

362k44 gold badges433 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

SamG Over a year ago

Thank you for the suggestion, works fine. For the 'title' SQL column, how do I make it a WHERE LIKE clause

LuckyLue Over a year ago

Though alone - it is no difficult. I suggest you think about OR instead AND in WHERE clausule too.

Marc B Over a year ago

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.

Collectives™ on Stack Overflow

Choosing the correct SQL query based off user input

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related