ds2010630
ds2010630
2015-04-28 19:10

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

已采纳

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?

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

2条回答

  • douyi7283 douyi7283 6年前

    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.

    点赞 评论 复制链接分享
  • dongzao4503 dongzao4503 6年前

    When using empty() to check, the value of $condition4 might be null since empty allows for NULL values. I'm still learning PHP; however, would isset() be a better approach? Otherwise there might be condition4 = null

    Also as that person commented on your post, please remember to validate all user input before you place it in a sql query or other places.

    http://php.net/manual/en/function.isset.php

    点赞 评论 复制链接分享