PHP Code Issue

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
RossEBY
Forum Newbie
Posts: 2
Joined: Fri Apr 13, 2012 3:57 am

PHP Code Issue

Post by RossEBY »

Hello there,

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());
This first bit of code determines the start date of the graph and the end date.

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 '],';
	}

?>
Any advise is much appreciated.

Ross
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: PHP Code Issue

Post by mikosiko »

your code doesn't make sense to me at all...
- 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.
are you trying to count every two days?.... that is not what you shown as expected results previously in your format data.

if you are using COUNT() (an aggregation function... then you must use GROUP BY otherwise the result are flawed)

why so many queries?, and in loop too?

seems to me that just one well formed query using a JOIN and the proper aggregation with GROUP BY will do the job.

In case I am missing something in your objectives and you have a reason for all that code, please explain its better to get most accurate help... posting your tables description as well as some example data for each one and example of your expected results will help
RossEBY
Forum Newbie
Posts: 2
Joined: Fri Apr 13, 2012 3:57 am

Re: PHP Code Issue

Post by RossEBY »

Okay i'll try and explain it better.

First off i grab the value of views for the first day that the email was sent out, as displayed in the code below

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'] . '],';
Then it creates a loop and works its way through a 2 week period. So essential i want within the loop to execute 14 SQL statements to grab the views for each day.

So essential i want to do the following statement within the while loop

Code: Select all

SELECT *, COUNT(messageid) AS count FROM `phplist_usermessage` WHERE messageid = '$messageID' AND viewed BETWEEN '2012-04-06' AND '2012-04-07'
SELECT *, COUNT(messageid) AS count FROM `phplist_usermessage` WHERE messageid = '$messageID' AND viewed BETWEEN '2012-04-07' AND '2012-04-08'
SELECT *, COUNT(messageid) AS count FROM `phplist_usermessage` WHERE messageid = '$messageID' AND viewed BETWEEN '2012-04-08' AND '2012-04-09'
SELECT *, COUNT(messageid) AS count FROM `phplist_usermessage` WHERE messageid = '$messageID' AND viewed BETWEEN '2012-04-09' AND '2012-04-10'
SELECT *, COUNT(messageid) AS count FROM `phplist_usermessage` WHERE messageid = '$messageID' AND viewed BETWEEN '2012-04-10' AND '2012-04-11'
SELECT *, COUNT(messageid) AS count FROM `phplist_usermessage` WHERE messageid = '$messageID' AND viewed BETWEEN '2012-04-11' AND '2012-04-12'
SELECT *, COUNT(messageid) AS count FROM `phplist_usermessage` WHERE messageid = '$messageID' AND viewed BETWEEN '2012-04-12' AND '2012-04-13'
SELECT *, COUNT(messageid) AS count FROM `phplist_usermessage` WHERE messageid = '$messageID' AND viewed BETWEEN '2012-04-13' AND '2012-04-14'
SELECT *, COUNT(messageid) AS count FROM `phplist_usermessage` WHERE messageid = '$messageID' AND viewed BETWEEN '2012-04-14' AND '2012-04-15'
SELECT *, COUNT(messageid) AS count FROM `phplist_usermessage` WHERE messageid = '$messageID' AND viewed BETWEEN '2012-04-15' AND '2012-04-16'
SELECT *, COUNT(messageid) AS count FROM `phplist_usermessage` WHERE messageid = '$messageID' AND viewed BETWEEN '2012-04-16' AND '2012-04-17'
SELECT *, COUNT(messageid) AS count FROM `phplist_usermessage` WHERE messageid = '$messageID' AND viewed BETWEEN '2012-04-17' AND '2012-04-18'
SELECT *, COUNT(messageid) AS count FROM `phplist_usermessage` WHERE messageid = '$messageID' AND viewed BETWEEN '2012-04-18' AND '2012-04-19'
SELECT *, COUNT(messageid) AS count FROM `phplist_usermessage` WHERE messageid = '$messageID' AND viewed BETWEEN '2012-04-19' AND '2012-04-20'
But currently it just repeats the same sql statment of

Code: Select all

SELECT *, COUNT(messageid) AS count FROM `phplist_usermessage` WHERE messageid = '$messageID' AND viewed BETWEEN '2012-04-06' AND '2012-04-07'
Post Reply