I am using Mysqli to display all results on my web page. But now i want to add security so i want to use Mysqli prepared statements to fetch data but i am confused how i can achieve it.
My web page URL is like
www.example.com/list.php?type=Car&brand=All
At first i check value in brand field if it is All then i create a variable like
$brand="";
And if it is not All then
$brand="AND brand='$_GET[brand]'";
And my query becomes
SELECT * FROM Table WHERE type='$_GET[type]' $brand;
Its working perfectly for me without prepared statements as if brand value is 'All' It displays all results and if It is something else like 'Maruti' It displays cars with brand name Maruti that are stored in my Table.
Similarly i also have created a variable $where in which where statements are stored.
$where="where type='$_GET[type]' $brand AND status='a'";
if(isset($_GET['sort'])){
if(($_GET['sort'])=='ASC'){
$where="where type='$_GET[type]' $brand AND status='a' ORDER BY price";
}
elseif(($_GET['sort'])=='DESC'){
$where="where type='$_GET[type]' $brand AND status='a' ORDER BY price DESC";
}
}
If i don't use these variables '$brand' and '$where' then i will have to repeat it no of times. You can see so far i have used $brand in 4 queries.
I have tried
<?php
$type='$_GET[type]';
if($brand!='All'){
$brand="AND brand= ?";
}
else{
$brand="";
}
if ($stmt = $mysqli->prepare("SELECT * FROM Table WHERE type= ? $brand")) {
/* bind parameters for markers */
$stmt->bind_param("ss", $type, $brand);
/* execute query */
$stmt->execute();
/* bind result variables */
$res = $stmt->get_result();
while ($row = mysqli_fetch_array($res)) {
?>
<li><?php echo $row['name']; echo ' '; echo $row['type']; echo ' '; echo $row['brand']; ?></li>
<?php
}
/* close statement */
$stmt->close();
}
/* close connection */
$mysqli->close();
?>
Now if brand value is 'All' then it causes error in bind_param statement because $brand is null in that. and if value is something right like 'Maruti' then it displays nothing...No error just empty screen.
Is it not possible to do it in this way or i am doing something wrong in coding, please help me out ??? Thanks in advance...