Jump to content

PDO errors in SELECT query using WHERE clause


rghollenbeck
Go to solution Solved by rghollenbeck,

Recommended Posts

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.

 

 

Link to comment
Share on other sites

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 by Ch0cu3r
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • Solution

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. 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.