[PHP] Inserting an array of lines into an SQL table

Question

Hello everyone, I would really appreciate if you would take a look at this piece of code:

<?php
if(isset($_POST['add'])) {

$self = $_SERVER['PHP_SELF']; //the $self variable equals this file
$ipaddress = ("$_SERVER[REMOTE_ADDR]"); //the $ipaddress var equals users IP

//connect
$connect = mysql_connect($host,$username,$password) or die('<p class="error">Unable to 
connect to the database server at this time.</p>');
 mysql_select_db($database,$connect) or die('<p class="error">Unable to connect to the 
database at this time.</p>');

//fetch data
$data = htmlspecialchars($_POST['list']);
$comment =  mysql_real_escape_string($_POST['comment']);

$data_lines = explode( "\r\n", $data );
$comment_lines = explode( "\r\n", $comment );
for($i=0;$i<count($data_lines);$i++)
{
$data_fields = explode( ",", $data_lines[$i]);

  $time = time();
  $queryb = "INSERT INTO coords SET tag='$data_fields[0]', guild='$data_fields[1]', name='$data_fields[2]', base='$data_fields[3]', econ='$data_fields[5]', maxecon='$data_fields[6]', location='$data_fields[4]', comment='$comment_lines[$i]', ipaddress='$ipaddress' ,date='$time';";

  // if it succeeds, display message
  if (@mysql_query($queryb))
  {
     echo('<p class="success">Successful posting of ['.$data_fields[3].']!</p>');
  }
  else
  {
     echo('<p class="error">Error could not post ['.$data_fields[3].'] to database!</p>');
}
}//end for loop
}//end if $_POST['add'] statement
?>

As you can see, it gets data received from a form's submission and explodes them into lines.

For $data, it explodes it again for each comma. It then inserts everything for each comma, in a new column and for each line, on a new row.

Now for $comment, it should be inserting it on a new row for each line, which isn't doing it. Been looking and testing at it for a few days now.

The comment column is a varchar of 100 of length.

So basically, it does everything i need it to do except inserting $comment on a new row for every line.

Any help is appreciated. Thank you in advance.

Answer 1

try replacing "\r\n" with "\n"

Answer 2

I am not entirely sure that I did understand your question exactly, but one potential problem in your string is interpolation. You should never put arrays like that without proper escaping. The correct string would look something like this:

$queryb = "INSERT INTO coords SET tag='{$data_fields[0]}', guild='{$data_fields[1]}', name='{$data_fields[2]}', base='{$data_fields[3]}', econ='{$data_fields[5]}', maxecon='{$data_fields[6]}', location='{$data_fields[4]}', comment='{$comment_lines[$i]}', ipaddress='$ipaddress' ,date='$time';";

Answer 3

As plaindrom mentioned you should try replacing "\r\n" with just "\n", as that will cover both cases where the form is submitted with "\r\n" and just "\n". I would say you may want to consider moving your escaping into the the loop, to make sure the escaping doesn't cause problems.

You will also want to add brackets around the variables inside the query string. Often times "some string $array[0]" will parse $array and not $array[0]. So you would want to switch your query to be:

$queryb = "INSERT INTO coords SET tag='{$data_fields[0]}', guild='{$data_fields[1]}', name='{$data_fields[2]}', base='{$data_fields[3]}', econ='{$data_fields[5]}', maxecon='{$data_fields[6]}', location='{$data_fields[4]}', comment='{$comment_lines[$i]}', ipaddress='$ipaddress' ,date='$time';";

You technically shouldn't need to do this around $ipaddress and $time but it may no be a bad idea, if only for consistency.

Answer 4

first:

print_r($data_lines ) ."<br>";
print_r($comment_lines) ."<br>";

and then for each insert:

$queryb = "INSERT INTO coords SET 
 tag='" . $data_fields[0] ."',  
 guild='" . $data_fields[1] ."', 
 name='". $data_fields[2] ."', 
 base='".$data_fields[3]."', 
 econ='".$data_fields[5]."', 
 maxecon='".$data_fields[6]."', 
 location='".$data_fields[4]."', 
 comment='".$comment_lines[$i]."', 
 ipaddress='".$ipaddress."' ,
 date='".$time."';";

echo $queryb  . "<br><br>";

---

look this is what comes from the client.

print '<pre>'; 
for ($ ind = 0, $ ind <strlen ($ _POST [ 'comment']); $ ind + +) ( 
   $ w = substr ($ _POST [ 'comment'], $ ind, 1); 
   print $ w. "." ord ($ _POST [ 'comment']). "<br>; 
) 
print '</ pre>';

Answer 5

Hello everyone, and thank you for investing your time in answering my question, but it seems that I have found a solution that works. Here it goes:

The mysql_real_escape_string() function here seems to be breaking the comments apart:

$comment =  mysql_real_escape_string($_POST['comment']);

So what I would need to do is add an extra backslash for every backslash. So here:

$comment_lines = explode( "\r\n", $comment );

Should be:

$comment_lines = explode( "\\r\\n", $comment );

Instead.

Anyway, I must thank you again for taking your time in reading my question and trying to resolve it.