SELECT * (start from middle of results)

[rubing]

I read the mysql manual page on sorting results, but couldn't find an answer to what i am trying to do.

 

I have an alphanumeric list of stuff, which I list one item at a time (mysql pagination).  However, I want the starting point based on user input.  The user should also be able to index back or forward in the now alphabetized list respective to his starting point. 

 

Is there a simle way to do this?  thanks!

[rhodesa]

You can either do it right in the MYSQL query:

SELECT * FROM `tablename` LIMIT 20,30

 

or you can seek forward in a data result with mysql_data_seek()

 

[rubing]

That is exactly what i am trying to do, however I don't know where to start the internal pointer.

 

For example:

If the users query is 'spaghetti' I have absolutely no idea which result # it is out of all my results.  In other words where to start the LIMIT statement.

[rhodesa]

First, decide how many results/page (rpp), i'm going to use 20 for this example. Now, you will need to keep track of the 'page' they are on through a GET variable.

 

We calculate the start with this: (page - 1) * rpp

 

Page 1

(1-1)*20 = 0

SQL: SELECT * FROM `tablename` LIMIT 0,20

 

Page 2

(2-1)*20 = 20

SQL: SELECT * FROM `tablename` LIMIT 20,20

 

etc

[rubing]

I understand that (i am only using 1 per page).  BUT how do you know which page to start on  ???

 

Let me try to explain better. 

 

My database has a thousand items, which are alphanumeric.  I want the user to input text (query) in order to get the nearest matched item (alphabetically) to his query.  So, If he types spaghetti...I need someway of knowing that the result (s)he sees is the 354th item in this last, when they are sorted alpabetically.

 

Does that make sense? 

 

Furthermore, I am running this as a commandline autoresponder email script and so want to do this as succintly as possible.  Programming this way is very prone to errors and bounced emails.

[rhodesa]

Well, the following should always return the same result set (unless you alter your data):

 

SELECT * FROM `tablename` WHERE `fieldname` = 'spaghetti'

 

So, to get the first one:

SELECT * FROM `tablename` WHERE `fieldname` = 'spaghetti' LIMIT 0,1

Second one:

SELECT * FROM `tablename` WHERE `fieldname` = 'spaghetti' LIMIT 1,1

etc

 

Instead of tracking what specific record they are looking at, you want to track the search criteria, and what row of the returned dataset you want. So, row 1 is the start (which is index 0), row 2 is next, etc.

 

Sorry if I'm not explaining this well...it makes sense in my head, I'm just having trouble putting it in writing.

[rubing]

But I want to allow them to index through every record in my table.  Not just the ones where

 

'fieldname' = 'spaghetti'

 

I just want to start the index at spaghetti. Then let them see the previous or next record if they desire.

[fenway]

Then you'll have to "number" *all* of the rows.

[Barand]

if your results are ordered by "fieldname", try

SELECT a.*, (SELECT COUNT(*) FROM `tablename` WHERE `fieldname` < 'spaghetti') as pos
FROM `tablename` a 
WHERE `fieldname` = 'spaghetti' LIMIT 0,1

[rubing]

Well, I guess there are two possible solutions then.  First Fenway's solution would involve:

 

Creating a virutal or copy of my table which I first sort ASC and then add an ID field with auto increment.  Then, whenever I retrieve a result I can refrence the records ID number.

 

Barand's solution looks a little more elegant, but I don't know if I completey understand the syntax.  I see that you are using the count function to figure out which # result spaghetti is.  COOL!!

 

But, I am not sure about your use of (a.*) or (a)  ???

 

 

[aschk]

The "a" is an alias for the tablename you're using. In other words, its a shortcode for the table. So instead of writing myreallylongtablenamethatistotallystupid.column1 you set up an alias for the table and reference that instead (i.e. a.column1)

[rubing]

Yeah, I figured something like that, it just threw me for a loop cause I always use the word AS when defining an alias.

 

[fenway]

If you use Barand's alternative, you had better stick in a meaningful order by clause... also, be aware that if the recordset is changed, your numbering will be off.

[Barand]

if your results are ordered by "fieldname", try

 

 

Hence my conditional statement above

[fenway]

if your results are ordered by "fieldname", try

 

 

Hence my conditional statement above

Right, that...