Help with insert multiple lines from different textarea into database

[IreneLing]

Good morning to all.

 

Recently I just read a post here , it's about how to insert different lines from <textarea> to different mysql rows .

http://www.phpfreaks.com/forums/index.php?topic=325239.0

 

Thanks to AbraCadaver , his code works well.

 

$lines = array_map('mysql_real_escape_string', explode("\n", $_POST['textarea']));

$values = "VALUES ('" . implode("'), ('", $lines) . "')";

 

$query = "INSERT INTO table_name (column_name) $values";

 

But now if have more than one textarea , such as area1,area2 and area3 , how should change the code so that all data can be inserted into database simultaneously?

 

Such as data of area1 is for column 1, row 1,

data for area2 is for column2 , row 1,

and data of area3 is for column 3 ,row 1.

 

Is it possible to make this works?

 

Thanks for every reply .

 

 

[Psycho]

So, you don't need to separate the input by lines then? Then the solution is simple:

$field1value = mysql_real_escape_string($_POST['textarea1']);
$field2value = mysql_real_escape_string($_POST['textarea2']);
$field3value = mysql_real_escape_string($_POST['textarea3']);

$query = "UPDATE `table_name` (`field1`, `field2`, `field3`)
          VALUES ('$field1value', '$field2value', '$field3value')";

[IreneLing]

So, you don't need to separate the input by lines then? Then the solution is simple:

 

 

Thanks for your reply mjdamato . Yes I do need to separate the input by \n.

 

If I have 3 textarea like this ,

when I submit it ,I wish to have a result as shown in the picture .

Wonder is it possible....?

 

Thanks again.

 

[Psycho]

That's a bad idea, IMHO, to associate data between records using line-breaks. It would be a better idea to have the user upload a csv file.

 

But, if you really want to do that:

//Process the input textareas into arrays
$cells      = array_map('mysql_real_escape_string', explode("\n", $_POST['cells']));
$firstNames = array_map('mysql_real_escape_string', explode("\n", $_POST['firstnames']));
$lastNames  = array_map('mysql_real_escape_string', explode("\n", $_POST['lastnames']));

//Determine the values with the least amoutn of elements
$min_count = min($cells, $firstNames, $lastNames);

//Create array to hold INSERT values
$values = array();

//Create the INSERT values
for($index=0; $index<$min_count; $index++)
{
    $values[] = "('{$cells[$index]}', '{$firstNames[$index]}', '{$lastNames[$index]}')";   
}

//Create the INSERT query
$query = "INSERT INTO table_name (`Cell`, `FirstName`, `LastName`)
          VALUES " . implode(', ', $values);

[IreneLing]

Truly thanks for your code and advice , I will take it seriously .

One last question and hope you won't mind ....

 

If one of a user try to upload a csv file to my website , but the data input is always wrong .

Eg my table is (cell,firstname,lastname)....but his csv is (firstname,cell,lastname) or (firstname,lastname,cell) , is there any other way to solve this problem other than ask the user edit his csv file himself ?

 

There are many users not willing to edit themselves but asking for a better solution , and this really troubling me since I'm really new to php...

 

Thanks again.

[Psycho]

If you allow the user to import a file it is up to you to define the business requirements and functionality you will provide.

 

On one end you can be very rigid. For example, you assume that the user will be putting the data in a specific order. You then process each line doing a minimal validation that each line contains at least three pieces of data and insert that record accordingly. This approach typically makes for a quick implementation.

 

The other extreme is giving users the ability to define headers, change what columns go where etc. For example, when the user uploads the file you could (programmatically or through user input) determine if the file has headers or not. Then display the data for the file on the screen for the user to review. You could highlight records that have potential errors, allow the user to specify which columns are for which data fields, do manual edits, etc. Then once the user is done they could finally have the imported data processed.

 

Or you could do something in-between - it's up top you. But, if your immediate problem is ONLY that users are getting the column orders incorrect, then an easy solution is on the upload page provide three select fields: Column 1, Column 2, Column 3. For each field the user will select what field that column of data will be for (First Name, Last Name, Cell). Of course you would need to implement some validation so the user doesn't select the same field for two different columns.

[IreneLing]

I have no words to thank you enough for the advices mjdamato , your every word do make a good start to solve my problem....

and yes , I think provide select fields is a good solution .

 

Can I get a simple example for the select fields part? Just a very simple example will be enough , I will try to work the rest .

 

Thanks for your responces .

[Psycho]

Please specify which field each column in your upload file is for

Column 1: 
<select name="col1">
  <option value="Cell">Cell</option>
  <option value="FirstName">First Name</option>
  <option value="LastName">Last Name</option>
</select>
Column 2: 
<select name="col2">
  <option value="Cell">Cell</option>
  <option value="FirstName">First Name</option>
  <option value="LastName">Last Name</option>
</select>
Column 3: 
<select name="col3">
  <option value="Cell">Cell</option>
  <option value="FirstName">First Name</option>
  <option value="LastName">Last Name</option>
</select>

[IreneLing]

Thanks for your code , but I seem not really get the idea....if my table is already (Cell,Firstname,Lastname) , but the user choose (Firstname,cell,lastname) ....

is it mean I can actually control one whole column from csv file whether to insert into which table column?

[Psycho]

The layout of your table in the database is irrespective to the user input. The user can submit the import file with the three fields in any order. You then use the selections they made to determine which column goes where.

 

Example

//Get the user's selections for which columns are for what data
$column1 = $_POST['col1'];
$column2 = $_POST['col2'];
$column3 = $_POST['col3'];

//process the data for input into the database
//Assume datafile was converted into an array by lines
foreach($lines as $line)
{
    $data = explode(',' $line);
    //Data now consists of an array with three value in the
    //order that they exist in the user's data file
    //But we don't know if col 1 is the Cell, First name, or last name

   //Put values into array
   $values[] = "('{$data[0}', '{$data[1]}', '{$data[2]}')";  
}

//So we have the data in VALUEs for DB input but we still don't know which field is what

//Using the user input for the columns build the INPUT query to match the column to the appropriate field
$query = "INSERT INTO table_name (`{$column1}`, `{$column2}`, `{$column3}`)
          VALUES " . implode(', ', $values);

[IreneLing]

Thanks for all the codes and your patient mjdamato , I get what you mean now , so sorry for my slow understanding .

 

Allow me to thank you again , it's really nice to be here and I learned a lot from you today .

 

Have a nice day .