Order of an item in MySQL query result

Question

Is there a way to know the order of an specific item in a query result with traversing the whole set?

I have a web application that show user comments in Ajaxified way, I would like to send the user a link to their comments like this:

http://my.web/post/12345#comment%5F45

and using the hash value "comment_45" load the comment page with the given comment, but I don't know how to get the right page with traversing the whole result.

Thank you

Answer 1

Are your comments ordered in a sensible manner? If they're always displayed in numerical order from first to last (or last to first) you should be able to just compute what page it's on based on knowing a) the total number of comments, b) the number of comments per page.

Answer 2

Rows in an SQL database are by definition unordered. In order to guarantee the same order on subsequent queries, you must include an "order by" clause. Then, you can do what you want with the following query

SELECT * FROM `your_table` ORDER BY `date_entered` LIMIT 45, 1

If rows are inserted or deleted between queries, the results won't be exactly what the user expects, but this is usually not a problem if the order-by field is a date-time.

Answer 3

I don't know how or where this URL is generated, but I will assume that if you know which comment it is when the URL is created, you should know at a later time and date. It sounds like each comment has a unique ID (which it should if it doesn't).

I don't think having comment_45 reference a certain comment on a certain page is a reliable way of retreiving the comment later (what if another comment earlier in the page or on another page is deleted? what if you change how many comments display on a page? etc)

If comments have an ID, and the "comment_45" in your URL is in fact this ID, you could use the following general SQL query to retrieve only this comment:

select field1, field2, ... fieldn from comments where comment_id = id;

In this case, id would be a variable with the comment ID provided by the URL (the "45" portion of comment_45). As this information is coming from an untrusted source (the internet), you will want to make sure you sanitize the input to prevent SQL injection.

Answer 4

I am usually against using stored procedures in MySQL since I think they are not mature enough but I think this is a classic case to create one.

We are still required to have two queries, one for getting the comment's order and one for fetching the relevant rows but I could not find of anything more creative for having it done within 1 single query since the LIMIT values have to be known before executing the query.

In cases when we are not talking about huge results sets then it might be more efficient to simply fetch all results and the comments order and then use array_slice for displaying only the relevant page comments.

/* get the comment order */
SET @commentOrder = 0;
SELECT commentOrder INTO @relevantCommentOrder FROM (
        SELECT commentID, @commentOrder := @commentOrder+ 1 as commentOrder
        FROM COMMENTS
        ORDER BY creationDate) commentsOrder
    WHERE commentID= 45;
/* get the value we want to use in the LIMIT clause */
SET @startFrom = CONVERT(ROUND(ROUND(@relevantCommentOrder/10)*10) ,UNSIGNED);
/* fetch relevant results */
PREPARE STMT FROM 'SELECT * FROM COMMENTS LIMIT ?,10';
EXECUTE STMT USING @startFrom;

The next one is 1 query that would get you the whole results set and put the comment's order in all rows so you could then just check the first row's relevantCommentOrder field inside PHP and then slice the array so it'd display only relevant comments, this one is only efficient if the whole results set is not too big.

SET @commentOrder = 0;
SELECT *, @relevantCommentOrder;
    FROM (
   SELECT commentID, @commentOrder := @commentOrder+ 1 as commentOrder, 
           @relevantCommentOrder := case when commentID = 45 
                                        then @commentOrder 
                                        else @relevantCommentOrder end 
      FROM COMMENTS
      ORDER BY creationDate ) subQuery;