如何从输入的数据中获取行号（sql和php）

Question

I've encountered this problem for a while and I can't seem to find the right answer on google. I don't know if maybe I'm just unlucky.

Anyway, how can I get row number from a specific record I input from a PHP text field, for example:

ID          NAME
11111       john
11112       roger
11113       ellis
11114       jack
11115       wendy

So if I input 11113, the output will be like "this ID is at number 3".

Here is my code:

$id=$_POST['id'];
$query="SELECT COUNT(*) from employee where id like '%$id%'";
$num=mysql_query($query);
echo "this ID is at number $num";

Can anyone point out where did I go wrong?

Answer 1

Here is a query example of how you can do it without adding another column for count:

SELECT e.*,b.count FROM employee as e, (SELECT COUNT(*) as count FROM employee WHERE id <= 3) as b WHERE e.id = 3

Using another select query to count all the rows with id smaller than the id requested

Or if you only want the offset of the row without its data:

SELECT COUNT(*) as count FROM employee WHERE id <= 3

Answer 2

1. At first, you should select * from the employee table

2. Then parse the result to array,

3. Scan the above array (step2) and find your value (11113)

4. If (3) is found, then you can return array index.

Answer 3

First thing: You're using old PHP-method for database, use PDO

If you wan't to get the last insert ID, it's possible to catch it with PDO

And I don't understand your SQL, because you want to get an ID, by filtering with the ID? And when your ID is an INTEGER/NUMBER, than your statement is senseless.