Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – 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

Tags:

2

Answers


  1. 0

    I’m assuming id and name are the column names in the database table.

    $sql_all = "SELECT id FROM tb_cad ORDER BY id ASC";
$result_all = mysqli_query($conn, $sql_all);

$record_positions = [];
$line_number = 1;

while ($row_all = mysqli_fetch_assoc($result_all)) {
    $record_positions[$row_all['id']] = $line_number;
    $line_number++;
}

$sql_filter = "SELECT * FROM tb_cad WHERE name LIKE '$src_names%' ORDER BY id ASC";
$result_filter = mysqli_query($conn, $sql_filter);

while ($row = mysqli_fetch_assoc($result_filter)) {
    $line_number = $record_positions[$row['id']];
    echo "Line Number: " . $line_number . " - Name: " . $row['name'] . "<br>";
}
    Login or Signup to reply.
  2. 0

    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%';
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search