2. PHPRunner General questions
  3. MySQL help
This topic is locked

MySQL help

 		9/13/2019 16:04:01
PHPRunner General questions
D
 DealerModules authorDevClub member

I need some mySQL direction if possible...
I am using the latest version and build of phprunner with a mysql database.
I have two tables:

Sales -

salesid(int), sales_date(date), sales_amount(int)
Forecast -

forecastid(int), forecast_date(date), forecast_amount(int)
I would like to enter my sales in daily at the end of the day and plot it against a second line in a chart that shows my forecast for the whole month.

My hope is to just put in my total forecast for the month along with the last day of the month. ( I really don't want to manually divide the total amount by 30 days and then do 30 entries into the forecast table)
ie. forecastid = 1, forecast_date = 09/30/2019, forecast_amount = 300000
My end result is if I can get the two data lines to plot on one chart.

The forecast line would start at zero and go in a diagonal line to the one ending number that I put in.
Thanks in advance for anyone who can share their knowledge on this post.
Paul
N
 		Nir Frumer 9/13/2019

i Paul

I would add a forcast column to the table sales
forcast table (id, year, month, monthly_forcast)
in after record added and after record updated events put the following code
if ($values['year']>0 and $values['month']>0 and $values['month']<13) {

$days=cal_days_in_month(CAL_GREGORIAN, $values['month'], $values['year']);

$daily_amount=$values['monthly_forcast'] / $days;
$strSql="update sales set forcast=" . $daily_amount. " where year(sales_date)=".$values['year']." and month(sales_date)=".$values['month'];

db_exec($strSql);

}
D
 		DealerModules authorDevClub member 9/15/2019

Thanks!

I will give this a try.

Paul
C
 		Corrie 9/17/2019

Good day.

I would add a period column to both tables.
Sales: salesid, period, sales_date, sales_amount
Forecast: forecastid, period, forecast_date, forecast_amount



The query:
SELECT forecast.period,

AVG(forecast.forecast_amount) AS forecast_amount,

AVG(sales.sales_amount) AS sales_amount

FROM forecast INNER JOIN sales ON forecast.period = sales.period

GROUP BY forecast.forecastid, forecast.period, forecast.forecast_date
It will output the following:
D
 		DealerModules authorDevClub member 9/18/2019

Hi Corrie,
Thanks for your direction. I will give it a try.

Also want to thank you for your phprunner videos on youtube.
Paul
C
 		Corrie 9/18/2019



Hi Corrie,
Thanks for your direction. I will give it a try.

Also want to thank you for your phprunner videos on youtube.
Paul


You are welcome.