Jump to content

php mysql row count with where statement


ewittli
 Share

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.

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.

 Share

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