Mysql php query help

Slowie · June 16, 2011

Hey Guys

Im having trouble with the following code. as it stands the code works however i need to alter it to do some calculations.

the code

<?php 
$path = $_SERVER['DOCUMENT_ROOT'];
$path .= "/dbc.php";
include_once($path);


page_protect();
company();

	   
$stafflist = mysql_query("SELECT * FROM StaffList WHERE full_name != 'Adam Carter' AND full_name != 'Jakata' AND branch = '$_SESSION[branch]' ");

function firstOfMonth() {
return date("Y/m/d", strtotime(date('m') -1 .'/01/'.date('Y').' 00:00:00'));
}

$lastmonth =  date("Y/m/d", strtotime(date('m') .'/01/'.date('Y').' 00:00:00'. '- 1 month'));
$lastmonth1 =  date("Y/m/d", strtotime(date('m') .'/01/'.date('Y').' 00:00:00'. '- 2 month'));
$lastmonth2 =  date("Y/m/d", strtotime(date('m') .'/01/'.date('Y').' 00:00:00'. '- 3 month'));
$lastmonth3 =  date("Y/m/d", strtotime(date('m') .'/01/'.date('Y').' 00:00:00'. '- 4 month'));


if (isset($_POST['submit'])) {
//Assign each array to a variable
$id = $_POST['id'];
$user_name = $_POST['user_name'];
$Serviceamount = $_POST['Serviceamount'];
$servicedate = $_POST['servicedate'];
$forecast = $_POST['forecast'];
$productsales = $_POST['productsales'];
$Clientsthismonth = $_POST['Clientsthismonth'];
$Personelret = $_POST['Personelret'];
$Totalret = $_POST['Totalret'];
$colours = $_POST['colours'];
$cuts = $_POST['cuts'];
$prebooking = $_POST['prebooking'];
$timeused = $_POST['timeused'];


$limit = count($id);




$values = array(); // initialize an empty array to hold the values
for($k=0;$k<$limit;$k++){
$msg[] = "$limit New KPI's Added";



	$values[$k] = "( '{$id[$k]}', '{$Serviceamount[$k]}', '{$servicedate[$k]}', '{$forecast[$k]}', '{$productsales[$k]}', '{$Clientsthismonth[$k]}'
					, '{$Personelret[$k]}', '{$Totalret[$k]}', '{$colours[$k]}', '{$cuts[$k]}', '{$prebooking[$k]}', '{$timeused[$k]}', {$productsales[$k]} / {$Clientsthismonth[$k]} * 100  )"; // build the array of values for the query string
}
$query = "INSERT INTO `Services` (Staffname, ServiceAmount, ServiceDate, ForecastForNextMonth, 
								  ProductSales, ClientsThisMonth, personnelRetension, 
								  totalRetension, Colours, Cuts, PreBooking, TimeUsed, productpercentage) 
								  VALUES " . implode( ', ', $values ); // Form the query string and add the implod()ed values




$Event = "INSERT INTO Events (UserName, Event ) VALUES ('$_SESSION[user_name]', 'Entered New KPI' )";	
								  
								  

if (!mysql_query($query,$link)){
	die('Error: ' . mysql_error());
} else {
	mysql_query($Event);
	echo "<div class=\"msg\">" . $msg[0] . "</div>";
}




}



if (checkAdmin()) {





?>



            
<html>
<head>
<title>Book Off Holiday</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<script src="php_calendar/scripts.js" type="text/javascript"></script>

<?php
Header1();
?>
</head>

<body>



<form name="form" action="/Admin/Newkpi.php" method="post"> 
<table width="100%" border="0" cellspacing="0" cellpadding="5" class="main">
  <tr> 
    <td colspan="2"> </td>
  </tr>
  
  
   
   
    <tr>
      <td width="500" valign="top">
        
        <p>
        <h3 class="titlehdr">New KPI</h3>  
        
        
        
        
        <table width="100%" border="0" align="Centre" cellpadding="2" cellspacing="0">
          <tr class="mytables">
            <td width="160"> </td>
            
            
            <td width="20px"><h3 class="Text2">Staff ID</h3></td>
            <td width="20px"><h3 class="Text2">Staff Member</h3></td>
            <td width="20px"><h3 class="Text2">Service Amount</h3></td>
            <td width="20px"><h3 class="Text2">Service Date</h3></td>
            <td width="20px"><h3 class="Text2">Forecast For Next Month</h3></td>
            <td width="20px"><h3 class="Text2">Product Sales</h3></td>
            <td width="20px"><h3 class="Text2">Clients This Month</h3></td>
            <td width="20px"><h3 class="Text2">Personel Retension</h3></td>
            <td width="20px"><h3 class="Text2">Total Retension</h3></td>
            <td width="20px"><h3 class="Text2">Colours</h3></td>
            <td width="20px"><h3 class="Text2">Cuts</h3></td>
            <td width="20px"><h3 class="Text2">Pre-Booking</h3></td>
            <td width="20px"><h3 class="Text2">Time Used</h3></td>
            <td width="160"> </td>
            
            </tr>
          
          <?php while ($rrows = mysql_fetch_array($stafflist)) {?>
          <tr>
            <td width="160"> </td> 
            
            
            
            <td><h3 class="Text3"><input type="" name="id[]" id="id[]" size="4" value="<?php echo $rrows['id'];?>" /></h3></td>
            <td><h3 class="Text3"><input type="" name="user_name[]" id="user_name[]" value="<?php echo $rrows['full_name'];?>" /></h3></td>
            <td><h3 class="Text3"><input name="Serviceamount[]" type="text" size="8" id="Serviceamount[]"></h3></td>
            <td><h3 class="Text3"><input name="servicedate[]" type="text" value="<?php echo firstOfMonth();?>"size="11" id="servicedate[]"></h3></td>
            <td><h3 class="Text3"><input name="forecast[]" type="text" size="8" id="forecast[]"></h3></td>
            <td><h3 class="Text3"><input name="productsales[]" type="text" size="8" id="productsales[]"></h3></td>
            <td><h3 class="Text3"><input name="Clientsthismonth[]" type="text" size="4" id="Clientsthismonth[]"></h3></td>
            <td><h3 class="Text3"><input name="Personelret[]" type="text" size="4" id="Personelret[]"></h3></td>
            <td><h3 class="Text3"><input name="Totalret[]" type="text" size="4" id="Totalret[]"></h3></td>
            <td><h3 class="Text3"><input name="colours[]" type="text" size="4" id="colours[]"></h3></td>
            <td><h3 class="Text3"><input name="cuts[]" type="text" size="4" id="cuts[]"></h3></td>
            <td><h3 class="Text3"><input name="prebooking[]" type="text" size="4" id="prebooking[]"></h3></td>
            <td><h3 class="Text3"><input name="timeused[]" type="text" size="4" id="timeused[]"></h3></td>
            <td> </td>
            
          </tr>
          
          <?php } ?>
        </table>
  <input name="submit" type="submit" id="submit" value="Create">
        
        
        
  </table>
</form> 
</body>
</html>
<?php }
	?>

from here what i need it to do is on each insert using the array it needs to

1. look up the current users id in services

2. look at the date what has just been inserted and get the 3 PREVIOUS services ( so if its marked as the 1/6/2011 then it needs to pull 01/05/2011, 01/04/2011 and 01/03/2011)

3. it then needs to add the cuts values from that query together and add the colours values together then divide the cuts total by the colours total to get a ratio.

4. then finally store the result of that sum into a field called cutscolourpercentage.

now the way im looking at it is to do an if function inside the current if function but i just cant seem to get the code right

any help would be amazing please

Sign In

Mysql php query help

Recommended Posts

Slowie

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information