date variable in query ?

[Person]

I have the following script and its something that needs to run everyday. But is needs to give me the report for that day.

 

Here is the script

 

<?php
$host = "";
$user = "";
$pass = "";
$dbname = "";

$con = mysql_connect($host,$user,$pass) or die (mysql_errno().": ".mysql_error()."<BR>");
mysql_select_db($dbname);

$query = "SELECT SUM(1) AS clicks, SUM(`clcpc`), SUM(`chcpc`) FROM `nuke_pnAffiliate_clicktracking` WHERE `pl` = 'rpu' AND  `date` > '20070228'AND `cl` IN (SELECT DISTINCT(cl) FROM rpu_sales WHERE salesman = 'ryan')";

$result = mysql_query($query);
$num_results = mysql_num_rows($result);
    
$row = mysql_fetch_assoc($result);

mysql_free_result($result);

$email_to = "";
$email_from = "";
$email_subject = "";
$email_body = "Content-Type: text/html; charset=UTF-8\n";
$email_body .= "Content-Transfer-Encoding: 8bit\n\n";
$email_body .=	"

		<TABLE BORDER=\"1\" CELLPADDING=\"0\" CELLSPACING=\"0\" WIDTH=\"500\" HEIGHT=\"70\">
			<TR>
				<TD ALIGN=\"CENTER\" COLSPAN=\"3\" BGCOLOR=\"#1a467d\">
					<B>
						RPU Sales yesterday for Ryan
					</B>
				</TD>
			</TR>
			<TR>
			<TD ALIGN=\"CENTER\" WIDTH=\"100\" BGCOLOR=\"#1a467d\">
					<B>
						Clicks
					</B>
			<TD ALIGN=\"CENTER\" WIDTH=\"200\" BGCOLOR=\"#1a467d\">
					<B>
						SUM(`clcpc`)
					</B>
			<TD ALIGN=\"CENTER\" WIDTH=\"200\" BGCOLOR=\"#1a467d\">
					<B>
						SUM(`chcpc`)
					</B>
			</TR>
		 <TR>
			<TD ALIGN=\"CENTER\" WIDTH=\"100\" BGCOLOR=\"#e36328\">" . $row['clicks'] . "
			<TD ALIGN=\"CENTER\" WIDTH=\"200\" BGCOLOR=\"#e36328\">" . $row['SUM(`clcpc`)'] . "
			<TD ALIGN=\"CENTER\" WIDTH=\"200\" BGCOLOR=\"#e36328\">" . $row['SUM(`chcpc`)'] . "
			</TD>
		</TABLE>

	";

mail($email_to,$email_from,$email_subject,$email_body,"From:$email_from\r\nReply-To:do not reply to sever");

?>

 

How would I make this date variable ? for the day it is what its running ?

$query = "SELECT SUM(1) AS clicks, SUM(`clcpc`), SUM(`chcpc`) FROM `nuke_pnAffiliate_clicktracking` WHERE `pl` = 'rpu' AND  `date` > '20070228'AND `cl` IN (SELECT DISTINCT(cl) FROM rpu_sales WHERE salesman = 'ryan')";

[benjaminbeazy]

i'm sure there's an easier way but this is simple to understand and works.

 

$today = getdate();
$day = $today['mday'];
$month = $today['mon']'
$year = $today['year']'

$today_stamp = date("U", mktime(0, 0, 0, $month, $day, $year));

$query = "SELECT SUM(1) AS clicks, SUM(`clcpc`), SUM(`chcpc`) FROM `nuke_pnAffiliate_clicktracking` WHERE `pl` = 'rpu' AND  `date` > '$today_stamp'AND `cl` IN (SELECT DISTINCT(cl) FROM rpu_sales WHERE salesman = 'ryan')";