Inserting a simple list into MySQL table (php)

Question

After a bit of hunting around for a solution. I thought it was time to ask the brains trust here on stackoverflow.

I'm looking for a way to take a list, in the form of a string, split it using commas and insert into columns in a mySQL table.

The list would be something like:

Tim Moltzen, 2. Joel Reddy, 3. Blake Ayshford, 4. Chris Lawrence, 5. James Tedesco, 6. Benji Marshall, 7. Braith Anasta, 8. Aaron Woods, 9. Robbie Farah, 10. Jack Buchanan, 11. Bodene Thompson, 12. Liam Fulton, 13. Adam Blair, 14. Ben Murdoch Masila, 15. Ava Seumanufagai 16. Matt Bell, 17. Eddy Pettybourne

From that list, two things are needed to be split. The number and the name.

So, the number would be inserted into the "player_number" column and the name into "player_name". Of course the commas are only used for spacing, and don't need to be interested into the table.

Any assistance on how to split the string and then insert into the table would be extremely appreciated.

EDIT::

I'll see what I can work with using explode and running a loop to insert them into the table.

Vary vague so I'll give a vague answer (Google things to learn how to do it). Use explode() to split by comma into an array. Then create a PDO prepared statement for inserting one row. Use foreach() to loop through the array and insert to DB using your prepared statement. — Sam
– Sam, Commented Apr 5, 2013 at 5:33
use preg_match(); to get your numbers, add them to an array using something like /([0-9]\.)\s+([a-zA-Z\])/ you should be able to get the numbers and values. also you might have to use the explode() function. — Grmn
– Grmn, Commented Apr 5, 2013 at 5:53

Alasdair · Accepted Answer · 2013-04-05 07:05:51Z

1

$string="1. Tim Moltzen, 2. Joel Reddy, 3. Blake Ayshford, 4. Chris Lawrence, 5. James Tedesco, 6. Benji Marshall, 7. Braith Anasta, 8. Aaron Woods, 9. Robbie Farah, 10. Jack Buchanan, 11. Bodene Thompson, 12. Liam Fulton, 13. Adam Blair, 14. Ben Murdoch Masila, 15. Ava Seumanufagai 16. Matt Bell, 17. Eddy Pettybourne";

$string=explode(', ',$string);
foreach($string as $val)
    {
    $val=explode('. ',$val);
    mysql_query('INSERT INTO yourtable (col_number,col_name) VALUES ("'.$val[0].'.","'.$val[1].'")';
    }

I don't understand why you want to insert the period along with the number, as this would mean that the column has to unnecessarily be varchar instead of INT. Anyway, it is as you asked.

Change mysql_query to mysqli_query if you prefer.

To explode between numbers use:

$string=preg_split('/ ?[0-9]+\.? /', $string, NULL, PREG_SPLIT_NO_EMPTY);

But now you don't have any numbers for each name. So you won't be able to insert it like this.

edited Apr 5, 2013 at 7:05

answered Apr 5, 2013 at 5:53

Alasdair

14.3k18 gold badges95 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Aaron Thomas Over a year ago

Gives me an error when I try and run it. "Parse error: syntax error, unexpected T_STRING"

Alasdair Over a year ago

I missed a ' from the end of the SQL string. It's fixed now.

Aaron Thomas Over a year ago

You sir are a champion, works perfectly. Appreciate it big time :) However, is it possible to have the explode work after each number? As in, instead of having to add in commas to the list. I can explode it between numbers? ie. $string=explode('<space>NUMBER',$string);

Aaron Thomas Over a year ago

So it's best to just add commas between the list instead of trying to get the numbers to work. But your solution worked as I thought it would, been very helpful thank you again :)

Alasdair Over a year ago

Actually you can split on the numbers and keep the numbers, but I can't be bothered to think of the regex for that. The easiest method is to use my original code there but to get it to insert the commas automatically with $string=preg_replace('/([0-9]+\.)/',', $1',$string); $string=trim($string,' ,');

CaffeineShots · Accepted Answer · 2013-04-05 05:56:18Z

0

 $abc= "1.Tim Moltzen, 2. Joel Reddy, 3. Blake Ayshford, 4. Chris Lawrence, 5. James Tedesco, 6. Benji Marshall, 7. Braith Anasta, 8. Aaron Woods, 9. Robbie Farah, 10. Jack Buchanan, 11. Bodene Thompson, 12. Liam Fulton, 13. Adam Blair, 14. Ben Murdoch Masila, 15. Ava Seumanufagai, 16.Matt Bell, 17. Eddy Pettybourne";
     $def = explode(",", $abc);
$countDEF= count($def);

for($a=0;$a<=$countDEF-1;$a++){
     $ghi = explode(".", $def[$a]);
     $countGHI = $ghi;
    for($b=0;$b<=1;$b++){
        echo $ghi[0]."-".$ghi[1]."<br>";
        //  do your query here.
        // ghi[0] is the number and ghi[1] is the fullname
    break; }

     }

try to run this and get some idea.

answered Apr 5, 2013 at 5:56

CaffeineShots

2,1727 gold badges35 silver badges58 bronze badges

Comments

kwelsan · Accepted Answer · 2013-04-05 06:49:06Z

0

You can import directly in MySQL table without PHP. You just need to use trick.

If your file have same pattern in data then you can make the data in CSV format by just replace "," to "\r\n" and then "." to ",".

Now you can use MySQL Load Infile to import data into database.

http://dev.mysql.com/doc/refman/5.1/en/load-data.html

load data local infile 'file_location' into table tbl_name fields terminated by ',' lines terminated by '\r\n';

edited Apr 5, 2013 at 6:49

answered Apr 5, 2013 at 6:40

kwelsan

1,2191 gold badge7 silver badges18 bronze badges

Collectives™ on Stack Overflow

Inserting a simple list into MySQL table (php)

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related