doutangguan2460
doutangguan2460
2016-08-05 07:33

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

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?

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

3条回答

  • douqu2712 douqu2712 5年前

    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
    
    点赞 评论 复制链接分享
  • doulan8054 doulan8054 5年前

    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.

    点赞 评论 复制链接分享
  • doucao8982 doucao8982 5年前
    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.

    点赞 评论 复制链接分享

为你推荐