Loop through a Script

[airpirate007]

I am trying to come up with a script that sends my clients their tracking number when they order from me. I upload their info into a MySQL DB and then I want to run a script that sends an email to the client with their tracking and order information.  Then I want it to update that row to show that the email has been sent.

 

I came up with a script, but it only sends an email to, and updates, the last row in the DB. So I need it to loop somehow through the script for every row in the DB that hasn't already been updated.

 

Any help would be appreciated.

 

<?php
include_once('config.php');

$send_db = 'db';
$send_table= 'table';

//query to pull names and email addresses from the db
$result = mysql_query("SELECT distinct(order_num),email,name,tracking,id,date FROM $send_db.$send_table where email_sent is NULL") or die ('Error: '.mysql_error());

if (mysql_num_rows($result) == 0) {
    echo "No rows found, nothing to send, so I am exiting";
    exit;
}

while ($row = mysql_fetch_array($result)) { 
    $email_id = $row['id'];
        $order_num = $row['order_num'];
        $name = $row['name'];
        $email = $row['email'];
	$date = $row['date'];
        $tracking = $row['tracking'];
}


$to = "$email";
$headers = "From: donotreply@website.com\r\n";
$headers .= "Reply-To: donotreply@website.com\r\n";
$subject = "Your Order Number $order_num Has Shipped";
$body = "\n\n
---------------------------------------------\n
DO NOT REPLY TO THIS MESSAGE \n
---------------------------------------------\n
\n
$name\n
\n
Thank you for Ordering our product!\n
\n
Your Order Number $order_num has shipped on $date and should arrive to you shortly.\n
Your Order Shipped via the USPS First Class Mail. The tracking number for your package is $tracking \n
\n
For Customer Service please call phone number. For Returns, please contact Customer Service at the above phone number for Return Authorization. \n
\n
Thank you! \n
Company \n
\n
\n
\n
\n
";
mail($to, $subject, $body, $headers);
  
  //update email_sent to Y
  $result1 = mysql_query("UPDATE $send_db.$send_table set email_sent = 'Y' where order_num = '$order_num' and email = '$email';");

if (!$result1) {
    echo "Could not successfully run query against the DB: " . mysql_error();
    exit;
}

  ?>

 

[airpirate007]

Figured it out. I had a } in the wrong place, it needed to be on the end instead of after the while statement

 

<?php
include_once('config.php');

$send_db = 'db';
$send_table= 'table';

//query to pull names and email addresses from the db
$result = mysql_query("SELECT distinct(order_num),email,name,tracking,id,date FROM $send_db.$send_table where email_sent is NULL") or die ('Error: '.mysql_error());

if (mysql_num_rows($result) == 0) {
    echo "No rows found, nothing to send, so I am exiting";
    exit;
}

while ($row = mysql_fetch_array($result)) { 
    $email_id = $row['id'];
        $order_num = $row['order_num'];
        $name = $row['name'];
        $email = $row['email'];
	$date = $row['date'];
        $tracking = $row['tracking'];



$to = "$email";
$headers = "From: donotreply@website.com\r\n";
$headers .= "Reply-To: donotreply@website.com\r\n";
$subject = "Your Order Number $order_num Has Shipped";
$body = "\n\n
---------------------------------------------\n
DO NOT REPLY TO THIS MESSAGE \n
---------------------------------------------\n
\n
$name\n
\n
Thank you for Ordering our product!\n
\n
Your Order Number $order_num has shipped on $date and should arrive to you shortly.\n
Your Order Shipped via the USPS First Class Mail. The tracking number for your package is $tracking \n
\n
For Customer Service please call phone number. For Returns, please contact Customer Service at the above phone number for Return Authorization. \n
\n
Thank you! \n
Company \n
\n
\n
\n
\n
";
mail($to, $subject, $body, $headers);
  
  //update email_sent to Y
  $result1 = mysql_query("UPDATE $send_db.$send_table set email_sent = 'Y' where order_num = '$order_num' and email = '$email';");

if (!$result1) {
    echo "Could not successfully run query against the DB: " . mysql_error();
    exit;
}
}
  ?>