Mysql php array help

PHP
1

Hello, I have a database that im struggling with. Im ok with basic mysql which is what ive been using for a while now but im stuck on the following and any help would be greatly appreciated.

I have two tables

Table one (items)

ID | order number | description | price

01 | sd01 | blah blah | 22.22

and

table two (orders)

ID | customer id | order number | paid

01 | 3 | sd01 | Y

Basically im setting up a invoice system for myself to use. The items table will store all the invoice items in e.g. if i sold some paperclips i would put

ID | order number | description | price

01 | sd01 | paperclip | 1.22

When filling in the main invoice form all the data except the items+item details are stored in the orders table.

Now my problem is as follows;

since i never know how many line items i will have e.g. i could have 1 or 70 items in an invoice i need the form to dynamically add new line items when a button is clicked.

(like on Code Examples -> Building a Dynamic Form using Javascript and innerHTML. Add form elements in realtime without refreshing the page.)

now so far i think i will be able to do the above BUT i dont know how to take all this and submit it to the database. From what i can gather on

(PHP Form with array of fields)

im looking at using arrays which i havent used before (i think!)

any help would be appreciated.

2

You could just loop through the POST and dynamically build the query so that you do everything in one INSERT.

<?php
if(isset($_POST['submit'])) {
	
	// init the values var
	$values = '';
	
	// loop through each post
	// enclose it in single quotes and append it with a comma
	foreach($_POST['field'] as $i => $value){
		$values .= "'".$value."',";
	}
	
	// remove the last comma
	$values = rtrim($values,',');
	
	$query = "INSERT INTO table (field) VALUES ($values)";
	
	// check the query
	var_dump($query);
}
?>

<form method="post">
<input type="text" name="field[]" value="one"><br>
<input type="text" name="field[]" value="two"><br>
<input type="text" name="field[]" value="three"><br>
<input type="submit" name="submit" value="submit">
</form>
3

Slightly streamlined…


    foreach($_POST['field'] as $i => $value){ 
        $values .= "'".$value."',"; 
    } 
     
    // remove the last comma 
    $values = rtrim($values,',');

becomes


   $values = "'".implode("','",$_POST['field'])."'";

:wink:

PS: Both these solutions assume that that values are all strings. If some of them are numbers, you’ll need to do a little bit more work.

4

hello,

thanks for your help.

ok ill be honest that for now is a little over my head lol my php skills are still growing!

i have an alternative way of entering the details for the invoice system for now but once its all finished ill be back on here seeing if i can improve on it using your suggestions.

Thanks for your help :slight_smile:

5

Each field is a line item, so it has to be set up as an array, hence the brackets in the name (line_item). This will allow PHP to just loop through each POSTed line item and add it to the query during the foreach loop.

Let’s say your form has two line items, such as this

<input type="text" name="line_item[]" value="line item one">
<input type="text" name="line_item[]" value="line item two">

and then you submitted the form, your SQL query would look like this

"INSERT INTO my_table_name (line_items) VALUES ('line item one','line item two')"

You’re submitting all of the line items in ONE query, which is much more efficient.

Use print_r($_POST) to see what’s actually being submitted.

<?php
if(isset($_POST['submit'])) {
	echo '<pre>';
	print_r($_POST);
	echo '</pre>';
?>

Just play around with the example code. Change the names, press submit and see what happens. That’s the best way to learn. :slight_smile:

6

“Break it, then learn how to fix it so you dont break it again.”

incidentally, tgavin, that line wont quite work - if you want to send multiple rows, the INSERT…VALUES syntax is INSERT [INTO] table_name [(fieldlistCSV)] VALUES (valuelistCSV)[,(valuelistCSV)]*

or in practical terms.

"INSERT INTO my_table_name (line_items) VALUES ('line item one'),('line item two')"
7

You’re right. Thanks for the clarification. It’s one of those “I think I remember how to do this…” instances - but you haven’t done it for a while. :slight_smile:

Sorry for any confusion.