Search code examples
phpmysql

How to get number of a specifc row php

/$sql = "SELECT * FROM tb_cad WHERE name like '$src_names%' ORDER BY id ASC";
$query = mysqli_query($conn, $sql);

Id | name
------------
1     James
2     Claus  
7     John
10    Maria
21    Sonia
22    Evelin

How do I get the result with the specific line number?

4-Maria

A form and function explanation.Thank you in advance

Solution

  • You can use the ROW_NUMBER function:

    SELECT ROW_NUMBER() OVER () AS id, name FROM tb_cad
WHERE name like '$src_names%' ORDER BY id ASC;

    If the result you want is to find Maria's row numbers in the entire table, you need to query twice:

    SELECT id, name FROM (
    SELECT ROW_NUMBER() OVER () AS id, name FROM tb_cad ORDER BY id ASC
) tmp 
WHERE name like '$src_names%';