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

Question

up vote 1 down vote favorite

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.

asked Aug 6 '09 at 14:02

user151797
165

	Have you check to see how many times it is going through your for loop? – Justin Giboney Aug 6 '09 at 14:06
	try to echo $queryb and see if the comment is in there – Justin Giboney Aug 6 '09 at 14:16
	Hello again, Thanks for the quick replies, but it seems that the comments are still not adding correctly, they seem to be adding the whole comment in a single row instead of adding it on a new row for every line. More help is appreciated. Thanks in advance. – user151797 Aug 6 '09 at 18:19

palindrom · Answer 1 · 2009-08-06 14:09:08Z

up vote 1 down vote

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

answered Aug 6 '09 at 14:09

palindrom
27519

Palantir · Answer 2 · 2009-08-06 14:11:32Z

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';";

Steven Surowiec · Answer 3 · 2009-08-06 14:15:32Z

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.

andres descalzo · Answer 4 · 2009-08-07 15:05:32Z

up vote 0 down vote

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>';

edited Aug 7 '09 at 15:05

answered Aug 6 '09 at 14:19

andres descalzo
2,1671312

	Hello again, Thanks for the quick replies, but it seems that the comments are still not adding correctly, they seem to be adding the whole comment in a single row instead of adding it on a new row for every line. More help is appreciated. Thanks in advance. – user151797 Aug 7 '09 at 1:27
	mmm, are you loads the comment in the textarea?, see the palindrom's Answers, and write here the html code for a comment. – andres descalzo Aug 7 '09 at 3:15
	The comments are submitted, but are not inserted on a new row for each line, which is the problem. <form action="<?php $self ?>" method="post" onsubmit="return valid(this)"> <textarea name="list" rows="10" cols="70"></textarea> <textarea name="comment" rows="10" cols="70"></textarea> <input name="add" type="hidden" /> <input type="submit" value="Add" /> </form> Thanks. – user151797 Aug 7 '09 at 13:48
	the user loads everything into one line in the textarea, or separated by enter? that's the difference. see the example – andres descalzo Aug 7 '09 at 15:05
	No, the user inputs an enter for each row of comment he would like to insert into the database. – user151797 Aug 7 '09 at 16:55

show 2 more comments

user151797 · Answer 5 · 2009-08-09 13:06:15Z

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.

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

5 Answers

Your Answer

Not the answer you're looking for? Browse other questions tagged php sql mysql php5 table or ask your own question.

Hello World!

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

5 Answers

Your Answer

Not the answer you're looking for? Browse other questions tagged php sql mysql php5 table or ask your own question.

Hello World!

Related