BadGoat Posted May 27, 2008 Share Posted May 27, 2008 Hello! Looking for pointers in how to enter comma separated lines of text into a db using a text field? The ideal would be to have a text box which I can paste large lumps of data into which would be inserted into a db line by line.. I don't know which concept I should be learning to do such a thing. Any guidance is much appreciated! (example of data which could be pasted into a text field for db insertion) 200005, Vegetable, Corn 200006, Vegetable, Peas 300001, Fruit, Apple 300002, Fruit, Banana 400004, Meat, Mutton 400005, Meat, Turkey Quote Link to comment Share on other sites More sharing options...
jonsjava Posted May 27, 2008 Share Posted May 27, 2008 here's how I did it in one of my scripts: $input = $_POST['contacts']; $array = explode("\n", $input); foreach ($array as $value){ //input each line into your db } this is a slimmed down version of my code, but you get the idea, and if you don't, just ask! Quote Link to comment Share on other sites More sharing options...
jonsjava Posted May 27, 2008 Share Posted May 27, 2008 modified version: <?php $input = $_POST['contacts']; //your post data here. $array = explode("\n", $input); foreach ($array as $value){ if ($value != null && $value != "" && $value != " " && $value != "\n" && $value != "\t" && $value != "\r"){ $array2 = explode(",", $value); $item1 = mysql_real_escape_string($array2[0]); $item2 = mysql_real_escape_string($array2[1]); $item3 = mysql_real_escape_string($array2[2]); //and so on, until you have each comma dillemidated (I can't spell) item in it's own variable $sql = "INSERT INTO `table_name`('$item1', '$item2', '$item3');"; mysql_query($sql) or die("Query failed: ". mysql_error()); } } ?> Quote Link to comment Share on other sites More sharing options...
BadGoat Posted May 27, 2008 Author Share Posted May 27, 2008 Thank you for the replies! I've been reading on the PHP site about EXPLODE and I see (in theory) how to use it.. And I see in your example code how it is done, so I think that I can play around with it. One question that comes to mind early on though.. As the field that I paste the data into is a textarea field, would I need to do something like $text = nl2br($text); to preserve the line breaks between each row of data I am entering? Quote Link to comment Share on other sites More sharing options...
jonsjava Posted May 27, 2008 Share Posted May 27, 2008 it's taking every new line as a new bit of data to be put into the db, so nl2br won't do any good in that sense. Quote Link to comment Share on other sites More sharing options...
BadGoat Posted May 28, 2008 Author Share Posted May 28, 2008 ok, I started small, to make sure I had it figured out before expanding it, and I am getting an error.. On the page where I can paste in the data to the textfield I type 1,1 and submit it. The error I get: Query failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''1', '1')' at line 1 Actual query: INSERT INTO `etest`('1', '1') Here's the code from the second page: <?PHP include ("../config.php"); echo' <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <head> <title></title> </head> <body>'; $input = $_POST['t_data']; //your post data here. $array = explode("\n", $input); foreach ($array as $value){ if ($value != null && $value != "" && $value != " " && $value != "\n" && $value != "\t" && $value != "\r"){ $array2 = explode(",", $value); $cola = mysql_real_escape_string($array2[0]); $colb = mysql_real_escape_string($array2[1]); $sql = "INSERT INTO `etest`('$cola', '$colb')"; mysql_query($sql) or die("Query failed: " . mysql_error() . " Actual query: " . $sql); } } echo $input; ?> Is there anything glaring as to why it errors out? Quote Link to comment Share on other sites More sharing options...
BlueSkyIS Posted May 28, 2008 Share Posted May 28, 2008 INSERT INTO `etest` VALUES ('1', '1') Quote Link to comment Share on other sites More sharing options...
BadGoat Posted May 28, 2008 Author Share Posted May 28, 2008 I can understand that but that defeats the purpose of just pasting the data into the textfield on the previous page.. (I think!) Would the VALUES portion have to be $array2[0] and $array2[1] instead? Quote Link to comment Share on other sites More sharing options...
BadGoat Posted May 29, 2008 Author Share Posted May 29, 2008 OK, been tinkering all day and night and still not having any luck.. I have tried: $sql = "INSERT INTO `etest`('$cola', '$colb') VALUES ('$cola', '$colb')"; and $sql = "INSERT INTO `etest`('$cola', '$colb')"; and $sql = "INSERT INTO `etest`('$cola', '$colb') VALUES ('$array2[0]', '$array2[1]')"; and various other attempts at getting the two numbers posted into the db.. The goal is to take the data from the first page, separate by comma and then insert into the data into the db. Help ??? Quote Link to comment Share on other sites More sharing options...
jonsjava Posted May 29, 2008 Share Posted May 29, 2008 lets say your row names are "cola" and "colb", respectively. INSERT INTO `etest`(`cola`, `colb`) VALUES('$cola', '$colb'); Quote Link to comment Share on other sites More sharing options...
BadGoat Posted May 29, 2008 Author Share Posted May 29, 2008 Yay! I fixed it.. Here's what works! (was a problem with the syntax of the MySQL query) Thank you kindly for pointing me in the right direction <?PHP include ("../config.php"); echo' <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <head> <title></title> <link rel=stylesheet href="../eve.css" type="text/css"> </head> <body>'; $input = $_POST['t_data']; //your post data here. $array = explode("\n", $input); foreach ($array as $value){ if ($value != null && $value != "" && $value != " " && $value != "\n" && $value != "\t" && $value != "\r"){ $array2 = explode(",", $value); $cola = mysql_real_escape_string($array2[0]); $colb = mysql_real_escape_string($array2[1]); echo 'column a is '.$cola.'<br />'; echo 'column b is '.$colb.'<br />'; $sql = "INSERT INTO etest VALUES ('$cola', '$colb')"; mysql_query($sql) or die("Query failed: " . mysql_error() . " Actual query: " . $sql); } } ?> Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.