[squibby]

Hello, I have a problem i need fixing.

I have various forms which collect data and is stored in sessions until the user reaches the final page where all the information is input into 2 queries.

The data goes into 2 linked tables transaction_master and transaction detail.

I have no problem getting data into the first table, but i am having trouble with the transaction_detail table. As the number of rows that will be entered by the user is different each time.

So i set the names in the form for the transaction lines as arrays by adding[]. Now what i need to know is how to loop that information into the array. The code i have up to now is below:

<?php
include('header.php');
include('auth.php');

/* Transaction master table Variables*/
$paymenttype = $_POST['paymenttype'];
$invoiceissued = $_POST['invoiceissued'];
$invoicenumber = $_POST['invoicenumber'];

$trx = "null";
$logdate = $_SESSION['entrydate'];
$saledate = $_SESSION['saledate'];
$soldby = $_SESSION['soldby'];
$student = $_SESSION['student'];
$contractnumber =$_SESSION['contractnumber'];
$notes = $_SESSION['notes'];
$splitwith = $_SESSION['splitwith'];

/* Transaction detail Variables*/

$serviceid = $_SESSION['service'];
$details = $_SESSION['details'];
$start = $_SESSION['startdate'];
$end = $_SESSION['enddate'];
$updatedend = $_SESSION['enddate'];
$hoursquantity = $_SESSION['quantity'];
$price = $_SESSION['price'];

$hoursused = "0";

include ('connect.php');
$tmquery = "INSERT INTO `transaction_master`(`TM_TRX`, `TM_Log_Date`, `TM_Actual_Sale_Date`, `TM_Sales_Rep_ID`, `TM_Customer_ID`, `TM_Contract_Number`, `TM_Payment_Type`, `TM_Invoice_Issued`, `TM_Invoice_Number`, `TM_Notes`, `TM_Splitwith`) VALUES ('$trx','$logdate','$saledate','$soldby','$student','$contractnumber','$paymenttype','$invoiceissued','$invoicenumber','$notes','$splitwith')";
	
mysql_query($tmquery)or die (' TM Error updating database');
echo "TM Data gone in";
 
$transmasterid = mysql_insert_id();

 // i need to now loop the arrays in to the following query line by line
  
 $tdquery = "INSERT INTO `transaction_detail`(`TD_ID`, `TD_Service ID`, `TD_Details`, `TD_Start`, `TD_End`, `TD_Updated_End`, `TD_Hours/Quantity`, `TD_Price/hr`, `TD_Hours_Used`)VALUES ('$transmasterid','$serviceid','$details','$start','$end','$updatedend','$hoursquantity','$price','$hoursused')";
 
mysql_query($tdquery)or die (' TD line  Error updating database');
echo "TD Data gone";
 
?>

[Dormilich]

squibby, on 04 February 2012 - 08:49 AM, said:

So i set the names in the form for the transaction lines as arrays by adding []. Now what i need to know is how to loop that information into the array.

I’d try a standard for() loop on the length of the arrays (though you need to make sure all arrays have the same length and the same indices.

Note:
- you’re susceptible (or rather, wide open) to SQL Injection
- Prepared Statements are secure and (in this case) probably faster

[squibby]

I tried various things and got this at the moment:

<?php
include('header.php');
include('auth.php');

/* Transaction master table Variables*/
$paymenttype = $_POST['paymenttype'];
$invoiceissued = $_POST['invoiceissued'];
$invoicenumber = $_POST['invoicenumber'];

$trx = "null";
$logdate = $_SESSION['entrydate'];
$saledate = $_SESSION['saledate'];
$soldby = $_SESSION['soldby'];
$student = $_SESSION['student'];
$contractnumber =$_SESSION['contractnumber'];
$notes = $_SESSION['notes'];
$splitwith = $_SESSION['splitwith'];

/* Transaction detail Variables*/

$serviceid = $_SESSION['service'];
$details = $_SESSION['details'];
$start = $_SESSION['startdate'];
$end = $_SESSION['enddate'];
$updatedend = $_SESSION['enddate'];
$hoursquantity = $_SESSION['quantity'];
$price = $_SESSION['price'];
$hoursused = "0";

include ('connect.php');
$tmquery = "INSERT INTO `transaction_master`(`TM_TRX`, `TM_Log_Date`, `TM_Actual_Sale_Date`, `TM_Sales_Rep_ID`, `TM_Customer_ID`, `TM_Contract_Number`, `TM_Payment_Type`, `TM_Invoice_Issued`, `TM_Invoice_Number`, `TM_Notes`, `TM_Splitwith`) VALUES ('$trx','$logdate','$saledate','$soldby','$student','$contractnumber','$paymenttype','$invoiceissued','$invoicenumber','$notes','$splitwith')";
	
mysql_query($tmquery)or die (' TM Error updating database');
	echo "TM Data gone in";


$transmasterid = mysql_insert_id();

$number = count ($serviceid);//count the array see how many lines i have
echo $number; // when i echo out this it is always 3 but should vary

for($i=0;$i <=$number; $i++){        
$ser = $serviceid[$i];
$det = $details[$i];
$sta = $start[$i];
$en = $end[$i];
$upend = $updatedend[$i];
$quant = $hoursquantity[$i];
$pri = $price[$i];

$tdquery = "INSERT INTO `transaction_detail`(`TD_ID`, `TD_Service_ID`, `TD_Details`, `TD_Start`, `TD_End`, `TD_Updated_End`, `TD_Hours/Quantity`, `TD_Price/hr`, `TD_Hours_Used`) VALUES ('$transmasterid','$ser','$det','$sta','$en','$upend','$quant','$pri','0')";


 mysql_query($tdquery)or die (' TD line  Error updating database');
} 
 
echo "TD Data gone";
 

 ?>

The problem i have seems to be related to the counting of the array to see how many lines are there. it is always 3. I know the query works as i tested in phpmyadmin, and also if i replace this line:

for($i=0;$i <=0 $i++){

and just put a 0 it happily goes through and puts the first line in. Im probably doing everything wrong, but im not sure What to with this?

[Dormilich]

basic course in loop constructs:

$l = count($array);
for ($i = 0; $i < $l; ++$i)
{
    // loop statements using array[$i]
}

[squibby]

$number = count ($serviceid);
for($i=0;$i <=$number; $i++)     

so whats wrong with this in my code - my code is the same as your example on line 42.

when i count the array is always 3. even when i only input 1 or 2 line in my form. i.e if
i dont input data in lines 2 and 3 in my form it still carries blank lines and counts them??



This is the form that collects the data. i have three blocks like this on my page.

<select name="service[]"class="transservice" >
	<option value="default"></option>
	<?php
	 include 'connect.php';
	$result = mysql_query('SELECT SER_ID,SER_NAME FROM services ORDER BY SER_NAME') or die (mysql_error());
		while ($row = mysql_fetch_array($result)) {
		       echo "<option value=\"".$row['SER_ID']."\">".$row['SER_NAME']."</option>";
			}
	?> 
		
</td>
  <td> <input type="text" id="transstartdateL1" name ="startdate[]" Class="transdate">&nbsp;&nbsp;<img src="icons/calendar.png"/>  </td>
  <td> <input type="text" id="transenddateL1" name ="enddate[]" Class="transdate">&nbsp;&nbsp;<img src="icons/calendar.png"/> </td>
  <td><INPUT TYPE="TEXT" NAME="details[]" CLASS="transdetails" >&nbsp;&nbsp; </td>
  <td><INPUT TYPE="TEXT" NAME="quantity[]" CLASS="transquantity" x>&nbsp;&nbsp; </td>
  <td><INPUT TYPE="TEXT" NAME="price[]" CLASS="transprice" > </td>
  <td><INPUT TYPE="TEXT" NAME="total[]" CLASS="transprice"></td>
</tr>

thanks for you help

[Dormilich]

squibby, on 06 February 2012 - 11:36 AM, said:

$number = count ($serviceid);
for($i=0;$i <=$number; $i++)     

so whats wrong with this in my code - my code is the same as your example on line 42.

nope, there is a subtle little difference, which can make all the difference in the world script.

just echo out the indices $i in each of the loops and you will see.

[squibby]

ok i seen it. It is this $i++ is the wrong way around. Thanks for giving me the hints i got the data entered into the databsase using this method however i have one more issue.if you have another clue for me to follow that would be wonderful!

$l = count($serviceid);
echo $l; // i echo this out to check how many in array the answer is always 3, even when i only enter 1 or 2 rows in the form!!

for ($i = 0; $i < $l; ++$i)
{
$ser = $serviceid[$i];
$det = $details[$i];
$sta = $start[$i];
$en = $end[$i];
$upend = $updatedend[$i];
$quant = $hoursquantity[$i];
$pri = $price[$i];

$tdquery = "INSERT INTO `transaction_detail`(`TD_ID`, `TD_Service_ID`, `TD_Details`, `TD_Start`, `TD_End`, `TD_Updated_End`, `TD_Hours/Quantity`, `TD_Price/hr`, `TD_Hours_Used`) VALUES ('$transmasterid','$ser','$det','$sta','$en','$upend','$quant','$pri','0')";


mysql_query($tdquery)or die ("TD line  Error updating database"); 
    }


		echo "TD Data gone";

It will loop through 3 times as the count of array is always 3. so any information in the array goes into the database but then it will show the error message 'TD line Error updating database' if there are less than 3 lines in the array. If there are 3 lines, then no problem no error.

I can fix this by just taking the or die(); away from the end of the query. But i shouldnt have to do that. Surely when the array is counted it should only count the number of items in it. And not empty rows. no?

because the error message sometimes was showing up i thought it wasnt working but when i looked in phpmyadmin they had gone into the database which surprised me. So how to make it count only rows that are there???

[Dormilich]

squibby, on 06 February 2012 - 12:52 PM, said:

ok i seen it. It is this $i++ is the wrong way around.

nope, that's just a minor detail. it's the $i <= $l part that was wrong (the highest array index on 0-based arrays is always 1 less than the length).

squibby, on 06 February 2012 - 12:52 PM, said:

It will loop through 3 times as the count of array is always 3. [...] Surely when the array is counted it should only count the number of items in it. And not empty rows. no?

count() indeed counts the number of items in the array. the problem is in your logic (that is, rather the assumptions you make on the array).

the array results from your form data being passed. and not filling a text field does not mean it doesn't have a value to submit (in that case it's an empty string). hence if you have 3 text fields, there will be always 3 elements in the posted array.

you can easily filter off empty values from the array with array_filter().

This post has been edited by Dormilich: 06 February 2012 - 05:28 AM

[squibby]

Hi thanks, yes i saw the '=' and took that out too. i also understand the counting logic you explained now.

i have fixed this and it all works. however i could not get the array_filter() to work. It should have stripped out any "" blank or null values but didnt seem to work.

After print_r ing the array it gave a result similar to this:

[0]=> 20 [1]=> [2]=> default so i sought a different method as can be seen in the following code if anyone else wanted to see.

I'm not if this is correct practise but it works fine for me.

foreach($serviceid as $key => $value) {
  if($value == "default") {
    unset($serviceid[$key]);
  }
}
$new_array = array_values($serviceid); 

Thank you 谢谢 Dormilich i figured it out in the end.

[Dormilich]

Quote

however i could not get the array_filter() to work.

that depends on what you tried.