running a mysqli query inside while loop

Question

I have this php code:

<?php 
//include database
include 'db.php';
//grab the emails from the database
$sql = "SELECT email FROM `emails`";
$result = mysqli_query($con, $sql);
while($row = mysqli_fetch_array($result, MYSQL_ASSOC)){
    // update the database
    //sanitinzw
    $row = mysqli_real_escape_string($con, $row['email']);
    //mail the emails 
    $to      = $row;
    $subject = 'HELLOO';
    $message = 'alooooo';
    $headers = 'From: [email protected]' . "\r\n" .
               'Reply-To: [email protected]' . "\r\n" .
               'X-Mailer: PHP/' . phpversion();

    mail($to, $subject, $message, $headers);    
}

?>

It works just fine; however, when I add a query to it only the first email in my database is sent here it what it looks like.

<?php 
//include database
include 'db.php';
//grab the emails from the database
$sql = "SELECT email FROM `emails`";
$result = mysqli_query($con, $sql);
while($row = mysqli_fetch_array($result, MYSQL_ASSOC)){
    // update the database
    //sanitinzw
    $row = mysqli_real_escape_string($con, $row['email']);
    //mail the emails 
    $to      = $row;
    $subject = 'hello';
    $message = 'aloooooo';
    $headers = 'From: [email protected]' . "\r\n" .
               'Reply-To: [email protected]' . "\r\n" .
               'X-Mailer: PHP/' . phpversion();

    mail($to, $subject, $message, $headers);    

    $sql = "UPDATE `emails` SET times_used = times_used + 1 WHERE email = '$row' ";
    $result = mysqli_query($con, $sql);
}

?>

for some reason it only updating the first email in the database and when I try to echo out $row only the first email is echoe'd

Thanks for the help

not sure mysqli_real_escape_string exists as a function... or at least php.net doesn't know about it.. — Félix Adriyel Gagnon-Grenier
– Félix Adriyel Gagnon-Grenier, Commented Dec 10, 2013 at 18:19
yeah it works just fine the first block of code works perfect its when I add the sql it messes up and only emails and updates the first email in the database — user3051232
– user3051232, Commented Dec 10, 2013 at 18:20

Martin Majer · Accepted Answer · 2013-12-10 18:20:39Z

3

You are rewriting the $result variable, which is also used in the while loop. After the first iteration, $result is set to the result of the update query, so there are no other rows that can be fetched.

You can write this, for example:

$sql = "UPDATE `emails` SET times_used = times_used + 1 WHERE email = '$row' ";
$result2 = mysqli_query($con, $sql);

That should work (if there is no other mistake).

answered Dec 10, 2013 at 18:20

Martin Majer

3,3724 gold badges26 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Félix Adriyel Gagnon-Grenier · Accepted Answer · 2013-12-10 18:21:08Z

0

not sure mysqli_real_escape_string exists as a function... or at least php.net doesn't know about it.. more over, with mysql_real_escape_string (what I guess you want to use) the arguments go the other way around:

$row = mysql_real_escape_string($row['email'],$con);

answered Dec 10, 2013 at 18:21

Félix Adriyel Gagnon-Grenier

8,93510 gold badges58 silver badges69 bronze badges

Collectives™ on Stack Overflow

running a mysqli query inside while loop

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related