-1

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?

tadman
  • 208,517
  • 23
  • 234
  • 262
Fariz R
  • 5
  • 4
  • 1
    `COUNT` counts rows that matches in the `WHERE` conditions. This is not what you expect. Additionally you don't have a row number in your table. Order of rows without using `ORDER BY` can be different – Ahmad Aug 05 '16 at 07:36
  • you have no field for the numbering, add a field with numbering where 'ellis' will have number 3 and then you'll be able to get that result. – Tedo G. Aug 05 '16 at 07:36
  • 1
    **WARNING**: If you're just learning PHP, please, do not use the [`mysql_query`](http://php.net/manual/en/function.mysql-query.php) interface. It’s so awful and dangerous that it was removed in PHP 7. A replacement like [PDO is not hard to learn](http://net.tutsplus.com/tutorials/php/why-you-should-be-using-phps-pdo-for-database-access/) and a guide like [PHP The Right Way](http://www.phptherightway.com/) explains best practices. Your user parameters are **not** [properly escaped](http://bobby-tables.com/php) and there are [SQL injection bugs](http://bobby-tables.com/) that can be exploited. – tadman Aug 05 '16 at 07:43
  • 1
    There's no intrinsic numbering here, so the order it appears in a list is entirely arbitrary unless you have an `ORDER BY` clause. – tadman Aug 05 '16 at 07:44

3 Answers3

0

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
WalksAway
  • 2,769
  • 2
  • 20
  • 42
0

  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.

Nguyen Thanh Hung
  • 11
  • 2
0

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.