如何从MySQL DB的多个表中获取数据并使用PHP来显示图形?(How to get data from multiple tables of MySQL DB and manuplate it using PHP to display graphs?)
我想为年度销售,购买和转移报告制作图表。 MySQL quires和PHP代码和图形代码如下。
MySQL Quires
销售查询
SELECT COUNT(id) AS sales, DATE_FORMAT( date, '%Y-%M' ) AS dataofmonth FROM sales WHERE `date` LIKE '2015%' GROUP BY DATE_FORMAT(date, '%Y-%M') ORDER BY DATE_FORMAT(date, '%Y-%M') ASC
购买查询
SELECT COUNT(id) AS num, DATE_FORMAT( date, '%Y-%M' ) AS dataofmonth FROM purchases WHERE `date` LIKE '2015%' GROUP BY DATE_FORMAT(date, '%Y-%M') ORDER BY DATE_FORMAT(date, '%Y-%M') ASC
转移查询
SELECT COUNT(id) AS num, DATE_FORMAT( date, '%Y-%M' ) AS dataofmonth FROM transfers WHERE `date` LIKE '2015%' GROUP BY DATE_FORMAT(date, '%Y-%M') ORDER BY DATE_FORMAT(date, '%Y-%M') ASC
现在,销售查询返回以下数据
sales, dataofmonth 1 2015-December 3 2015-January
数据库中只有1月和12月的数据。 所以,我想在其他月份显示0。
图表/图表
我正在使用高级图表来显示图形页面,使用以下代码,这些代码完美无缺,但我无法用PHP实现动态。
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.2/jquery.min.js"></script> <script src="http://code.highcharts.com/highcharts.js"></script> <script src="http://code.highcharts.com/modules/exporting.js"></script> <script type="text/javascript"> jQuery.noConflict(); var example = 'line-basic', theme = 'default'; (function($){ // encapsulate jQuery $(function () { $('#container').highcharts({ title: { text: 'Monthly Average Report', x: -20 //center },/* subtitle: { text: 'Source: WorldClimate.com', x: -20 },*/ xAxis: { categories: ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'] }, yAxis: { title: { text: '<?=$_SESSION['mcs_name']?>' }, plotLines: [{ value: 0, width: 1, color: '#808080' }] },/* tooltip: { valueSuffix: '°C' },*/ legend: { layout: 'vertical', align: 'right', verticalAlign: 'middle', borderWidth: 0 }, series: [{ name: 'Purchases', data: [7.0, 6.9, 9.5, 14.5, 18.2, 21.5, 25.2, 26.5, 23.3, 18.3, 13.9, 9.6] }, { name: 'Sales', data: [<?=@$janS?>, <?=@$febS?>, <?=@$marS?>, <?=@$aprS?>, <?=@$mayS?>, <?=@$junS?>, <?=@$julS?>, <?=@$augS?>, <?=@$sepS?>, <?=@$octS?>, <?=@$novS?>, <?=@$decS?>] }, { name: 'Transfers', data: [-0.9, 0.6, 3.5, 8.4, 13.5, 17.0, 18.6, 17.9, 14.3, 9.0, 3.9, 1.0] }] }); }); })(jQuery); </script> <div id="container" style="min-width: 310px; height: 400px; margin: 0 auto"></div>
PHP代码
我的PHP代码有很多问题,因为我是创建图表的初学者。
<?php $purchases = $db->query("SELECT COUNT(id) AS num, DATE_FORMAT( date, '%Y-%M' ) AS dataofmonth FROM purchases WHERE `date` LIKE '2015%' GROUP BY DATE_FORMAT(date, '%Y-%M') ORDER BY DATE_FORMAT(date, '%Y-%M') ASC"); $sales = $db->query("SELECT COUNT(id) AS num, DATE_FORMAT( date, '%Y-%M' ) AS dataofmonth FROM sales WHERE `date` LIKE '2015%' GROUP BY DATE_FORMAT(date, '%Y-%M') ORDER BY DATE_FORMAT(date, '%Y-%M') ASC"); $transfers = $db->query("SELECT COUNT(id) AS num, DATE_FORMAT( date, '%Y-%M' ) AS dataofmonth FROM transfers WHERE `date` LIKE '2015%' GROUP BY DATE_FORMAT(date, '%Y-%M') ORDER BY DATE_FORMAT(date, '%Y-%M') ASC"); while($objP = $purchases->fetch(PDO::FETCH_OBJ)) { if($objP->dataofmonth == "".date('Y')."-January") $janP = $objP->num; if($objP->dataofmonth == "".date('Y')."-February") $febP = $objP->num; if($objP->dataofmonth == "".date('Y')."-March") $marP = $objP->num; if($objP->dataofmonth == "".date('Y')."-April") $aprP = $objP->num; if($objP->dataofmonth == "".date('Y')."-May") $mayP = $objP->num; if($objP->dataofmonth == "".date('Y')."-June") $junP = $objP->num; if($objP->dataofmonth == "".date('Y')."-July") $julP = $objP->num; if($objP->dataofmonth == "".date('Y')."-August") $augP = $objP->num; if($objP->dataofmonth == "".date('Y')."-September") $sepP = $objP->num; if($objP->dataofmonth == "".date('Y')."-October") $octP = $objP->num; if($objP->dataofmonth == "".date('Y')."-November") $novP = $objP->num; if($objP->dataofmonth == "".date('Y')."-December") $decP = $objP->num; } while($objS = $sales->fetch(PDO::FETCH_OBJ)) { if($objS->dataofmonth == "".date('Y')."-January") $janS = $objS->num; else $janS = 0; if($objS->dataofmonth == "".date('Y')."-February") $febS = $objS->num; else $febS = 0; if($objS->dataofmonth == "".date('Y')."-March") $marS = $objS->num; else $marS = 0; if($objS->dataofmonth == "".date('Y')."-April") $aprS = $objS->num; else $aprS = 0; if($objS->dataofmonth == "".date('Y')."-May") $mayS = $objS->num; else $mayS = 0; if($objS->dataofmonth == "".date('Y')."-June") $junS = $objS->num; else $junS = 0; if($objS->dataofmonth == "".date('Y')."-July") $julS = $objS->num; else $julS = 0; if($objS->dataofmonth == "".date('Y')."-August") $augS = $objS->num; else $augS = 0; if($objS->dataofmonth == "".date('Y')."-September") $sepS = $objS->num; else $sepS = 0; if($objS->dataofmonth == "".date('Y')."-October") $octS = $objS->num; else $octS = 0; if($objS->dataofmonth == "".date('Y')."-November") $novS = $objS->num; else $novS = 0; if($objS->dataofmonth == "".date('Y')."-December") $decS = $objS->num; else $decS = 0; } while($objT = $transfers->fetch(PDO::FETCH_OBJ)) { if($objT->dataofmonth == "".date('Y')."-January") $janT = $objT->num; if($objT->dataofmonth == "".date('Y')."-February") $febT = $objT->num; if($objT->dataofmonth == "".date('Y')."-March") $marT = $objT->num; if($objT->dataofmonth == "".date('Y')."-April") $aprT = $objT->num; if($objT->dataofmonth == "".date('Y')."-May") $mayT = $objT->num; if($objT->dataofmonth == "".date('Y')."-June") $junT = $objT->num; if($objT->dataofmonth == "".date('Y')."-July") $julT = $objT->num; if($objT->dataofmonth == "".date('Y')."-August") $augT = $objT->num; if($objT->dataofmonth == "".date('Y')."-September") $sepT = $objT->num; if($objT->dataofmonth == "".date('Y')."-October") $octT = $objT->num; if($objT->dataofmonth == "".date('Y')."-November") $novT = $objT->num; if($objT->dataofmonth == "".date('Y')."-December") $decT = $objT->num; } ?>
在上面的PHP代码中,我试图将数据放入高图表代码的后续行但却失败了。
{ name: 'Sales', data: [<?=@$janS?>, <?=@$febS?>, <?=@$marS?>, <?=@$aprS?>, <?=@$mayS?>, <?=@$junS?>, <?=@$julS?>, <?=@$augS?>, <?=@$sepS?>, <?=@$octS?>, <?=@$novS?>, <?=@$decS?>] }
I want to make graph for yearly report of sales, purchases and transfers. MySQL quires and PHP codes and Graph codes are below.
MySQL Quires
SALES QUERY
SELECT COUNT(id) AS sales, DATE_FORMAT( date, '%Y-%M' ) AS dataofmonth FROM sales WHERE `date` LIKE '2015%' GROUP BY DATE_FORMAT(date, '%Y-%M') ORDER BY DATE_FORMAT(date, '%Y-%M') ASC
PURCHASES QUERY
SELECT COUNT(id) AS num, DATE_FORMAT( date, '%Y-%M' ) AS dataofmonth FROM purchases WHERE `date` LIKE '2015%' GROUP BY DATE_FORMAT(date, '%Y-%M') ORDER BY DATE_FORMAT(date, '%Y-%M') ASC
TRANSFERS QUERY
SELECT COUNT(id) AS num, DATE_FORMAT( date, '%Y-%M' ) AS dataofmonth FROM transfers WHERE `date` LIKE '2015%' GROUP BY DATE_FORMAT(date, '%Y-%M') ORDER BY DATE_FORMAT(date, '%Y-%M') ASC
Now, the sales query returned following data
sales, dataofmonth 1 2015-December 3 2015-January
There are only data of January and December available in database. So, I want to display 0 for other months.
CHART / GRAPH
I'm using high-charts to display graph page using following code which works perfect but I'm unable to make it dynamic with PHP.
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.2/jquery.min.js"></script> <script src="http://code.highcharts.com/highcharts.js"></script> <script src="http://code.highcharts.com/modules/exporting.js"></script> <script type="text/javascript"> jQuery.noConflict(); var example = 'line-basic', theme = 'default'; (function($){ // encapsulate jQuery $(function () { $('#container').highcharts({ title: { text: 'Monthly Average Report', x: -20 //center },/* subtitle: { text: 'Source: WorldClimate.com', x: -20 },*/ xAxis: { categories: ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'] }, yAxis: { title: { text: '<?=$_SESSION['mcs_name']?>' }, plotLines: [{ value: 0, width: 1, color: '#808080' }] },/* tooltip: { valueSuffix: '°C' },*/ legend: { layout: 'vertical', align: 'right', verticalAlign: 'middle', borderWidth: 0 }, series: [{ name: 'Purchases', data: [7.0, 6.9, 9.5, 14.5, 18.2, 21.5, 25.2, 26.5, 23.3, 18.3, 13.9, 9.6] }, { name: 'Sales', data: [<?=@$janS?>, <?=@$febS?>, <?=@$marS?>, <?=@$aprS?>, <?=@$mayS?>, <?=@$junS?>, <?=@$julS?>, <?=@$augS?>, <?=@$sepS?>, <?=@$octS?>, <?=@$novS?>, <?=@$decS?>] }, { name: 'Transfers', data: [-0.9, 0.6, 3.5, 8.4, 13.5, 17.0, 18.6, 17.9, 14.3, 9.0, 3.9, 1.0] }] }); }); })(jQuery); </script> <div id="container" style="min-width: 310px; height: 400px; margin: 0 auto"></div>
PHP CODE
My PHP code have a lot of issues as I'm beginner in creating graphs.
<?php $purchases = $db->query("SELECT COUNT(id) AS num, DATE_FORMAT( date, '%Y-%M' ) AS dataofmonth FROM purchases WHERE `date` LIKE '2015%' GROUP BY DATE_FORMAT(date, '%Y-%M') ORDER BY DATE_FORMAT(date, '%Y-%M') ASC"); $sales = $db->query("SELECT COUNT(id) AS num, DATE_FORMAT( date, '%Y-%M' ) AS dataofmonth FROM sales WHERE `date` LIKE '2015%' GROUP BY DATE_FORMAT(date, '%Y-%M') ORDER BY DATE_FORMAT(date, '%Y-%M') ASC"); $transfers = $db->query("SELECT COUNT(id) AS num, DATE_FORMAT( date, '%Y-%M' ) AS dataofmonth FROM transfers WHERE `date` LIKE '2015%' GROUP BY DATE_FORMAT(date, '%Y-%M') ORDER BY DATE_FORMAT(date, '%Y-%M') ASC"); while($objP = $purchases->fetch(PDO::FETCH_OBJ)) { if($objP->dataofmonth == "".date('Y')."-January") $janP = $objP->num; if($objP->dataofmonth == "".date('Y')."-February") $febP = $objP->num; if($objP->dataofmonth == "".date('Y')."-March") $marP = $objP->num; if($objP->dataofmonth == "".date('Y')."-April") $aprP = $objP->num; if($objP->dataofmonth == "".date('Y')."-May") $mayP = $objP->num; if($objP->dataofmonth == "".date('Y')."-June") $junP = $objP->num; if($objP->dataofmonth == "".date('Y')."-July") $julP = $objP->num; if($objP->dataofmonth == "".date('Y')."-August") $augP = $objP->num; if($objP->dataofmonth == "".date('Y')."-September") $sepP = $objP->num; if($objP->dataofmonth == "".date('Y')."-October") $octP = $objP->num; if($objP->dataofmonth == "".date('Y')."-November") $novP = $objP->num; if($objP->dataofmonth == "".date('Y')."-December") $decP = $objP->num; } while($objS = $sales->fetch(PDO::FETCH_OBJ)) { if($objS->dataofmonth == "".date('Y')."-January") $janS = $objS->num; else $janS = 0; if($objS->dataofmonth == "".date('Y')."-February") $febS = $objS->num; else $febS = 0; if($objS->dataofmonth == "".date('Y')."-March") $marS = $objS->num; else $marS = 0; if($objS->dataofmonth == "".date('Y')."-April") $aprS = $objS->num; else $aprS = 0; if($objS->dataofmonth == "".date('Y')."-May") $mayS = $objS->num; else $mayS = 0; if($objS->dataofmonth == "".date('Y')."-June") $junS = $objS->num; else $junS = 0; if($objS->dataofmonth == "".date('Y')."-July") $julS = $objS->num; else $julS = 0; if($objS->dataofmonth == "".date('Y')."-August") $augS = $objS->num; else $augS = 0; if($objS->dataofmonth == "".date('Y')."-September") $sepS = $objS->num; else $sepS = 0; if($objS->dataofmonth == "".date('Y')."-October") $octS = $objS->num; else $octS = 0; if($objS->dataofmonth == "".date('Y')."-November") $novS = $objS->num; else $novS = 0; if($objS->dataofmonth == "".date('Y')."-December") $decS = $objS->num; else $decS = 0; } while($objT = $transfers->fetch(PDO::FETCH_OBJ)) { if($objT->dataofmonth == "".date('Y')."-January") $janT = $objT->num; if($objT->dataofmonth == "".date('Y')."-February") $febT = $objT->num; if($objT->dataofmonth == "".date('Y')."-March") $marT = $objT->num; if($objT->dataofmonth == "".date('Y')."-April") $aprT = $objT->num; if($objT->dataofmonth == "".date('Y')."-May") $mayT = $objT->num; if($objT->dataofmonth == "".date('Y')."-June") $junT = $objT->num; if($objT->dataofmonth == "".date('Y')."-July") $julT = $objT->num; if($objT->dataofmonth == "".date('Y')."-August") $augT = $objT->num; if($objT->dataofmonth == "".date('Y')."-September") $sepT = $objT->num; if($objT->dataofmonth == "".date('Y')."-October") $octT = $objT->num; if($objT->dataofmonth == "".date('Y')."-November") $novT = $objT->num; if($objT->dataofmonth == "".date('Y')."-December") $decT = $objT->num; } ?>
In above php code I was trying to put data into follow line of high chart codes but failed.
{ name: 'Sales', data: [<?=@$janS?>, <?=@$febS?>, <?=@$marS?>, <?=@$aprS?>, <?=@$mayS?>, <?=@$junS?>, <?=@$julS?>, <?=@$augS?>, <?=@$sepS?>, <?=@$octS?>, <?=@$novS?>, <?=@$decS?>] }
原文:https://stackoverflow.com/questions/28177845
最满意答案
您在
@ISA
声明中缺少分号。在
sub max
,$foo
是未声明的; 使用foreach my $foo ...
这应该让它编译。 我没有超越那个。
(顺便说一句,我没有得到同样的错误,我使用perl 5.14.0,
perl -cw master-math.pm
。)You're missing a semicolon on the declaration of
@ISA
.In
sub max
,$foo
is undeclared; useforeach my $foo ...
.That should get it to compile. I haven't looked beyond that.
(BTW, I didn't get the same errors you did. I used perl 5.14.0,
perl -cw master-math.pm
.)
相关问答
更多-
Perl模块的用法(Perl Module usage)[2022-04-04]
试试package My::ModuleTest; 在你的文件ModuleTest.pm - 你应该使用全名。 Try package My::ModuleTest; in your file ModuleTest.pm - you should use the full name. -
Perl模块安装?(Perl Module installation?)[2021-10-04]
ActiveState Perl和Strawberry Perl是已经配备了所有必要部件的发行版。 (在过时的ActiveState Perl版本上,您必须自己安装并设置编译器工具链 。) 然后,根据Stack Overflow Perl FAQ简单地安装模块: 安装缺少Perl模块的最简单方法是什么? ActiveState Perl and Strawberry Perl are distributions that already comes with all necessary parts. (On ... -
您在@ISA声明中缺少分号。 在sub max , $foo是未声明的; 使用foreach my $foo ... 这应该让它编译。 我没有超越那个。 (顺便说一句,我没有得到同样的错误,我使用perl 5.14.0, perl -cw master-math.pm 。) You're missing a semicolon on the declaration of @ISA. In sub max, $foo is undeclared; use foreach my $foo .... That s ...
-
尝试通过Inline :: Java模块将Java内联到Perl中(Attempting to inline Java into Perl via the Inline::Java module)[2021-10-06]
好的。 我想到了。 我有几件事情错了: 在Study下,我有方法和类,但我只应该有Class。 错误 use Inline ( Java => 'DATA', J2SDK => $ENV{JAVA_HOME}, CLASSPATH => $PERL_CLASSPATH, STUDY => ["SFTP", "close", "list", "get", "put", "remove"], # DEBUG => 4, ); 对 use Inline ( Java ... -
似乎你已经为RHEL / Centos 4配置了RPMforge,但是你使用的是Centos 6: 安装:perl-Params-Validate x86_64 0.95-1。 el4 .rf 按照本网站的指示进行全球远程升级以修复损坏的依赖关系(我很幸运,不需要重新安装)。 然后安装你想要的perl包。 Seems that you have RPMforge configured for RHEL / Centos 4 but you use Centos 6: Installing: perl-Par ...
-
Perl模块安装(Perl Module Installation)[2023-01-30]
看来你正在使用ActiveState Perl,它带有自己的软件包管理器(称为PPM),以减轻安装模块的痛苦,尤其是那些通常需要C编译器才能安装的模块。 请按照说明设置您的环境,以便PPM知道您的代理。 例如,要为单个cmd.exe实例设置代理设置: C:\>set http_proxy=http://username:password@proxy.example.org:8080 然后,在相同的命令提示符窗口中,试试这个(它适用于我,尽管我不在代理之后): C:\>ppm install File-Co ... -
或者有些遗忘或遗失(我错过了什么?) 另一种选择: 不要让你的模块与你的脚本 。 该模块是独立的,因为它是可重用的,所以把它放在一个库文件夹中。 这可以来自个人项目的本地库,包含在use lib任何东西(也可能引用您为该项目设置的环境变量),将模块制作为CPAN库,并让cpan管理它所在的位置。 你决定做什么取决于代码的可重用性,除了脚本如何使用它。 为什么这种明显而常规的操作需要很多工作? 在这个计划中,这并不是很多工作。 您期望在文件系统中分组的文件应该在语言级别自动由Perl use来解决requir ...
-
设置Perl模块结构(Setting Up Perl Module Structure)[2021-12-15]
从您的示例中,在您的主模块中,我假设您将提供访问器方法来获取子类。 所以你要做的就是包括require Sub::Module; 在该方法的顶部。 在编译时不会发生任何事情,但是第一次运行代码时,perl将加载模块。 第一次加载后,该行require Sub::Module; 将成为一个无操作。 如果所有代码都是面向对象的,则无需担心导入函数。 但如果你这样做,声明use Module qw(abc); 被解释为: BEGIN { require Module; Module->import ... -
如何在Perl中使用模块(How to use a module in Perl)[2023-02-24]
如果您只是想打印哈希,只需添加Data::Dumper模块以及strict和warnings,然后 print Dumper(\%modules); 更新:尝试类似 use warnings; use strict; use Module::CoreList; use Data::Dumper; my %module = %{ $Module::CoreList::version{5.006} }; print Dumper (\%module); If you simply want to p ... -
迁移perl模块(Migrate a perl module)[2022-04-03]
应足以全球取代Foo :: Bar和Foo / Bar find -type f -exec perl -i -pe's{Foo(::|-|/)Bar}{MyBetterName${1}Bar}g;' {} + 然后重命名文件本身 find -depth -execdir rename 's/Foo/MyBetterName/g' {} \; Should be enough to globally replace Foo::Bar and Foo/Bar find -type f -exec perl ...