How to insert arrays into mysql using php

Question

I am scraping data and then turning the data into arrays that output as follows:

array(8) {   
           [0]=> array(8) {   
                             [1]=> string(1) "1"   
                             [2]=> string(8) "Henricho"   
                             [3]=> string(10) "Bruintjies"   
                             [4]=> string(2) "23"   
                             [5]=> string(3) "Agn"   
                             [6]=> string(6) "10.17A"   
                             [7]=> string(4) "-0.2"   
                             [8]=> string(1) "8" }  
           [1]=> array(8) {   
                             [1]=> string(1) "2"   
                             [2]=> string(5) "Akani"   
                             [3]=> string(7) "Simbine"   
                             [4]=> string(2) "23"   
                             [5]=> string(3) "Agn"   
                             [6]=> string(6) "10.21A"   
                             [7]=> string(4) "-0.2"   
                             [8]=> string(1) "7" }

It is displayed by using var_dump($results);

Is there a simple way to input the data into a sql table using $results?

You can create your own class to do that. Simply put, you can run a foreach loop over the array and putting data to MySQL. — Janno
– Janno, Commented Aug 15, 2016 at 7:01
You can use nested foreach loop to loop over the arrays or array and insert the data into sql table — Han Lim
– Han Lim, Commented Aug 15, 2016 at 7:03

Luthando Ntsekwa · Accepted Answer · 2016-08-15 10:01:52Z

2

You need to you something like:

foreach ($results as $value){  
    $allValues = implode('", "', $value); 
    //the above line will give us something like ("1", "Henricho"....etc)
    $query ='Insert into(col1, col2, col3, col4, col5, col6, col7, col8)  
         Values("'.$allValues.'")';
    //You can now execute your query
    echo $query."<BR />";
}

Check Demo Here

edited Aug 15, 2016 at 10:01

answered Aug 15, 2016 at 7:20

Luthando Ntsekwa

4,2486 gold badges25 silver badges53 bronze badges

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

9 Comments

Seef Over a year ago

I have added include('connect.php'); to link to my database and added the table name + headings:

include('connect.php');  foreach ($results as $value){       foreach ($value as $key => $str)       {         //here we remove this 'string(1) ' including the space         //then put back the value inside the array          $value[$key] = trim(substr($str, 10));       }     $allValues =  implode(",", $values);  $query ="Insert into 100mMenFinal (Pos, Name, Surname, Age, Team, Perf, Q, Pts)                Values($allValues)"     }

I do however get: syntax error, unexpected '}'

Luthando Ntsekwa Over a year ago

print your query variable and show me the results echo $query;

Seef Over a year ago

Should echo $query; go after the last }?

Luthando Ntsekwa Over a year ago

before that, put this echo $query."< /BR>"; after this //You can now execute your query

Seef Over a year ago

Now I get this:

Warning: implode(): Invalid arguments passed in /home/productp/public_html/webscraper/live/test.php on line 166 Insert into 100mMenFinal (Pos, Name, Surname, Age, Team, Perf, Q, Pts) Values()< /BR>

|

gentle · Accepted Answer · 2016-08-16 00:07:11Z

0

The data you used var_dump on to give the above result in my guess would be:

$result = array(
array("1","Henricho","Bruintjies","23","Agn","10.17A","-0.2","8"),  
array("2","Akani" , "Simbine" ,"23" ,"Agn", "10.21A","-0.2","7" )
);

lets assume the fields you want to insert data to are id, firstname, lastname, age, anotherfield,anotherfield,anotherfield,anotherfield

foreach ($result AS $innerArray){
// In above $result, each $innerArray is an indexed array
    $sqlQuery = 'INSERT INTO `tablename` (`id`, `firstname`, `lastname`, `age`, `anotherfield`, `anotherfield`, `anotherfield`, `anotherfield`) VALUES('.implode(",", $innerArray).')';
// You can now execute your sql query depending on the mysql adapter you are using.

}

edited Aug 16, 2016 at 0:07

answered Aug 15, 2016 at 8:09

gentle

607 bronze badges

Collectives™ on Stack Overflow

How to insert arrays into mysql using php

2 Answers 2

9 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related