Gossamer Forum: General: Internet Technologies: Good sorting algorithm...

Feb 6, 2003, 4:13 AM

Andy

Veteran / Moderator (17364 posts)

Feb 6, 2003, 4:13 AM

Post #1 of 8

Shortcut

Good sorting algorithm...

Does anyone have a good system for sorting items? I'm working on a wallpaper script, in PHP. I am going to try and add a sorting system, that will show new links to be sorted first, and then order it by hits. I'm trying to think how to structure the code/SQL.

How does LSQL work out if a link is new? I think this is going to be my main problems. The actual query shouldn't be hard. Something like this should do;

SELECT * FROM gallery_Wallpapers WHERE CatID = 5 ORDER BY AddStamp, Downloads

The main problem I'm thinking there is going to be, is working out if AddStamp (contains a UNIX timestamp from NOW()) is equal to 1 week.

Does anyone have some ideas they can throw at me about this? It doesn't matter if the ideas are Perl related...cos it shoudn't be that hard to translate into PHP.

Thanks in advance Smile

Andy (mod)
andy@ultranerds.co.uk

IMPORTANT: I've now moved to ultranerds.co.uk, and the .com will no longer work!
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package (plugins total "value" $3,325 & rising, for just $350)| GLinks ULTRA Package PRO (plugins total "value" $5,625 & rising, for just $500)
Support Forum | Links SQL Plugins | DMOZ Dumps | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Compare our different Plugin packages *new* Free CSS Templates

Feb 6, 2003, 4:15 AM

Paul

Veteran (19537 posts)

Feb 6, 2003, 4:15 AM

Post #2 of 8

Shortcut

Re: [Andy] Good sorting algorithm... In reply to

I'm not too sure on what you are having trouble with?

If you are just trying to work out a time difference then you can work out how many seconds are in a week and you know what the current time is using time() so just substract the timestamp of the record from the current time and see if its bigger or smaller than the number of seconds in a week.

Last edited by:

Paul: Feb 6, 2003, 4:16 AM

Feb 6, 2003, 4:20 AM

Andy

Veteran / Moderator (17364 posts)

Feb 6, 2003, 4:20 AM

Post #3 of 8

Shortcut

Re: [Paul] Good sorting algorithm... In reply to

Eugh..how stupid of me! Duh!

Thanks Smile

Andy (mod)
andy@ultranerds.co.uk

Feb 6, 2003, 5:01 AM

Andy

Veteran / Moderator (17364 posts)

Feb 6, 2003, 5:01 AM

Post #4 of 8

Shortcut

Re: [Andy] Good sorting algorithm... In reply to

Ok..I managed to get this sorted by using the getdate() function in PHP, then grabbing the day of the year, and then minusing 7, to give the new number date. Then its just a case of running the SQL queries...

Anyway..I don't seem to be able to think straight today Unimpressed

I'm trying to figure out how I will get it to work with multiple pages.... the reason I am envisaging a problem, is because of the ORDER BY statement. I currently have;

SELECT * FROM Wallpapers WHERE CatID = $catID ORDER BY isNew, Downloads.

I'm passing the following variables into the script;

page_num = page number

i.e;

wallpapers.php?page_num=3&catID=1

Has anyone got any ideas on how to order these links, whilst preserving the 'isNew' and 'download' statement Unsure

I'm off to my lunch break now....hopefully I'll be able to think straight after :|

Thanks in advance.

Andy (mod)
andy@ultranerds.co.uk

Feb 6, 2003, 7:54 AM

Paul

Veteran (19537 posts)

Feb 6, 2003, 7:54 AM

Post #5 of 8

Shortcut

Re: [Andy] Good sorting algorithm... In reply to

>>
then grabbing the day of the year, and then minusing 7
<<

I don't know how that php function works so correct me if I'm wrong, but what happens if the day is between 01 and 07 and you minus 7...won't you end up with your day at like minus "x"?

>>
Has anyone got any ideas on how to order these links, whilst preserving the 'isNew' and 'download' statement
<<

Im not sure what you mean?

Last edited by:

Paul: Feb 6, 2003, 7:54 AM

Feb 6, 2003, 8:05 AM

Andy

Veteran / Moderator (17364 posts)

Feb 6, 2003, 8:05 AM

Post #6 of 8

Shortcut

Re: [Paul] Good sorting algorithm... In reply to

>>>I don't know how that php function works so correct me if I'm wrong, but what happens if the day is between 01 and 07 and you minus 7...won't you end up with your day at like minus "x"? <<<

Yeah, i accommidated for that Smile

>>>Im not sure what you mean? <<<

Basically, i have my database....i.e;

Code:
ID     |     Title    |     isNew      |     Download 
           test1          1                    34 
           test2          0                    35 
           test3          1                    42 
           test4          1                    34 
           test5          0                    35 
           test6          1                    42 
           test7          1                    34 
           test8          0                    35 
           test9          1                    42

Now, if I'm ordering by isNew, then ID's 1,3,4,6,7 and 9 get shown first, and then 2,5 and 8 get shown.

Now, if I have 5 links per page, then that will cause 1,3,4,6 and 7 to get shown.

When someone clicks on the 'next page' link, it needs to carry on from where it left...i.e 9, then 2,5 and 8.

Do you understand what I'm saying now?

Thanks Smile

Andy (mod)
andy@ultranerds.co.uk

Feb 6, 2003, 9:05 AM

Paul

Veteran (19537 posts)

Feb 6, 2003, 9:05 AM

Post #7 of 8

Shortcut

Re: [Andy] Good sorting algorithm... In reply to

Well thats handled automatically using the LIMIT clause.

If you do:

SELECT * FROM Table ORDER BY isNew DESC LIMIT 1,5

....you'll get your first five new links, then:

SELECT * FROM Table ORDER BY isNew DESC LIMIT 6,5

...will show your remaining non new links.

Feb 6, 2003, 9:24 AM

Andy

Veteran / Moderator (17364 posts)

Feb 6, 2003, 9:24 AM

Post #8 of 8

Shortcut

Re: [Paul] Good sorting algorithm... In reply to

OMG..how could i forget about such an important feature in LIMIT!

Thanks loads Paul Smile

Andy (mod)
andy@ultranerds.co.uk