Calculations in a query

[elite311]

Everyone here has been really helpful and I appreciate everyone's time, I have learned quite a bit over the past few days reading the MySQL site, looking at examples and of course from peoples help here.

 

However I think I am over my head with this and its the last piece of the puzzle to what I am trying to create. I have a query showing all the results the way I want them but I am trying to have the query make a calculation and I'm not even sure if its possible to do solely with MySQL.

 

I have 3 tables: assets, assethours, pm

 

assets table has a field called pmcount, which is and integer (1-24)

assethours table has a a field called hoursasset which is the asset number, hoursdate which is the date I entered the hours  and currhours which is the current number of hours

pm has a field called pmasset which is the asset number, pminterval which is the amount of hours the service is due at

 

Basically what I'm trying to do with this is, take the last hours of the asset and add the value of pmcount for everyday and then show me the date the unit will hit the pminterval I want to do it this way since the piece of equipment isn't used 24hrs a day but only so many hours a day.

 

My sql looks like this right now:

$assetresult = $db->fetch_all_array("SELECT asset, category, descrip, currentjob, pmcount,
								MAX(assethours.hourscurr) AS pmhours
								SUM(pmhours*pmcount) AS nextpm
								FROM assethours
								INNER JOIN assets 
								ON assets.asset = assethours.hoursasset
								WHERE currentjob  = '".$_GET['id']."'
								GROUP BY assets.asset");

 

This is getting me the last hours and displaying the asset info just fine, but I have been trying to add multiplication and other things to the query and every time I do the query fails. Would it be better to use PHP for all the calculations and just pull the values from the database? or can this be done with just MySQL I was hoping it could be but I have been reading about different functions and I'm not sure if it can. Before I keep trying to make it work with MySQL solely I was hoping someone could tell me if it's even possible.

 

Thanks

 

[elite311]

I was able to figure this out and thought I would post my code for anyone else trying to do something similar as a reference. I was not able to figure out how to do it with MySQL solely so I created a PHP/MySQL function.

 

function get_date($asset,$nb){
    $db = new Database($db_host, $db_username, $db_password, $db_database, $db_table_prefix);
$db -> connect();
$sql="SELECT * FROM assets a,assethours ah,pm p WHERE a.asset = ah.hoursasset and a.asset=p.pmasset 
      AND a.asset='".$asset."' ";
$result = $db->dbQuery($sql);
while ($row = $db->dbFetchAssoc($result)) {
	extract($row);
	$i=1;
	$c=1;
	$hourscurr = ($hourscurr>$pminterval)?($hourscurr%$pminterval): $hourscurr ;
	while(($hourscurr+($pmcount*$i))<$pminterval){
		$i++ ;
	}
	$dateSec=strtotime($hoursdate) + $i*24*3600 ;
	if($nb==1) return date('d - m - Y ',$dateSec);
	$nextdate =$dateSec + ceil($pminterval/$pmcount)*24*3600 ;
	if($nb==2) return date('d - m - Y ',$nextdate);
}