问如何进行my_sql计数
EN

Stack Overflow用户

提问于 2013-08-21 05:18:41

回答 1查看 92关注 0票数 0

当我试图得到这个查询的计数时，我遇到了一个问题。

$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，这是假的。

以下是表: 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

命令：

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

php

mysql

count

回答 1

Stack Overflow用户

回答已采纳

发布于 2013-08-21 05:38:27

与其尝试计数所有记录并在同一个查询中返回它们，不如使用mysql_num_rows I.即：

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