CODEWITHSUNDEEP
mysql
Yang Jie Domodomo
Yang Jie Domodomo

Reputation: 685

selecting specific row number from select

I'm a beginner at MySQL syntax. So there are a few question I want to ask.

I got a clue DB where users can add in clues. And have a webmethod that select a range of numbers to do random function. (random for the sake of the game, no point doing same clue over and over right?)

But my main problem right now is that what if the author decided to add in more clues? then my clue db will be looking like this. 

+--------+-------------+-----------+--------+
| cID    | clueDetails |  location | author |
+--------+-------------+-----------+--------+
|   1    |      abcde  |   loc 1   | auth 1 |
|   2    |      efghi  |   loc 1   | auth 1 |
|   3    |      jklmno |   loc 2   | auth 1 |
|   4    |      pqrstu |   loc 2   | auth 1 |
|   5    |      vwxyz  |   loc 1   | auth 1 |
+--------+-------------+-----------+--------+

If the player select loc1 auth 1, it will be showing cID 1,2 and 5. so I couldn't use my random function effectively as it select the first and last of loc and auth and 3 and 4 doesnt fit in. I know right now it's very vague as information are scarce. And to actually understand the whole process, goes right down to the game, and the method/function I have. (which will be very long)

Cutting to the chase, my result will be something as shown below, and the way to identify it will be by cID, but in the event that clue were added in different order ( as shown above) then my function will get rather screw up.

EDIT: assuming this random function give me back 2 clues, because I want to play 2 clues. this random function give me back 1 and 3. so from the table result below, 1 and 3 will give me cID1 and cID5 as they are row number 1 and 3. (sorry for the confusion caused)

+--------+-------------+-----------+--------+
| cID    | clueDetails |  location | author |
+--------+-------------+-----------+--------+
|   1    |      abcde  |   loc 1   | auth 1 |
|   2    |      efghi  |   loc 1   | auth 1 |
|   5    |      vwxyz  |   loc 1   | auth 1 |
+--------+-------------+-----------+--------+

So with that, I want to ask if can we select row by its number? e.g row[3] = cID 5, vwxyz, loc 1, auth 1.

As far as I'm concerned, I've done massive research and there doesn't seem to be any function in MySQL that allow us to select by row number. (though all the article were pretty old dated, 2010 and before. Not sure if MySQL has added in any new function)

I saw a SO thread - MySQL - Get row number on select and from how I see it, it seems to be generating a field called ranking.

What I want to know is, is this field ranking temp or permanent? Because if it's just a temp field, then I could shift the identifier from cID to this numbering.

Or do any of you have any suggestion to go around solving this issue? I thought of clearing the db, and re create the db, but that will be taking too much time. And over time when the DB get large it will be slower as well. And another method is to make a datatable to fill all the current clue where loc=?loc and auth=?auth and add them in again with the new clue(latest), but i figure that will cause the cID to boom and fly at a very fast rate. And I'm afraid this will cause memory management issue / memory leak.

EDIT2: As the create field is just a temp field, and seem to be the only alternative, I tried this MySQL command.

set @rank=0;
select @rank:=@rank+1 AS rank, cId, clueDetails, location, author from tbl_clue where location = "loc" and author = "auth" order by rank ASC

It seem to display what I want, but my command seem different from what other usually give. (more bracket and other stuff). Is my command ok? will there be any indirect implication caused by it?

Upvotes: 1

Views: 1952

Answers (2)

Pippa Rose Smith
Pippa Rose Smith

Reputation: 1416

I'm not sure if I understand you correctly, but assuming you end up with:

+--------+-------------+-----------+--------+
| cID    | clueDetails |  location | author |
+--------+-------------+-----------+--------+
|   1    |      abcde  |   loc 1   | auth 1 |
|   2    |      efghi  |   loc 1   | auth 1 |
|   5    |      vwxyz  |   loc 1   | auth 1 |
+--------+-------------+-----------+--------+

and you only want one record at random instead of 3 records you could do the following:

$query = "THE QUERY";

if ($result = $dbc->query($query))
{
    $num_rows = mysql_num_rows($result);

    $random_number = rand(1, $num_rows);

    $count = 1;

    while($nt = $result->fetch_assoc())
    {
       if ($count = $random_number)
       {
           //SAVE THE CLUE DETAILS
       }

       $count = $count + 1;
    }
}

Upvotes: 0

John Woo
John Woo

Reputation: 263933

You can try this one. Please add a comment if this helps :)

SELECT     cID, clueDetails, location, author
FROM
   (
      SELECT    @rownum := @rownum + 1 as `RowNo`, 
                p.cID, 
                p.clueDetails, 
                p.location, 
                p.author 
        FROM (
               SELECT cID, clueDetails, location, author
                 FROM myTableName
                WHERE location = 'loc 1' AND author = 'auth 1'
             ) p , (SELECT @rownum:=0) r 
    ) y
WHERE y.RowNo = 3
ORDER BY RowNo

Upvotes: 2

Related Questions