Jump to content

php mysql row count with where statement


ewittli

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

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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

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

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