php - 为什么我的日期没有显示在此图表上(drupal、php、mysql)？

Question

我本周的任务是为我的 Drupal 6、Ubercart 和 Ubercart Marketplace 安装创建销售图表。

我选择了 charts_graphs 模块并决定使用 Bluff 库来绘制数据图表。尽管受支持的模块 views_charts 允许您创建范围广泛的图表，但它无法检索我绘制销售数据所需的数据集。如果有人想知道为什么不这样做的详细信息，请询问，我会尽力详细说明。

所以我找到了生成虚张声势图所需的 php 示例:

     <?php 
$canvas = charts_graphs_get_graph('bluff');

      $canvas->title = "Bluff Chart";
      $canvas->type = "line"; // a chart type supported by the charting engine. See further in the doc for the list.
      $canvas->y_legend = "Y Legend";
      $canvas->colour = '#D54C78';
      $canvas->theme = 'keynote';  
      $canvas->series = array(
        'Some Value' => array(9,6,7,9,5,7,6,9,7),
        'Page Views' => array(6,7,9,5,7,6,9,7,3),
      );
      $canvas->x_labels = array('one', 'two', 'three', 'four', 'five', 'six', 'seven', 'eight', 'nine');

      $out = $canvas->get_chart();

      return $out;
    }
?>

此代码然后生成此图...

当然，下一阶段是从 MySQL 查询中获取数据到图中。这是我在舒适区之外的地方，因为我以前没有真正使用 PHP 做过任何事情。

所以经过一番挖掘，我发现了这个 post with an example这给了我一些工作的机会。然后我使用 View 来帮助我开始使用 SQL。我已经很接近了，我能尝到它的味道，但最后一期让我陷入困境，我想不出更多的搜索要做……我相信你能体会到那种感觉:)

这是我到目前为止开发的代码:

<?php
    global $user;
$uname = $user->uid;
$sql = "SELECT DATE_FORMAT((FROM_UNIXTIME(uc_orders.created) + INTERVAL 3600 SECOND), '%d/%m/%Y') as OrderDate, round(SUM(uc_order_products.cost * uc_order_products.qty),2) AS SellerCommission,
round(SUM(uc_order_products.price * uc_order_products.qty),2) AS CustomerPrice
FROM uc_order_products
LEFT JOIN node node_uc_order_products ON uc_order_products.nid = node_uc_order_products.nid 
LEFT JOIN uc_orders uc_orders ON uc_order_products.order_id = uc_orders.order_id 
INNER JOIN users node_uc_order_products__users ON node_uc_order_products.uid = node_uc_order_products__users.uid 
WHERE (node_uc_order_products__users.uid = $uname )
AND (uc_orders.created >= UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL DAYOFMONTH(CURDATE())-1 DAY)))
AND (uc_orders.order_status in ('payment_received', 'completed'))
GROUP BY DAY(FROM_UNIXTIME(uc_orders.created))
ORDER BY MONTH(FROM_UNIXTIME(uc_orders.created)) desc, DAY(FROM_UNIXTIME(uc_orders.created)) desc
LIMIT 31";
$result = db_query($sql,$uname);


  while($row = db_fetch_array($result))
{

    $sellercommission[] = (int) $row[SellerCommission];
    $customerprice[] = (int) $row[CustomerPrice];
    $orderdate[] = (string) $row[OrderDate];

}


  $canvas = charts_graphs_get_graph('bluff');

  $canvas->title = "Volume of sales";
  $canvas->type = "line"; // a chart type supported by the charting engine. See further in the doc for the list.
  $canvas->y_legend = "Y Legend";
  $canvas->colour = '#FFFFFF';
  $canvas->width = '450';
  $canvas->height = '300';
  $canvas->theme = '';  
  $canvas->series = array(
   'Seller Commission' =>array_values($sellercommission),
   'Customer price' =>array_values($customerprice),
  );
  $canvas->x_labels = array_values($orderdate);

  $out = $canvas->get_chart();

  return $out;

?>  

这段代码生成了一个完美的图表，除了 x 轴之外，所有日期都只显示“//”...

当我在数据库上运行查询时，这是我得到的结果集。显然我用 UID 替换了占位符:

09/09/2013  1328.13 1897.32
07/09/2013  455.00  650.00

我无法解决的是我应该使用的“$orderdate[] ....”的类型。我已经尝试过 varchar、int(显示 0)、date(中断它并且不显示任何内容)、datetime(再次中断它)...或者我可能正在检索错误的数据作为 SQL 中的 OrderDate。任何想法将不胜感激...

Answer 1

所以我终于解决了(完全侥幸，不明白其中的区别)但这是最终有效的代码:

<?php
    global $user;
$uname = $user->uid;
$sql = "SELECT concat(DAY(FROM_UNIXTIME(uc_orders.created)),'/',MONTH(FROM_UNIXTIME(uc_orders.created)),'/',  YEAR(FROM_UNIXTIME(uc_orders.created))) as OrderDate, round(SUM(uc_order_products.cost * uc_order_products.qty),2) AS SellerCommission,
round(SUM(uc_order_products.price * uc_order_products.qty),2) AS CustomerPrice
FROM uc_order_products
LEFT JOIN node node_uc_order_products ON uc_order_products.nid = node_uc_order_products.nid 
LEFT JOIN uc_orders uc_orders ON uc_order_products.order_id = uc_orders.order_id 
INNER JOIN users node_uc_order_products__users ON node_uc_order_products.uid = node_uc_order_products__users.uid 
WHERE (node_uc_order_products__users.uid = $uname )
AND (uc_orders.created >= UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL DAYOFMONTH(CURDATE())-1 DAY)))
AND (uc_orders.order_status in ('payment_received', 'completed'))
GROUP BY DAY(FROM_UNIXTIME(uc_orders.created))
ORDER BY MONTH(FROM_UNIXTIME(uc_orders.created)) desc, DAY(FROM_UNIXTIME(uc_orders.created)) asc
LIMIT 31";
$result = db_query($sql,$uname);


  while($row = db_fetch_array($result))
{

    $sellercommission[] = (int) $row[SellerCommission];
    $customerprice[] = (int) $row[CustomerPrice];
    $orderdate[] = (string) $row[OrderDate];

}


  $canvas = charts_graphs_get_graph('bluff');

  $canvas->title = "Volume of sales";
  $canvas->type = "line"; // a chart type supported by the charting engine. See further in the doc for the list.
  $canvas->y_legend = "Y Legend";
  $canvas->colour = '#FFFFFF';
  $canvas->width = '450';
  $canvas->height = '300';
  $canvas->theme = '';  
  $canvas->series = array(
   'Seller Commission' =>array_values($sellercommission),
   'Customer price' =>array_values($customerprice),
  );
  $canvas->x_labels = array_values($orderdate);

  $out = $canvas->get_chart();

  return $out;

?>

我基本上改变了我在 SQL 中请求“OrderDate”的方式……还更正了其他一些小细节……