Increase ID by one every 7 days

Is this possible?

I have very simple query

SELECT *
FROM toptenlist
ORDER BY topTenListID ASC

I'm showing this on the page as a list

< ol>
< li>< ?php echo $row_rsTopTenList['1']; ? >
< li>< ?php echo $row_rsTopTenList['2']; ? >
< li>< ?php echo $row_rsTopTenList['3']; ? >
< li>< ?php echo $row_rsTopTenList['4']; ? >
< li>< ?php echo $row_rsTopTenList['5']; ? >
< li>< ?php echo $row_rsTopTenList['6']; ? >
< li>< ?php echo $row_rsTopTenList['7']; ? >
< li>< ?php echo $row_rsTopTenList['8']; ? >
< li>< ?php echo $row_rsTopTenList['9']; ? >
< li>< ?php echo $row_rsTopTenList['10']; ? >
< /ol>

There are about 20 records all with a list of 10 results

The client wants to show the next list (ID1 then ID2 then ID3 etc.) but with an interval of 7 days in between.

So record 1 shows for a week, then record 2... When it gets to 20 (20 is just a figure there may be more there may be less) it goes back to 1 again.

Is this possible either using PHP or SQL?

I haven't a clue myself.

I can just put an extra column in (Yes or No) and have them change it to either show or not but that's a bit cumbersome so if there was an auto solution that would be great. I have searched the web but not found anything. It's difficult to know what to search for.

hi John.

There is a mySQL function called week() which will show the week number of a date field.
i think you could use this quite effectively once you have filtered your recordset

Thanks Craig.

I'm none the wiser though. I'm OK using server behaviours but getting to grips with PHP or MySql can be rather testing for me.

Can you elucidate a bit more?

Sorry.

OK, I'll try.

Not knowing what your table structure is like, I imagine you have a DATE field in there.

if you use the following syntax in your select statement, for example..

SELECT *, week(DATE) as week FROM tablename, (where DATE is the name of your date field)

You will have a column called week returned, with a value of between 1 and 52.

if you can't get to grips with this, drop me a PM or post a reply.

Hi Craig, this is very kind of you. I'll leave it here as it may be useful to others.

The table does NOT have a date. These will just a a selection of top ten items (like cars, or albums, or whatever). A date isn't necessary (for the list)

This is the MySql dump

-- phpMyAdmin SQL Dump
-- version 3.2.0.1
-- www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: May 19, 2010 at 07:03 PM
-- Server version: 5.1.36
-- PHP Version: 5.2.9-2

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `cvmorguk`
--

-- --------------------------------------------------------

--
-- Table structure for table `toptenlist`
--

CREATE TABLE IF NOT EXISTS `toptenlist` (
`topTenListID` int(9) NOT NULL AUTO_INCREMENT,
`1` varchar(50) NOT NULL,
`2` varchar(50) NOT NULL,
`3` varchar(50) NOT NULL,
`4` varchar(50) NOT NULL,
`5` varchar(50) NOT NULL,
`6` varchar(50) NOT NULL,
`7` varchar(50) NOT NULL,
`8` varchar(50) NOT NULL,
`9` varchar(50) NOT NULL,
`10` varchar(50) NOT NULL,
`Image` varchar(100) NOT NULL,
`Active` int(1) NOT NULL,
PRIMARY KEY (`topTenListID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

--
-- Dumping data for table `toptenlist`
--

INSERT INTO `toptenlist` (`topTenListID`, `1`, `2`, `3`, `4`, `5`, `6`, `7`, `8`, `9`, `10`, `Image`, `Active`) VALUES
(1, 'Mazda MX-5L', 'Toyota MR2 (Mk1)', 'Subaru Impreza WRX', 'Peugeot 205 GTI', 'Nissan 200 SX', 'Mini Cooper S (not BMW!)', 'Renault Clio Williams ', 'Lotus Elan', 'Ford Focus ST170', 'VW Golf GTI', 'budget_fast_cars.jpg', 2);


I have got the "Active" field in there but don't really want to use that unless there is no other way.

So the result of this would be someone visiting the site this week would see a list of cars, next week a list of albums the week after a list of whatever and so on.

Is that more helpful?

Hi John.

I'll take a look at this and previous posts and get back to you

:-)

Thanks Craig, I appreciate it.

you dont really have to have a date, just have a column that stores the week number you want the record to be shown for.


for example, running the following query:
SELECT DATE_FORMAT(CURDATE(),'%v') as weekNum


returns 20 for the current date 5/19/2010. This is the 20th week in the year.

So any records that should show this week, should have the week column set to 20

For the sake of example, lets say we add a column to your table for the week number and name it "weekNum".

To get the records for this week we would use:
SELECT * FROM tblName
WHERE weekNum = DATE_FORMAT(CURDATE(),'%v')


assuming that the value stored in weekNum is 20, those records will be returned.

the '%v' format return 1 - 53 where Monday is the fist day of the week. If you want Sunday to be the first day of the week, use '%V' instead

Hi Jason.

I was thinking something similar, but I reasoned that with 20 rows in the table, weeknum would need to appear on 2 or more occasions for each row.

i thought that calculating the existing weeknum from the current date and carrying out some sort of modulus function on this would fix that, but then, starting at week 1, you would loop through 1-20 twice, then 1-13, then back to one again. (does this make sense).

one thing that struck me was to possibly use a stored procedure which would run every time the weeknum changed and increment the active field.??

still scratching my head

still thinking out loud here.

if we were to calculate datediff(todays date, fixed start date) and divide by 7, then we can calculate number of weeks since a fixed point in time.

taking a mod(20) of this value would always provide a number between 1 and 20, so this value can be used to select the 'active' row.

Using this method, we would always be in sequence.

does this seem plausible ?

***

If so, here is my rough and ready SQL...

SELECT * FROM toptenlist
WHERE topTenListID = (mod(round((datediff(curdate(), '2008-10-10')/7),0),3)+1)

2008-10-10 is just an arbitrary date
+1 is required at the end in case the modulus returns 0

(in the example above, i used a modulus of 3 (in red), but this number should be the same as the number of rows in the table.

Feel free to point out the errors in my logic !! :-)

It kept my brain going anyway.

Hope this helps