如果列返回空，则SQL查询“WHERE EXISTS”

Question

I am using this sql query as part of a PHP script to take user input (being applied to variables $condition1-$condition4) and compare it against a MySQL database and return relevant results.

My problem is that not all the forms on the site output a $condition4 value so it is not always inputted into the script/query.

I tried using the EXISTS predicate within the SQL query but could not get it to work.

Here is the query as i have it working:

 $sql = "SELECT columnXYZ
    FROM table_1
    WHERE condition1 = '" .$condition1."'
    and condition2 = '" .$condition2."'
    and condition3 = '" .$condition3."'
    and condition4 = '".$condition4."'";

Do I need to determine whether $condition4 was inputted before i run the query or is there a way to use the WHERE EXISTS predicate to achieve this?

The whole script: (var_dump to see the results of the query)

<?php
$condition1 = $_POST['condition1'];
$condition2 = $_POST['condition2'];
$condition3 = $_POST['condition3'];
$condition4 = $_POST['condition4'];
$dbhost = 'localhost';
$dbuser = 'admin';
$dbpass = 'pwd';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);

if(! $conn )
{
  die('Could not connect: ' . mysql_error());
}
$sql = "SELECT columnXYZ
        FROM table_1
        WHERE condition1 = '" .$condition1."'
        and condition2 = '" .$condition2."'
        and condition3 = '" .$condition3."'
        and condition4 = '".$condition4."'";

mysql_select_db('database_1');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not get data: ' . mysql_error());
}
while($row = mysql_fetch_array($retval, MYSQL_ASSOC))
{
    $columnXYZ = $row['columnXYZ'];
    var_dump($columnXYZ);   
} 



mysql_close($conn);
?>

The query works fine when $condition4 is inputted, as a work around for forms that do not have a $condition4 i have just been directing to a similar php script that has the $condition4 removed.

To clarify my question: Can i use the EXISTS predicate in a SQL query to determine if an input has a value or do i need to do so with PHP or some other method beforehand?

Answer 1

Just check if $condition4 is empty() before adding that part to your SQL query.

$sql = "SELECT columnXYZ
    FROM table_1
    WHERE condition1 = '" .$condition1."'
    and condition2 = '" .$condition2."'
    and condition3 = '" .$condition3."'";

if !(empty($condition4)){
    $sql .= "' and condition4 = '".$condition4."'";
}

As Seth mentions, google for 'SQL injection' if you're going to put this anywhere near the public internet.