Hello, i’m stuck for a day now on transforming my “unsafe normal query” to a safe one using the prepare statement.
Really need some help and will be very thankfull if someone is willing to read through my code:
<?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 ? BETWEEN ? AND ? AND userid=? ORDER BY ?")) {
$stmt->bind_param("sssis", $costdate, $date1_raw, $date2_raw, $session_id, $costdate);
$stmt->execute();
$result = $stmt->get_result();
$num_of_rows = $result->num_rows;
while($row = $result->fetch_assoc()) { ?>
<tr>
<td><?php echo $row['subcategory']; ?></td>
<td><?php echo $row['costname']; ?></td>
<!-- Added "€" sign infront of price !-->
<td><?php echo "€ " . $row['price']; ?></td>
<td><?php echo $row['info']; ?></td>
<!-- Added Date Function to convert format !-->
<td><?php echo date( "d/m/y",strtotime ($row['costdate'])); ?></td>
</tr>;
<?php }}} ?>
<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>
Do you think the ? here represents a value or or a column name? You already had help with the order by column name. Try to apply the same thought process to other column names.
Yep. Now the question is what format are your date strings. Look like you have day/month/year while I suspect your database expects year/month/day. Might consider firing up a mysql console window and executing the query by hand until you know you have the correct format.
$sql_datefilter = "SELECT * FROM costs WHERE costdate BETWEEN '$date1_raw' AND '$date2_raw' AND userid='".$_SESSION['id']."'ORDER BY costdate";
Works fine and selects what i need.
But the prepare statement query:
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();
$stmt->store_result();
printf("Number of rows: %d.\n", $stmt->num_rows);
Prints: Number of rows: 0.
also my loop to echo says this:
Fatal error: Call to a member function fetch_assoc() on null in location on line 20
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']));
}
that’s very weird since i watched dozens of videos explaining both PDO and OOP and they all did it like this for OOP
Also followed the official documentation on the php website that explained it like this for OOP…
I must admit I always get a bit confused by this, but would passing the session_id value in as an integer tie in with the fact that the working query has it surrounded by single-quotes? That is, should it be passed in using an ‘s’ mask instead? I’m not familiar with mysqli.
