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>";
}
?>