current row id or row number in php mysql query

Question

Is there any way to get the row id or row number from the result set in php mysql query? What is the use of _rowid in mysql?

The following query fetch the details of applicant 2435

SELECT * FROM applications WHERE _rowid =2435

but here _rowid is not a field of the table. Like this we need to get the record id from a result set in php mysql.

guess he means, the `SN` of the row or `id` of the table row — S L, Mar 29 '11 at 11:51
There is no internal rowid in MySQL. Try use PK (+auto_increment) — Devart, Mar 29 '11 at 11:52
@Faraona - CREATE TABLE table_test( id INT(11) NOT NULL AUTO_INCREMENT, NAME VARCHAR(255) DEFAULT NULL, PRIMARY KEY (id) ); INSERT INTO table_test (NAME) VALUES ('text1'), ('text2'), ('text3'); SELECT id, name FROM table_test; — Devart, Mar 29 '11 at 12:00
@Devart PK has nothing to do with row number and cannot be used this way — Your Common Sense, Mar 29 '11 at 12:20
Ben you have to rephrase your question, to make it more certain, or it will be closed. — Your Common Sense, Mar 29 '11 at 12:21
The following query fetch the details of applicant 2435 "SELECT * FROM applications WHERE _rowid =2435", but here _rowid is not a field of the table. Like this we need to get the record id from a result set in php mysql. — Ben, Mar 29 '11 at 12:30
you have to edit your question instead of commenting it. I did it for you this time. However you can edit it again and add more details. I've never heard of _rowid thing though — Your Common Sense, Mar 29 '11 at 12:38

score 4 · Answer 1 · edited May 23 '17 at 12:03

Extracted from this question:

SELECT @row_number := NULL;
SELECT
    @row_number := IFNULL(@row_number, 0) + 1 AS row_number,
    Column1,
    Column2,
    ColumnN
FROM the_table
WHERE 1 = 1
ORDER BY Column1

If you are running this query from PHP, you need to execute the mysql_query() function twice. First query would be SELECT @row_number := NULL and second one would be the SELECT ... query. Both queries must be executed together over same connection.

score 1 · Accepted Answer · answered Mar 29 '11 at 11:53

1

Check this function mysql_insert_id

answered Mar 29 '11 at 11:53

Faraona

1,685
2
22
33

this function only useful when we insert into table, but not in case of select – Ben Mar 29 '11 at 12:21
Yes this functions returns id of inserted data into table – Fawwad Shafi Dec 17 '13 at 07:44

current row id or row number in php mysql query

2 Answers2