insert different lines from <textarea> to different mysql rows

[Th3Boss]

Well I replied to an old thread but as it is old it doesnt seem to be getting any activity as it is marked answered.

 

The thread is: http://forums.phpfreaks.com/topic/228584-insert-different-lines-from-textarea-to-different-mysql-rows/

 

This doesnt work. I have tried it so many different ways.

 

I have a textarea that has data like

Info1

Info2

Info3

Info4

Info5

 

I have a table that has an auto increment ID and a Name column and I want each line from the textarea in its own row inserted in the Name column.

 

The only reply to the thread says to try:

 

 

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

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

Which didnt work. Ive tried a bunch of different things and cant get this to work.

 

Here is my last attempt of trying to get the above to work:

 

 

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

$query = "INSERT INTO Table (`ID`,`Name`) VALUES (NULL, '$values')";
 

[Jessica]

A. You need to normalize your data.

B. what debugging have you done? Echo the query, run it in MySQL/phpMyAdmin, etc?

 

But ignore B until after you do A.

 

 

Edit: That's assuming your structure looks like that. You said rows, but your code doesn't look like it's trying to do rows. Do you have many COLUMNS with those names, or you want many rows? (Hint: rows is right.)

Edited April 1, 2013 by Jessica

[Th3Boss]

A. You need to normalize your data.

B. what debugging have you done? Echo the query, run it in MySQL/phpMyAdmin, etc?

 

But ignore B until after you do A.

 

 

Edit: That's assuming your structure looks like that. You said rows, but your code doesn't look like it's trying to do rows. Do you have many COLUMNS with those names, or you want many rows? (Hint: rows is right.)

 

What do you mean normalize the data?

 

the structure I want is this

 

From Textarea:

Info1

Info2

Info3

Info4

Info5

 

Into Table:

ID | Name

1  | Info1

1  | Info2

1  | Info3

1  | Info4

1  | Info5

Edited April 1, 2013 by Th3Boss

[Jessica]

Okay that's fine. I misunderstood the problem

 

Your query looks right then. Move to step B.

[Th3Boss]

oh I just noticed I have that wrong.

 

The ID needs to be the same as the ID from another table that is auto increment, the structure is this.

It inserts the ID row # for Table 1 as auto increment and then inserts the ID from Table 1 into Table 2 with the data from the textarea.

 

 

From Textarea:

Info1

Info2

Info3

Info4

Info5

 

Table 1:

ID

1

2

3

4

 

 

Into Table2:

ID | Name

4  | Info1

4  | Info2

4  | Info3

4  | Info4

4  | Info5

Edited April 1, 2013 by Th3Boss

[Psycho]

Try something like this:

 

<?php

//Convert text input into lines based upon newline
$textLines = explode("\n", $_POST['TextArea']);
//Trim all the values
$textLines = array_map('trim', $textLines);
//Remove empty line
$textLines = array_filter('trim', $textLines);
//Escape input for query
$textLines = array_filter('mysql_real_escape_string', $textLines);

//Create parent record in table 1
$query = "INSERT INTO table1 (field1) VALUES ('value1')";
$result = mysql_query($query) or die(mysql_error());
$table1ID = mysql_insert_id();

//Process the lines into separate values for INSERT query
$insertValues = array();
foreach($textLines as $line)
{
    $insertValues[] = "('{$table1ID}', '{$line}')";
}

//Create INSERT query
$query = "INSERT INTO table2 (ID, Name) VALUES " . implode(', ', $insertValues);

?>

[Th3Boss]

 

Try something like this:

 

<?php

//Convert text input into lines based upon newline
$textLines = explode("\n", $_POST['TextArea']);
//Trim all the values
$textLines = array_map('trim', $textLines);
//Remove empty line
$textLines = array_filter('trim', $textLines);
//Escape input for query
$textLines = array_filter('mysql_real_escape_string', $textLines);

//Create parent record in table 1
$query = "INSERT INTO table1 (field1) VALUES ('value1')";
$result = mysql_query($query) or die(mysql_error());
$table1ID = mysql_insert_id();

//Process the lines into separate values for INSERT query
$insertValues = array();
foreach($textLines as $line)
{
    $insertValues[] = "('{$table1ID}', '{$line}')";
}

//Create INSERT query
$query = "INSERT INTO table2 (ID, Name) VALUES " . implode(', ', $insertValues);

?>

 

Warning: array_filter() expects parameter 1 to be array, string given in on line 60

 

$textLines = array_filter('trim', $textLines);
 

 

Warning: array_filter() expects parameter 1 to be array, string given on line 62

$textLines = array_filter('mysql_real_escape_string', $textLines);
 

 

Warning: Invalid argument supplied for foreach() on line 71

foreach($textLines as $line)
 

 

And should field1 be a new column for the parent record or should that be the auto increment ID from table 1?

 

 

Try something like this:

//Create parent record in table 1
$query = "INSERT INTO table1 (field1) VALUES ('value1')";
$result = mysql_query($query) or die(mysql_error());
$table1ID = mysql_insert_id();
 

 

Like:

//Create parent record in table 1
$query = "INSERT INTO table1 (ID) VALUES (NULL)";
$result = mysql_query($query) or die(mysql_error());
$table1ID = mysql_insert_id();
 

Edited April 2, 2013 by Th3Boss

[nbst]

Flip the parameter order of both array_filter() function calls.  That should fix all of the warnings.

 

E.x.

$textLines = array_filter('trim', $textLines); 

to

$textLines = array_filter($textLines, 'trim'); 

 

nbst

Edited April 2, 2013 by nbst

[Th3Boss]

Flip the parameter order of both array_filter() function calls.  That should fix all of the warnings.

 

E.x.

$textLines = array_filter('trim', $textLines); 

to

$textLines = array_filter($textLines, 'trim'); 

 

nbst

 

Yeah that fixed the errors.

echo $query looks like this:

INSERT INTO Table2 (`ID`,`Name`) VALUES ('50', 'Name1'), ('50', 'Name2'), ('50', 'Name3')

Though it doesnt insert it into the table. It does insert the 50 into Table1 though from.

$query = "INSERT INTO table1 (ID) VALUES (NULL)";

[nbst]

You have to execute it, like with the other query.

$result = mysql_query($query) or die(mysql_error());

Place that below the last line.

 

nbst

Edited April 2, 2013 by nbst

[Th3Boss]

Ok got this all working. Thanks a lot everyone who helped.

[Psycho]

Flip the parameter order of both array_filter() function calls.  That should fix all of the warnings.

 

Not quite. The first instance should be array_filter with only the array and no second parameter (which will remove empty values). The second instance should have been array_map()

 

//Remove empty line
$textLines = array_filter($textLines);
//Escape input for query
$textLines = array_map('mysql_real_escape_string', $textLines);

[nbst]

Not quite. The first instance should be array_filter with only the array and no second parameter (which will remove empty values). The second instance should have been array_map()

 

//Remove empty line
$textLines = array_filter($textLines);
//Escape input for query
$textLines = array_map('mysql_real_escape_string', $textLines);

What do you mean "not quite"?  It was your code, so how does it not being what it should have been make me wrong?  The parameter order was wrong even if the parameters weren't what they should have been.  I was aiming to fix your syntax errors and it looks like my advice did just that.

 

If I'm missing something, do let me know.

 

nbst

[Psycho]

What do you mean "not quite"?  It was your code, so how does it not being what it should have been make me wrong?  The parameter order was wrong even if the parameters weren't what they should have been.  I was aiming to fix your syntax errors and it looks like my advice did just that.

 

If I'm missing something, do let me know.

 

nbst

 

I mean that your corrections were not quite right. Yes, there were errors in the code I provided, but as I clearly state in my signature I don't always test the code I provide here. It is meant to be a framework for the person asking for help and to do the final implementation.

 

I provided this:

 

//Trim all the values
$textLines = array_map('trim', $textLines);
//Remove empty line
$textLines = array_filter('trim', $textLines);
//Escape input for query
$textLines = array_filter('mysql_real_escape_string', $textLines);

 

And you stated that the parameters for the array_filter() calls should be flipped. array_filter() removes items from an array based upon the "value". The line after the comment "//Remove empty line" is meant to remove empty values from the array. I incorrectly copied/pasted the array_map() line above and changed the function name but forgot to remove the parameter for the trim function. If the second, optional parameter is not provided then array_filter() will remove any values that are "interpreted" as false (such as an empty string). If you flip the parameters then that line will not do anything since trim() would not return false - which is required for array_filter() to remove values as it is intended.

 

So that line should be

 

//Remove empty lines
$textLines = array_filter($textLines);

 - which will remove empty lines/values from the array

 

For the last line, which the comment states is for the purpose of escaping values, I again did a copy/paste and changed the callback function to use, but did not change the function, which should be array_map(). Again, using array_filter() with 'mysql_real_escape_string' would have absolutely no affect since mysql_real_escape_string() would not return false for any of the input. Even worse, that line would not perform the intended function of escaping the values, leaving the script open to SQL Injection attacks. That line should use array_map() with 'mysql_real_escape_string' as the second parameter.

 

That last part should be

 

//Escape input for query
$textLines = array_map('mysql_real_escape_string', $textLines);

 - which will escape the values in the array and make then safe for use in a database query

 

Yes, I goofed on the code on those two lines, but I did provide clear comments on what the intent of those lines was. So, just making the comment to flip the parameters without understanding what that would do or not do was just as bad, if not worse.

Edited April 3, 2013 by Psycho