Getting started > Entering multiple dynamic results into new table

  • (2 Pages)
  • +
  • 1
  • 2

15 Replies - 3223 Views - Last Post: 15 September 2011 - 04:24 PM Rate Topic: -----

#1 bradley1983  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 39
  • Joined: 24-August 11

Getting started > Entering multiple dynamic results into new table

Posted 07 September 2011 - 10:58 AM

I have a query that is displaying potential items to add to a final invoice. The query returns multiple rows and I need to be able to allow the user to click a "complete" button and finalize this invoice. A couple of things that need to happen:

1) the user should have to click a checkbox next to each record and only those records that are checked would be included
2) each records should be stored in a new table (e.g. CompleteInvoice) and each would have a number associating it with this invoice creation (so each record would have an ID that matched the other records submitted at this same time - making it not unique)

I have the query to get the potential items working and it outputs the results on the front end using a basic while loop inside of the form...
<form>
while ($Row = mysql_fetch_array($result)) {
   // show results > id, name, description, date
   <input type="hidden" value="$_POST value here" name="field name here" />
}
<input type="submit" value="complete" name="Insert" />
</form>



The problem is that I don't really know where to begin with a multiple insert, I have this so far...
if (isset($_POST['Insert'])) {

$random = substr(number_format(time() * rand(),0,'',''),0,6);

$insertSQL = "INSERT INTO `TimeCompleteInvoice` (InvoiceID,CompleteTimeId,...) VALUES ('$random','$_POST[CompleteTimeId]',...)";
	mysql_query($insertSQL);
	header("Location: /invoicecomplete.php");
}



But this only inserts the first record in the while loop. Is there a foreach method or some other way to insert each record? I also haven't even begun to attempt the checkbox (part 1) of my question, but I'd imagine that can come after I figure this part out.

Thanks for taking a look! I can provide more detail if necessary.

This post has been edited by bradley1983: 07 September 2011 - 10:58 AM


Is This A Good Question/Topic? 0
  • +

Replies To: Getting started > Entering multiple dynamic results into new table

#2 Jstall  Icon User is offline

  • Lurker
  • member icon

Reputation: 434
  • View blog
  • Posts: 1,042
  • Joined: 08-March 09

Re: Getting started > Entering multiple dynamic results into new table

Posted 07 September 2011 - 11:17 AM

Hi,

Depending on how your back end is set up you could approach this in two ways.

First,you can specify that you want post data from form inputs as an array by adding "[]" to the name of the input. For example:
 <input type="hidden" value="$_POST value here" name="field_name[]" />
 <input type="hidden" value="another_value" name="field_name[]" />



This would result in an array with the two values accessed via $_POST['field_name']. Then you could just iterate through it building your query or using a prepared statement swapping out the wildcard.

If you need to do a totally different query for each value that is checked then you may just have to do a series of if statements checking for each type. Something like:
 if(isset($_POST['some_value']))
 {
   //do a query specific to this condition
 }

 if(isset($_POST['some_other_value']))
 {
   //do a query specific to this condition
 }



Hope this helps :)
Was This Post Helpful? 0
  • +
  • -

#3 E_Geek  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 45
  • View blog
  • Posts: 236
  • Joined: 20-February 11

Re: Getting started > Entering multiple dynamic results into new table

Posted 07 September 2011 - 11:21 AM

1. I have reason to believe this link could be helpful to you. :)
Was This Post Helpful? 0
  • +
  • -

#4 bradley1983  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 39
  • Joined: 24-August 11

Re: Getting started > Entering multiple dynamic results into new table

Posted 07 September 2011 - 11:58 AM

[quote name='Jstall' date='07 September 2011 - 11:17 AM' timestamp='1315419465' post='1429376']
 <input type="hidden" value="$_POST value here" name="field_name[]" />
 <input type="hidden" value="another_value" name="field_name[]" />



Thanks for the reply! Okay, so I added [] to my value as you suggested and I build the following,
if (isset($_POST['Insert'])) {
	$random = substr(number_format(time() * rand(),0,'',''),0,6);

	$row_data = array();
	foreach($_POST['CompleteTimeId'] as $post_key) {
		$ctimeid = $post_key['CompleteTimeId'];
		$ctimeuser = $post_key['CompleteTimeUser'];
		$ctimedate = $post_key['CompleteTimeDate'];
		$ctimeclient = $post_key['CompleteTimeClient'];
		$ctimejob = $post_key['CompleteTimeJob'];
		$ctimebillcat = $post_key['CompleteTimeBillingCat'];
		$ctimedesc = $post_key['CompleteTimeDescription'];
		$ctimehours = $post_key['CompleteTimeHours'];
		$ctimestatus = $post_key['CompleteTimeStatus'];
		$ctimelocked = $post_key['CompleteTimeLocked'];
		$ctimebilled = $post_key['CompleteTimeBilled'];
		// first part as before (setting up the variables)
		$row_data[] = "('$random','$ctimeid','$ctimeuser','$ctimedate','$ctimeclient','$ctimejob','$ctimebillcat','$ctimedesc','$ctimehours','$ctimestatus','$ctimelocked','$ctimebilled')";
	}
	
	// Insert final invoice into Complete invoice table
	$insertSQL = "INSERT INTO `TimeCompleteInvoice` (InvoiceID,CompleteTimeId,CompleteTimeUser,CompleteTimeDate,CompleteTimeClient,CompleteTimeJob,CompleteTimeBillingCat,CompleteTimeDescription,CompleteTimeHours,CompleteTimeStatus,CompleteTimeLocked,CompleteTimeBilled) VALUES '".implode(',', $row_data)."";
	mysql_query($insertSQL);
	header("Location: /dev/rm/timekeeper/invoicecomplete.php");
}



With the above, nothing is being inserted and with the test I'm doing, there are 12 records that should be added to the table. When I echo $post_key['CompleteTimeId'], I get twelve 1's > 111111111111
Was This Post Helpful? 0
  • +
  • -

#5 bradley1983  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 39
  • Joined: 24-August 11

Re: Getting started > Entering multiple dynamic results into new table

Posted 07 September 2011 - 02:11 PM

Okay, so this is where I'm at...
// the sql
$sql = "";
  foreach($_POST['CompleteValue'] as $key => $val){
	$ctimeid = $val['CompleteTimeId'];
	$ctimeuser = $val['CompleteTimeUser'];
	
        ...
	
        if($val != ""){
	     $sql .= "($val,'$key'),";
	}
    }
...



* the above gives me an error > Warning: Invalid argument supplied for foreach() > referring to the line with the foreach($_POST.... - it looks good to me, I don't understand the error.

// some of the form inputs to show how i have it setup
...
 <input type="hidden" name="CompleteValue[CompleteTimeId]" value="<?php echo $Row['PTimeId'];?>" />
 <input type="hidden" name="CompleteValue[CompleteTimeUser]" value="<?php echo $Row['PTimeUser'];?> />
...


This post has been edited by bradley1983: 07 September 2011 - 02:12 PM

Was This Post Helpful? 0
  • +
  • -

#6 JackOfAllTrades  Icon User is offline

  • Saucy!
  • member icon

Reputation: 6058
  • View blog
  • Posts: 23,495
  • Joined: 23-August 08

Re: Getting started > Entering multiple dynamic results into new table

Posted 07 September 2011 - 02:47 PM

The error means that $_POST['CompleteValue'] is either unset or just plain not an array over which you can iterate via foreach.

Try some basic debugging techniques, like var_dump($_POST); to see what you're getting in the $_POST array.

You're also leaving yourself wide open to SQL injection attacks. There is a topic right on the main page about this.

Consider using PDO, about which there are several excellent tutorials in our tutorials section.
Was This Post Helpful? 0
  • +
  • -

#7 bradley1983  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 39
  • Joined: 24-August 11

Re: Getting started > Entering multiple dynamic results into new table

Posted 07 September 2011 - 02:57 PM

When I do
var_dump($_POST);
, I get the proper output in array form
array(12) { ["CompleteTimeId"]=> array(5) { ["one"]=> string(6) "102683" [0]=...


And then I get the following error after that output:
Warning: Invalid argument supplied for foreach() in

... which is in reference to
foreach($_POST['CompleteValue'] as $key => $val){


I'll deal with the injection issue after I get this solved, thanks for the heads up on that. Any idea where to go from here? I thought everything seemed okay with this foreach and the fact the var_dump is outputting the proper info, what does that mean?
Was This Post Helpful? 0
  • +
  • -

#8 JackOfAllTrades  Icon User is offline

  • Saucy!
  • member icon

Reputation: 6058
  • View blog
  • Posts: 23,495
  • Joined: 23-August 08

Re: Getting started > Entering multiple dynamic results into new table

Posted 07 September 2011 - 03:11 PM

I don't see the 'CompleteValue' index in that POST array. I wrote this quick form:

<form id='blah' action='posttest.php' method='post'>
<input type='hidden' name='CompleteValue[ID]' value='1' />
<input type='hidden' name='CompleteValue[Name]' value='Joe' />
<input type='submit' name='submit' />
</form>


and var dump:

array(2) { ["CompleteValue"]=> array(2) { ["ID"]=> string(1) "1" ["Name"]=> string(3) "Joe" } ["submit"]=> string(12) "Submit Query" } 

Was This Post Helpful? 0
  • +
  • -

#9 bradley1983  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 39
  • Joined: 24-August 11

Re: Getting started > Entering multiple dynamic results into new table

Posted 07 September 2011 - 03:28 PM

I'm now able to insert 1 record, but I need to be able to insert multiple records. Not sure what's wrong b/c when I do a var_dump($_POST) I get the right output, but when I do var_dump($_POST['CompleteValue']) I only get the results for 1 record.

Current SQL:
if (isset($_POST['Insert'])) {
	
	$random = substr(number_format(time() * rand(),0,'',''),0,6);
	

    $sql = "";
	foreach($_POST['CompleteValue'] as $key => $val){
		if($val != ""){
			$sql .= "'$val',";
		}
    }
	
    $sql = substr($sql,0,-1);
	
	$insertSQL = "INSERT INTO `TimeCompleteInvoice` (InvoiceID,CompleteTimeId,CompleteTimeUser,CompleteTimeDate,CompleteTimeClient,CompleteTimeJob,CompleteTimeBillingCat,CompleteTimeDescription,CompleteTimeHours,CompleteTimeStatus,CompleteTimeLocked,CompleteTimeBilled) VALUES ('$random',$sql)";
	mysql_query($insertSQL);
	header("Location: http://mysite/complete.php");
}



The form:
...
<input type="hidden" name="CompleteValue[CompleteTimeId]" value="<?php echo $Row['PTimeId'];?>" />
<input type="hidden" name="CompleteValue[CompleteTimeUser]" value="<?php echo $Row['PTimeUser'];?>" />
...


Was This Post Helpful? 0
  • +
  • -

#10 bradley1983  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 39
  • Joined: 24-August 11

Re: Getting started > Entering multiple dynamic results into new table

Posted 12 September 2011 - 09:02 AM

I am still stuck on this problem. Can anyone think of a reason that only the last record is being inserted. Same when I do a var_dump($_POST), only the last records info is there. How can I get all records inside of my while loop to insert into the database when the user clicks "complete." Ultimately, I'd like to also add a checkbox so the records that were not checked, would not be submitted to the database, but I'm more concerned with entering multiple records at the moment.
Was This Post Helpful? 0
  • +
  • -

#11 Jstall  Icon User is offline

  • Lurker
  • member icon

Reputation: 434
  • View blog
  • Posts: 1,042
  • Joined: 08-March 09

Re: Getting started > Entering multiple dynamic results into new table

Posted 13 September 2011 - 06:46 AM

Hi,

Quote

Same when I do a var_dump($_POST), only the last records info is there

What do you mean by that? Is there only one key=>value pair for $_POST['CompleteValue'] ? If that is the case then perhaps there is something wrong with the values of your form inputs. Have you done something(inspect element with Firebug or even right-click view source) to ensure that your inputs have the values you expect them to?
Was This Post Helpful? 0
  • +
  • -

#12 bradley1983  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 39
  • Joined: 24-August 11

Re: Getting started > Entering multiple dynamic results into new table

Posted 13 September 2011 - 08:41 AM

Yes, there was only one key=>value. The problem was with my inputs, I came up with the following and it seems to work. Any ideas how to add a feature so users would have to check the checkbox next to each record and only insert those records that were checked?

if (isset($_POST['Insert'])) {
	
	$random = substr(number_format(time() * rand(),0,'',''),0,6);
	
	$sql = "";
	
	$completelistID = $_POST['CompleteValueId'];
	$completelistUser = $_POST['CompleteValueUser'];
	$completelistDate = $_POST['CompleteValueDate'];
	$completelistClient = $_POST['CompleteValueClient'];
	$completelistJob = $_POST['CompleteValueJob'];
	$completelistBillingCat = $_POST['CompleteValueBillingCat'];
	$completelistDescription = $_POST['CompleteValueDescription'];
	$completelistHours = $_POST['CompleteValueHours'];
	$completelistStatus = $_POST['CompleteValueStatus'];
	$completelistLocked = $_POST['CompleteValueLocked'];
	$completelistBilled = $_POST['CompleteValueBilled'];
	
	
	
	for ($i = 0; $i < count($completelistID); $i++) {
		
		$completeID = $completelistID[$i];
		$completeUser = $completelistUser[$i];
		$completeDate = $completelistDate[$i];
		$completeClient = $completelistClient[$i];
		$completeJob = $completelistJob[$i];
		$completeBillingCat = $completelistBillingCat[$i];
		$completeDescription = $completelistDescription[$i];
		$completeHours = $completelistHours[$i];
		$completeStatus = $completelistStatus[$i];
		$completeLocked = $completelistLocked[$i];
		$completeBilled = $completelistBilled[$i];
		
		
		$sql .= "('$random','$completeID','$completeUser','$completeDate','$completeClient','$completeJob','$completeBillingCat','$completeDescription','$completeHours','$completeStatus','$completeLocked','$completeBilled'),";

	}
	
	$sql = substr($sql,0,-1);
	
	$insertSQL = "INSERT INTO `TimeCompleteInvoice` (InvoiceID,CompleteTimeId,CompleteTimeUser,CompleteTimeDate,CompleteTimeClient,CompleteTimeJob,CompleteTimeBillingCat,CompleteTimeDescription,CompleteTimeHours,CompleteTimeStatus,CompleteTimeLocked,CompleteTimeBilled) VALUES $sql";
	mysql_query($insertSQL);
	header("Location: /complete.php");
	
}




... and the form (partially)...
<form method="post" action="">

while ... {

  ...
  <input type="hidden" name="CompleteValueUser[]" value="<?php echo $Row['TimeId'];?>" />
  <input type="hidden" name="CompleteValueDate[]" value="<?php echo $Row['TimeUser'];?>" />
  ...


}   

<input name="Insert" type="submit" value="Complete Invoice" />

</form>


Was This Post Helpful? 0
  • +
  • -

#13 bradley1983  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 39
  • Joined: 24-August 11

Re: Getting started > Entering multiple dynamic results into new table

Posted 13 September 2011 - 01:07 PM

Okay, getting close :), but still have one problem.

$completelistID = $_POST['CompleteValueId'];
	
	
	foreach($completelistID as $id) { 
        $onoff = 0;
        if (isset($_POST["CompleteValueChecked".$id])) {
            $onoff = 1;
        }
        if($onoff == 1) {
				
				$completeUser = $_POST['CompleteValueUser'.$id];
				$completeDate = $_POST['CompleteValueDate'.$id];
				$completeClient = $_POST['CompleteValueClient'.$id];
				$completeJob = $_POST['CompleteValueJob'.$id];
				$completeBillingCat = $_POST['CompleteValueBillingCat'.$id];
				$completeDescription = $_POST['CompleteValueDescription'.$id];
				$completeHours = $_POST['CompleteValueHours'.$id];
				$completeStatus = $_POST['CompleteValueStatus'.$id];
				$completeLocked = $_POST['CompleteValueLocked'.$id];
				$completeBilled = $_POST['CompleteValueBilled'.$id];
				
				$sql .= "('$random','$id','$completeUser','$completeDate','$completeClient','$completeJob','$completeBillingCat','$completeDescription','$completeHours','$completeStatus','$completeLocked','$completeBilled'),";
				
				echo $sql;
				echo '<br />';
			
        }
    } 



Let's say I check 3 checkboxes, the results look like:
123, SampleUsername, 8-2-11, description goes here, ...

123, SampleUsername, 8-2-11, description goes here, ..., 444, AnotherUsername, 8-3-11, another description goes here, ...

123, SampleUsername, 8-2-11, description goes here, ..., 444, AnotherUsername, 8-3-11, another description goes here, ... 777, AnotherUsername, 8-5-11, last description goes here, ...



I only want the last record, but adding
$count = count($completelistID); $num = 0;
before the foreach and $num++; after gives me the last record (777... in my above example rather than the whole string). Any ideas? I can provide more detail if nec.

Thanks!
Was This Post Helpful? 0
  • +
  • -

#14 bradley1983  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 39
  • Joined: 24-August 11

Re: Getting started > Entering multiple dynamic results into new table

Posted 15 September 2011 - 09:42 AM

Working solution...

INSERT..
if (isset($_POST['Insert'])) {
	
	$sql = "";
	
	$random = substr(number_format(time() * rand(),0,'',''),0,6);
	
	$completelistchecked = $_POST['CompleteValueChecked'];
	
	$i = 0;
	$numItems = count($completelistchecked);
	foreach ($completelistchecked as $id) { 
        
		$completeId = $_POST['CompleteValueId'.$id];
		$completeUser = $_POST['CompleteValueUser'.$id];
		$completeDate = $_POST['CompleteValueDate'.$id];
		$completeClient = $_POST['CompleteValueClient'.$id];
		$completeJob = $_POST['CompleteValueJob'.$id];
		$completeBillingCat = $_POST['CompleteValueBillingCat'.$id];
		$completeDescription = $_POST['CompleteValueDescription'.$id];
		$completeHours = $_POST['CompleteValueHours'.$id];
		$completeStatus = $_POST['CompleteValueStatus'.$id];
		$completeLocked = $_POST['CompleteValueLocked'.$id];
		$completeBilled = $_POST['CompleteValueBilled'.$id];	
				
		$sql .= "('$random','$completeId','$completeUser','$completeDate','$completeClient','$completeJob','$completeBillingCat','$completeDescription','$completeHours','$completeStatus','$completeLocked','$completeBilled'),";
		
		// update time sheet regular table bill status
		$updateSQL = "UPDATE `TimeSheet` SET `TimeBilled`='yes' WHERE `TimeId`='$completeId'";
		mysql_query($updateSQL);
		
		if($i+1 == $numItems) {
			$sql = substr($sql,0,-1);
			
			$insertSQL = "INSERT INTO `TimeCompleteInvoice` (InvoiceID,CompleteTimeId,CompleteTimeUser,CompleteTimeDate,CompleteTimeClient,CompleteTimeJob,CompleteTimeBillingCat,CompleteTimeDescription,CompleteTimeHours,CompleteTimeStatus,CompleteTimeLocked,CompleteTimeBilled) VALUES $sql";
			mysql_query($insertSQL);
			header("Location: /dev/rm/timekeeper/invoicecomplete.php?invoiceID=$random");
		}
		
		$i++;
    } 
}




FORM..
<input type='checkbox' name='CompleteValueChecked[]' value='".$Row['PTimeId']."' />
<input type="hidden" name="CompleteValueId<?php echo $Row['PTimeId'];?>" value="<?php echo $Row['PTimeId'];?>" />
            <input type="hidden" name="CompleteValueUser<?php echo $Row['PTimeId'];?>" value="<?php echo $Row['PTimeUser'];?>" />
            <input type="hidden" name="CompleteValueDate<?php echo $Row['PTimeId'];?>" value="<?php echo $Row['PTimeDate'];?>" />
            <input type="hidden" name="CompleteValueClient<?php echo $Row['PTimeId'];?>" value="<?php echo $Row['PTimeClient'];?>" />
            <input type="hidden" name="CompleteValueJob<?php echo $Row['PTimeId'];?>" value="<?php echo $Row['PTimeJob'];?>" />
            <input type="hidden" name="CompleteValueBillingCat<?php echo $Row['PTimeId'];?>" value="<?php echo $Row['PTimeBillingCat'];?>" />
            <input type="hidden" name="CompleteValueDescription<?php echo $Row['PTimeId'];?>" value="<?php echo $Row['PTimeDescription'];?>" />
            <input type="hidden" name="CompleteValueHours<?php echo $Row['PTimeId'];?>" value="<?php echo $Row['PTimeHours'];?>" />
            <input type="hidden" name="CompleteValueStatus<?php echo $Row['PTimeId'];?>" value="<?php if (empty($Row['PTimeStatus'])) { echo 'null';} else { echo $Row['PTimeStatus'];}?>" />
            <input type="hidden" name="CompleteValueLocked<?php echo $Row['PTimeId'];?>" value="<?php echo $Row['PTimeLocked'];?>" />
            <input type="hidden" name="CompleteValueBilled<?php echo $Row['PTimeId'];?>" value="<?php echo $Row['PTimeBilled'];?>" />


Was This Post Helpful? 0
  • +
  • -

#15 JackOfAllTrades  Icon User is offline

  • Saucy!
  • member icon

Reputation: 6058
  • View blog
  • Posts: 23,495
  • Joined: 23-August 08

Re: Getting started > Entering multiple dynamic results into new table

Posted 15 September 2011 - 10:00 AM

Glad you got it working to your satisfaction.

The next step for you is to secure yourself against SQL injection attacks, as you are wide open to them. Here's a tutorial on what it is and how to prevent it. However, the mysql extension you are using is deprecated; you should consider moving to a more up-to-date extension such as mysqli or PDO, the latter of which (at least) allows you to use prepared statements which will remove the threat of these injections.
Was This Post Helpful? 1
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2