Combine Multiple Rows Into One

Slowie · June 6, 2011

Hi Guys

I'm having trouble with this one. what i want to do is have a query which pulls multiple rows from the database.

i then want to have a textbox for notes to be taken based on the data presented.

after that i want the rows to be inserted into a seperate table.

currently it looks for a staff id which you select from a drop bown box. it then queries a table called "Services" for all data which has that staff id and presents the last 3 records.

it has one textarea dispalayed at the bottom of the data for notes called "comment". when the submit button is pressed i want it to take the data presented (which will always be 3 rows with 12 columns each) and the notes and insert them into a table called "review"

im just struggling with the mysql query which would combine the 3 rows into one and add the notes onto the same row

thank you in advance

WebStyles · June 6, 2011

please post the code you have so far.

Slowie · June 6, 2011

Here is the code i have so far

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

page_protect();

company();




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>

<link href="/styles.css" rel="stylesheet" type="text/css">
</head>

<body>



<form name="form" action="/MonthReview.php" method="post"> 
<table width="100%" border="0" cellspacing="0" cellpadding="5" class="main">
  <tr> 
    <td colspan="3"> </td>
  </tr>
  
  
   
   
    <td width="160" valign="top">
	<?php 
		if (isset($_SESSION['user_id'])) {
		}


	?>
		<a href="/Admin/admin.php">Admin CP </a>


</td>


  
    <td width="732" valign="top">

<p>
	<h3 class="titlehdr">New KPI</h3>  



<form name="form" action="/MonthReview.php" method="post"> 
  <table width="50px" border="0" align="Centre" cellpadding="2" cellspacing="0">
          <tr bgcolor="#000050">
            
          
		    <td width="100%"><h3 class="Text2">Staff Member</h3></td>





                   </tr>
         
          <?php 
		$sql="SELECT * FROM StaffList"; 
		$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>'; 

		 } 
		?>
          <tr> 


		<td> <SELECT NAME=full_name > <OPTION VALUE=0>Choose <?=$options?> </SELECT> </td>

                 
          </tr>
        
          <?php } ?>
  </table>
<input name="submit" type="submit" id="submit" value="Find">
</form> 





<?php 



if (isset($_POST['submit'])) {
?>
<form name="Insert" action="/Insert Review.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="90%" border="0" align="Centre" cellpadding="2" cellspacing="0">
          <tr bgcolor="#000050">
            
			<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">Clients This Month</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>





                   </tr>
         
          <?php 
		$sql="SELECT * 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><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 $row['ClientsThisMonth'];?>" /></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>	


                 
          </tr>
	  

          <?php }    ?>

  		  <tr> 
		<td colspan="11"><textarea rows="6" name="comment" cols="100"></textarea></td>	
	  </tr>
	  
	  <tr> 
		<td><input name="submit" type="submit" id="submit" value="Submit"></td>
	  </tr>

     </table>  
</form> 

<?php }  ?>

</table>
<?php

?>
</body>
</html>

WebStyles · June 6, 2011

(... ) it then queries a table called "Services" for all data which has that staff id and presents the last 3 records.

To get the last 3 records from Services all you need is something like this:

$sql="SELECT * FROM `Services` WHERE `Staffname`='$id' ORDER BY `ServiceDate` DESC limit 3" ;

instead of

$sql="SELECT * FROM Services WHERE Staffname='$id' AND ServiceDate='$lastmonth' OR 
										   Staffname='$id' AND ServiceDate='$lastmonth1' OR 
										   Staffname='$id' AND ServiceDate='$lastmonth2' ORDER BY ServiceDate" ;

From what I understand, all you want to do is add a comment on each record, 3 at a time, and instead of saving into same database, you want to save it all in a new table. correct?

Why would you create this duplication? Why not create a table called comments, just add the comments and the reference to witch record the comment belongs to?

Unless you're duplicating everything with the purpose of then deleting the first table, but in that case you could also just add the comment field to the first table an populate that.

Slowie · June 6, 2011

the reason for the duplication is because this part is for a month review. i also want to mark the time and date of the review (easy part) and i grab the last 3 months instead of 3 records for a fair review of the employee. i only want one comment per review so i want to copy the 3 months into a new table with one comment and the staff member will later be able to log in and see all of their previous reviews linked to their account

WebStyles · June 6, 2011

you can grab the 3 months with a simple BETWEEN statement

SELECT * FROM `table_name` WHERE `date` BETWEEN '$start_date' AND `$end_date`

Slowie · June 6, 2011

yeah never thought of that ill deffo change that. how would i go about inserting the data into the new table though. inserting the multiple rows into a new table isnt a problem but joining them all into one row is what stumps me

WebStyles · June 6, 2011

it really depends on your table structure. You can just create a table for the comments like I said before, and use a field to track whick month you're referring to, something like: id, month, year, comment. That way you don't need to store all the information, because you can always fetch it from the original database if needed, since you'll have the year, month and id to reference it.

Slowie · June 6, 2011

Yeah i get what your getting at and thinking on it that is a better way to do it. im now thinking of having the new table store the id of the services with the date and time and the comment.

WebStyles · June 6, 2011

well, since the comment (from what I understand) refers to a full month, you will also need to store what month/year is refers to, otherwise you'll just have a series of comments with a date and an id, that's ok for linking it back to the correct id, but not the correct month (image the comment is inserted in the first of july, but actually refers to the month of june.

Slowie · June 6, 2011

wouldnt this work better?

query pulls 3 different serviceIDs

89

91

96

these ids have other data attached to them but we will ignore that.

i then have a single box underneath for text called "comment"

on insert i want the new table to have

Comment ID (auto increment so not in query)

comment

date(date of the review)

serviceid 1

serviceid 2

serviceid 3

then the staff member can select which review he/she want to look at and it pulls the data referred to by the serviceids

how could i get the query to insert the 3 different serviceIDs into the same row?

WebStyles · June 6, 2011

you're still going to have the same problem. Imagine you add a comment for id 89 and store it nicely in a database (with or without other ids)...

then 3 months later you happen to add another comment for the same id (89). how will you know which comment refers to which months?

Trust me, it's no extra trouble for you to store the month/year and it might be very useful later.

Regarding your sql question about storing 3 servicerIds's, it depends. if you will always have exactly 3 ids, then you just create those fields in the database: `id1`,`id2`,`id3`.

if there are going to be situations when you have more and don't know how many they will be, just create a text field in the database called `ids` and dump them all in there separated by commas or something.

Slowie · June 6, 2011

yeah i can see where your comming from. luckily for me this record will not be edited once its inserted unless really needed as this is for someones review its just static text . thank you for all the input though i think i may have this cracked

Sign In

Combine Multiple Rows Into One

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information