Mysql Return all values of a table where column is equal to user input

Question

I have some sql statements that accepts a number and if that number is equal to the value of a column in a database, it should return all rows in the database that has the same value. Unfortunately the rows only return blog_post_id that has a value 0.

This is my codes below:

<?php
$options = array(
PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
);
$blog_post_id = !empty($_POST['blog_post_id']) ? $_POST['blog_post_id'] 
: '';

$pdo=new PDO("mysql:dbname=db;host=localhost","username","password", 
$options);
$statement=$pdo->prepare("SELECT * FROM comment WHERE blog_post_id = 
'$blog_post_id'");
$statement->execute();
$results=$statement->fetchAll(PDO::FETCH_ASSOC);
$json=json_encode($results);
if ($json)
    echo $json;
else
    echo json_last_error_msg();

?>

Learn to use parameters. If you used parameters, your query would work. — Gordon Linoff, Jul 07 '17 at 11:20
Might this SO question help you [question](https://stackoverflow.com/questions/16130698/php-pdo-get-only-one-value-from-mysql-value-that-equals-to-variable) — always-a-learner, Jul 07 '17 at 11:22
I recommend using `{}` for conditionals, and indenting params for easier reading of code — treyBake, Jul 07 '17 at 11:24

score 0 · Answer 1 · answered Jul 07 '17 at 11:29

0

You are actually missing the point of using the function prepare() and you need to check if the query does actually return any results..

<?php
$options      = array(
    PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'
);
$blog_post_id = !empty($_POST['blog_post_id']) ? $_POST['blog_post_id'] : '';

$pdo       = new PDO("mysql:dbname=db;host=localhost", "username", "password", $options);
$statement = $pdo->prepare("SELECT * FROM comment WHERE blog_post_id = ?");
$statement->execute([$blog_post_id]);
$results   = $statement->fetchAll(PDO::FETCH_ASSOC);

$json = array();

if (count($results) > 0) {
    foreach ($results as $row) {
        $json[] = array(
            'id' => $row['blog_post_id'],
            'Field' => $row['Column'],
            'AnotherField' => $row['AnotherColumn'],
            'AnotherField1' => $row['AnotherColumn1'],
            'ETC' => $row['AnotherColumnName']
        );
    }

    echo json_encode($json);
} else {

    echo "no data found";
}

?>

answered Jul 07 '17 at 11:29

Masivuye Cokile

4,754
3
19
34

my query returns results when i set the blog_post_id to the value 1 – Josh Jul 13 '17 at 16:55
what i want is if the user enters 1 or 2 my query should return the results – Josh Jul 13 '17 at 16:56
do u have id with 2? – Masivuye Cokile Jul 14 '17 at 09:26
i have an id of 0 and 1 – Josh Jul 14 '17 at 13:33
so how do you expect results if you have id of 0 and 1 with a 2? – Masivuye Cokile Jul 14 '17 at 13:36

score 0 · Answer 2 · answered Jul 07 '17 at 11:35

You should do variable binding like so:

$pdo=new PDO("mysql:dbname=db;host=localhost","username","password", $options);
$statement=$pdo->prepare("SELECT * FROM comment WHERE blog_post_id = :blog_post_id");
$statement->execute(['blog_post_id' => $blog_post_id]);

This will also prevent 1st level SQL-Injection as described here: Are PDO prepared statements sufficient to prevent SQL injection?

Mysql Return all values of a table where column is equal to user input

2 Answers2