[SOLVED] Inserting multiple lines from a text area to SQL table

[_DarkLink_]

Hello, I am continuing on with my project and here is my problem:

 

I have text areas, the user inputs information in them and after submitting, I would like PHP to get the submitted data and insert them like so:

 

For each new line, insert the data as a new row and for each comma (','), insert them as a new column.

 

---

SQL structure

#
# Data for table `coords`
#

DROP TABLE IF EXISTS coords;
CREATE TABLE coords (
    id int(10) NOT NULL auto_increment,
    tag varchar(5) NOT NULL,
    guild varchar(27) NOT NULL,
    name varchar(45) NOT NULL,
    base varchar(16) NOT NULL,
    econ int(6) NOT NULL,
    maxecon int(6) NOT NULL,
    location varchar(12) NOT NULL,
    comment varchar(100) NOT NULL,
    ipaddress varchar(45) NOT NULL,
    date int(12) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE id (id),
    UNIQUE location (location),
    UNIQUE location_2 (location)
);

 

---

The structure would resemble this:

---

id

tag

guild

name

base

econ

maxecon

location

comment

ipaddress

date

1

tag1

guild1

name1

base1

econ1

maxecon1

location1

comment1

ipaddress1

date1

2

tag2

guild2

name2

base2

econ2

maxecon2

location2

comment2

ipaddress2

date2

---

 

---

Right now, I have tried working up a piece of code with some success.

The adding Form

database.php

 

<form action="<?php $self ?>" method="post">
<textarea name="list" rows="10" cols="50"></textarea>
</textarea>
<textarea name="comment" rows="10" cols="50">
</textarea>
<input name="add" type="hidden" />
<input type="submit" value="Add" />

 

---

Data processing code after form submission.

<?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(mysql_real_escape_string($_POST['list']));
		$comment =  htmlspecialchars(mysql_real_escape_string($_POST['comment']));

		$lines = explode( "\r\n", $data );
		$linesa = explode( "\r\n", $comment );
		foreach( $lines as $line )
		{
			$bycomma = preg_split('/,/', $line, -1, PREG_SPLIT_DELIM_CAPTURE);
                $line_words_by_comma=array();
                foreach($bycomma as $word) $line_words_by_comma[] = $word;

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

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


		}//end foreach loop


		}//end if $_POST['add'] statement
		?>

---

 

The code is adding the data correctly in the database, however, it is not adding like I wanted it to, it is not adding the data for a new line on a new row, it simply doesn't think it exists.

 

And for the comments part, it will add the whole text area inside the same column, which i don't want it to, but a new row for every line of comment.

 

Okay, so that's about it.

Any help is appreciated,

Thank you in advance.

[gevans]

See if this sorts it...

 

<?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($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 foreach loop
}//end if $_POST['add'] statement
?>

[_DarkLink_]

Hello again, and thanks for replying.

But it seems that there is still an error in it, maybe a syntax error or something?

 

The page is returning blank with no error codes,

hmmm, I'm still trying to see what's wrong.

 

Anyway, you missed a parenthesis here. =P

 

$data = htmlspecialchars$_POST['list']);

---

 

Thanks again for replying,

More help is appreciated.

[_DarkLink_]

Oh never mind, it was me, I seem to have added two 'if' statements.

 

Anyway, how are you counting $lines if it has no value?

[gevans]

sorry,

 

for($i=0;$i<count($lines);$i++)

 

 

should be

 

for($i=0;$i<count($data_lines);$i++)

 

[_DarkLink_]

Yes, Now there are a few more problems,

 

The data, when submitted, seems to add another line of void to the database.

And the comments are still not added for a new row for each line...

 

I wonder what is wrong.

---

Here is a quick refresh of my script for now.

<?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
?>

 

Thanks in advance,

Even more help is appreciated.