hardeep hardeep - 7 months ago 12
SQL Question

how to count specific rows based on data in table fields for pagination?

I'm have a database with users from different countries. My pagination is working because the query counts all rows. So it gives you like 5 pages and let's say the total records are finished by page3.

My question is:

1) How can I count only records from one country let's say "australia"? And the pagination should only give me pages with results no extra pages with no results?

My code for pagination is below:

<?php

$per_page = 1;
$pages_query = mysql_query("SELECT COUNT(*) FROM `users`");
$pages = ceil(mysql_result($pages_query,0)/$per_page);

if (!isset($_GET['page']))

{
header("location: blood.php?page=1");

}
else

{
$page = $_GET['page'];

}

$start = (($page - 1)*$per_page);

$colours = mysql_query("SELECT * FROM users WHERE country ='australia' LIMIT $start,$per_page");

while($row = mysql_fetch_assoc($colours))
{
$username = $row['first_name'];

echo "$username<br>";

}

for($number=1; $number<=$pages; $number++)
{
echo '<a href= "?page='.$number.'">'.$number.'&nbsp;</a>';
echo"&nbsp;";
}

echo "<br>Current Page: $page";
?>


how do i include the above code together with the correction suggested in the below code where results are returned after user searches for a username and lastname.

<?php

include 'core/init.php';
include 'includes/overall/oheader.php';
?>

<?php
if(isset($_POST['submit']))
{
$query = $_POST['uname_search'];
$queryl = $_POST ['lname_search'];

$min_length = 3;
if(strlen($query) >= $min_length && strlen($queryl) >= $min_length)
{
$query = htmlspecialchars($query);
$query = mysql_real_escape_string($query);
$queryl = htmlspecialchars($queryl);
$queryl = mysql_real_escape_string($queryl);
echo "<table border='1' width='250px' align='center' cellpadding='1' cellspacing='1'>";
echo "<tr align='center' bgcolor='#002C40' style='color:#FFF'>
<td height='30px' width='150px'>Username</td> <td>first_name</td> <td>last_name</td><td>email</td><td>Mobile_Number</td><td>gender</td><td>county</td><td>blood_group</td>


$raw_results =

mysql_query("SELECT * FROM `users` WHERE (`username` LIKE '%".$query."%') AND (`last_name` LIKE '%".$queryl."%')");
if(mysql_num_rows($raw_results) > 0)
{
while($results = mysql_fetch_array($raw_results))
{

$image = "images/profile/1a4671c319.jpg" ;

echo "<tr align='center' bgcolor='#002C40' style='color:#FFF'>


<td rowspan='4'>"."<img src=".$results['profile']." width=100px height=100px> "."</td>
<td >Username</td>
<td >first_name</td>
<td>last_name</td>
<td>email</td>


</tr>";

echo "<tr align='center' bgcolor='#0f7ea3'>
<td>".$results['username']."</td>
<td>".$results['first_name']."</td>
<td>".$results['last_name']."</td>
<td>".$results['email']."</td>

</tr>" ;

echo "<tr align='center' bgcolor='#002C40' style='color:#FFF'>




<td>Mobile_Number</td>
<td>gender</td>
<td>county</td>
<td>blood_group</td>

</tr>";

echo "<tr align='center' bgcolor='#0f7ea3'>

<td>".$results['Mobile_Number']."</td>
<td>".$results['gender']."</td>
<td>".$results['county']."</td>
<td>".$results['blood_group']."</td>

</tr>" ;
}

}
else{
echo "<tr align='center' bgcolor='#6C0000'>

<td colspan='8' height='25px'>No results</td><tr>";
echo "</table>";
}
}
else{
echo "Minimum length is ".$min_length;
}

}

include 'includes/overall/ofooter.php';

?>

Answer

You could apply the same where clause to the first query which counts the users:

$pages_query = 
    mysql_query("SELECT COUNT(*) FROM `users` WHERE country ='australia'");
    # Here -----------------------------------^
Comments