[RESOLVED] fetch num rows

neodize

I want to fetch number of rows in a query

and then if there is 60 results then each table should show no 1,2,3 to 60

Can someone point me on how to do it?

NogDog

Most (all?) of the database extensions have some sort of num_rows() function you can use, such as mysql_num_rows() if using the basic MySQL extension.

halojoy

And if there is 120 rows?
Should all 120 be displayed?

And if not, if only 60 of those 120 rows should be displayed,
which 60?
The newest or oldest rows?

You have to give us an idea how you want to it to be. Right now.
This is not very difficult to do. When we know.

Note, if you change your mind later, we will help you do that change.

neodize

I know how to use mysql_num_rows() to make it count how many rows in that query and echo the total

but i dont know how to make them count 1 by 1 ex. 1,2,3,4,5,6 ---- 60

Im trying to make a report page that on the 1st column is No. but i dont know how to make it count by itself.

can you show me an example?

bradgrafelman

Sounds like you just want a simple counter variable...

$i = 1;
while($result = mysql_fetch_assoc($query)) {
    echo "Row #" . $i++;
}

Res

I can point you in the write direction, if you need code examples, I'll have to come back tonight and try and get those for you.

Basically you need to first count the total records, sounds like you know how to do that already. You will need to do this each time the user changes pages, or make it persistent. This number will be used to build the result page links.
Basically:

$total_count = 1000; //where 1000 is the total results that can be displayed.
$page_count = $total_count % 60;  //where 60 is the results per page.

So that gets you how many pages there are for the total results. We are half way there, we still need to pull the results for the specific page the user is viewing (we have to pull records 1-60, or 61-120 if they are on page 2, etc.)

To do this you want to use a little bit of SQL at the end of you query.

$x = 0;   //where x is the starting record, in this case we are starting with the first record.
$y = 60;  //where y is the number of records to pull, in this case 60.
$sql = "select * from table LIMIT $x, $y";

//again, the same thing but pulling results 60-120
$x = 60;   //where x is the starting record, in this case we are starting with the first record.
$y = 60;  //where y is the number of records to pull, in this case 60.
$sql = "select * from table LIMIT $x, $y";

That SQL is how you will pull the specific records for that page. You will need to use $page_count variable to build links, the links will have _GET vars that you will use as you xy for pulling records. That code will come something like.

$i = 1;
while($i <= $page_count){
   $records_start = 60 *$i;
   echo "<a href='script.php?x=1&y=60'>$i</a>
   $i++;
}

Hope that helps!

neodize

Thanks

actually this is what i wanted to do:

<tr>
<td width="25px">No</td>
<td width="25px">Ticket No</td>
<td width="200px">Name</td>
<td width="150px">IC NO</td>
<td width="100px">Date</td>
</tr>
<?
$query="SELECT * FROM issueveh WHERE route='$trip' AND time='$time' AND date BETWEEN '$mydate1' AND '$mydate2' ORDER BY date";
$result = mysql_query($query) or die(mysql_error());

// Start looping rows in mysql database.
while($rows=mysql_fetch_array($result)){
?>
<tr>
<td width="25px">NUMBER</td>
<td width="25px"><?php echo $rows['id'] ?></td>
<td width="200px"><?php echo $rows['name'] ?></td>
<td width="150px"><?php echo $rows['icno'] ?></td>
<td width="100px"><?php echo $rows['date'] ?></td>
</tr>
<?php
}
?>

What I want to do is to make the number run from number 1 to the end of the rows.

bradgrafelman

neodize;10946139 wrote:
What I want to do is to make the number run from number 1 to the end of the rows.

See my post above.

Also, when posting PHP code, please use the board's [noparse]

..

[/noparse] bbcode tags as they make your code much easier to read and analyze.

neodize

sorry about that. some how that didnt work for me. it came out nothing :/

Res

Are you initilizing your count variable, and adding to it?

//decalre counter First.
$i = 0;
//loop over data
while($row = mysql_fetch_array($result){

   echo "the counter \$i is currently set to the value:".$i."<br/>";

   //then you have to increment the value, so it will be counting.
   $i++;
}

bradgrafelman

Show us the code you tried and we can help you fix it, otherwise I don't know what else to suggest.

neodize

$query="SELECT * FROM issueveh WHERE route='$trip' AND time='$time' AND date BETWEEN '$mydate1' AND '$mydate2' ORDER BY date";
$result = mysql_query($query) or die(mysql_error());

// Start looping rows in mysql database.
while($rows=mysql_fetch_array($result)){
?>
<tr>
<td width="25px">
<?php 
$i = 1;
while($row = mysql_fetch_assoc($result)) {
    echo "Row #" . $i++;
} 
?>
</td>
<td width="200px"><?php echo $rows['plateno'] ?></td>
<td width="150px"><?php echo $rows['vehicle'] ?></td>
<td width="100px"><?php echo $rows['category'] ?></td>
<td width="25px"><?php echo $rows['board'] ?></td>
<td width="100px"><?php echo $loading ?></td>
<td width="100px"><?php echo $rows['dimension'] ?></td>
<td width="100px"><?php echo $rows['name'] ?></td>
<td width="100px"><?php echo $rows['phone'] ?></td>
<td width="100px"><?php echo $rows['id'] ?></td>
</tr>
<?
}
?>

Please point out whats wrong

Weedpacket

You've already got a loop ("while($rows=...."). You don't need to add another one.

neodize

oh thanks!! i get it now