Forum Moderators: coopster
I'm new to PHP and MySQL and have a question on how to find a particular row in a Recordset.
Basically I have a bunch of IDs(auto number PK) from the database that I have in a certain order. They are in order because I have calculated the distance from a postcode to another postcode and are closest to furthest.
I then use IN(123,67,34,89,234,...) in my SQL to bring back the records.
What I need to do next is to loop through an array that I have that holds the IDs in the order I need and move to that rows ID in the record set.
I would just like to know anyones advice on the best way to do this, there may be 10-100 users at any one time using the system. Is it quicker to query the DB for each individual ID?
Things Ive tried
I have had success with using UNION in my SQL ie SELECT.... UNION SELECT.... UNION SELECT... etc for each ID
I have ruled out using Temporary tables because of the number of users.
I am currently looking at using a function that I pass the record set and ID too, which then returns the row number in the record set.
I apologise for the length of this post any help would be appreciated
I'm a bit confused on what you are doing. You stated that:
I then use IN(123,67,34,89,234,...) in my SQL to bring back the records.
This leads me to believe that you already have the information that you need. Is that true? If it is, then you don't need to query the table again to get it.
I could be completely misunderstanding you.
Sorry its a bit confusing, the bit I forgot to mention is that I only show 12 results in the page.
So i get all the records from the data base that I need and then using the longditude and latitude I calculate how far each record in the results is away from the user.
For simplicity the fields are:
Unique ID_PK, Prod Description, Longitude, Latitude
I then loop through each record and calc the distance to the users Logditude and Latitude (as the crow flies).
Then I store the ID_PK and distance in an array and use asort() to get the distance from smallest to largest.
Then I show the results in a table in the order of the array but limit 1,12
When the user clicks the show next page, I pass the array, with the distances in order, back through and show the next bunch limit 12,12 and so on.
I can get it to work but its the efficency and speed that worries me.
So if i had a function that did:
move to the row in the results where ID_PK = key of the array
then I would be all set.
Sorry if this is a bit noobish.
PS The IN( was my SQL statement, I thought that it might bring the results back in the order of the IN statement.
SELECT * FROM y_t WHERE ID_PK IN(123,45,65,255......)
