[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 552
  • Last Modified:

PHP Mysql query

Hi All

Please have a look at the code attached .Currently i get sales ,purchases etcc details per month back in a table format from multiple mysql  tables with current code .

It calls in variables $month="05" $year="2010" $period="12"  for example.

I want the code to take the current month that the script gets called so datenow() and then work back 24 months as per current month .So i am looking for a mysql script that can do this.
Currently i have to keep on changing the period i want this to happen automatically.

All help will be appreciated.

Thank you
Thomas


 
<?php

$departments=array("bakery","butchery","deli");  // These are tables in the mysql database

function sum_all_tables ($month,$year,$department) {
global $period;
    echo "<table cellpadding='4px' width='100%' height='100%' border='1'>";
	echo "<tr><th class='th1'>Total Store</th><th>Sales</th><th>Sales TG</th><th>Purchases</th><th>Int Sales</th><th>Waste</th><th>CF GP</th></tr>";
	
	for($i=0;$i<$period;$i++) {	
			list($var1,$var2,$var3,$var4,$var5,$var6)=all_departments_monthcount($month,$year);
			echo "<tr><td class='td1'>";
			echo "$year-$month";
			echo "</td><td>";
			echo money_format('%.0n', "$var1")."\n";
			echo "</td><td>";
			echo money_format('%.0n', "$var2")."\n";
			echo "</td><td>";
			echo money_format('%.0n', "$var3")."\n";
			echo "</td><td>";
			echo money_format('%.0n', "$var4")."\n";
			echo "</td><td>";
			echo money_format('%.0n', "$var5")."\n";
			echo "</td><td>";
			echo money_format('%.0n', "$var6")."\n";
			echo "</td></tr>";
			$month++;	
				if($month == 13) {
					$month = 01;
					$year++;
					;
			
				}
		 } 					
						
echo "</table>" ;


}

function all_departments_monthcount($month,$year,$department) {
	global $departments;
	$startdate = "$year-$month-01"; 
	$enddate = "$year-$month-31";
	/// All departments to change to group
	$querybuild = "SELECT SUM(salestotal) as totalsales,SUM(purchasetotal) as totalpurchases ,SUM(internalsalestotal) as totalinternalsales,SUM(wastetotal) as totalwaste ,SUM(claimstotal) as totalclaims ,SUM(ibttotal) as totalibt from (";
	$depcount = 0;
	foreach($departments as $department){	
		$querybuild = $querybuild . "(SELECT SUM(sales) as salestotal,SUM(purchases) as purchasetotal,SUM(internal_sales) as internalsalestotal,SUM(waste) as wastetotal ,SUM(claims) as claimstotal,SUM(ibt) as ibttotal FROM " . $department . " WHERE 					        date BETWEEN '$startdate' AND        '$enddate' )";
			if (!(($departments[((sizeof($departments)) - 1)]) == $departments[$depcount])) {
				$querybuild = $querybuild . " UNION ";
			}
			$depcount = $depcount + 1;
			}
			$querybuild = $querybuild .  ")as sale_table_alias ";
			$result = mysql_query($querybuild);
			$result1=mysql_query( "SELECT sales as target FROM salestargets WHERE date BETWEEN '$startdate' AND '$enddate'")or die(mysql_error());
			$salestotal = mysql_result($result, 0, "totalsales");
			$purchasestotal = mysql_result($result, 0, "totalpurchases");
			$internalsalestotal = mysql_result($result, 0, "totalinternalsales");
			$wastetotal = mysql_result($result, 0, "totalwaste");
			$claimstotal = mysql_result($result, 0, "totalclaims");
			global $idt; 
$ibttotal = mysql_result($result, 0, "totalibt");
$purchasestotal=$purchasestotal-$claimstotal;
$purchasestotal=$purchasestotal-$ibttotal;
if ($idt=="plus"){$cashflowgp =$salestotal-($purchasestotal+$internalsalestotal);}else{$cashflowgp =$salestotal-($purchasestotal-$internalsalestotal);}

			$target = mysql_result($result1, 0, "target");
			
			return array($salestotal,$target,$purchasestotal,$internalsalestotal,$wastetotal,$cashflowgp);
}

Open in new window


0
TomCatEL
Asked:
TomCatEL
2 Solutions
 
Jagadishwor DulalBraces MediaCommented:
0
 
Lukasz ChmielewskiCommented:
0
 
TomCatELAuthor Commented:
Hi Roads Roads

I do not want difference in months and i dont have a start and enddate.

I need a solution that takes current month and then calculates every month 24 months back and displays each months result and  it pulls it from multiple tables.

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
TomCatELAuthor Commented:
I am also not sure how i would go about writing the code to use perioddiff.
0
 
Ray PaseurCommented:
Hi, Thomas.  You might find this article useful.  It will help you get some foundation in how PHP and MySQL work together when you want to do date-related calculations.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html

Basically, you want to have a DATETIME column associated with each row.  Then if you want to go back 24 months, you can use something like this:

$long_ago = date('c', strtotime('Today - 24 months')); // GET THE ISO8601 DATETIME STRING FOR 24 MONTHS AGO.

Your SELECT statement will use something like "my_datetime_column BETWEEN '$long_ago' AND NOW"
0
 
TomCatELAuthor Commented:
Hi Ray

You put me onto the right track . I wrote the following code that goes back 24 months and works out the data each month if i hard code the tables in . But with the following script to loop through the array of tables i get the following error "PHP Warning:  mysql_fetch_array(): supplied argument is not a valid MySQL result resource  "

If you can please assist
 
<?php 

$datenow = mktime();  
$datenow = date("Y-m-d",($datenow)); 
$startdate = strtotime(date("Y-m-d", strtotime($datenow)) . " -24 months");
$startdate= date("Y-m-01",$startdate);


$link = connect_demo();
$namedb=connect_demo();
mysql_select_db("$namedb",$link); 	 //Connecting to database

$departments=array("bakery","butchery", "fruitveg","deli");  // These are tables in database
  

	$querybuild = "SELECT SUM(salestotal) as totalsales,SUM(purchasetotal) as totalpurchases ,SUM(internalsalestotal) as totalinternalsales,SUM(wastetotal) as totalwaste ,SUM(claimstotal) as totalclaims ,SUM(ibttotal) as totalibt,
	 'month' from (";
	$depcount = 0;
	foreach($departments as $department){	
		$querybuild = $querybuild . "(SELECT SUM(sales) as salestotal,SUM(purchases) as purchasetotal,SUM(internal_sales) as internalsalestotal,SUM(waste) as wastetotal ,SUM(claims) as claimstotal,SUM(ibt) as ibttotal,
		 LEFT(date, 7) AS 'month' FROM $department WHERE date BETWEEN $startdate AND $datenow GROUP BY month )";
			if (!(($departments[((sizeof($departments)) - 1)]) == $departments[$depcount])) {
				$querybuild = $querybuild . " UNION ";
			}
			$depcount = $depcount + 1;
			}
			$querybuild = $querybuild .  ")as sale_table_alias GROUP BY 'month' ";
			$result = mysql_query($querybuild);
			
					
			
			while($row = mysql_fetch_array($result)) {
	          echo $row["month"];
	          echo " ";
	          echo $row["totalsales"];
			  echo "</br>";          }
		

?>

Open in new window

0
 
Ray PaseurCommented:
PHP Warning:  mysql_fetch_array(): supplied argument is not a valid MySQL result resource

This almost always means that the query failed.  MySQL-Query() gives a return value.  Test it, and if it is FALSE, print out the contents of MySQL_Error() to see what went wrong.  You probably also want to print the contents of the final query string, too.
0
 
Ray PaseurCommented:
So something like this...

$result = mysql_query($querybuild);

if ($result === FALSE)
{
    echo "Oops: $querybuild";
    echo mysql_error();
    die("Catastrophe");
}
0
 
TomCatELAuthor Commented:
I am getting the following can not find problem

Oops: "SELECT SUM(salestotal) AS totalsales, month FROM ((SELECT SUM(sales) AS salestotal, LEFT(date, 7) AS month FROM bakery WHERE date BETWEEN 2009-05-01 AND 2011-05-07 GROUP BY month ) UNION (SELECT SUM(sales) AS salestotal, LEFT(date, 7) AS month FROM butchery WHERE date BETWEEN 2009-05-01 AND 2011-05-07 GROUP BY month ) UNION (SELECT SUM(sales) AS salestotal, LEFT(date, 7) AS month FROM fruitveg WHERE date BETWEEN 2009-05-01 AND 2011-05-07 GROUP BY month ) UNION (SELECT SUM(sales) AS salestotal, LEFT(date, 7) AS month FROM deli WHERE date BETWEEN 2009-05-01 AND 2011-05-07 GROUP BY month ))as sale_table_alias GROUP BY month "
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"SELECT SUM(salestotal) AS totalsales, month FROM ((SELECT SUM(sales) AS salesto' at line 1
Catastrophe
0
 
TomCatELAuthor Commented:
After some hours I have sorted out my syntax problem and my solution is working . GREAT !!!!!!!!


Thanks Ray_Paseur for leading me on the right path


Thanks
Thomas
0
 
Ray PaseurCommented:
@TomCatEL: You asked a question that evinced ignorance of the way PHP and MySQL handle DATETIME processing, and you got good examples of how that is done.  At that point you said you were "onto the right track."  Then you changed your query, it failed, and you asked, "If you can please assist."  So I showed you how to detect query failure and how find the error message.  And you wrote, "Thanks Ray_Paseur for leading me on the right path."

Then you gave the worst possible grade you can give at EE.  Can you please explain to me why you did that?  What did you expect?  Here are the grading guidelines, for your future reference.

http://www.experts-exchange.com/help.jsp#hs=29&hi=403

Looking forward to hearing what you have to say about this, thanks.
0
 
TomCatELAuthor Commented:
@Ray_Paseur: Sorry to have offend you . I am quite new to Expert Exchange and you did guide me on the right track maybe I was not being clear enough to what i wanted .

Is it possible that i can change the rating I will also read the grading guidelines .

Does a lower grade affect you in a negative way on EE  , it was not my intention at all I realy appreciate your help.

Thank you
Thomas
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Tackle projects and never again get stuck behind a technical roadblock.
Join Now