How do I retreive yesterdays data 1

[kzn]

Hi

First off sorry if this should be in the mysql section. Basically I have created a page counter that stores information in a database. The fields are as follows:
id
visit
ip
created

I am trying to retreive yesterdays unique visits with the following code. It retreives data but not the amount of unique visits. Any pointers appreciated.

Thanks in advance.

echo "Unique Visits";
$date = date('Y-m-d');

$date1 = $date . " 00:00:00";
$date2 = $date . " 23:59:59";

$query = "SELECT count(ip) as h FROM pagecounter WHERE created between '$date1' and '$date2' group by ip ";
$result = mysql_query($query);

while($hits = mysql_fetch_array($result)) {

echo $hits['h'];


}

 

[kzn]

Probably not the most ideal way of doing things. I have found a solution.

echo "Total Visits Today <br />";
$date = date('Y-m-d');

$date1 = $date . " 00:00:00";
$date2 = $date . " 23:59:59";

$query = "SELECT count(*) as h FROM pagecounter WHERE created between '$date1' and '$date2' group by ip ";
$result = mysql_query($query);
$counter = 0;
while($hits = mysql_fetch_array($result)) {

echo $hits['h'].'<br />';
$counter++;


}
echo "Unique Visits Today<br />";

echo $counter;

 

[jpadie]

$sql = "Select count(*) as h from pagecounter where cast (created as date) = date_sub(curdate(), interval 1 day)";