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:

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

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.

Answer 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.

Answer 2

 $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.

Answer 3

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