| Author |
 Topic  |
|
|
Shaggy
Support Moderator
Ireland
6780 Posts |
 Posted - 05 January 2005 : 09:50:38
|
Been scratching me noodle over this one for a couple of days now with no joy. What I'm trying to do is select 10 members from our database using a WHERE IN clause but I need to retain the order of the string I'm looking in. So, for example, if I have 5,3,4,1,2, I need the records to be returned in that order. I know there's no way of directly forcing that order on the recordset but I was wondering if anyone had some sort of workaround or hack to achieve this without making further, unnecessary trips to the database.
Using MySQL, by the way, if it makes any differnce.
|
 Search is your friend
“I was having a mildly paranoid day, mostly due to the
fact that the mad priest lady from over the river had
taken to nailing weasels to my front door again.” |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
Shaggy
Support Moderator
Ireland
6780 Posts |
Posted - 05 January 2005 : 12:43:42
|
It's pulled from the database. It's for a page where members can see the last x people who have viewed their profile. Every time a profile is viewed, it updates a field for that member by adding the member id of the member viewing the profile to the start of the string and dropping the last one. For example, if the last 5 members to view my profile were 5,3,4,1,2 and member 8 viewed my profile then the field next to my name in the database is updated to read 8,5,3,4,2.
On the page where members can see who viewed their profile, that string is pulled from the database and then thrown into the WHERE IN statement.
Hope all that makes sense, I can post the files if that'd be of any help.
And, I realise that probably isn't the best method to achieve what I want to do but it's just a temporary solution until I come up with a better way.
|
Search is your friend
“I was having a mildly paranoid day, mostly due to the
fact that the mad priest lady from over the river had
taken to nailing weasels to my front door again.” |
 |
|
|
Gremlin
General Help Moderator
New Zealand
7528 Posts |
Posted - 05 January 2005 : 16:12:00
|
Not very efficient, but you could do 10 seperate queries instead of using the IN statement. Or alternatively SELECT ALL and then use your ASP instead of the SQL to pull out the matching members that way you can do them in the order you want.
Whilst it's often nice to try and do everything in SQL, sometimes it's just more practical to get all the data you need and do the manipulation/selection via ASP instead. |
Kiwihosting.Net - The Forum Hosting Specialists
|
Edited by - Gremlin on 05 January 2005 16:13:53 |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 05 January 2005 : 17:11:03
|
Ok, this is how I'd do it. I would create a 3 column table to register the members who view other member's profiles. The columns would be:
VISITED_ID
VISITOR_ID
VISIT_DATETIME
This would make it a snap to get the desired profiles, since the VISITOR_ID would be there and VISIT_DATETIME would provide the order you want.
A new visit would mean deleting the record relative to the "oldest" visit and adding a new one. Sounds simple enough, though if your version of MySQL still does not support subqueries finding the oldest record to delete will involve two queries. One to find the record to delete, the other to delete it. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
Shaggy
Support Moderator
Ireland
6780 Posts |
Posted - 06 January 2005 : 06:54:12
|
Hmmm ... your second solution might be the way to go Gremlin, cheers  Was going to go with the SQL query for each member id at one stage but the 9 extra database hits just didn't seem worth it.
Rui, that's actually the solution I had started working on before more pressing issues came up; hence this temporary solution. The only difference being, I won't be deleting records - that way we can show people who's been viewing their profile the most by adding another field to that table. Only downside I can see to it though is, the more member we have the bigger that tables going to be, obviously. For example, if we have 500 members that's potentially quarter of a million records (500*500)! That's worst case scenario, of course; we won't get all 500 members looking at every single profile.
|
Search is your friend
“I was having a mildly paranoid day, mostly due to the
fact that the mad priest lady from over the river had
taken to nailing weasels to my front door again.” |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
Shaggy
Support Moderator
Ireland
6780 Posts |
Posted - 06 January 2005 : 13:05:13
|
500 members was just an example; we're hoping to eventually push 20000 members  But by that time, we should be on a dedicated server so I s'pose it wouldn't be that big an issue.
|
Search is your friend
“I was having a mildly paranoid day, mostly due to the
fact that the mad priest lady from over the river had
taken to nailing weasels to my front door again.” |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 06 January 2005 : 16:40:55
|
| It really wouldn't matter much, would it? Surely each of the 20000 members wouldn't view all the other member's profiles... |
Snitz 3.4 Readme | Like the support? Support Snitz too |
Edited by - ruirib on 06 January 2005 16:41:22 |
|
|
|
Shaggy
Support Moderator
Ireland
6780 Posts |
Posted - 07 January 2005 : 09:47:02
|
Gawd, no!
Just we pessimists always have to plan for the worst!
|
Search is your friend
“I was having a mildly paranoid day, mostly due to the
fact that the mad priest lady from over the river had
taken to nailing weasels to my front door again.” |
|
|
| |
Topic |
|
Topic Locked
