monthly top 25

[Al_90]

ok i want a table that lists the top entries for the past month only and it needs to be ordered by score, i hope this is clear enough, and heres my table

CREATE TABLE `portal` (
`id` int(5) unsigned NOT NULL auto_increment,
`title` varchar(26) NOT NULL default '',
`author` varchar(22) NOT NULL default '',
`userid` int(5) NOT NULL default '0',
`category` varchar(18) NOT NULL default '',
`width` varchar(4) NOT NULL default '',
`height` varchar(4) NOT NULL default '',
`description` text NOT NULL,
`userfile` varchar(26) NOT NULL default '',
`filesize` int(6) NOT NULL default '0',
`views` int(10) NOT NULL default '0',
`date` varchar(30) NOT NULL default '',
`score` float(3,2) default '2.50',
`votes` int(5) unsigned NOT NULL default '1',
`type` varchar(9) NOT NULL default '',
`thumb` varchar(26) NOT NULL default '',
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=3 ;

thanks alot

[Velox Letum]

SELECT * FROM portal WHERE date BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(NOW(), '%Y-%m-01 %T')) AND UNIX_TIMESTAMP(LAST_DAY(NOW())) ORDER BY score DESC LIMIT 25;

[Al_90]

SELECT * FROM portal WHERE date BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(NOW(), '%Y-%m-01 %T')) AND UNIX_TIMESTAMP(LAST_DAY(NOW())) ORDER BY score DESC LIMIT 25;
thanks, but this is really hard to understand if i'm supposed to modify it at all, if not then please tell me that. When i look at it i think i need to add somthing to it to work if thats the case would you mind doing that bacuse this is very confusing, thanks alot

[Velox Letum]

Okay, the first step to understanding the query is to break it into easily readable steps. (I modified the query a bit).

SELECT *
FROM portal
WHERE date
BETWEEN UNIX_TIMESTAMP( '2005-11-01' )
AND UNIX_TIMESTAMP( DATE_ADD(LAST_DAY( '2005-11-01' ), INTERVAL '23:59:59' HOUR_SECOND) )
ORDER BY score DESC
LIMIT 25

The only thing you really need to worry about is the between values. Now that I think about it, you can just fill in the time values for the UNIX_TIMESTAMP() to fit a particular month, however leaving the LAST_DAY() function ensures you get the correct last day of the month, and the DATE_ADD function makes sure that all of the values from that day are also included by adding 23 hours, 59 minutes, and 59 seconds to the output.

The values you want to change are the 2005-11 section. They control the year and month respectively, and the query gets all rows from between the two values. I highlighted the value in red, which you can control the month and year it retrieves.

The rest is pretty simple, order the score descending so that the largest value is first, and then limit the rows retrieved to the first 25.

[Al_90]

thanks, so alll i have to do is put this code in my page and i wont have to do anything to it ever? i'm trying to do this so i will NEVER have to edit it manually

$date = date(f, m, d);
sql = "SELECT *
FROM portal
WHERE date
BETWEEN UNIX_TIMESTAMP( '$date' )
AND UNIX_TIMESTAMP( DATE_ADD(LAST_DAY( '$date' ), INTERVAL '23:59:59' HOUR_SECOND) )
ORDER BY score DESC
LIMIT 25"

[Velox Letum]

If you never want to touch it and just want the current month, my first query works for that purpose. The above query wouldn't work correctly. This one will, I've tested it with data.

SELECT *
FROM portal
WHERE date
BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(NOW(), '%Y-%m-01'))
AND UNIX_TIMESTAMP(DATE_FORMAT(LAST_DAY(NOW()), '%Y-%m-%d 23:59:59'))
ORDER BY score DESC
LIMIT 25;

(slightly edited to reflect the edits I made before and other improvements I just made).

[Al_90]

thank you so much :) god bless

[Al_90]

ok i uploaded it but i got this:
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/httpd/vhosts/gbullet/httpdocs/portal/index.php on line 422
and that is:

<?php //Top This Month
$date = date('F jS, Y');
$query = "SELECT *
FROM portal
WHERE date
BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(NOW(), '%Y-%m-01'))
AND UNIX_TIMESTAMP(DATE_FORMAT(LAST_DAY(NOW()), '%Y-%m-%d 23:59:59'))
ORDER BY score DESC
LIMIT 25";
$result = mysql_query($query);
$i = 0;
while ($row = mysql_fetch_array($result))
{
$i++;
$title = $row['title'];
$author = stripslashes($row['author']);
$category = stripslashes($row['category']);
$id = stripslashes($row['id']);
$userid = $row['userid'];
$score = $row['score'];
$votes = $row['votes'];
$views = stripslashes($row['views']);
$size = stripslashes($row['filesize']);
?>
<tr>
<td bgcolor="#757E87"><div align="left"><?php echo($i); ?>) <a class="overlib" href="view.php?id=<?php print "$id"; ?>" onmouseover="overlib('<center><B>Title:</B> <?php print "$title"; ?><BR> </TD></TR><TR><TD BGCOLOR=#757E87 HEIGHT=2></TD></TR><TR><TD BGCOLOR=#757E87 ALIGN=CENTER> <B>By:</B> <?php print "$author"; ?><BR> <B>Size:</B> <?php print "$size"; ?> Kb <BR> <B>Score:</B> <?php print "$score"; ?> <BR> <B>Category:</B> <?php print "$category"; ?> <BR> <B>Votes:</B> <?php print "$votes"; ?> / <B>Views:</B> <?php print "$views"; ?><BR></center>', 100, 75, RIGHT, BELOW, SNAPX, 0, SNAPY, 0); return true;" onmouseout="nd(); return
true;"> <?php print "$title"; ?></a></div></td>
</tr>
<?php } ?>

[Velox Letum]

Do you have data? Because I just ran the query and it worked fine.

[Al_90]

what do you mean, do i have data? i have info in the mysql database and the code i posted is what i use and i still get that error

[Kid Charming]

Your query is failing for some reason -- a syntax error, connection error, or something like that. Use this to get a more informative error message:


$result = mysql_query($query) or die(mysql_error());


That will kill the script on error and return the database's error message.