In my script for a web app, I have a search box that shows a list of records when a person’s last name is typed out.
How can I modify what the user types in to include in the search result code – something like “Brown, James” instead of just “Brown” for example? At the moment, it only works on the person’s last name. When I type in a comma after the last name, it says “No records found.”
I know I can add an AND clause in the SQL query, and it will work when I test it in phpMyAdmin, but not in my code.
<?php
require('../dbconnect.php');
// Attempt search query execution
try{
if(isset($_REQUEST["term"])) {
// create prepared statement
$sql = "SELECT id, last_name, first_name, middle_name, suffix, precinct,
residential_address FROM members WHERE last_name LIKE :term";
$stmt = $pdo->prepare($sql);
$term = $_REQUEST["term"] . '%';
// bind parameters to statement
$stmt->bindParam(":term", $term);
// execute the prepared statement
$stmt->execute();
$result = $stmt->fetchAll();
if($result) {
foreach ($result as $row) {
echo "<p>{$row['id']} - {$row['last_name']}, {$row['first_name']}
{$row['middle_name']} {$row['suffix']} ({$row['precinct']}) |
{$row['residential_address']}</p>";
}
} else {
echo "<p>No records found</p>";
}
}
} catch(PDOException $e){
die("ERROR: Could not able to execute $sql. " . $e->getMessage());
}
// Close statement
unset($stmt);
// Close connection
unset($pdo);
?>
SELECT id, last_name, first_name, middle_name, suffix, precinct,
residential_address
FROM members WHERE last_name LIKE "brown" AND first_name LIKE "angela"
makes this result in phpMyAdmin:
This line of the code makes the output for my application:
If the format is always consistent, that makes thinks a lot simpler.
You can explode() the search term to an array of the two values, then pass that directly into the execute() as un-named parameters, to target two ? placeholders in the query.
I guess? So at the moment, people type in the last name, like “Brown” for example. Then my code takes it and shows something like “Brown, James (01-04) | 123 Sample Street, Somewhere, US 12345”
What I want to do is modify the search so that when someone types in “Brown, James” it modifies the search results to narrow down what that person is looking for. So if there’s 30 records with the last name “Brown”, only 5 of them may have James Brown in it, so only 5 records should show in the results.
So it is people typing “Brown, James”.
That does make it simple, like I explained. Though I’m not sure I trust humans to be so consistent with formatting search queries. That’s why I ask if it is coming from code or people typing.
But assuming well formed consistent queries:-
$params = explode(', ', $_REQUEST["term"]);
$sql = "SELECT id, last_name, first_name, middle_name, suffix, precinct,
residential_address
FROM members WHERE last_name LIKE ? AND first_name LIKE ?" ;
$stmt = $pdo->prepare($sql);
$stmt->execute($params);
Something along those lines should work.
Though it is fragile, relying on the user query being typed in the exact, correct format.
I modified that into my query and I’m getting this:
ERROR: Could not able to execute SELECT id, last_name, first_name, middle_name, suffix, precinct, residential_address FROM members WHERE last_name LIKE ? AND first_name LIKE ?. SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens
try{
if(isset($_REQUEST["term"])) {
// create prepared statement
$sql = "SELECT id, last_name, first_name, middle_name, suffix, precinct,
residential_address FROM members
WHERE last_name LIKE ? AND first_name LIKE ?";
$stmt = $pdo->prepare($sql);
$params = explode(', ', $_REQUEST["term"]);
//$term = $_REQUEST["term"] . '%';
// bind parameters to statement
//$stmt->bindParam(":term", $term);
// execute the prepared statement
$stmt->execute($params);
$result = $stmt->fetchAll();
if($result) {
foreach ($result as $row) {
echo "<p>{$row['id']} - {$row['last_name']}, {$row['first_name']}
{$row['middle_name']} {$row['suffix']} ({$row['precinct']}) |
{$row['residential_address']}</p>";
}
} else {
echo "<p>No records found</p>";
}
}
There is no need with PDO. If you are still doing that, that is the problem.
The SQL error says there is a mis-match in the number of parameters passed to it, to the number of placeholders.
I used two ? in the query, and the array has two values, so it should work.
I can’t see why it won’t work, maybe someone else needs to take a look.
I have a members table in a database here, not identical of course, but similar. Running a similar query works there.
$request = 'Doe, John' ;
$params = explode(', ', $request);
$query = "SELECT members.id, members.firstname, members.lastname, members.profile FROM members WHERE lastname LIKE ? AND firstname LIKE ?";
$sql = $db->prepare($query) ;
$sql->execute($params);
$result = $sql->fetchAll() ;
var_dump($result); exit;
The code is not identical either, but the basics are the same.
Are you getting the sql error about the number of bound variables not matching on the same page request as the var_dump() output you are showing? I suspect that you are telling/showing the result from two different page requests.
Also, to do this in a more flexible and general way, you would explode the submitted search term on just the ‘,’ character, trim the values, then dynamically build the sql statement with an AND’ed term for each element. For a single value, you would end up with one LIKE term in the query (which you may want to actually search for in both the first and last name columns) and for two values, you would end up with two LIKE terms in the query.
Yes, but are you getting the sql error at the same time the var_dump() looks like that?
No. You would dynamically build that part of the the sql query to match how many elements there are in the exploded array.
You also need to decide how you want to use wild-card matches with this. Your goal should be for a search to work, regardless of how much or how little the visitor on the site enters. To do that you need to define what you want to happen for all possible variations of the submitted input value.
