Splitting delimited input

[dawnstar]

Hi, I currently use a form with 3 fields to pass 3 text strings onto 3 fields in a mysql database.

Everything works ok, but I'm trying to enable a bulk upload sort of thing to save the time of doing them 1 row at a time.

I was wondering how to have one text area for input and put in the text delimited by pipe for example?

String 1 | String 2 | String 3 | String 1 | String 2 | String 3 | String 1 | String 2 | String 3 | String 1 | String 2 | String 3 | String 1 | String 2 | String 3 |

I currently use the following code for my original set up.

<?php
$dbcnx = mysql_connect('localhost','dbname','dbuser');
if (!$dbcnx){
exit('<p>unable to connect with database server</p>');
}
if (!@mysql_select_db('dbname')) {
exit('<p>unable to connect with database</p>');
}
$string1 = $_POST['string1'];
$string2 = $_POST['string2'];
$string3 = $_POST['string3'];

$sql = "INSERT INTO my_table SET
string1='$string1',
string2='$string2',
string3='$string3'";

if (@mysql_query($sql)) {
echo '<p>done</p>';
}
else
{
exit('<p>error ' . mysql_error() . '</p>');
}
?>

Would this be difficult to do as I'm learning this as I go!

Cheers for any help!

[CyBerAliEn]

The following should do about what you want:

<?php
$dbcnx = mysql_connect('localhost','dbname','dbuser'); 
if (!$dbcnx){ 
exit('<p>unable to connect with database server</p>'); 
} 
if (!@mysql_select_db('dbname')) { 
exit('<p>unable to connect with database</p>'); 
} 

//GET USER INPUT
$textarea = $_REQUEST['mytextarea'];

//SPLIT INPUT INTO RECORDS
$parts = explode(' | ',$textarea);
$records = array_chunk($parts,3);

//INSERT RECORDS TO DATABASE
if (count($records)>0)
{
foreach ($records as $record)
{
$string1 = mysql_real_escape_string($record[0]);
$string2 = mysql_real_escape_string($record[1]);
$string3 = mysql_real_escape_string($record[2]);
$sql = "INSERT INTO my_table SET string1='{$string1}',string2='{$string2}',string3='{$string3}'";
if (@mysql_query($sql)) { 
echo '<p>record added!</p>'; 
} 
else 
{ 
echo '<p>ERROR: Could not insert a record!</p>';
exit(); 
} 
}
}
?>

Notes:
The above does not contain any checks to ensure that the data is going to be "correct". For example, suppose someone entered:
str1 | str2 | str3
str1 | str2 | str3
str1 | str2

You're missing one element off the end. This code won't account for this. So if this matters to you, you'll need to build in some checks to check for such things.

You could also combine all the query strings together to do one massive query (could be more efficient).

Also, it is fine to tell mySQL to output the error while testing/debugging, but you will want to remove it and replace it with a simple "error occurred" type message for public/production levels. Why? A malicious user can use info from the error message to try and manipulate your database! Hiding such info with a vague error message helps to protect you.

Further note, the command to "explode" your results from a string to an array will require it be in this format:
STR1 | STR2 | STR3 | STR1 | STR2 | STR3 | STR1 | STR2 | STR3 | STR1 | STR2 | STR3 | STR1 | STR2 | STR3 | STR1 | STR2 | STR3 | STR1 | STR2 | STR3 | STR1 | STR2 | STR3
(etc)

It cannot be on different lines/etc. You could get around this by incorporating an additional explode (first by line, then by pipe) or perhaps by a regular expression.

[dawnstar]

Thanks mate!
I had a bit of trouble, but then realised that my data was as you noted above i.e.
str1 | str2 | str3
str1 | str2 | str3
str1 | str2 | str3

The last pipe's not there, so the first string in the next row was in with the last string in the one above.

Anyway you can show me how I could change the code to catch that out?

Either way thanks for the help so far! It's got me out of the mental cul-de-sac I was stuck in!

[dawnstar]

Just seemed to figure out something that seems to have done the trick! Added a preg_replace() before the explode() to change the new line tags itno pipes.

//SPLIT INPUT INTO RECORDS
$textarea = preg_replace("/\r\n/",'|', $textarea);

$parts = explode('|',$textarea);
$records = array_chunk($parts,3);

[Readie]

What i believe he actually meant was: you need to check to make sure that all of the inputs have been entered properly.

[CyBerAliEn]

He (dawnstar) is correct: my code expected strings in a specific format (all one single line separated by pipes). In his example (values separated by pipes, but each record separated by a new line), he would need to modify the code to account for this. He managed to figure this out. :)

But as Readie reminds... dawnstar does need to add some checks in to ensure data integrity. IE: make sure that all 3 strings exist (you could end up in a situation where 'string3' is missing, etc). This not a huge issue if it is the last string (as noted in my first post, buy image something like:
str1 | str2 | str3 | str1 | str2 | str1 | str2 | str3 | str1 | str2 | str3
(etc)

See the missing 'str3'? Well, your code will not notice/care. It will process and group them in pairs of 3 in a manner that you will not anticipate. If this matters, you need to consider putting checks in place to ensure it can't happen or that if it does, it is appropriately handled. This specific example (a string missing in the middle of the data) would be very difficult to account for given the nature of the input (FYI)!

You might also want to do other common checks on the values: Check the content type, length, values, sanitize, strip HTML, etc. Whatever you need, do it.

Why do all this 'extra'? It is good programming. Throwing something together now that isn't quite up to standard or is a quick/dirty fix will always result in issues, confusion, etc down the road. When you program something, you want [the goal] to do it once and do it right. :)

[dawnstar]

Cheers Guys! CyBerAliEn in particular!

Yeah, I'll start to put in the safeguards now, but I’m just happy to see it "work" if you know what I mean.

Now I got that going (with your help) the sky's the limit! ;-)