1

I'm basically trying to accomplish this query with autocomplete (each keystroke triggers a query)(it's working, except last name isn't querying correctly):

$query="SELECT * FROM names WHERE (first like '" . $_POST["keyword"] . "%') OR (last like '%" . $_POST["keyword"] . "') AND year>$curr_year";

Any help is appreciated!

draft
  • 305
  • 2
  • 14
  • 1
    You're missing an additional level of () around the OR conditions. `$query="SELECT * FROM names WHERE ((first like '" . $_POST["keyword"] . "%') OR (Last like '" . $_POST["keyword"] . "%')) AND year>$curr_year";` What you have is looking for first name like wihtout a year and last name like with a specific year. – xQbert Feb 26 '16 at 18:07
  • 1
    Well, what would be the correct way to query `last`? You're matching on the _end_ of lastname. – Joachim Isaksson Feb 26 '16 at 18:07
  • 1
    You should use prepared statements. – chris85 Feb 26 '16 at 18:08
  • Got it! Thanks! My updated query is: $query="SELECT * FROM names WHERE ((first like '%" . $_POST["keyword"] . "%') OR (last like '%" . $_POST["keyword"] . "%')) AND gradyear>$curr_year"; – draft Feb 26 '16 at 18:11
  • Also, I'll look into prepared statements ... what are some of the advantages? – draft Feb 26 '16 at 18:12
  • @faalbane Well, the first person called O'Connor won't crash your search for a start :) – Joachim Isaksson Feb 26 '16 at 18:14
  • Thank you for this advice ... I'm researching right now; I see the advantages ... Wow ... – draft Feb 26 '16 at 18:40

1 Answers1

1
$query="SELECT * FROM names WHERE ((first like :first) OR (last like :last)) AND gradyear > :year";
$pdo = new PDO($dsn, $username, $password, $options);
$con = $pdo->prepare($query);
$con->execute([
 ':last'=> "%$_POST[keyword]",
 ':first'=> "$_POST[keyword]%",
 ':year'=>$curr_year
]);
$results = $con->fetchAll(PDO::FETCH_ASSOC);

That is how you do a prepared statement. The reason why you would want to use it is to prevent a SQL injection. You can use PDO for prepared statements.

Roger
  • 3,226
  • 1
  • 22
  • 38
  • Thank you! I'm researching PDO and SQL injection right now ... Wow ... I appreciate the advice ... – draft Feb 26 '16 at 18:42
  • Is "%$_POST[keyword]%" and $_POST["keyword"] equivalent? – draft Feb 26 '16 at 18:43
  • No, you have to add the `%` wildcards around the variable. `"%".$_POST["keyword"]."%"` is the equivalent, but it has more characters in it. The wildcards tell the database to look for anything that contains the keyword. http://www.w3schools.com/sql/sql_wildcards.asp – Roger Feb 26 '16 at 18:46
  • Thank you ... do you know how to install PDO - MySql driver on server? I have access to web root and PHPMyAdmin ... – draft Feb 26 '16 at 19:02
  • pdo and mysqli are standard libraries in php5 apt get http://stackoverflow.com/questions/13375061/installing-pdo-driver-on-mysql-linux-server – Roger Feb 26 '16 at 19:16
  • You need to bind each placeholder, also looks like OP only wants any trailing characters on first, and any leading characters on last. – chris85 Feb 26 '16 at 19:17
  • I'm getting this error: Uncaught exception 'PDOException' with message 'could not find driver' in C:\filename.php:21 Stack trace: #0 – draft Feb 26 '16 at 19:42
  • What would be the mysqli syntax? – draft Feb 26 '16 at 20:20
  • http://php.net/manual/en/pdo.installation.php If you are using windows, then install using that. And here is the mysqli syntax http://php.net/manual/en/mysqli.quickstart.prepared-statements.php – Roger Feb 26 '16 at 20:39