Query using multiple variables from a form not acting as expected

Question

Here is the page I am asking about:

http://rdavidbeales.com/archiveprototype/advancedsearch.php

I've gotten the query to ignore NULL variables by doing some searching using the terms from constructive criticism to the original post.

The only problem now is that I cannot include AND statements in the definitions of the $where variable, because if only one field is selected, then I get an error for a meaningless AND statement.

• So how can I edit the new code to to include AND when necessary?

Using this link Possible to have PHP MYSQL query ignore empty variable in WHERE clause? I've come up with the code below:

$anf = $_POST["artistnameform"];
$df = $_POST["decadeform"];
$stf = $_POST["subjecttermform"];
$ptf = $_POST["phototitleform"];
$where = "WHERE";

if(!empty($ptf)){
$where .= " P.PhotoTitle='$ptf '";
}
if(!empty($anf)){
$where .= " PE.idPeople = '$anf '";
}
if(!empty($stf)){
$where .= " S.idSubjectTerm = '$stf '";
}
if(!empty($df)){
$where .= " P.PhotoCreatedDate LIKE CONCAT ('%', '$df ', '%')";
}

And here is the query that uses $where:

$query = "
   SELECT P.PhotoTitle, 
          P.PhotoURL, 
          P.PhotoCaption, 
          PE.FirstName, 
          PE.LastName 
     FROM Photo P
LEFT JOIN People_has_Photo PHP 
       ON idPhoto=PHP.Photo_idPhoto
LEFT JOIN People PE 
       ON idPeople=People_idPeople
LEFT JOIN SubjectTerm_has_Photo SHP 
       ON idPhoto=SHP.Photo_idPhoto
LEFT JOIN SubjectTerm S 
       ON idSubjectTerm=SubjectTerm_idSubjectTerm
$where
GROUP BY P.PhotoFileName";
$result = mysql_query($query);

Answer 1

Use an array of conditions, then concatenate them with ' AND ' between

$wheres = array();

if(!empty($ptf)){
    $wheres[] = " P.PhotoTitle='$ptf '";
}
if(!empty($anf)){
    $wheres[] = " PE.idPeople = '$anf '";
}
if(!empty($stf)){
    $wheres[] = " S.idSubjectTerm = '$stf '";
}
if(!empty($df)){
    $wheres[] = " P.PhotoCreatedDate LIKE '%{$df} %')";
}

$where = implode(' AND ', $wheres);

I also got rid of your CONCAT function, since it was redundant.

Also:

1. Check if you really need that space after each value. I think you don't.
2. Use specialized DateTime functions in MySQL, and not LIKE. Searching for time values can be quick if indexed, but such a LIKE operator kills all the performance.
3. The script is vulnerable to SQL Injection. Escape the quotes, or, even better, use parametrized queries in MySQLi or PDO.
4. Don't use mysql_... functions. They are deprecated and will be removed in the future.
5. When writing SQL queries, with multiple tables, ALWAYS prefix the field with the table name or alias. You will avoid A LOT of errors in the future by following this simple rule.