PHP MySQL - Top 50 Rows Between...

[digiplaystudios]

Hi There,

I currently have a PHP/MySQL query script which displays the top 50 MySQL rows in the table, however, it displays the top 50 of all time. I would love a way for it to display the top 50 rows in the last 7 days. The table looks a bit like this,

-------------------------------------
|id       | tag            |date    |
-------------------------------------
|1        | search 1       |05-27-2010
|2        | search 2       |05-27-2010
|3        | search 3       |05-28-2010
|4        | search 4       |05-29-2010
-------------------------------------

My current PHP/MySQL script looks like this (the script that grabs the top 50 of all time):

$result = mysql_query("SELECT tag, COUNT(*) FROM tags
GROUP BY tag
ORDER BY 2 DESC LIMIT 50");

[ivatanako]

Hi,

You could add this where clause in your query

WHERE date <= curdate()
AND date >= DATE_SUB(curdate(), INTERVAL 7 day)

Regards,
Al

[digiplaystudios]

I added this to my query so the script looked like this:

$result = mysql_query("SELECT tag, COUNT(*) FROM tags
GROUP BY tag
WHERE date <= curdate()
AND date >= DATE_SUB(curdate(), INTERVAL 7 day)
ORDER BY 2 DESC LIMIT 50");

and unfortunately it came back with this error:

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in ***.php  on line 20

[ivatanako]

You use it like this.

SELECT tag, COUNT(*) FROM tag
WHERE date <= curdate()
AND date >= DATE_SUB(curdate(), INTERVAL 7 day)
GROUP BY tag
ORDER BY 2 DESC LIMIT 50

Regards,
Al

[digiplaystudios]

I must be really stupid, sorry! I keep getting errors, here is what I have for my entire script. By the way I really appreciate all of your help. :)

<?php
  
$result = mysql_query("SELECT tag, COUNT(*) FROM tag
WHERE date <= curdate()
AND date >= DATE_SUB(curdate(), INTERVAL 7 day)
GROUP BY tag
ORDER BY 2 DESC LIMIT 50");

if($type=="lyrics") { $type = "lyrics"; }
elseif($type=="video") { $type = "video"; }
else { $type = "mp3"; }

while ($tags = mysql_fetch_assoc($result))
{
	$tag = urldecode($tags['tag']);
	$tag = str_replace(" ","-",$tag);

	if(($type=="lyrics")||($type=="video")) { $tag = str_replace("-","+",$tag); }

	$tag_name = urldecode($tags['tag']);
	$tag_name = ucwords($tag_name);
?><a href="<?="http://www.jarvmp3.com/".SeoFriendly("search",$tag,"1",$type);?>"><?=$tag_name?></a>&nbsp;&nbsp;&nbsp;&nbsp;<? } ?>

[ivatanako]

Hi,

what errors are you getting?

[digiplaystudios]

I again get this error:

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in ***.php  on line 20

Line 20 when the entire page of script is included is this

while ($tags = mysql_fetch_assoc($result))

[ivatanako]

what's the name of your table?

[digiplaystudios]

tags - okay now I feel like a complete and utter idiot! I've added the 's' onto the end but now instead of getting an error it simply doesn't display anthing. :(

[ivatanako]

Maybe because the date column are already more than 7 days? Could that be the reason?

Regards

[digiplaystudios]

Yes, the date column is about 2 months of search inputs. I am looking for a way to display the most popular results in the past 7 days while ignoring any results inputted more than 7 days previous.

[ivatanako]

Then based on your requirements, it should work. The where clause we added would filter any dates less than 7 days old. So it would only display those dates in the past 7 days.

To simulate, you could add new data with a newer date.

[digiplaystudios]

I have lots of rows of data in between the last 7 days dates. Could it be to do with the format of the date? eg, 27-03-2010, 27/03/2010, 03/27/2010, ect.

[ivatanako]

Yes, can you still change your table structure?

[digiplaystudios]

I can yes but it would not be too practical unless this is the only way.

My currently date format in the date column of the table is: 06-05-2010 (That's today's date)

[ivatanako]

Why would it be impratical? Are this test data or live data?

[digiplaystudios]

Live data. What would it require to change the table structure?

[ivatanako]

If this are live data then we could get into a problem. What you can do is to do conversion of this data into a proper date format using mysql functions.

[digiplaystudios]

Would you be able to help with that? Sorry, I'm just fairly new to PHP/MySQL queries.

[digiplaystudios]

I was just thinking, along with the date column I also have the ID column which is literally numbers, would it be easier to select the top 50 out of the most recent 1000 ID entries?

[digital-ether]

After the mysql_query() do some error checking. It will let you know what went wrong with the query. Example:

if (!$result) {
  trigger_error("MySQL error: ".mysql_error(), E_USER_ERROR);
}

I believe the problem is that DATE is a function so you need to escape it in the SQL query.

[ivatanako]

I was just thinking, along with the date column I also have the ID column which is literally numbers, would it be easier to select the top 50 out of the most recent 1000 ID entries?

The best way i think is to restructure this table. Use an ISO standard date format, and don't use data type as column name.

But with regards to your question, you can query the latest id entries but you would still need to undergo conversion of your current date format.

Regards

[digiplaystudios]

Thanks so much for everything guys, I've finally managed to get it working. It turns out the date format was incorrect! Thanks so much! I love you all! :D