Jump to content

mysql query

Slowie

By Slowie
in PHP Coding Help

 Share

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
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.