RE: PHP previous and next week stats problems

[kamal213]

Hi guys,

 

Was wondering if you can help me, I have a customer management system where users/employees can add customers and turn the added customers to appointments. So specifically the script counts the number of customers that where added by an employee and how many that employee turned into appointments.

 

I would like to count how many customers were added by an employee and turned into appointments in a particular week.

 

What I did was create a column called 'week_added' to store the week the customer was added (i.e. week_added=24) and another called 'w_added' for the week the appointment was booked for that customer(i.e. w_added=24), I also have a column for the empoyee called 'c_employee'(i.e. c_employee=kamal213).

 

I apologies for the lengthiness of my topic. To count and retrieve the amount of customers added by an employee for the week from the database I use the following:

<?php 
// Connect to the MySQL database  
include "leadscript/connect_to_mysql.php"; 
//Run a select query to count the amount of leads - Murray
$sql = mysql_query("SELECT COUNT( * ) AS Customers, c_employee AS name, week_added AS week FROM customer WHERE c_employee='Murray' AND week_added='".$week."'");
$customerCount = mysql_num_rows($sql); // count the output amount
if ($customerCount > 0) {
	while($row = mysql_fetch_array($sql)){ 
			 $customers = $row["Customers"];
			 $name = $row["name"];
			 $week = $row["week"];
			 echo '<a href="canvasserweekleads.php?pid=' . $name . '&cid=' . $week . '">' . $leads . '</a>';
	}
}
?>

 

 

And to count and retrieve the number of customers and employee turned into appointments for the week:

<?php 
// Connect to the MySQL database  
include "leadscript/connect_to_mysql.php"; 
//Run a select query to count the amount of leads - Murray
$sql = mysql_query("SELECT COUNT( * ) AS Leads, c_employee AS name, w_added AS b_day, b_time AS time FROM customer WHERE c_employee='Murray' AND w_added='".$week."' AND NOT b_time=''");
$customerCount = mysql_num_rows($sql); // count the output amount
if ($customerCount > 0) {
	while($row = mysql_fetch_array($sql)){ 
			 $leads = $row["Leads"];
			 $name = $row["name"];
			 $b_day = $row["b_day"];
			 $time = $row["time"];
			 echo '<a href="canvasserweekapps.php?pid=' . $name . '&cid=' . $b_day . '&bid=' . $time . '">' . $leads . '</a>';
	}
}
?>

 

To go on the previous and next week i use the following code:

 <?php   function diaryHrefFromTimestamp($timestamp)
                    {    
                    $day   = date('d', $timestamp);    
                    $month = date('m', $timestamp);    
                    $year  = date('Y', $timestamp);
				$week  = date('W', $timestamp);    
                    return "canvasserweekstats.php?&day={$day}&month={$month}&week={$week}&year={$year}";
                    }
                    
                    //Get vars from URL
                   if(isset($_GET['day'])){
				$day = $_GET['day'];
				} else{
				//Get today's date and put them in date, month, year
					$day = date("j");
					$day = $day;
					}
					if(isset($_GET['month'])){
					$month = $_GET['month'];
					} else{
					$month = date("n");
					$month = "0".$month;
					}
					if(isset($_GET['year'])){
					$year = $_GET['year'];
					} else{
					$year = date("Y");
					}
					if(isset($_GET['week'])){
					$week = $_GET['week'];
					} else{
					$week = date("W");
					}
					//Create timestamps for prev/next days
                    $prevDayTimestamp = strtotime("$day-$month-$year -1 week");
                    $nextDayTimestamp = strtotime("$day-$month-$year +1 week");
                    
                    //Create href values for prev/next links
                    $prevDayHref = diaryHrefFromTimestamp($prevDayTimestamp);
                    $nextDayHref = diaryHrefFromTimestamp($nextDayTimestamp);
                    echo "<a href='{$prevDayHref}'><img src='images/previous.gif' alt='' /></a>\n";
				echo" ";echo" ";
                    echo "<a href='{$nextDayHref}'><img src='images/thenext.gif' alt='' /></a>\n";
				echo" ";echo" ";echo" ";
				echo $week;
                    ?>

 

The problem is it doesnt work properly, I used this code in week 23 and it retrieve all the above info as planned but since then it just counts every thing as 0 even when customers and appointment have been added for week 24 and now week 25.

 

Please guys kindly help, I apologies for the long nature of the topic and please let me know if you need more info.

 

Thanks

[kamal213]

Would you guys want me to explain it in a different/shorter way if it will help?

[AMcHarg]

Where are you defining $week in the initial $sql?  :-\

[kamal213]

Thanks for getting back to me AMcHarg,

 

I didn't know I had to define in the $sql. Could you give an example how it would look like

 

Thanks

[PFMaBiSmAd]

Using and storing the week number is ambiguous. You also need the year that the week occurred in.

 

You should instead store the actual DATE (YYYY-MM-DD) when something occurs. Then you can simply query for the matching data for any time period, such as a specific year/week or a range of year/weeks...

[kamal213]

It works fine now.

 

Actually what I did was use your idea of defining $week in the initial $sql.

 

so basically this is how it look:

<?php 
				// Connect to the MySQL database  
				include "leadscript/connect_to_mysql.php"; 
if(isset($_GET['week'])){
$week = $_GET['week'];
} 
else{			
//Get today's date and put them in date, month, year
$day = date("j");
$day = $day;
}
if(isset($_GET['month'])){
$month = $_GET['month'];
} else{
$month = date("n");
$month = "0".$month;
}
if(isset($_GET['year'])){
$year = $_GET['year'];
} else{
$year = date("Y");
}
if(isset($_GET['week'])){
$week = $_GET['week'];
} else{
$week = date("W");
}
//Run a select query to count the amount of leads - Khalid
$sql = mysql_query("SELECT COUNT( * ) AS Leads, c_employee AS name, week_added AS week FROM customer WHERE c_employee='Khalid' AND week_added='".$week."'");
$customerCount = mysql_num_rows($sql); // count the output amount
if ($customerCount > 0) {
	while($row = mysql_fetch_array($sql)){ 
			 $leads = $row["Leads"];
			 $name = $row["name"];
			 $week = $row["week"];
			 echo '<a href="canvasserweekleads.php?pid=' . $name . '&cid=' . $week . '">' . $leads . '</a>';
	}
}
?>

 

Not the cleanest of coding but it works now.

 

Thanks for getting back to me, if not for that idea dont know what I would have done.