php - 如何计算 my_sql

Question

当我尝试获取此查询的 COUNT 时遇到问题

$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>';
    }

从这个查询中，我从 $echo 计数器中没有得到任何结果

输出:

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

如果我尝试这个查询:

$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>';
    }

输出:

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

我得到 1 ，这是 false 。

这些是表格:订单 ID:

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

订单:

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

只是查询:

$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 ");

Answer 1

不要尝试计算所有记录并在同一查询中返回它们，而是使用 mysql_num_rows 即:

    $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>';
    }

还要注意。 mysql_* 函数在 PHP 5.5.0 中已弃用，并将在将来的某个时候删除。考虑切换到 PDO 或 mysqli，特别是对于新代码。