doucong6884
doucong6884
2019-08-02 07:22

SQL Query在一个参数中提供错误的输出

已采纳

I'm using sql statement in PHP to get results from a server database. The Query is executing fine, however I'm getting errors in query results, from one of the arguments (cardName) in the SQL statement.

Search Test Cases Results:

  • IF (cardNumber= '', cardName != empty, cardOwner ='' ) Result=> Returns all records in table, regardless if cardName value exists on database or not.

  • IF cardName + (cardNumber OR cardOwner ) search filter Result => returns more records than actually anticipated.

  • IF cardNumber, CardName and cardOwner are all not empty Result=> accurate result shown in all scenarios

  • cardNumber and cardOwner gives correct results in all tested scenarios (unless in combination with cardName)


Data Get and Search Code

    /*...........Get Values............*/

    if($_POST['srch_cardNumber_txt']){
        $cardNumber = $_POST['srch_cardNumber_txt'];
    }
    else{$cardNumber="";}

    if($_POST['srch_cardName_txt']){
        $cardName = $_POST['srch_cardName_txt'];            // _/
    }
    else{$cardName = "";}                                   // _/

    if($_POST['srch_cardOwner_txt']){
        $cardOwner = $_POST['srch_cardOwner_txt'];
    }
    else{ $cardOwner="";}

    echo "Filters:: Card Number: ".$cardNumber." -- Card Name: ".$cardName.
        " -- Card Owner: ".$cardOwner."<br>";

 if(!empty($cardName) or !empty($cardNumber) or !empty($cardOwner)  ){
    include_once("db_connect.php");

     if ( mysqli_connect_error()){
         die('Connect Error('.mysqli_connect_errno().')'.mysqli_connect_error());
         }

         else{
                 //.............Search Pseudo Code........
/*                      SELECT *        
                         FROM Table
                         WHERE (f1  = '' or c1 = f1)
                           AND (f2  = '' or c2 = f2)
                           AND (f3 = '' or c3 = f3)
                           AND (f4 = '' or c4= f4)
*/

                 $SELECT = "SELECT *
                                FROM cards
                                WHERE (? = '' or cardNumber = ? )
                                    AND (? = '' or cardName = ? )
                                    AND (? = '' or cardOwner = ? )
                                ";
                                                        /*ERROR: Searching only cardName returns all cards data - cardName filter can work in combination with additional filters*/


                // if All values are equal to null, then  retrun false / end search
                if($cardName ='' AND $cardNumber ='' AND $cardOwner='')
                {
                    echo "Search fields are all empty<br>";s
                    return False;
                }
                 //.........Prepare statement.....
                 $stmt = $conn->prepare($SELECT);
                 $stmt->bind_param("iissss",$cardNumber,$cardNumber,$cardName,$cardName,$cardOwner, $cardOwner);            //Works with HardCoded cardName value
                 $stmt-> execute();
                 $stmt->store_result();
                 $rnum = $stmt->num_rows;

                 if($rnum == 0){
                     $stmt->close();                     
                     echo "No card records found on given search inputs <br>";                   
                     }
                     else {                      
                         $stmt->bind_result($cardNumber,$cardName,$cardOwner);                       
                     }
//                  $conn->close();
             }   
     }
     else {
         echo "no data entered for search";
         die();
         }       

?>

Data Ouput code

                <?php  
                if( $rnum > 0 ){
                    echo "Number of records found: ".$rnum."<br>";
                    while($stmt->fetch()) {
                             echo "A card is found with Card #".$cardNumber."<br>".$cardName."<br>".$cardOwner."<br>------------<br>";
                    } 
                    $stmt->close();
                     $conn->close();
                }else{
                    echo "No records found<br>";
                }
                     ?> 

Data Images

https://imgur.com/a/xBztII5

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

1条回答

  • dongyukang7006 dongyukang7006 2年前

    You have error after 48 row:

    if($cardName ='' AND $cardNumber ='' AND $cardOwner='')
    {
        echo "Search fields are all empty<br>";s
        return False;
    }
    

    You are checking values, not setting. Use double equals (==). It should be:

    if ($cardName =='' AND $cardNumber =='' AND $cardOwner=='') {
        echo "Search fields are all empty<br>";
        exit;
    }
    
    点赞 评论 复制链接分享