SQLite Query Returns 0 rows When Similar Query Has No Problems

Question

I've recently started using PHP and decided to create a website with an attached SQLite database. All was running smoothly until I ran into a very confusing issue. The query in the function below returns with 0 rows, no matter what parameters are passed in.

public function articleListSearch($blog_id, $search_for, $search_column){
        $sql = "SELECT article_id, article_name, article_content, art_img FROM articles WHERE parent_blog = :blog_id AND :search_column LIKE :search_for";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([
            ":blog_id"=>$blog_id,
            ":search_column"=>$search_column,
            ":search_for"=>"%{$search_for}%",
        ]);
        $results = [];
        while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)){
            $results[] = [
                'article_id' => $row['article_id'],
                'article_name' => $row['article_name'],
                'article_content' => $row['article_content'],
                'art_img' => $row['art_img'],
            ];
        }
        return $results;
    }

Now, the reason this is so confusing to me is that a similar query returns the correct number of rows with the correct data. To create the articleListSearch function, I literally modified the code from the following query and modified the SQL and execute() parameters.

public function search($param){
        $sql = "SELECT blog_id, blog_name, about, cover_img FROM blogs WHERE blog_name LIKE :param ORDER BY hits DESC";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([
            ":param"=>"%{$param}%",
        ]);
        $results = [];
        while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)){
            $results[] = [
                'blog_id'=>$row['blog_id'],
                'blog_name'=>$row['blog_name'],
                'about'=>$row['about'],
                'cover_img'=>$row['cover_img'],
            ];
        }
        return $results;
    }

I'm sure the issue is something small that I've overlooked, but I can't find it.

score 0 · Accepted Answer · answered Apr 04 '20 at 06:46

You cannot bind columns. You need to pass the actual column via your query. The first query should be

$sql = "SELECT article_id, article_name, article_content, art_img FROM articles WHERE parent_blog = :blog_id AND $search_column LIKE :search_for";

Then modify your execute line as:

$stmt->execute([
            ":blog_id"=>$blog_id,                
            ":search_for"=>"%{$search_for}%",
 ]);

Just make sure $search_column cannot be modified by the user

If you still get 0, make sure the parameters you are searching for actually exist in your database.

SQLite Query Returns 0 rows When Similar Query Has No Problems

1 Answers1