Retrieve Record Number

[SaRiD]

Ok, simple question (hopefully simple answer)

Is there an QUICK easy way to retrieve the record number of a row in my database?

At the moment I can loop through all the records with a counter running and when I find the record I'm after I note down what the record number was.

This is fine, but if I'm to be searching through 1000's of records this is not so efficent any more.

If I could select the record I wanted out of the database and then run some sort of mysql function (like mysql_insert_id gets the last inserted id) to retrieve the record number that would be fantastic.

Cheers in advance!

 

[Karl Blessing]

1) First off what database are you using. (I assume MySQL)
2) Can't you use SQL query language to find the exact record you want. Not all have record numbers, as its based on how its queried.
3) Whats the criteria of the record you want to find. If its a field of specific name you can simply do.

Select RecordNumber FROM Table Where FieldDesired = "Desired Result"

Thats assuming you have a record number field, or some other kind of record identifier.

Karl Blessing
PHP/MySQL Developer

http://www.karlblessing.com

 

[SaRiD]

What I'm after is:

say there is a table called "foo" (everyone loves foo!)

foo has 1 column called "names"

as I insert data into the table foo it then gets pulled out of the table like so

name
---------
Nigel
Bert
Mandy
Other Fullar
Stephen
Ralph
Kim
Bishop
etc...

thats how they were entered into the database, therefore if a simple "SELECT * FROM foo" would result exatly as above. Correct?

Now if I ran a loop through that table using mysql_fetch_array() and used a counter $i++; and I was searching fot the name "Stephen", it would loop through and as soon as the mysql_array matched up with "Stephen" I would make a variable $intFound = $i; for example.

So Running that would make $intFound = 4 because "Stephen" was the 4th entry entered into the database. Correct?

Is there a command, or more effecient way of finding out what record number "Stephen" would be without having to search through every record with a counter?

For example:

$strSQL = mysql_db_query($db, "SELECT * FROM foo WHERE name = 'Stephen'");
$row = mysql_fetch_array($strSQL);
$intRecordID = mysql_get_record_id();

and the result of that would have $intRecordID = 4 because "Stephen" was the 4th record entered into the database.

Make more sense???

 

[Olavxxx]

If you read the user comments here:

http://no2.php.net/mysql_num_rows

it might give you some clues.

Are you not pleased by having a uniqe_id field?
eg.
1 Nigel
2 Bert
3 Mandy
4 Other Fullar
5 Stephen
6 Ralph
7 Kim
8 Bishop
...

those id' would be unique, and then you could either query:
SELECT id, name FROM users WHERE id = '4';

if you however, only need the id of the user, not the name:
SELECT `id` FROM `users` WHERE name LIKE '%Stephen%';

I think this is what you asked for? I might have mis-understood though.

Olav Alexander Mjelde

http://www.volvo-power.net

Admin & Webmaster

 

[ingresman]

You seem to be after the ordinal number of a record. This concept doesn't exist in relational databases. You could number the recrords with an auto insert id, in which case the number would not change (unless you SQL updated it) or are you after the physical (ordinal) place in the table which can vary if you rebuild the table etc.
hope this helps !

 

[Karl Blessing]

As they said above, unlike something like an Access database, where everytime you add a record, it comes exactly behind the last entry, MySQL is a relational database, which means all rows are not tied to the table in any particular order, except how you query it ( alphabetical, random, reverse, etc ), ifyou must have a row number you must also create another field , such as ID, UserID, UserNumber, etc something of that sort. Its possible to create a field such as ID, and have MySQL auto-increment that feild, meaning to say everytime you add a record, MySQL will automatically increment the number in the unique ID field you created.

Course the other question is , why/when would you need the record number, after you've already found the record? if its to update/delete the record you can simply throw the "Where" clause on the end of those as well. Of course since people's names can vary, and you might have two different steven's I can imagine getting a unique identifier will be helpful in loading the record if someone searches for the full name.

Karl Blessing
PHP/MySQL Developer

http://www.karlblessing.com

 

[ingresman]

Just as a side point, Ingres (of which I know about hence my name) together with Oracle and Informix allow you to see the "hardware key" of any record. In Ingres it's called the TID (Tuple ID), in oracle the RID (Row ID) and you can use the value in queries as every row has a system column with TID or RID. The manuals say you should not manipulate directly as they are for internal use (index tabless etc) but for various admin things they are usefull if you know what you are doing. Does MYSQL have such a facility (I know I should ask there but most of use are in both forums any how !!)