help - unique values & reoccurance reporting

array_count_values?

[matrix223]

I'm having trouble using array_count_values which makes me question if I'm even using the right thing for my application. Heres what I'm trying to do:

1. Selecting a date column in a mysql DB
2. Take the resulting array of dates and report how many of each date was found in order from most recent to not as recent. (note there are no specefic dates to pull, just the most recent and a total of how many times that date appears.
3. A continuation of what I was going to do would be to delete any dates older then 10 days.

I havnt started on the deletion part as the first bit hasnt been working. What approach should I be taking with this? I've tried using array_count_values with a combination of array_unique. For some reason when I do this:

$result = mysql_query("SELECT DATE_FORMAT(timestamp, '%Y-%c-%d') as date FROM sqltable",$db);
$table = mysql_fetch_array($result);
$num = count($table);

Num always reports 2 when there are 8 rows entered in the table.

I also cant figure out why a simple:
$newarray = array_count_values($table);
wont work properly (nor have I found a proper way to extrat the results from a new array.

So if the DB had this:

2007-5-12
2007-5-12
2007-5-14
2007-5-15
2007-5-15
2007-5-15
2007-5-15

I would want output to look like:
2007-5-12 - 2
2007-5-14 - 1
2007-5-15 - 4

My other option would be to loop through each value in the initial while loop and put the values in a new array which could then be altered more easily - because I believe half the problem is the fact that mysql_fetch_array returns an associative ray. I feel like looping through everything was not very efficient and am wondering if theres a better way.

If someone could even point me to an existing example it would be helpful, usually thats what I do, but I have no idea how to describe this problem in a simple search phrase :-\

[ahmedtheking]

Well looping is the best way. The way mysql_fetch_array works is that it only returns one row, hence we need to use 'while' in order to get all rows, so:

$results = array();
$q = mysql_query("SELECT DATE_FORMAT(timestamp, '%Y-%c-%d') as date FROM sqltable",$db);
while ($r = mysql_fetch_array($result,MYSQL_ASSOC)) {
$results[$r['id']] = $r;
}

// now we have the results, we just count them
echo array_count_values($results);

[mcibor]

I must disagree with Ahmed.

Looping is ok, but you don't really need it :)

What you need is other mysql query:
SELECT DATE_FORMAT(timestamp, '%Y-%c-%d') as date, COUNT(*) AS date_count FROM sqltable GROUP BY date;

Here the mysql will count how many dates there were. No need to count array :)

And to delete you can use:
DELETE FROM sqltable WHERE DATE_SUB( CURDATE( ) , INTERVAL 30 DAY ) > timestamp;

if it doesn't work for you, then I think

DELETE FROM sqltable WHERE DATE_SUB( CURDATE( ) , INTERVAL 30 DAY ) > DATE_FORMAT(timestamp, '%Y-%c-%d');

Regards
Michal

[ahmedtheking]

Good idea! I think it's time I brushed up on my SQL.

[matrix223]

Thanks! You both gave me some great ideas.

Mcibor:
Will this code count how many different dates there are, or how many of each date?:

SELECT DATE_FORMAT(timestamp, '%Y-%c-%d') as date, COUNT(*) AS date_count FROM sqltable GROUP BY date;

I can see how this might work possibly nested somewhere but given this data I see it reporting like this:

2007-5-12
2007-5-12
2007-5-14
2007-5-15
2007-5-15
2007-5-15
2007-5-15

date_count would then = 7? or maybe 3? I assume 3 which would allow me to run another loop with a nested SELECT statement to count the rows and then output the date and row count from the result of a:
SELECT timestamp WHERE DATE_FORMAT(timestamp, '%Y-%c-%d') = {$row['date']}.
Dont have access to test it out at the moment but that is my best guess. Am I wrong? I'm not sure how your SELECT statement would lead me to having an output like:

2007-5-12 - 2
2007-5-14 - 1
2007-5-15 - 4

And the method I just described seems like an awfuly inefficent way of accomplishing this...but I think we're definatly heading in the right direction.

[mcibor]

Michal:
Will this code count how many different dates there are, or how many of each date?:

It should count both.
I have a table `sqltable` with field
`timestamp`
2007-05-15 16:13:54
2007-05-16 16:12:57
2007-05-15 16:14:17
2007-05-16 16:13:01
2007-05-16 16:13:36
2007-05-16 16:13:40
2007-05-16 16:13:42

If I run this query:
SELECT DATE_FORMAT(`timestamp`, '%Y-%c-%d') as date, COUNT(*) AS date_count FROM `sqltable` GROUP BY date;

I get results:
date date_count
2007-5-15 2
2007-5-16 5

So it functions OK by me.

Hope this helps you

Michal

PS. To get the number of unique dates just count the rows:
$result = mysql_query($sql) or die(mysql_error());
$number_of_unique_dates = mysql_num_rows($result);

[matrix223]

Thanks much for the expllination mcibor, you have been extremly helpful! =)