When i make a query like: $query = mysql_query("SELECT id, name from employees where wc>'100' ORDER BY wc ASC"); this is a query with some n results. what i want to do is get the row Index of employee JOE, also i want the row indexes from the adjacent rows to JOE. I can do this by reading every row until i reach joe and then calculate the previous and next row, but that would be stupid if for example this query had 1 million results :\ Basically what i want is to retrieve row indexes from within a query without having to read row by row, something like: $index = mysql_get_index($query,'employee', 'joe'); mysql_data_seek($query,$index-1); mysql_data_seek($query,$index+1); If something like this is not possible is there any efficient way to do it?
What purpose will it solve? Finding previous and next row does not signify any purpose. If you can share with us, what you are trying to achieve, with an example possibly, we will be able to help you better.
well, for image gallery, practically you won't have more than 20 - 30 or 50 images for an average user. For several core users, you may have 200 - 300, but not even in thousand so forget about millions unless the example was not relevant to real scenario you are referring to. Anyways, following flow can be little helpful for all cases actually: 1. When displaying first image, do query of limit 2, which will give you first and 2nd image. [LIMIT 2] First entry is to display, second is for making link for "Next". 2. When someone does next, query with limit 2 by increasing start of limit query by 1 each time. [LIMIT 1, 2 OR LIMIT 5, 2]. First entry is to display, second is for making link for "Next". 3. When someone does prev, query with limit 2 by decreasing start of limit query by 1 each time. [LIMIT 5, 2 then LIMIT 4, 2]. First entry is for making link for "Prev", second is for display. In 2nd and 3rd step, existing entry will become Prev and Next respectively This way, you won't have to take burden of going through big loop or fetching huge data from mysql.
My suggestion is to run 3 separate queries. #1--$sql="SELECT wc FROM employees where name='JOE';"; #2--$sql="SELECT id, name from employees where wc>".$wc." LIMIT 0, 1;"; #3--$sql="SELECT id, name from employees where wc<".$wc." LIMIT 0, 1;"; #1 pulls out the wc value for Joe, you put this in the variable $wc. #2 pulls out the information you want for the record immediately after Joe's record using the value you pulled out of the first query. #3 pulls out the information you want for the record immediately before Joe's record using the value you pulled out of the first query. Couple of notes--if multiple records can have the same wc value then this might miss records that share the same wc value as Joes. Also, I am sure you could combine #2 & #3 into one query using an absolute value function.
The problem with the > and < operations is that many records can have the same value, for example if all employees have the same value, the prev and next links will always be the same (the first row) I think this is major flaw in mysql... I never had this problem when i used flat files as DB, there i knew i have to store all records in one array then sort and it would always work, but in mysql you find that it is silly to use the same techniques as flat files
That's not a flaw with Mysql, that's a flaw in your query. You need to better define how to order your data. If you are ordering on a field that isn't unique, you should bring in more fields into your ORDER clause so that all ambiguity of how your data should be sorted is removed. For example you could make the clause like this: ...ORDER BY wc ASC, name DESC, id ASC... Since id is most likely unique this will ensure the ordering of your data is consistent among runs of your query.
OK, you right...i suck So the only solution for this is to make > & < queries and play with mysql_data_seek in order to get the prev and next? So let me see... #query to display the list and provide links to the profiles $query_list = "SELECT * FROM db WHERE wc>='100' order BY wc ASC, id ASC"; ID NAME VALUE #201 Paulo = 105 #005 Albert = 1056; #045 Joe 1056 #200 Mario = 1056 #10 Jones = 3067 #Joe's profile id (Im loading the page for Joe's profile following a link from a list of wcs>100) $id_joe = '45'; $wc_joe = '1056'; so how do i get albert and mario as prev and next being on joe's profile? This is a mind job, note there's ppl with the same pee value just to complicate your life If i try to calculate the prev with wc<=wc_joe, Mario gets in the way, if i use wc<wc_joe i lose Albert, the only way is... (wc<=wc_joe AND id<id_joe SORT BY wc DESC LIMIT 1)...i guess next would be (wc>=wc joe AND id>id_joe SORT BY wc ASC LIMIT 1) Stil how this queries would behave for 1 million results query?
First and foremost, are the id and wc fields numbers? If not, you're going to get some weird results when doing comparisons (i.e. 108 is less than 20 when you compare them as strings). Also, now that you've added more sorting rules I think its possible to accurately pull data from your table reliably. #1--$sql="SELECT wc, id FROM employees where name='JOE';"; #2--$sql="SELECT id, name, wc from employees WHERE IF (wc=".$wc_joe." AND id<".$id_joe"., 1, IF(wc<".$wc_joe"., 1,0))=1 ORDER BY wc DESC, id DESC LIMIT 0, 1;"; #3--$sql="SELECT id, name, wc from employees WHERE IF (wc=".$wc_joe." AND id>".$id_joe"., 1, IF(wc<".$wc_joe"., 1,0))=1 ORDER BY wc ASC, id ASC LIMIT 0, 1;"; #1 pulls Joe's wc and id value from the table since both of those are needed for ordering. #2 should pull the record just prior to Joe's (Albert's). #3 should pull the record just after Joe's (Mario's). The key is the WHERE clause. #2 essentially pulls all records where the wc is less than Joe's--unless its equal to Joe's and the id is less. #3 essentially pulls all the records where the wc is greater than Joe's--unless its equal to Joe's and the id is more. I haven't tested the quereis so I might have made some typos, but essentially that's how you should go about it--by using a conditional WHERE clause.