PDO errors in SELECT query using WHERE clause

[rghollenbeck]

I have a MySQL database on justhost with a field called "category" that I want to use in a WHERE query. The database opens fine without any WHERE clause. It's only when I begin to use a WHERE clause that I begin to get into trouble. All the "Gurus" out there tell me I need to be shifting over to PDO, so here is my attempt to get up to speed.

The previous page (select_category.php) had a form with a selection drop-down box for category. I addressed it in the next file (quiz.php) like this:
 

$cat = $_POST['category'];

Now I want to query the data:

$db = new PDO('mysql:host=localhost;dbname=' . $dbname , $dbusername, $pass);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$sql->prepare("SELECT * FROM QuestionsAnswers WHERE category = " . $cat); //This is line 23

foreach ($db->query($sql) as $row) {
print '<p>' . $row['question'] .' <br /> '. $row['displaystring'] . '</p>';
}

but I'm getting errors:

 

 

PHP Notice: Undefined variable: sql in quiz.php on line 23
PHP Fatal error: Call to a member function prepare() on a non-object in quiz.php on line 23

 

I have been around and around on this for two days. Does anybody see what I am obviously missing? Thanks.

 

 

[Ch0cu3r]

The problem is you are trying to invoke a prepared query. But the variable $sql doesn't exist. Then trying to pass it to pdo query.

 

To query the database with a standard pdo query your code will be

$cat = $_POST['category'];

$db = new PDO('mysql:host=localhost;dbname=' . $dbname , $dbusername, $pass);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$sql = "SELECT * FROM QuestionsAnswers WHERE category = " . $cat;

foreach ($db->query($sql) as $row) {
print '<p>' . $row['question'] .' <br /> '. $row['displaystring'] . '</p>';
}

If you want to use prepared statements then the code would be

$cat = $_POST['category'];

$db = new PDO('mysql:host=localhost;dbname=' . $dbname , $dbusername, $pass);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$stmt = $db->prepare('SELECT * FROM QuestionsAnswers WHERE category = ?'); // Values used in the query are marked with the ? placeholder
$stmt->execute(array($cat));  // execute the query. Pass in the an array listing the values to be used in the query
$results = $stmt->fetchAll(); // Get the results

foreach ($results as $row) {
    print '<p>' . $row['question'] .' <br /> '. $row['displaystring'] . '</p>';
}

Full documentation (and examples) on PDO can be found at http://php.net/pdo

Edited October 29, 2013 by Ch0cu3r

[rghollenbeck]

Thank you Ch0cu3r.  

 

Actually, I did have the variable ($cat = $_POST['category'] declared but I failed to show it in my question.

 

I changed my query as you recommended:

$sql = "SELECT * FROM QuestionsAnswers WHERE category = " . $cat;

foreach ($db->query($sql) as $row) { // this is line 27
print '<p>' . $row['question'] .' <br /> '. $row['displaystring'] . '</p>';
}

but, fearing I may have inadvertently picked a reserved word as a column name, I changed category to categoryname in the database, in the form, and in my php file.  Everything else is the same.  Here's what happened in the error_log, both before and after the column name change:

 

PHP Fatal error:  Uncaught exception 'PDOException' with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Acts' in 'where clause'' in /~my path~/quiz.php:27

Stack trace:

#0 /~my path~/quiz.php(27): PDO->query('SELECT * FROM Q...')

#1 {main}

  thrown in /~my path~/quiz.php on line 27

 

 

 

"Acts" was the value of "categoryname", not a column.  I'm still getting something screwed up, but thanks to you, I'm much closer. I will keep working on it after a nice nap.  I really need to get this working so I won't quit.  I'd rather use a prepared statement if that is better, but the answer you gave me didn't work at all.  I can continue studying and learning about prepared statements to get them to work right, but at this moment, my database is worthless and I just need something--anything--to work.

 

Oh! Another change I made was to use double quotes (") for string items like "</p>"; etc. and single quotes (') or apostrophes to identify field names, etc., like $row['question'] to avoid confusion.

 

Thank you again.  I am still on this case.

[rghollenbeck]

The field "categoryname" is a string.  So, I had to put $cat in single quotes:

 

"SELECT * FROM QuestionsAnswers WHERE categoryname = '$cat'";

 

Thank you Ch0cu3r.  A classroom setting would have been handy because this topic would have surely been covered in a lecture.  But you directed me to  http://php.net/pdo where more information was available.  That website can be overwhelming, but with patience, much useful data can be found there.