Jump to content

Mysql php query help

Slowie

By Slowie
in PHP Coding Help

 Share

Recommended Posts

Slowie Member

Slowie

Posted
Posted

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

Link to comment
https://forums.phpfreaks.com/topic/239527-mysql-php-query-help/
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.