Hi,
First I must note that I have little knowledge of either php or mysql, so if the solutions I chose aren't the best, feel free to correct me.
The situation is as follows:
I'm trying to create some code to calculate the number of unique visitors to my website today and yesterday.
For calculating the unique visitors today I used the code:
$date_today = date("Y-m-d");
$unique_today = mysql_query ("SELECT COUNT(*) FROM x_analys_visitor WHERE (date LIKE '$date_today%') ");
$unique_today = mysql_fetch_row($unique_today);
The column I'm using from the table x_analys_visitor is of format "2004-02-03 04:10:42" so therefor I used the "%" at the end of the mysql query. This works fine, and the result is correct.
In the same way I'm trying to calculate the visitors for yesterday. However, when I subtract a day using php's date function, I no longer have a date in the format '2004-02-03' but '2004-02-3'. Since I need to use this date in the filter for the "2004-02-03 04:10:42" format, I won't get the proper results, it will try to match all entries with date 2004-02-3%.
So I thought it would be a possibility to use the date_sub function from mysql.
select date_sub(current_date, interval 1 day); gives the perfect result: 2004-02-03.
So I created the following code:
$date_yesterday = mysql_query("SELECT DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY)");
$unique_yesterday = mysql_query ("SELECT COUNT(*) FROM x_analys_visitor WHERE (date LIKE '$date_yesterday%') ");
$unique_yesterday = mysql_fetch_row($unique_yesterday);
But for some reason, the result is "0" and should have been "19".
What am I doing wrong here? Thanks in advance for any comment.
Dennis
