I need urgent help with my prepare statement!

[Jimmy_jolling]

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 .

 

[Barand]

Read the manual.

 

store_result() returns boolean, not a result object.

[thara]

Here you need get_result()

 

try it like this:

if (!empty($date1_raw) && !empty($date2_raw))  {

  $sql = "SELECT * FROM costs
          WHERE costdate BETWEEN ? AND ? AND userid= ?
          ORDER BY costdate";

  $stmt = $conn->prepare($sql);
 
  if ($stmt) {    
    $stmt->bind_param("ssi", $date1_raw, $date2_raw, $session_id);
    $stmt->execute();
    $result = $stmt->get_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']));
    }
  }
}

NOTE:

It is best practice to explicitly list every column you want returned instead of using select *, there are situations where you might want to save time and omit certain columns from the results.

Edited November 17, 2016 by thara

[Jacques1]

Several things:

• mysqli is a very cumbersome and counter-intuitive API compared to PDO, so unless you absolutely must use it, just switch to PDO.
• This is not really how prepared statements in mysqli work. You're supposed to get the result set with mysqli_stmt::bind_result() and mysqli_stmt::fetch().
• mysqli_stmt::get_result() depends on a specific MySQL library (mysqlnd) and is not available on all systems. While mysqlnd has become the standard, there are still hosters which choose to deactivate it.
• Refrain from declaring your topics as “urgent”. It's just annoying.

Edited November 17, 2016 by Jacques1