What is the best way to import data to DB with Foreach in php?

Question

Thank you for your attention. I am newbie in php & MySQL and have one project as simple flow. Here I have one question and would like to know the exact things. So when add data into db with foreach in php, what is the best way to do it for performance of code?

here is one example.

first case.

foreach ($data as $key=>$item){//start for each loop
$id = $item['id'];
$Name = $item['adsname'];
if (!$id || !$bingAdsName) {
    continue;
}
$sql="select* from table name ";
//$dbData = ...  //fetch all db data.
//And below it will compare dbdata and the above id&Name with some 
condition and add to db these value.
}//end foreach loop

second case

foreach ($data as $key=>$item){
    $id = $item['id'];
    $Name = $item['adsname'];
    if (!$id || !$bingAdsName) {
       continue;
    }
    $sql="select* from table name where id='".$id."' AND name='".$Name."'";
    $existData= ...;
    if(!existData){
        continue;
    }
    $sql="INSERT INTO table name(id, name,)
    VALUES ('".$id."', '".$name."'); ";
    //here insert id& value into db;
}

So in first case, I will call db connection only one,but it will deal with 2 dimensional foreach.

in second case, it will call db connection the count of $data times.

So I would like to know what is best way to reduce the running time of code for performance. thanks.

Your code is at high risk to [sql injection](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — Zain Farooq, Aug 06 '18 at 11:09
hi, @ZainFarooq, it is just example sql, I used PDO to avoid sql injection. — Web Master, Aug 06 '18 at 11:14
Hi, @Dormilich, can you please let me know exactly what here means? — Web Master, Aug 06 '18 at 11:15
@WebMaster I would suggest grouping the `INSERT`s that will speed up whole process a lot. — LukasS, Aug 06 '18 at 11:22
_"I used PDO to avoid sql injection"_ - PDO by itself doesn't protect you against SQL Injections. You need to use parameterized prepeared statements for that (which might be what you meant, but it doesn't hurt to point it out either way). — M. Eriksson, Aug 06 '18 at 11:46
I would argue that it depends of the size of the data set. When it comes to inserting the data, you can also insert many rows in one query: `INSER INTO foo (id, name) VALUES (?, ?), (?, ?), (?, ?), ... etc` which will make it perform better (since there's only one DB request). — M. Eriksson, Aug 06 '18 at 11:51

score 0 · Answer 1 · answered Aug 06 '18 at 11:30

In my point of view, your second case is a way more efficient as sorting out or filtering data in simple php will make your application much slower. MySQL is much powerful and efficient in this case. So you should apply MySQL in filtering data to make your app more efficient.

For more info you can refer to this link
Thanks

What is the best way to import data to DB with Foreach in php?

1 Answers1