PDO equivalent of: while ($row = mysqli_fetch_array($result))

Question

I'm trying to traslate from mysqli to PDO this:

  `$query = "SELECT * FROM email_list";
   $result = mysqli_query($dbc, $query)
   or die('Error querying database.');

   while ($row = mysqli_fetch_array($result)){
   $to = $row['email'];
   $first_name = $row['first_name'];
   $last_name = $row['last_name'];
   $msg = "Dear $first_name $last_name,\n$text";
   mail($to, $subject, $msg, 'From:' . $from);
   echo 'Email sent to: ' . $to . '<br />';
   } 

   mysqli_close($dbc);

by reading other questions, I tried this:

 $sql = "SELECT * FROM email_list";


 while ($row = fetch(PDO::FETCH_BOTH)){
 $to = $row['email'];
 $first_name = $row['first_name'];
 $last_name = $row['last_name'];
 $msg = "Dear $first_name $last_name,\n$text";
 mail($to, $subject, $msg, 'From:' . $from);
 echo 'Email sent to: ' . $to . '<br />';
 } 

 $conn->exec($sql);

 $conn = null;

but I got this error: Parse error: syntax error, unexpected '{' in C:\xampp\htdocs\sendemail.php on line 36.

UPDATE:

<?php
$from = 'elmer@makemeelvis.com';
$subject = $_POST['subject'];
$text = $_POST['elvismail'];

$servername = "localhost";
$username = "davide";
$password = "";

try {
$conn = new PDO("mysql:host=$servername;dbname=elvis_store", $username,   $password);
// set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "Connected successfully<br>";
}
catch (PDOException $e) {
echo 'Connection failed: ' . $e->getMessage();
}

//  $dbc = mysqli_connect('data.makemeelvis.com', 'elmer', 'theking',   'elvis_store')
//   or die('Error connecting to MySQL server.');

// $sql = "SELECT * FROM email_list";


// while ($row = fetch(PDO::FETCH_BOTH))
//  {
//  $to = $row['email'];
//    $first_name = $row['first_name'];
//    $last_name = $row['last_name'];
//    $msg = "Dear $first_name $last_name,\n$text";
//    mail($to, $subject, $msg, 'From:' . $from);
//    echo 'Email sent to: ' . $to . '<br />';
//
//   } 
$query = $conn->prepare("SELECT * FROM email_list");
$query->execute();

while($row = $conn->fetch(PDO::FETCH_BOTH)){
$to = $row['email'];
$first_name = $row['first_name'];
$last_name = $row['last_name'];
$msg = "Dear $first_name $last_name,\n$text";
mail($to, $subject, $msg, 'From:' . $from);
echo 'Email sent to: ' . $to . '<br />';
}

$conn->exec($sql);

$conn = null;

?>

Connected successfully

Notice: Undefined variable: dbc in C:\xampp\htdocs\study\ch03\final\makemeelvis\sendemail.php on line 47

Fatal error: Call to a member function prepare() on null in C:\xampp\htdocs\study\ch03\final\makemeelvis\sendemail.php on line 47

UPDATE 2:

I haven't this error anymore, but now the html page continue to load...without giving any error but also no message as "connected" or similar...

what i'm doing wrong?

thanks

A closing `)` is missing on `while ($row = fetch(PDO::FETCH_BOTH)` — Michael Berkowski, Apr 06 '16 at 19:12
But that alone isn't going to make this work. You have the variable `$sql`, but don't execute it before attempting to fetch. Instead of `exec()` later, you should be calling `prepare()/execute()` _before_ fetching from it. Examples here http://wiki.hashphp.org/PDO_Tutorial_for_MySQL_Developers#Running_Statements_With_Parameters — Michael Berkowski, Apr 06 '16 at 19:14
I updated the code, but I have a new error. Shuld I use this? prepare("SELECT * FROM table WHERE id=:id AND name=:name"); $stmt->execute(array(':name' => $name, ':id' => $id)); $rows = $stmt->fetchAll(PDO::FETCH_ASSOC); — dave, Apr 08 '16 at 16:31
You created a PDO object as `$conn`, not `$dbc`. So you must use `$conn->prepare(...)` — Michael Berkowski, Apr 08 '16 at 16:35
You are right, I updated the code, but now my html page continue to load when I press the submit button... Any idea? — dave, Apr 12 '16 at 17:13

James Kent · Answer 1 · 2016-04-09T14:06:27.923

0

This should do it for you!

$query = $conn->prepare("SELECT * FROM email_list");
$query->execute();

while($row = $query->fetch(PDO::FETCH_ASSOC)){
    $to = $row['email'];
    $first_name = $row['first_name'];
    $last_name = $row['last_name'];
    $msg = "Dear $first_name $last_name,\n$text";
    mail($to, $subject, $msg, 'From:' . $from);
    echo 'Email sent to: ' . $to . '<br />';
}

EDIT:

Dave, use the edited code above and you should be fine. I'll explain why you're getting the error.

My original reply was using the variable $dbc, whereas your PDO database connection string...

$conn = new PDO("mysql:host=$servername;dbname=elvis_store", $username,   $password);

Uses the variable $conn as the PDO handler.

Hope this makes sense.

edited Apr 09 '16 at 14:06

answered Apr 06 '16 at 20:34

James Kent

13
5

I updated the question with the new code and the new error. Please, can you have a look? Thanks – dave Apr 07 '16 at 17:07
Dave, I've updated my reply too :) – James Kent Apr 09 '16 at 14:06
Hi James, yes make sense, I updated the code in the question and in my PHP page, but now when I submit the form, my html page does not do anything..just load and I haven't any new data in my database; Maybe it's the mail function? or because I have not an mail server? Do I need to configure an email account somehow? I'm sorry for all this questions, I just started to study PHP and it is not so easy :) – dave Apr 12 '16 at 12:40
this part of code it is not anymore use right? $conn->exec($sql); $conn = null; – dave Apr 12 '16 at 12:42

PDO equivalent of: while ($row = mysqli_fetch_array($result))

1 Answers1