php mysql row count with where statement

[ewittli]

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...

 

 

 

 

 

[dalecosp]

Doesn't $active_row contain the information you need, then?
 

Change

echo "Record: " $active_number . " of " . $num_rows;

 

to

echo "Record: " $active_row . " of " . $num_rows;

... assuming I'm understanding, of course.  I am a bit thick sometimes ;-)

[ewittli]

I need someone who can help me with the loop and figure how to count up to the record so I can display:

 

"Record: 3 of 5" with the above sample.

 

renaming the variables is not the solution, please, I need some help!

[Barand]

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.

[ewittli]

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 August 18, 2014 by ewittli

[ewittli]

It will be greatly appreciated if someone can help me with this issue.

[ewittli]

I would like to do this with a for loop where $i = 1 to get the first record and then increment $i each time through till the id_number is equal to the $id_number variable I'm new at this, and cannot figure this one out.

[Barand]

I'll type slowly so you can take it in.

 

Your ... query ... returns ... one ... row ... whose ... id ... is ... 74 ... so ... you ... will ... always ... get ... 1 ... of ... 1

[ewittli]

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?

[ewittli]

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)...

[Barand]

So use a query that returns all 5 records and not just 1. Simples.

[ewittli]

I guess getting any help with the for loop is not going to happen tonight!  At this point, if you are unwilling to help me with this code, find another post!

[boompa]

You need to read the manual on for loops, paying particular attention to the middle argument.

[Barand]

try

// 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>";
        break;
    }
    ++$i;
}

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