如果值为空，如何忽略SQL Select命令

Question

I have this SQL query:

$sql2 = "SELECT * FROM MyTable WHERE time1='".$_POST['time']."' AND type1='".$_POST['type']."';

The result:

$result1 = $conn->query($sql2);

if ($result1->num_rows > 0) {
    // output data of each row
    while($row = $result1->fetch_assoc()) {

        echo $row["name1"];

• name1 row is the name of the activity in database.

The HTML form has these two select lists:

<select id="time" name="time" required="" data-do-validate="true">
    <option value="">Select ...</option> // this is the NULL value
    <option value="time1">Type 1</option>
    <option value="time2">Type 2</option>
    <option value="time3">Type 3</option>
    <option value="time4">Type 4</option>
                        </select>

<select id="type" name="type" required="" data-do-validate="true">
    <option value="">Select ...</option> // this is the NULL value
    <option value="type1">Type 1</option>
    <option value="type1">Type 2</option>
    <option value="type1">Type 3</option>
    <option value="type1">Type 4</option>
                        </select>

So what I try to do is selecting from the database the name of all activities with matching data submitted by the HTML form.

BUT what I additionally want is to ignore the SQL Select for the Time ( For example ) if the user leave the value empty ( Select ... ), the result in this case will be the names of all activities based only on the Type. And if he select a time and a type the result in this case will be more specified based on the Time and the Type of the activity.

What can I do?

Thank you!

Answer 1

You could build your Statement depending on the $_POST Values (by the way, in productive you should't use $_POST values directly in the SQL statement).

$connect = "";
$sql2 = "SELECT * FROM MyTable ";

if($_POST['time']!='' || $_POST['type']!='') {
  $sql2 .= "WHERE ";
  if($_POST['time']!='') {
      $sql2 .= "time1='".$_POST['time']."'";
      $connect = " AND ";
  }
  if($_POST['type']!='') {
     $sql2 .= $connect . "type1='".$_POST['type']."'";
  }
}