Inserting multiple records with "unknown" number of fields?

[ianh]

Hi everyone,

 

I have the task of building a PHP page where users can enter or remove as many records as they like into different categories. This then makes the number of records entered unknown, and therefore it becomes more complicated.

 

To make this clearer, I have attached an image of what I am trying to do.

 

Two questions:

- Before submitting the form, how can each record entry be matched with the correct category? (maybe hidden form fields somehow?)

- What is the best way of inserting each record (and it's associated category) into a MySQL database table from a single form submit?

 

I want to try and keep this as simple as possible as the data will later be updated, removed etc.

 

I'm experiencing a brain fart and just cannot picture the best way of going about this

 

Any help and advice is much appreciated!

 

Thanks in advance.

 

Basic table schema example...

CATEGORY TABLE
----------------------------
| catid |    category    |
----------------------------
|  1    |  Category A  |
|  2    |  Category B  |
etc.   etc. 
----------------------------

DATA ENTRY TABLE
----------------------------
dataid | catid |    data     |
--------------------------------
|  1  |   2    |  data xyz |
|  2  |   1    |  data zyx |
etc.  etc  etc.
--------------------------------

[dzelenika]

Let's suppose that every category1 textbox is called data1[] and category1 textbox is called data2[]

 

data receiving code should look like

 

foreach($_REQUEST["data1"] as $value)

{

//do some insert

 

}

 

foreach($_REQUEST["data2"] as $value)

{

// do some insert

 

}

[Psycho]

Just to clarify. The form may have multiple "categories" and each category can have multiple pieces of data. What isn't clear from your explanation is whether the categories will always be added as new or if they are being edited. In either case the solution is similar but will have to be handled differently. For the purposes of providing a solution I will assume that you are editing existing categories (not adding new ones)

 

The solution is to simply create the data fields as a mulch-dimensional array value with the first key associated with the primary key of the category. In your example above you have two categories (category A & Category B) with ids of 1 & 2. So, all the data fields for category A will look like this:

<input type="text" name="data[1][]" />

and the ones for category 2 will look like this

<input type="text" name="data[2][]" />

 

Then when you process the form you can access the arrays of the new/updated values. Since I am assuming you are editing existing records you would have to DELETE any records associated with the records being updated because you can't determine what records have changed vs. added ones. So the processing code might look something like this

//Create temp arrays
$deteleIDsAry = array();
$insertValues = array();

//Process POST data
foreach($_POST['data'] as $categoryID => $dataAry)
{
    //Add category ID to array
    $deteleIDsAry[] = intval($categoryID);

    foreach($dataAry as $dataValue)
    {
        $dataValue = mysql_real_escape_string($dataValue);
        $insertValues[] "('{$categoryID}', '{$dataValue}')";
    }

}

$deteleIDsStr = implode(',' $deteleIDsAry);

//Delete existing data records for the categories that were updated
$query = "DELETE FROM data_entry_table WHERE catid IN($deteleIDsStr)";
$result = mysql_query($query) or die(mysql_error());

//Create ONE Insert query to add all the records for the categories that were updated
$query = "INSERT INTO data_entry_table (catid, data) VALUES " . implode(', ', $insertValues);
$result = mysql_query($query) or die(mysql_error());

 

[ianh]

@dzelenika and @Psycho thanks for your replies, really appreciate it 

 

To clarify, the category names will always be added as new by the admin (me), not by the end user. Only records in the 'data entry' table can be added/edited/removed by the end user.

 

@Psycho, I see what you mean about deleting records is tricky. Maybe the records can be deleted on the fly directly from the database using AJAX? Or even saved into session variables for standard form post submit? What do you think? 

[monkeybidz]

Why not add to both tables the field "userid" to track what the user edited, deleted or posted. "userid" will be inserted to any changes to the tables and you can also later call any changes made by that user.

[Psycho]

@Psycho, I see what you mean about deleting records is tricky. Maybe the records can be deleted on the fly directly from the database using AJAX? Or even saved into session variables for standard form post submit? What do you think? 

 

Well, I think you are making it more difficult than it needs to be. But, it really depends on your requirements. But, my approach is that when a user edits the data for a category you would simply delete all existing records and add the ones submitted in the form.

 

So, let's say Category A currently has three values as follows:

1. Apple

2. Banana

3. Cherry

 

A user then selects to edit Category 1 and you populate three fields with the current values and allow the user to edit the current values and add news ones. So, lets say the user leaves the first field alone, removes the value from field #2, changes the value in field #3 and lastly adds a new filed with a new value. You are then left with:

1. Apple

2.

3. Grapes

4. Pear

 

So, when the user submits the page don't try and figure out what changed. Instead just delete everything currently associated with Category A and add all the values in the form.