I have started to write a content management system in PHP. I need to run the following SQL query against a database table:

select * from blogposts where title like %:term%

This query however returns an empty result set and no data is being printed out to a PHP web page. Here is my code:

Database.php:

<?php

class Database {

    protected $connection = null;
    protected $statement = null;

    public function openConnection($username, $password) {
        try {
            $this->connection = new PDO("mysql:host=localhost;dbname=content_management_system", $username, $password);
        } catch (Exception $e) {
            die($e);
        }
        $this->connection->exec("SET CHARACTER SET UTF-8");
    }

    public function getResultSet() {
        return $this->statement->fetch();
    }

}

?>

BlogPosts.php:

<?php

include('./Database.php');

class BlogPosts extends Database {

    function __construct() {
        $this->openConnection("stack", "overflow");
    }

    public function getBlogPostsWithTag($tag) {
        $sql = "select * from blogposts where tag = :tag";
        $this->statement = $this->connection->prepare($sql);
        $this->statement->bindParam(":tag", $tag);
        $this->statement->execute();
    }

    public function getBlogPostsWithName($term) {
        $sql = "select * from blogposts where title like %:term%";
        $this->statement = $this->connection->prepare($sql);
        $this->statement->bindParam(":term", $term);
        $this->statement->execute();
    }

}

?>

Just so that we are clear, getBlogPostsWithTag() works fine, but this very similar getBlogPostsWithName($term) doesn't return anything from the database.

search.php:

<?php
    include("./BlogPosts.php");
    if (isset($_REQUEST["search"])) {
        $blogpostsTerm = new BlogPosts();
        $blogpostsTerm->getBlogPostsWithName($_REQUEST["term"]);
        while ($resultset = $blogpostsTerm->getResultSet()) {
            echo "<div class='blog-post'>";
            echo "<h2 class='blog-post-title'>" . $resultset["title"] . "</h2>";
            echo "<p class='blog-post-meta'>" . $resultset["created_at"] . " by <a href='#'>" . $resultset["author"] . "</a></p>";
            echo $resultset["lede"];
            echo "</div>";
        }
    }
?>

I am expecting the method getBlogPostsWithName($term) to return some data from the database table, but the actual result is that nothing is being returned/echoed to the PHP web page.

3 Answers

3
Elanochecer On Best Solutions

Change this:

public function getBlogPostsWithName($term) {
        $sql = "select * from blogposts where title like %:term%";
        $this->statement = $this->connection->prepare($sql);
        $this->statement->bindParam(":term", $term);
        $this->statement->execute();
    }

to this:

public function getBlogPostsWithName($term) {
        $sql = "select * from blogposts where title like :term";
        $this->statement = $this->connection->prepare($sql);
        $bindingTerm = "%$term%";
        $this->statement->bindParam(":term", $bindingTerm);
        $this->statement->execute();
    }
1
user3783243 On

A placeholder needs to be on its own. Either concatenate the wildcards on the variable, or in the SQL concat the %s with the placeholder.

$sql = "select * from blogposts where title like :term";
$this->statement = $this->connection->prepare($sql);
$this->statement->bindParam(":term", $term);
$term =  '%' . $term . '%';

or

$sql = "select * from blogposts where title like concat('%', :term, '%')";
$this->statement = $this->connection->prepare($sql);
$this->statement->bindParam(":term", $term);

or you could use https://www.php.net/manual/en/pdostatement.bindvalue.php

$sql = "select * from blogposts where title like :term";
$this->statement = $this->connection->prepare($sql);
$this->statement->bindValue(":term", '%' . $term . '%');
0
GodlyHedgehog On

As mentioned above by others, you should have % characters inside the parameter.

$this->statement->bindParam("term", "%" . $term . "%");

Reason behind it is that the wildcards should be a part of the string. Your result is, however, equal to like %"blabla"% which is wrong.

And you don't need a colon in bindParam()