CODEWITHSUNDEEP
phpmysqlcount
Βασιλης Ιωσηφιδης
Βασιλης Ιωσηφιδης

Reputation: 769

HOW TO my_sql COUNT

I have a problem when i try to get the COUNT of this query

$query = "SELECT COUNT(*) AS counter FROM (";
$query .= "SELECT *, GROUP_CONCAT(orders.product) as PRO FROM id_of_orders,orders WHERE 1=1 AND orders.order_id = id_of_orders.id_order";
if(!empty($_SESSION['employees']))
  $query .= " AND id_of_orders.username='$_SESSION[employees]'";
if(!empty($_SESSION['timis']))
  $query .= " AND id_of_orders.price='$_SESSION[timis]'";
if(!empty($_SESSION['dates']))
  $query .= " AND DATE(time)='$_SESSION[dates]'";

$query .= " GROUP BY id_order " ;
$query .= " LIMIT $offset, $rec_limit )" ;
    $result = mysql_query($query);


$row = mysql_fetch_array($result);
$counter = $row['counter'];
echo $counter . '<br>' ; 

while($row = mysql_fetch_array($result))
    {
        echo $row['id_order'] . ": -> " . $row['time']. " " .$row['username'] . " " . $row['price'] . " " . $row['PRO'] . '<br>';
    }

From this query i get nothing from the $echo counter

OUTPUT :

11: -> 2013-08-18 17:29:39 skata 1.00 eee
35: -> 2013-08-11 15:03:01 0.00 frento,frento,frento,frapes
36: -> 2013-08-21 12:43:31 skata 0.00 frapes
37: -> 2013-08-21 12:43:55 skata 12.00 frento

If I try this query: 

$query = "SELECT *,  COUNT(id_order) AS counter, GROUP_CONCAT(orders.product) as PRO FROM id_of_orders,orders WHERE 1=1 AND orders.order_id = id_of_orders.id_order";
        if(!empty($_SESSION['employees']))
          $query .= " AND id_of_orders.username='$_SESSION[employees]'";
        if(!empty($_SESSION['timis']))
          $query .= " AND id_of_orders.price='$_SESSION[timis]'";
        if(!empty($_SESSION['dates']))
          $query .= " AND DATE(time)='$_SESSION[dates]'";

        $query .= " GROUP BY id_order " ;
        $query .= " LIMIT $offset, $rec_limit" ;

$row = mysql_fetch_array($result);
$counter = $row['counter'];
echo $counter . '<br>' ; 

while($row = mysql_fetch_array($result))
    {
        echo $row['id_order'] . ": -> " . $row['time']. " " .$row['username'] . " " . $row['price'] . " " . $row['PRO'] . '<br>';
    }

OUTPUT :

1 // <~~~ this is the echo $counter
11: -> 2013-08-18 17:29:39 skata 1.00 eee
35: -> 2013-08-11 15:03:01 0.00 frento,frento,frento,frapes
36: -> 2013-08-21 12:43:31 skata 0.00 frapes
37: -> 2013-08-21 12:43:55 skata 12.00 frento

I get 1 , which is false .

These are the tables: id_of_orders:

id_order| time              | username | price
35      |2013-08-11 15:03:01|          |0.00
36      |2013-08-21 12:43:31|skata     |0.00
37      |2013-08-21 12:43:55|skata     |12.00

orders:

order_id  | product |   ulika   
35        |frento   |zaxari,fountoukia,troufa
35        |frento   |zaxari,kakao
35        |frento   |
36        |frapes   |zaxari
37        |frento   |
35        |frapes   |

JUST THE QUERY:

$query = (" SELECT *, GROUP_CONCAT(orders.product) as PRO 
FROM id_of_orders,orders 
WHERE 1=1 
AND orders.order_id = id_of_orders.id_order 
AND id_of_orders.username='$_SESSION[employees] 
AND id_of_orders.price='$_SESSION[timis]' 
AND DATE(time)='$_SESSION[dates]' 
GROUP BY id_order 
LIMIT $offset, $rec_limit ");

Upvotes: 0

Views: 97

Answers (1)

Jim
Jim

Reputation: 22646

Rather than trying to count all the records and return them in the same query use mysql_num_rows i.e:

    $counter = mysql_num_rows($result);
    echo $counter . '<br>' ; 

    while($row = mysql_fetch_array($result)){
        echo $row['id_order'] . ": -> " . $row['time']. " " .$row['username'] . " " . $row['price'] . " " . $row['PRO'] . '<br>';
    }

Also be aware. The mysql_* functions are deprecated in PHP 5.5.0 and will be removed at some point in the future. Consider switching to PDO or mysqli particularly for new code.

Upvotes: 1

Related Questions