I'm having an issue with a bit of code that generates dates and values for a jQuery graph.
Basically it takes data from a database for email campaigns. What i'm trying to do is generate the code for the jQuery graph that should be formated like so:
["2012-04-05",683],
["2012-04-06",452],
["2012-04-07",252],
["2012-04-08",168],
["2012-04-09",102],
["2012-04-10",58],
["2012-04-11",24],
["2012-04-12",0],
["2012-04-13",0],
["2012-04-14",0]
The issue i'm having at the moment is that the dates within the SQL statement are not looping through. So for example below the code should work through the start date and the end date grabbing the values of views between those dates. So the first line for example should be between 2012-04-06 & 2012-04-07 then between 2012-04-07 & 2012-04-08 and so on.
Code: Select all
$line_sql = mysql_query("SELECT *, COUNT(messageid) AS count FROM `phplist_usermessage` WHERE messageid = '$messageID' AND viewed BETWEEN '" . $sentDate . "' AND '" . $sentDate_plus . "'") or die(mysql_error());Code: Select all
$messageID = $_REQUEST['id'];
include('config.php');
$message_sent_sql = mysql_query("SELECT sendstart FROM phplist_message WHERE `id` = '" . $messageID . "'") or die(mysql_error());
$message_sent = mysql_fetch_array($message_sent_sql);
$sent = $message_sent['sendstart'];
$start_date_new = $message_sent['sendstart'];
$start_date = $sent;
$check_date = $start_date;
$end_date = date("Y-m-d", strtotime("+14 day", strtotime($start_date)));
$min_date = strtotime($message_sent['sendstart']);
$min_date = date('Y-m-d', $min_date);
$max_date = date("Y-m-d", strtotime("+14 day", strtotime($message_sent['sendstart'])));Below creates a loop through the dates and grabs the amount of views from the database.
Code: Select all
$inital_sql = mysql_query("SELECT *, COUNT(messageid) AS incount FROM `phplist_usermessage` WHERE messageid = '$messageID' AND viewed BETWEEN '" . date('Y-m-d', strtotime($message_sent['sendstart'])) . "' AND '" . date('Y-m-d', strtotime("+1 day", strtotime($message_sent['sendstart']))) . "'") or die(mysql_error());
$inital = mysql_fetch_array($inital_sql);
echo '["' . date('Y-m-d', strtotime($message_sent['sendstart'])) . '",' . $inital['incount'] . '],';
while ($start_date != $end_date) {
$sentDate = date("Y-m-d", strtotime("+1 day", strtotime($start_date_new)));
$sentDate_plus = date("Y-m-d", strtotime("+1 day", strtotime($sentDate)));
$line_sql = mysql_query("SELECT *, COUNT(messageid) AS count FROM `phplist_usermessage` WHERE messageid = '$messageID' AND viewed BETWEEN '" . $sentDate . "' AND '" . $sentDate_plus . "'") or die(mysql_error());
$line = mysql_fetch_array($line_sql);
$count = $line['count'];
$start_date = date("Y-m-d", strtotime("+1 day", strtotime($start_date)));
echo '["' . $start_date . '",';
echo $count;
echo '],';
}
?>Ross