Hello iv'e been coding on my website for a while and recently added a filter that filters 2 dates selected and show the costs to the screen. This works excellent, but as a rookie in coding i got told that normal queries like this:
if (!empty($date1_raw) && !empty($date2_raw)) {
//Query for selecting all costs of the date filter.
$sql_datefilter = "SELECT * FROM costs WHERE costdate BETWEEN '$date1_raw' AND '$date2_raw' AND userid='".$_SESSION['id']."'ORDER BY costdate";
$result_costs = mysqli_query($conn, $sql_datefilter);
} else {
// costslist define mysql
$sql_costs = "SELECT * FROM costs WHERE userid='".$_SESSION['id']."'ORDER BY costdate";
$result_costs = mysqli_query($conn, $sql_costs);
}
Are very unsafe and prone to SQL_injection...
So now i made a test file to transform this into a prepared statement to be safe:
<?php
include('includes/sessionstart.php');
include 'dbh.php';
$date1_raw = $_POST['selected_date1'];
$date2_raw = $_POST['selected_date2'];
if (!empty($date1_raw) && !empty($date2_raw)) {
if ($stmt = mysqli_prepare($conn, "SELECT * FROM costs WHERE costdate BETWEEN ? AND ? AND userid=? ORDER BY costdate")) {
$stmt->bind_param("ssi", $date1_raw, $date2_raw, $session_id);
$stmt->execute();
$result = $stmt->store_result();
printf("Number of rows: %d.\n", $stmt->num_rows);
while($row = $result->fetch_assoc()) {
echo $row['subcategory'];
echo $row['costname'];
// Added "€" sign infront of price !-->
echo "€ " . $row['price'];
echo $row['info'];
// Added Date Function to convert format !-->
echo date( "d/m/y",strtotime ($row['costdate']));
}}} ?>
<form action="test.php" method="post">
<input type="date" name="selected_date1" value="<?php echo date('d-m-Y'); ?>" />
<input type="date" name="selected_date2" value="<?php echo date('d-m-Y'); ?>" />
<input type="submit" name="filter_date" value="Filter">
</form>
But this prints: Number of rows: 0.
Fatal error: Call to a member function fetch_assoc() on boolean in D:\appdata\IIS\vhosts\webtex.be\budgetc.webtex.be\test.php on line 20
can anyone read through my code and see if i did something wrong? I'm really confused and i am a beginner that really needs a bump in the right direction .