PHP loop and count

[wiggst3r]

Hi

 

I have a database with 2 tables.

Table one has: id, created_at

Table two has: id, created_at

Table three has: id, total_1, total_2

 

The script I'm writing, loops through table one and counts all the id's that have a created_at date of, 1st jan, 2nd jan etc.

If there are any records with the date, such as there are 3 rows in table one which has the date of 1st jan, it puts the count into table two and the field total_1.

The loop then checks how many ids have the same created_at date in table two. If there are 2 rows dated 1st jan, then this count is then inserted into total_2.

So table three now has:

id, total_1, total_2

1, 3, 2

 

The only problem I'm having is, that the start date is from 17th Nov. Every time i run my script, it keeps inserting the rows from 17th nov until today everytime.

 

I need a check such to make sure that it only inserts dates/rows that have not already been in.

I have records from 17th nov - 16th jan. So the next time it runs, for example tomorrow, then the only record inserted should be those records where table one and table two have created_at dates of 17th jan, rather than all the records from 17th nov - 17 jan.

 

My code is as follows:

 

<?php
session_start();

require(dirname(__FILE__) . "/../../config.php");
require(dirname(__FILE__) . "/../../models/index_model.php");

class AdminModel extends IndexModel
{
function AdminModel()
{
	$codes = array('Voucher 1', 'Voucher 2', 'Voucher 3');
	$db = $this->connect_to_db();

	$curr_date = '2008-11-17';
	$date_now = date('Y-m-d', time() + 86400);
	$today = date('Y-m-d', time());

	while($curr_date != $date_now)
	{
		$tommorow = Date('Y-m-d', strtotime($curr_date) + 86400);

		// Get total of vouchers from the daily cache table
		$voucher_total_cached = $db->db_query(" SELECT * FROM entries WHERE created_at BETWEEN '$curr_date' AND '$tommorow' ");
		$num_rows = $db->db_num_rows($voucher_total_cached);
		echo '<br/>';

		//insert the count from the vouchers for each date
		//needs a check on here, so it doesn't keep inserting - possible if/else statement?
		$voucher_total_cached_insert = $db->db_query(" INSERT INTO daily_entries_cache (date, total_vouchers ) VALUES ( '$curr_date', '$num_rows') ");

		//can keep this - gets from cached table
		echo "Total Vouchers filled in on $curr_date <strong>$num_rows</strong><br/>";

		//get all added friends between dates we want
		$friends_total_cached = $db->db_query(" SELECT * FROM added_friends WHERE created_at BETWEEN '$curr_date' AND '$tommorow' ");
		$num_rows_friends = $db->db_num_rows($friends_total_cached);

		//update the friends count in daily cache where the date is the one we need to update
		$friends_total_cached_insert = $db->db_query(" UPDATE daily_entries_cache SET total_friends = '$num_rows_friends' WHERE date = '$curr_date' ");

		//can keep this - gets from cached table
		echo "Total Friends added on $curr_date <strong>$num_rows_friends</strong><br/><br />";


		foreach($codes as $one_code)
		{
			//echo out the codes and the total count
			$total_count = $db->db_fetch($db->db_query(" SELECT COUNT(id) as count FROM entries WHERE created_at BETWEEN '$curr_date' AND '$tommorow' AND offer = '$one_code' "));
			echo "$one_code vouchers filled in on $curr_date <strong>{$total_count['count']}</strong><br/>";

		}

		echo '<br/>';
		echo '========================================================';
		echo '<br/>';

		$curr_date = $tommorow;
	}
}
}

new AdminModel;
?>

[abdfahim]

sql have to have the info abt the last date somewhere .. so, wat u ca do is

 

1) delete the days from table1 and table2 after updating table3.

or 2) store the latest date updated in a table

or 3) add a new field in table3 and update that field with the latest date each time. then make sqls like

write the SQL queries like followings

Select COUNT(table1.id) from table1,table3 where table1.date between startdate AND enddate and table1.date>table3.date

[wiggst3r]

Ok, I sorted the above out with an if/else statement which seems to work.

 

Now, the second part of my script.

 

Each row in table one, has a field, called code

 

As they are several codes rows could be something like this

id, created_at, code

1, 2008-11-17, email_17

2, 2008-11-17, email_17

3, 2008-11-18, email_17

4, 2008-11-19, email_18

5, 2008-11-19, email_19

6, 2008-11-19, email_19

7, 2008-11-20, email_19

8, 2008-11-20, email_20

 

So the outcome should be on screen:

 

2008-11-17 - email_17 = 2

2008-11-18 - email_17 = 1

2008-11-19 - email_18 = 1

                  - email_19 = 2

2008-11-20 - email_19 = 1

                  - email_20 = 1

 

I'm not sure how to go about this.

 

Would i need another loop?

 

Thank you