1

I want to migrate a site from some poorly written MySQLi to clean PDO.

I have looked at three similar questions and their answers, and this is a straightforward question, but none of them are giving me results. Here's my code:

$state = "Alaska";

//trying to implement PDO here
$sql = "SELECT * FROM sales WHERE state = ? ORDER BY type";
$result = $conn->prepare($sql);
$result->execute(array($state));

/* 
this was the old, successfully working way before
$sql = "SELECT * FROM sales WHERE state = '$state' ORDER BY type";
$result = $conn->query($sql);
*/

Previous questions on this site show me answers that look like my PDO implementation, yet mine doesn't work. I have made sure the PDO class exists and that the extension is loaded.

If you see the error, let me know!

rtoken
  • 303
  • 6
  • 14
  • There's a lot of unknowns here. Have you connected properly? Do you fetch the results? What do you do with the presumed fetched result? – Qirel Jul 20 '17 at 21:44
  • ...and using the same api. Not still `mysqli_` is it? – Funk Forty Niner Jul 20 '17 at 21:45
  • *"If you see the error, let me know!"* - not for what you posted and probably more like what you didn't post; the connection used and besides what's been said by @Qirel - Question's unclear. – Funk Forty Niner Jul 20 '17 at 21:47
  • You're right, I'm sure it's the way I'm fetching the results. Is PDO::FETCH_ASSOC the preferred way to do it? – rtoken Jul 20 '17 at 21:49
  • `try{ $result->execute(array($state)); } catch(PDOException $e){ exit($e->getMessage()); }` and see what it returns. – icecub Jul 20 '17 at 21:50
  • I posted an answer with a complete example of [PDO prepared statements with exception handling](https://stackoverflow.com/questions/45214516/pdo-global-instance-new-pdo-its-possible/45216199#45216199). Maybe it answers to some of your questions. Good luck. –  Jul 21 '17 at 05:42

1 Answers1

4

The difference between the two, aside from difference in libraries, is that one is using a direct query() (the mysqli_*), while the other is using a prepared statement. Those are handled a bit different, regardless which API is running.

When using MySQLi, doing 

$result = $conn->query($sql);

would have $result be a mysqli-result object, which holds the data. You can use mysqli_result::fetch_assoc() on that to fetch the data. However, when you're using PDO::prepare(), your $result variable will be a PDOStatement - which is a bit different. You'll need to run a fetch() method on it first, and then you can use the return-value of it, as such

$state = "Alaska";
$sql = "SELECT * FROM sales WHERE state = ? ORDER BY type";
$stmt = $conn->prepare($sql);
$stmt->execute(array($state));
$result = $stmt->fetch(PDO::FETCH_ASSOC);

Note that I've changed the names of your variables. Now $result is an array (if there are any results fetched), which you can use as you normally do when fetching associative-arrays. If there are no results, PDOStatement::fetch() will return a boolean false.

var_dump($result['state']);

You can loop the fetch() method as 

while ($result = $stmt->fetch(PDO::FETCH_ASSOC)) {

if you expect more than one row. Use $result as you would without looping, as shown above.

Note that this assumes a valid PDO-connection. Beware that you cannot interchange any MySQL libraries, mysql_, mysqli_* and PDO are all different animals in the zoo.

Qirel
  • 25,449
  • 7
  • 45
  • 62