I have a table called "participants" that has 3 fields:
prt_id
prt_event_id
prt_participant_id
What I have is a select query with a where condition on event_id. The query returns let's say 20 rows (20 different participants). What I would like to do is to be able to figure out the row number for a given participant (prt_id).
SELECT *
FROM participants
WHERE prt_id = someinteger
While you can't specifically find a row ID using MySQL, you could do something like the following:
$conn = new mysqli(/*dbinfo*/);
$res = $conn->query("SELECT prt_id FROM participants");
$rowids = array(); $currid = 1;
while ($row = $res->fetch_object()) { // this is using the mysqli library
$rowids[$row->prt_id] = $currid;
$currid++;
}
This would give you an array of ids associated with prt_id.
ORDER BY prt_id clause or delete a value then the ids should always stay the same.You could do something like:
<?php
$counter = 1; // Start at one for first entry
$res = mysql_query("SELECT * FROM participants WHERE prt_id = 12");
while( $array = mysql_fetch_assoc($res) )
{
// Do something with the counter, store it into array with details
$counter++;
}
?>
This should do what you want inside MySQL (ie assign a rownum in the order of prt_id), but the performance will be dependent on the number of rows in the table so it's not optimal.
SELECT * FROM (
SELECT @tmp:=@tmp+1 rownum, p.*
FROM (SELECT @tmp:=0) z, participants p
ORDER BY prt_id
) participants
WHERE prt_id = 36;
Demo here.
Edit: This "doh level" rewrite uses an simple index range instead of a table scan, so should be much faster (provided prt_id is a PRIMARY KEY)
SELECT *, COUNT(p2.prt_id) ROWNUM
FROM participants p1
JOIN participants p2
ON p1.prt_id >= p2.prt_id
WHERE p1.prt_id=36;
Demo here.
you could just add an index column in your database, set it as int, primary key and auto increment. then when retrieving the row you retrieve the index number.
RowID is a feature of Oracle: http://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns008.htm.
MySQL does not have something like that, you can basically emulate that by assign number to an array inside php as you retrieve each row, but that doesn't guarantee you the same number next time you retrieve that results. You probably have to settle for using one of the primary IDs
