Using COUNT & Distinct in PHP pagination

Question

HI I have a PHP Pagination system, what i am trying to do is use Distinct to return only unique values, what happens is the page shows how many pages there should be but doesn't give me any results.

My code is here

$query = "SELECT DISTINCT ip_address, 
                 COUNT(*) as num 
          FROM $tableName  
          WHERE hostname 
          LIKE 'mail%' AND type='6'";
$total_pages = mysql_fetch_array(mysql_query($query));
$total_pages = $total_pages[num];

$stages = 3;
$page = mysql_escape_string($_GET['page']);

if($page){
    $start = ($page - 1) * $limit; 
}else{
    $start = 0; 
}   

// Get page data
$query1 = "SELECT DISTINCT ip_address, 
                  hostname 
            FROM masterip_details  
                WHERE hostname 
            LIKE 'mail%' AND type='6' 
                LIMIT $start, $limit";
$result = mysql_query($query1);

Answer 1

You should put the DISTINCT inside the COUNT function to count the number of unique IP adresses in your $tableName table. Like this:

$query = "SELECT COUNT(DISTINCT ip_address) as num 
             FROM $tableName  
             WHERE hostname 
             LIKE 'mail%' AND type='6'
         ";

Answer 2

If you don't have an index on the ip_address field, I'd just use SELECT COUNT(DISTINCT ip_address) as num. If you have an index, you could just as well do this:

SELECT COUNT(type) AS num FROM tbl WHERE hostname LIKE 'mail%' and type = 6 GROUP BY ip_address;

Because, if you're grouping on an index, DISTINCT is equivalent to GROUP BY. More details here and here.

The real reason for my answering this question though: Please, don't use the deprecated mysql_* extension. Use either one of the replacements, mysqli_*, with i for improved, or PDO, which seems to be more commonly used.