How can I search multiple inputs from a form in php?

Question

I have a PHP program that allows users to search through an SQL table depending on the input or combination of inputs. I can do single search combination, but can't figure out a way to search by any criteria. What I got so far is terrible because I'm trying to search by every input possibility (and it's not working). This is what I got so far.

<?php

include_once("config.php");

if(isset($_POST['submit'])){

$name = mysqli_real_escape_string($mysqli, $_POST['name']);

$day = mysqli_real_escape_string($mysqli, $_POST['day']);

$month = mysqli_real_escape_string($mysqli, $_POST['month']);

$year = mysqli_real_escape_string($mysqli, $_POST['year']);


// 1 2 3 4

if( !empty($name) && !empty($day) && !empty($month) && !empty($year) ) {
   $sql = mysqli_query($mysqli, "SELECT * 
                                FROM transfer 
                                WHERE name like '%$name%' 
                                and day LIKE '%$day%' 
                                AND month LIKE '%$month%' 
                                AND year LIKE '%$year%'");
} else if (!empty($name) && !empty($day) && !empty($month) ) {

    $sql = mysqli_query($mysqli, "SELECT * 
                                    FROM transfer 
                                    WHERE name like '%$name%' 
                                    and day LIKE '%$day%' 
                                    AND month LIKE '%$month%'");
 } else if (!empty($day) && !empty($month) && !empty($year) ) {

    $sql = mysqli_query($mysqli, "SELECT * 
                                    FROM transfer 
                                    WHERE day LIKE '%$day%' 
                                    AND month LIKE '%$month%' 
                                    AND year LIKE '%$year%'");
} else if (!empty($name && !empty($day) ) {   
    $sql = mysqli_query($mysqli, "SELECT * FROM transfer 
                                    WHERE name like '%$name%' and 
                                    day LIKE '%$day%'");
}

//1 3

else if (!empty($name) && !empty($month) )

{

$sql = mysqli_query($mysqli, "SELECT * FROM transfer WHERE name like '%$name%' and month LIKE '%$month%'");

} 

//1 4

else if (!empty($name) && !empty($year) )

{

$sql = mysqli_query($mysqli, "SELECT * FROM transfer WHERE name like '%$name%' and year LIKE '%$year%'");

}

//2 3

else if (!empty($day) && !empty($month) )

{

$sql = mysqli_query($mysqli, "SELECT * FROM transfer WHERE day like '%$day%' and month LIKE '%$month%'");

}

//2 3

else if (!empty($day) && !empty($month) )

{

$sql = mysqli_query($mysqli, "SELECT * FROM transfer WHERE day like '%$day%' and month LIKE '%$month%'");

}

//2 4

else if (!empty($day) && !empty($year))

{

$sql = mysqli_query($mysqli, "SELECT * FROM transfer WHERE day like '%$day%' and year LIKE '%$year%'");

}

//3 4

else if (!empty($month) && !empty($year))

{

$sql = mysqli_query($mysqli, "SELECT * FROM transfer WHERE month like '%$month%' and year LIKE '%$year%'");

}

//1

else if (!empty($name))

{

$sql = mysqli_query($mysqli, "SELECT * FROM transfer WHERE name like '%$name%'");

}

//2

else if (!empty($day))

{

$sql = mysqli_query($mysqli, "SELECT * FROM transfer WHERE day like '%$day%'");

}

//3

else if (!empty($month))

{

$sql = mysqli_query($mysqli, "SELECT * FROM transfer WHERE month like '%$month%'");

}

//4

else if(!empty($year))

{

$sql = mysqli_query($mysqli, "SELECT * FROM transfer WHERE year like '%$year%'");

}

else

{

echo "<p>you must insert an input</p>";

}


//while loop used to retrieve data from the SQL database

while ($res = mysqli_fetch_array($sql))

{       

echo "<tr>";

echo "<td>".$res['name']."</td>";

echo "<td>".$res['confirmation']."</td>";

echo "<td>".$res['code']."</td>";

echo "<td>".$res['hora']." ".$res['horario']."</td>";

echo "<td>".$res['day']."/".$res['month']."/".$res['year']."</td>";

echo "<td>".$res['extra']."</td>";

echo "</tr>";                                               

}

}

?>

</table>

Answer 1

(Note: It has been said to use prepared statements, which is right - but I don't want to give a copy & paste answer anyway, so here is just an example on how you can achieve your result - use prepared statements anyway. It works the same, except you are creating your query with placeholders and provide the variables that are not empty)

You can create your query in a more "dynamic" way. This is a little tricky, and becomes very "challenging" if joins are required - but what you actually want is to end up with a single query, containing all your constraints.

The first thing, you should define: Are your Searchfields "and" or "or" fields?

if it's "and" it is quite simple to achieve - something like this:

$query = "SELECT * FROM transfer";

$andParts = array();

if(!empty($name))
    $andParts[] = "name = '$name'";

if(!empty($day))
    $andParts[] = "day = $day";

if (!empty($month))
    $andParts[] = "month = $month";

if (!empty($year))
    $andParts[] = "year = $year";

if (!empty($andParts))
   $query .= " WHERE ".implode(" AND " , $andParts);

$sql->Query($query);  

if theres also "or" involved, you'll need another array $orParts, where you first join all the "ors", and finally glue that array together to the final "ands".

If conditions could match columns from "joined" tables, you need to keep track of that, so that you know, from with tables you need to "select".

---

If you have very complex query for each "searchfield" (i.e. every searchfields result is a result of multiple joins etc...) you can query just the id's for each searchfield, then intersect the results and retrieve the ids matching all criterias:

   $result1 = $sql->Query("SELECT id FROM transfer left join .... ");
   // array(1,2,3,5,7,10,15,19,27)

   $result2 = $sql->Query("SELECT id FROM transfer right join .... ");
   // array(2,3,10,15,19,27,43,123)

   $result3 = $sql->Query("SELECT id FROM transfer inner join .... ");
   // array(2,10,15,27,43,711)

   $ids = array_intersect($result1, $result2, $result3);
   // array(2,10,15,27)

   $finalResult = $sql->Query("SELECT * FROM transfer WHERE id in (".implode(",", $ids).");");