Page 1 of 1

Inserting data from indefinite number of fields into MySQL

Posted: Wed Mar 18, 2009 11:48 am
by imderek
For this app, users are able to create a "bundle" and add as many items as they'd like to the bundle.

Here's how it works:

The user enters the bundle title and description, and can then add items below. For each item, there is a row of six fields. See screenshot. Using jQuery, I've made it so that the user can dynamically add additional rows of fields (limited to 50). Each field's name auto-increments according to its row (e.g. item_name1 for row 1, item_name2 for row 2).

When the form is submitted, PHP inserts the bundle (just the title and description, not the individual items) into the Bundles table and retrieves the newly created bundle's ID. The individual items will be stored in the Bundle_Items table, and will be associated with their parent bundle by Bundle_ID.

Here's my table structure:
"Bundles" Table
ID, Title, Description

"Bundle_Items" Table
Bundle_ID, ID, City, State, Type, Etc.

The question is: how can I best insert each row of fields (bundle items) into their own row in the database (table: Bundle_Items), considering the number of rows will be different each time? I imagine a loop will be necessary when building the SQL query?

In other words, $_POST['item_name1'], $_POST['item_type1'], etc go into Bundle_Items as a single row.... and then $_POST['item_name2'], $_POST['item_type2'], etc go into Bundle_Items as another row.... until there are no more items to insert.

Re: Inserting data from indefinite number of fields into MySQL

Posted: Wed Mar 18, 2009 12:05 pm
by William
So you want to be able to insert an unlimited amount of items that are related to a parent "bundle"? Well to make things easier you might want to change the name of your field names from item_nameN, item_typeN to item['N']['name'], item['N']['type']. Then on your PHP page loop through the array and do all of your validation. Don't run a new MySQL insert for every single item, instead do something like...

Code: Select all

 
<?php
 
$bundle_id = 1;
$items = (isset($_POST['items'])) ? (array)$_POST['items'] : array();
$query = array();
 
// You also might want to do something like this to make sure they only add "50" items into the database.
while (count($items) > 50) $items = array_pop($items);
// Or you might want to just give an error so you won't have to go through the overhead of removing all the "extra" arrays.
if (count($items > 50)) { error-here }
 
foreach ($items as $item)
{
    // NOTE: You still need to validate the incoming data and set default values.
    // This includes adding slashes if needed, adding quotes, etc.
    $name = (isset($item['name'])) ? $item['name'] : 'null';
    $type = (isset($item['type'])) ? $item['type'] : 'null';
 
    $query[] = sprintf('(%d, %s, %s)', $bundle_id, $name, $type);
}
if (isset($query['0']))
    mysql_query(sprintf('INSERT INTO items (bundle_id, name, type) VALUES %s', implode(',',$query));
}
?>
 
That is completely untested I just wrote it right now. Remember to read the comments that is open to SQL injection and I'm not adding any quotes around the input if it's a string.

Re: Inserting data from indefinite number of fields into MySQL

Posted: Wed Mar 18, 2009 1:01 pm
by imderek
Well to make things easier you might want to change the name of your field names from item_nameN, item_typeN to item['N']['name'], item['N']['type'].
At risk of sounding completely stupid: how would I do that?

Re: Inserting data from indefinite number of fields into MySQL

Posted: Wed Mar 18, 2009 1:04 pm
by William
imderek wrote:
Well to make things easier you might want to change the name of your field names from item_nameN, item_typeN to item['N']['name'], item['N']['type'].
At risk of sounding completely stupid: how would I do that?

Code: Select all

 
<input type="text" name="item[INDEX][name]" value="blah" />
<select name="item[INDEX][type]">
    <option value="blah2">Blah #2</option>
</select>
 
Are examples. INDEX would be a unique number for each item.

Re: Inserting data from indefinite number of fields into MySQL

Posted: Wed Mar 18, 2009 1:13 pm
by imderek
William wrote:
imderek wrote:
Well to make things easier you might want to change the name of your field names from item_nameN, item_typeN to item['N']['name'], item['N']['type'].
At risk of sounding completely stupid: how would I do that?

Code: Select all

 
<input type="text" name="item[INDEX][name]" value="blah" />
<select name="item[INDEX][type]">
    <option value="blah2">Blah #2</option>
</select>
 
Are examples. INDEX would be a unique number for each item.
AH. Gotcha. Thanks, William.

Re: Inserting data from indefinite number of fields into MySQL

Posted: Wed Mar 18, 2009 1:14 pm
by William
No problem. :-)

Re: Inserting data from indefinite number of fields into MySQL

Posted: Wed Mar 18, 2009 1:18 pm
by php_east
imderek wrote: Here's my table structure:
"Bundles" Table
ID, Title, Description

"Bundle_Items" Table
Bundle_ID, ID, City, State, Type, Etc.

The question is: how can I best insert each row of fields (bundle items) into their own row in the database (table: Bundle_Items), considering the number of rows will be different each time? I imagine a loop will be necessary when building the SQL query?
i would suggest the follwing table structure
"Bundles" Table
Bundle_ID, Title, Description

"Bundle_Items" Table
Bundle_ID, Item_ID, City, State, Type, Etc.

or something similar.

Re: Inserting data from indefinite number of fields into MySQL

Posted: Wed Mar 18, 2009 2:17 pm
by temidayo
William wrote:Well to make things easier you might want to change the name of your field names from item_nameN, item_typeN to item['N']['name'], item['N']['type']
Will it not be better if the naming is like this:

item['name']['N']
item['type']['N']

or are they the same thing?

Re: Inserting data from indefinite number of fields into MySQL

Posted: Wed Mar 18, 2009 5:43 pm
by php_east
does not look like it really matter to me. like right hand left hand drive. is what in the db that matters, which is to be indexed.