Prepared Statements not allowing me to Search and Echo on the Same Page

[mslilafowler]

Hi everyone…

Could someone be so kind as to explain to me why my code cannot work?

I’m trying to build a product page, where a search query is listed out on the same page.
So while constructing how the products will be echoed out, I first check if isset($_GET[‘search’]) to see if the user simply landed on the page or if he’s actually searching for something.

The code:

$sql = 'SELECT * FROM listings';

if (isset($_GET['search'])) {

    //Users can search by:
    //Title or Description or Vendor Code
    $sql .= " WHERE L_TITLE LIKE ? OR v_ref LIKE ? OR L_DESCRIPT LIKE ? "; //check if there is any search queries in the url
    //MySQL uses short-circuit evaluation for the OR operator. In other words, MySQL stops evaluating the remaining parts of the statement when it can determine the result.

    $search_param = $_GET['search'];
    $passThis = "%" . $search_param . "%";

    $sql .= ' ORDER BY L_OPEN_DATE '; //learn how to search by relevance instead

} else {
    $sql .= 'ORDER BY rand(700)'; //refresh this seed every 24hrs using a user-session
}

$sql .= ' LIMIT 200 ';

$stmt = $conn->prepare($sql);
$stmt->bind_param('sss', $passThis, $passThis, $passThis);
$stmt->execute();
$result = $stmt->get_result();

while ($row = $result->fetch_assoc()) {
	//assign rows to variables
	//echo out product list here
}

The trouble I’m faced with is when I try to use prepared statements on the search value… The page works (lists all the products with the keyword the user typed in), however, I can’t view the ‘landing’ page any longer. In other words, if I land on the page without actually searching for anything, it does not list any products and gives this error:
Fatal error: Uncaught Error: Call to a member function bind_param() on boolean in...
Which, I assume, means that the actual query is failing, because it’s looking to bind paramaters it cannot find (since it’s out of the “if” statement).

How, pray, can I modify this code to use prepared statements while also displaying the search results on the same page?

[phdr]

There are two problems.

1. You have a syntax error in the sql query statement when there is no WHERE clause. This is what is causing the current fatal error. The prepare call is failing but you have no error handling for it, so, the code continues to run and produces a follow-on error at the bind_param() call. You need to always have error handling for all database statements. The easiest way of adding error handling without adding logic to each statement that can fail (the connection, query, prepare, and execute statements can all fail) is to use exceptions for errors and in most cases simply let php catch the exception where it will use its error_reporting, display_errors, and log_errors settings to control what happens with the actual error information. To enable exceptions for the mysqli extension, add the following line of code before the point where you make the connection -

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

Once you do this, you will be getting an sql query error that should allow you to find the problem.

2. When there is no WHERE clause in the query, there’s nothing to supply to the bind_param() call, so there’s no point in calling it or using prepare(). Your if(){} logic should completely build the remainder of the sql query statement with the WHERE clause in it, prepare it, bind the inputs, execute the query, and get the result in $result. Your else{} logic should completely build the remainder of the sql query without the WHERE clause in it, and execute the query by calling the mysqli query() method. This will produce the same query result in $result. The remainder of your code, after the end of the if/else logic, will just fetch the data using the common $result variable.

[mslilafowler]

Ah I see, thank you. I fixed the first syntax error (there was a missing whitespace after the 'FROM listings';

It’s now giving a second error, the one in your second explanation (no parameters to bind cause there’s no WHERE clause)

But the two codes are quite similar (nearly identical…sometimes actually identical) after the query is built.
I’ve always been told that your code should never have unnecessary duplication, which I think will happen once I repeat the code blocks inside the if{} and else{} statements… What do you recommend ?

[phdr]

If you are concerned about unnecessary and inconsistent code when dynamically building sql query statements, switch from the overly complicated and inconsistent mysqli extension to the much simpler PDO extension and extend the PDO class with a general-purpose prepared/non-prepared query method. Your posted code would become -

<?php

$params = []; // an array to hold any input parameters

$sql = 'SELECT * FROM listings';

if (isset($_GET['search'])) {

    //Users can search by:
    //Title or Description or Vendor Code
    $sql .= " WHERE L_TITLE LIKE ? OR v_ref LIKE ? OR L_DESCRIPT LIKE ? "; //check if there is any search queries in the url
    //MySQL uses short-circuit evaluation for the OR operator. In other words, MySQL stops evaluating the remaining parts of the statement when it can determine the result.

    $search_param = $_GET['search'];
    $passThis = "%" . $search_param . "%";

	$params[] = $passThis;
	$params[] = $passThis;
	$params[] = $passThis;

    $sql .= ' ORDER BY L_OPEN_DATE '; //learn how to search by relevance instead

} else {
    $sql .= ' ORDER BY rand(700)'; //refresh this seed every 24hrs using a user-session
}

$sql .= ' LIMIT 200 ';

$stmt = $pdo->q($sql,$params);
$result_data = $stmt->fetchAll();

foreach($result_data as $row) {
	//assign rows to variables
	//echo out product list here
}

For any portion of the sql query statement that has a ? place-holder, just add an entry to the $params array for each place-holder.

The class to extend the PDO class is -

<?php
class _pdo extends pdo
{
	public function __construct($dsn,$username=null,$password=null,$driver_options=null)
	{
		parent::__construct($dsn, $username, $password, $driver_options);
		$this->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // set the error mode to exceptions
		$this->setAttribute(PDO::ATTR_EMULATE_PREPARES,false); // turn off emulated prepared queries, run real prepared queries
		$this->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE,PDO::FETCH_ASSOC); // set default fetch mode to assoc
	}

	// general purpose query method. accepts an optional array of input parameters and returns a pdostatement object
	public function q($sql,$params=array())
	{
		if($params){
			// prepared query
			$stmt = parent::prepare($sql);
			$stmt->execute($params);
			// note: to execute a prepared query more than once, you would call the ->execute() method on the returned stmt object, supplying an array of new input values for each new execution
		} else {
			// non-prepared query
			$stmt = parent::query($sql);
		}
		
		// return the pdo statement object
		return $stmt;
	}
}

[mslilafowler]

Thank you for the detailed response, that is so cool. I’m working on one of my biggest projects right now, and moving to PDO (learning about it) currently seems daunting. I will def make a note about it for the near future though, this will serve as a great starting point for me. Thank you

[phdr]

I’m not sure why you would think switching to the PDO extension would be a daunting task.

Both mysqli and PDO use ? place-holders in prepared queries, so the sql query syntax is the same.

The general purpose q() query method in the extended class makes executing a query easy. If the query is a non-prepared query, just call $stmt = $pdo->q($sql); If the query is a prepared query or optionally can be (what this thread is doing), define the $params array and call $stmt = $pdo->q($sql,$params);

For a SELECT query, you need to fetch the data from the query. By pre-fetching the data from the query into an appropriately named php variable, you are separating the different concerns in your code, and decoupling the database specific code, that knows how to query for and retrieve data, from the presentation code, that knows how to produce the output from the data. For a set of data, consisting of zero or more rows, use the PDOStatement fetchAll() method (shown in the example in this thread.) The fetachAll() method can also be used to fetch a column of data as an array, instead of an array of rows of data. For a query that will match at most one row, use the PDOStatement fetch() method. The PDOStatement fetchColumn() method can be used to fetch a single column from a single row, such as for a COUNT(*) query.

In the case of a set of data, after you fetch it into a php variable, you can use the php count() function to find the number of rows (the PDOStatement ->rowCount() method is not universally functional for SELECT queries and should not be used in this case.)

The result from all the fetch methods is a boolean true if the query returned any data and a boolean false if the query didn’t return any data.

To get the last insert id for an insert/update query, call $pdo->lastInsertId(); To get the number of affected rows for an insert/update/delete query, call the PDOStatement ->rowCount() method.

This list covers most database usage for typical applications. See the PDO and PDOStatement php.net documentation for anything not covered here.