Generating Ordered Lists

bradley252

hi, im in a bit of a pickle! Im trying to make generate a list from a MYSQL database using PHP. But this list is a bit tricky, because it is ordered by the date at which the users joined it. Which is straight forward. But the users can jump a place in the queue by referring someone to join this list. In doing so, they jump a place forward. But i cant think how to do this? So far i have managed to display the list ordered by date, but i dont know how to add another user, as it messes up the positioning of the lsit!

This may sound strange, probably because i havnt explained it very well, but what im trying to do is make a similar script, to the one used on www.thephonelist.com/gift.php?ID=2

Any help would be greatful!

Matt Bradley

nemonoman

You need to add a 'sortorder' column somewhere. You would update the sortorder of the referrer

tekky

or you can just do a multiple order by....

ORDER BY column1, column2

basically increment column1 when someone reffers and the person buys....

increment column2 when a person refers and the person joins....

might work..... maybe.... 😉

davidforbes

hmm, i dont want to seem like a total idiot! But.... i have the column 1, which counts the number of people a user has referred. But i dont understand what u mean is kept in column2?

ps (This is the same person as bradley252), made another username! The other one is bust!!

tekky

ahh more simple, I was basing my last reply off the site you offered the link to....

use something like

ORDER BY datecolumn ASC/DESC, refferalcolumn ASC/DESC

this will base the ORDER on their join date.. .and if 2 people have the SAME date... then it will order those based on date THEN referrals....

make sense?

davidforbes

yes it makes perfect sence, and it works like a charm! cheers!

davidforbes

hold up a minute! it doesnt work as i thought it would! This code only makes it order by date, then refferals, which means that no matter how may refferals you make, if someone in the queue joined before you you cannot pass them?! Any suggestions for changing the code?! It was sooo close too!!

tekky

Originally posted by davidforbes
yes it makes perfect sence, and it works like a charm! cheers!

cool beans, dont forget to Mark thread resolved!
😃

nemonoman

You want to add a new column for sorting. Each new member gets the max(value)+1 of that column. If a member refers somebody, then his sort value is updated to max(value)+1.

davidforbes

but surely the max() function will still not take into account that i need to jump people over others, regardless of date?! But still order them nito date order to start with?!

Im still running this ......

SELECT * FROM bigList WHERE gift = '$row[name]' GROUP BY ORDER by date_joined ASC, refferals DESC

which isnt doing what i need! Any code snippets would be a great help! To point me in the right direction!!

nemonoman

Once you create this sortorder column, THAT'S the column you use for all sorting.

Your problem is shared by most programmers: We tend to think of the problem in the terms we first express it "I want to sort by date, but with some exception, etc.,etc."

In fact, you DON'T want to sort by date. In fact, you want to set by an arbitrary sorting method, based on business rules.

There are, according to your rules, SORTING EVENTS. You push a member to the top of the stack each time a sorting event occurs. So far you've listed 2 sorting events:

Joining
Referring new member

Get away from the idea that dates have anything to do with the sorting you've described. Dates are a secondary issue.

You need a separate column to keep track of the sort order being updated by sorting events.

When an event occur, update the user's sortorder to max(sortorder)+1

THen SELECT * FROM sometable ORDER BY sortorder DESC

davidforbes

no, that code is to push a user to the top of the list. What i want to do is, when a user referrers someone, they jump ONE place in the list.

So if they were 3rd in the list, and they referred someone, they would jump to 2nd, and the person who was in 2nd would be 3rd.

Sorry if i wasnt clear about that! My fault!

nemonoman

You are almost there and don't realize it. If you can say this

when a user referrers someone, they jump ONE place in the list.

you've defined both the problem and solution.

Your table

Name/Sortorder

Tekky/1
Nemo/2
David/3

David has referred somebody and gets to move up 1 notch.

First figure out David's current sortorder:

SELECT sortorder FROM table WHERE name='David'

Now you know David is sortorder 3

Next find the name and sortorder of the person 1 step over david's sort order (3)

SELECT name, sortorder FROM table WHERE sortorder<3
ORDER BY sortorder DESC LIMIT 1

Now you know that Nemo is above David in slot 2.

UPDATE table SET sortorder = 2 WHERE name='David'
UPDATE table SET sortorder = 3 WHERE name='Nemo'

If you are absolutely sure that the list will be sorted in a series with no gaps ever (ie, always 1,2,3...n), your job is even easier.

You'll need to do a little gotcha checking...for example, since Tekky is number 1 already, you don't want errors arising from trying to move him up even higher.

tekky

the solution to your sortorder = 1 delema is to use 1 as the lowest an always INCREMENT in value, then just inverse the list using DESC to get the highest person frist

davidforbes

cheers, its working now!