Jump to content

php mysql row count with where statement


Recommended Posts

I have a table with 5 records with the following "id_number" for each record in ASC order:

2, 6, 74, 86,87


There is one other field called "tag_number" and for each record in ASC order here is the data:


50670, 50077, 1234, 1235, 1236


I have a text field and if I search for a spastic "tag_number" I want to return the record but count and display what record like this:


Record 3 of 5


The above 3 of 5 means I searched for "1234".


I'm not sure what loop I need and how to count the records, any help would be great!


Here is what I'm thinking:


$id_number = 74;


$result = db_query("select * from table order by id_number ASC");


$num_rows = mysql_num_rows($result); //this will provide how many records are in the table


$result = db_query("select * from table where id_number = '$id_number' order by id_number ASC");


for ($i = 1; $i == $num_rows; $i = $i + 1) { 


    if ($result['id_number'] == $id_number) {
          $active_row = $i; //this is where I'm counting how many times the loop iterates till it finds the record
echo "Record: " $active_number . " of " . $num_rows;
------------------- Notes -------------------
I need to figure this out very quick, any help would be appreciated, thanks...



Link to comment
Share on other sites

your query


select * from table where id_number = '$id_number' order by id_number ASC


will return 1 row (that with id_number = 74)


So you are trying to count from 1 to 5 when there is only 1 record

Why are you testing for 74?  You know that single record will have id = 74 because that is what you queried for.

Link to comment
Share on other sites

I understand there will only be one record, but I'm trying to count how many records the loop goes through to get to that record so I can display "Record: 3 of 5" please help.


I'm testing with 74 for this example, can you help me figure out how to count the rows so the echo statement will display "Record: 3 of 5"

Edited by ewittli
Link to comment
Share on other sites

I understand that, but I still need to count how many records so we can publish within an application what record is being displayed.  In this case with this query, the application will display "Record: 3 of 5".  Are you going to help me figure this out?

Link to comment
Share on other sites

I just need a for loop to count how many records it takes to get to a pacific row.  in this case, in this example, there are five rows.  in a query, I'm trying to locate a record that happens to be the third row.  Once this database expands, I will have no clue where any record/row is within the table.  But I still want to count up to the row I'm asking for, and in this case it will produce "Record: 3 of 5".


Even though the database is returning only one row (smile)...

Link to comment
Share on other sites


// query to retrieve all 5 rows
$sql = "SELECT id_number, tag_number 
        FROM ewitti
        ORDER BY id_number";

$search = '1234';          // tag_number to search for

$res = $db->query($sql);
$num_rows = $res->num_rows;
$i = 1;

// Loop through the rows until $search is found
while ($row = $res->fetch_assoc()) {
    if ($row['tag_number']==$search) {
        echo "Found ID: {$row['id_number']} — Record $i of $num_rows<br>";

Output--> ID: 74 — Record 3 of 5

Link to comment
Share on other sites

This thread is more than a year old.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.