How to take data from a Select Mysqli and send newsletter

Question

I’m trying to send a newsletter after a search in my database, it’s the first time I’m using the Mysqli extension and I’m having some difficulty going through the second WHILE and I believe it’s not the right way, I did so:

$mysqli = new mysqli($host,$user, $password, $database);
$sql = "SELECT id, email FROM newsletter WHERE status = 1 AND enviado = 0";
$result = $mysqli->query($sql) or trigger_error($mysqli->error." [$sql]"); 
    while($row = $result->fetch_array()) {
     $id = $row['id'];
     $email = $row['email'];     
    }

while($row = $result->fetch_assoc()) {

    $mail->setFrom('[email protected]', 'Newsletter');
    $mail->addAddress($email);
    $mail->Subject = 'Envio Newsletter';
    $mail->msgHTML(file_get_contents('news.php'), dirname(__FILE__));
    $mail->send();
    $mail->ClearAddresses();

    $sqlEdicao_usuarios = " UPDATE newsletter SET enviado = 1 WHERE id = $id ";
    mysql_select_db($database_conexao, $conexao);
    $sqlEdicao_usuarios;
    $fim = mysql_query( $sqlEdicao_usuarios,$conexao ) or die ( "Erro alterando dados no Banco de Dados" ); 


}

Answer 1

Test as follows:

I put the second while code in the first so eliminating it (because there was no need for it) and simplifying its code as well.

What happened before: the first while only kept an id and email in the variable $id, $email, which probably caused the problems (This when returning some result...).

$mysqli = new mysqli($host,$user, $password, $database);
$sql = "SELECT id, email FROM newsletter WHERE status = 1 AND enviado = 0";
$result = $mysqli->query($sql) or trigger_error($mysqli->error." [$sql]"); 
while($row = $result->fetch_array()) {
    $mail->setFrom('[email protected]', 'Newsletter');
    $mail->addAddress($row['email']);
    $mail->Subject = 'Envio Newsletter';
    $mail->msgHTML(file_get_contents('news.php'), dirname(__FILE__));
    $mail->send();
    $mail->ClearAddresses();

    $sqlEdicao_usuarios = " UPDATE newsletter SET enviado = 1 WHERE id = $row['id']";// ou jogue em uma variável e substitua por ela.... ($id = $row['id'])
    mysql_select_db($database_conexao, $conexao);
    $sqlEdicao_usuarios;
    $fim = mysql_query( $sqlEdicao_usuarios,$conexao ) or die ( "Erro alterando dados no Banco de Dados" ); 
}