CODEWITHSUNDEEP
phpmysqlpdo
cainy393
cainy393

Reputation: 462

Problems with using PDO to perform an advanced search query

I have the following code and all of the search functions work except for the title field. So I can search by genre, date, location etc... but not by title. When attempting to search by title nothing is returned at all. Can anyone help me with this?

Also, is there a more efficient way to count all the fields before limiting it for use in pagination later on?

$today = date("Y-m-d");
$query = "SELECT * FROM TABLE_NAME WHERE Date >= '$today'";

$bind = Array();

if ($_GET["Title"] && $_GET["Title"] != "") {
    $query .= " and Title like %?%";
    $bind['Title'] = $_GET['Title'];
}
if ($_GET["Genre"] && $_GET["Genre"] != "") {
    $query .= " and Genre like %?%";
    $bind['Genre'] = $_GET['Genre'];
}
if ($_GET["Location"] && $_GET["Location"] != "") {
    $query .= " and Location like %?%";
    $bind['Location'] = $_GET['Location'];
}
if ($_GET["Date"] && $_GET["Date"] != "") {
    $query .= " and Date = %?%";
    $bind['Date'] = $_GET['Date'];
}

$stmt = $db->prepare($query);
$stmt->execute($bind);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
$num = count($rows);

$query .= " ORDER BY Date LIMIT $limit, 9";
$stmt = $db->prepare($query);
$stmt->execute($bind);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

Edit: After everyone's help I thought I would post my now revised code for future reference. It turns out the other fields were not working, but instead due to the if statement all this was nested in the code simply wasn't being executed.

$today = date("Y-m-d");
$query = "SELECT * FROM TABLE_NAME WHERE Date >= '$today'";
$countq = "SELECT count(*) FROM TABLE_NAME WHERE Date >= '$today'";

$bind = Array();

if ($_GET["Title"] && $_GET["Title"] != "") {
    $query .= " and Title like :title";
    $countq .= " and Title like :title";
    $bind[':title'] = "%{$_GET['Title']}%";
}
if ($_GET["Genre"] && $_GET["Genre"] != "") {
    $query .= " and Genre like :genre";
    $countq .= " and Genre like :genre";
    $bind[':genre'] = "%{$_GET['Genre']}%";
}
if ($_GET["Location"] && $_GET["Location"] != "") {
    $query .= " and Location like :loc";
    $countq .= " and Location like :loc";
    $bind[':loc'] = "%{$_GET['Location']}%";
}
if ($_GET["Date"] && $_GET["Date"] != "") {
    $query .= " and Date = :date";
    $countq .= " and Date = :date";
    $bind[':date'] = "{$_GET['Date']}";
}

$stmt = $db->prepare($countq);
$stmt->execute($bind);
$rows = $stmt->fetchAll();
$num = count($rows);

$query .= " ORDER BY Date LIMIT $limit, 9";
$stmt = $db->prepare($query);
$stmt->execute($bind);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

Upvotes: 0

Views: 501

Answers (1)

Your Common Sense
Your Common Sense

Reputation: 157872

all of the search functions work

With the given query it is not true

From PDO tag wiki:

placeholders cannot represent an arbitrary part of the query, but a complete data literal only. Neither part of literal, nor whatever complex expression or a syntax keyword can be substituted with prepared statement.

Prepare FULL literal first: $name = "%$name%"; and then bind it.

As for the "more" efficient method for pagination - yes, oh yes.
With your current way of counting data you don't actually need other queries. as you have ALL the data already and can paginate it as well.
But of course it will pollute all the memory soon. So, if you want to get a count of rows from database, get the very count: run the same query but instead of SELECT * make it "SELECT count(*)

There are not any errors returned, that's why I am so confused

From PDO tag wiki again:

It is essential to set ERRMODE_EXCEPTION as a connection option as it will let PDO throw exceptions on connection errors. And this mode is the only reliable way to handle PDO errors.

Upvotes: 2

Related Questions