Jump to content

mysql query

Slowie

By Slowie
in PHP Coding Help

Recommended Posts

Slowie Member

Slowie

Posted
Posted

i need help with this query. im sure its more simple than im thinking but my mind is over complicating it.

all i need to do is pull back 3 records on a form which i have done but i also need it to add two columns together and divide one by another my table layout is

 

        ServicesID                     |          int(11) |       

Staffname                     |          varchar(50) |

ServiceAmount                     |          varchar(50) |

ServiceDate                     |          date |

ForecastForNextMonth     |          int(11) |

ProductSales                     |          int(11) |

ClientsThisMonth             |          int(11) |

personnelRetension             |          double |

totalRetension                    |       double |

Colours                             |          smallint(6) |

Cuts                                     |          smallint(6) |

PreBooking                     |          double |

TimeUsed                             |          double |

productpercentage             |          int(11) |

CutsColourPercentage     |          int(11)              |

 

what i want displayed is all the columns plus i want it to add together all 3 of the colours then all 3 of the cuts then divide the colours total by the cuts total

 

so much thanx in advance

Link to comment
https://forums.phpfreaks.com/topic/239330-mysql-query/
Share on other sites
Slowie Member

Slowie

Posted
  • Author
Posted

my current code on the page is

 

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

page_protect();

company();










?>



            
<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/MonthReview.php" method="post"> 
<table width="100%" border="0" cellspacing="0" cellpadding="5" class="main">
  <tr> 
    <td colspan="12"> </td>
  </tr>
  
  
   
   
    <tr>
      <td width="732" colspan="11" valign="top">
        
        
        <h3 class="titlehdr">New KPI</h3>  
        
    <tr>
    
      <td valign="top"> </td>
      <td valign="top"> </td>
      <td valign="top"> </td>
      <td valign="top"> </td>
      <td valign="top"> </td>
      <td width="140" valign="top" class="mytables"><h3 class="Text2">Staff Member</h3></td>
      <td valign="top"> </td>
      <td valign="top"> </td>
      <td valign="top"> </td>
      <td valign="top"> </td>
      <td valign="top"> </td>       
    <tr>
    <?php 
		$sql="SELECT * FROM StaffList WHERE branch = '$_SESSION[branch]'"; 
		$result=mysql_query($sql); 
		$options=""; 

		while ($row=mysql_fetch_array($result)) { 

		$id=$row["id"]; 
		$full_name=$row["full_name"]; 
		$options.="<OPTION VALUE=\"$id\">".$full_name.'</option>'; 

		 } 
		?>
      <td colspan="11" valign="top"><SELECT NAME=full_name > <OPTION VALUE=0>Choose <?=$options?> </SELECT>
      <input name="submit" type="submit" id="submit" value="Find"></td>
    </table>

</form>



































<?php 
if (isset($_POST['submit'])) {
?>
<form name="Insert" action="/InsertReview.php" method="post"> 
<?php
//Assign each array to a variable
$id = $_POST['full_name'];
$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'));



?>
<table width="100%" border="0" align="Centre" cellpadding="2" cellspacing="0">
      <tr class="mytables">
            <td width="5%"> </td>
            
			<td width="5%"><h3 class="Text2">Service ID</h3></td>
			<td width="5%"><h3 class="Text2">Staff Member</h3></td>
		    <td width="5%"><h3 class="Text2">Service Amount</h3></td>
			<td width="10%"><h3 class="Text2">Service Date</h3></td>
			<td width="10%"><h3 class="Text2">Forecast For Next Month</h3></td>
			<td width="10%"><h3 class="Text2">Product Sales</h3></td>
			<td width="10%"><h3 class="Text2">Product %</h3></td>
			<td width="10%"><h3 class="Text2">personnel Retension</h3></td>
			<td width="10%"><h3 class="Text2">total Retension</h3></td>
			<td width="10%"><h3 class="Text2">Colours</h3></td>
			<td width="10%"><h3 class="Text2">Cuts</h3></td>
			<td width="10%"><h3 class="Text2">PreBooking</h3></td>
			<td width="10%"><h3 class="Text2">Time Used</h3></td>
			<td width="5%"> </td>





      </tr>
         
          <?php 
		$sql="SELECT *, ProductSales / ClientsThisMonth * 100 AS ProductPercent FROM Services WHERE Staffname='$id' AND ServiceDate='$lastmonth' OR 
										   Staffname='$id' AND ServiceDate='$lastmonth1' OR 
										   Staffname='$id' AND ServiceDate='$lastmonth2' ORDER BY ServiceDate" ; 


		$result=mysql_query($sql); 


		while ($row=mysql_fetch_array($result)) { 




		?>
          <tr>
            <td width="5%"> </td>
            <td><h3 class="Text3"><input type="" name="ServicesID[]" id="ServicesID[]" size="2" value="<?php echo $row['ServicesID'];?>" /></h3></td>
		<td><h3 class="Text3"><input type="" name="Staffname[]" id="Staffname[]" size="2" value="<?php echo $row['Staffname'];?>" /></h3></td>
		<td><h3 class="Text3"><input type="" name="ServiceAmount[]" id="ServiceAmount[]" size="2" value="<?php echo $row['ServiceAmount'];?>" /></h3></td>		 
		<td><h3 class="Text3"><input type="" name="ServiceDate[]" id="ServiceDate[]" size="10" value="<?php echo $row['ServiceDate'];?>" /></h3></td>	
		<td><h3 class="Text3"><input type="" name="forecast[]" id="forecast[]" size="4" value="<?php echo $row['ForecastForNextMonth'];?>" /></h3></td>
		<td><h3 class="Text3"><input type="" name="productsales[]" id="productsales[]" size="4" value="<?php echo $row['ProductSales'];?>" /></h3></td>	
		<td><h3 class="Text3"><input type="" name="Clientsthismonth[]" id="Clientsthismonth[]" size="4" value="<?php echo (int)$row['ProductPercent'];?>" /></h3></td>	
		<td><h3 class="Text3"><input type="" name="Personelret[]" id="Personelret[]" size="4" value="<?php echo $row['personnelRetension'];?>" /></h3></td>	
		<td><h3 class="Text3"><input type="" name="Totalret[]" id="Totalret[]" size="4" value="<?php echo $row['totalRetension'];?>" /></h3></td>	
		<td><h3 class="Text3"><input type="" name="colours[]" id="colours[]" size="4" value="<?php echo $row['Colours'];?>" /></h3></td>	
		<td><h3 class="Text3"><input type="" name="cuts[]" id="cuts[]" size="4" value="<?php echo $row['Cuts'];?>" /></h3></td>	
		<td><h3 class="Text3"><input type="" name="prebooking[]" id="prebooking[]" size="4" value="<?php echo $row['PreBooking'];?>" /></h3></td>	
		<td><h3 class="Text3"><input type="" name="timeused[]" id="timeused[]" size="4" value="<?php echo $row['TimeUsed'];?>" /></h3></td>
		<td> </td>	


                 
          </tr>
	  

          <?php }    ?>

  		  <tr>
  		    <td width="5%"> </td> 
		<td colspan="14"><textarea rows="6" name="comment" cols="100"></textarea></td>
           
	  </tr>
	  
	  <tr>
	    <td width="5%"> </td>
                <td colspan="14" valign="top"><input name="submit2" type="submit" id="submit2" value="Submit"></td>
      </tr>

  </table>  
</form> 

<?php }  ?>



</body>
</html>

 

basically i will run the script once for all the records in the database which will store the result in the colours cutst % field then everytime this page is brought up it will calculate the code and then it will be submitted upon the button being clicked

Link to comment
https://forums.phpfreaks.com/topic/239330-mysql-query/#findComment-1229491
Share on other sites

Archived

This topic is now archived and is closed to further replies.

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.