PHP - loop through array and insert values to SQL

Question

I have an excel file that I'm stripping down in AIR and converting to $ delimited string. I send that string to PHP ($pushFinal) and then use $array = explode("$",$pushFinal); to convert the string to an array. Now I want to loop through the array, inserting the values into SQL, mimicking the excel format. Each 'line' in excel is 49 columns, or 'values' so I need to insert 49 values at a time from the $array for each row in SQL.

What is the best way to do this?

I'm a rookie so have mercy on me :)

So, I tried this:

$pushFinal = $_POST["pushFinal"];
$lines = array();
$lines = explode("|",$pushFinal); 
$lineItems = array();


foreach ($lines as $val){
    $lineItems = explode("$",$val);
    $temp = "";
    foreach($lineItems as $val2){
        $temp = $temp."'".$val2."',";   
    }
    $sql="insert into OPS_SCHEDULE values($temp)";
    $stmt = sqlsrv_query($conn,$sql);
}

The INSERT is failing though. Does this look right? I delimited each line from excel with | and then delimited each value in that line by $. That shows up correctly in $pushFinal.

Wouldn't it be better to, say, convert it to a string with rows delimited by one character and columns delimited by another? This way you can just explode once to get your rows and again to get the values in the row. — lc.
– lc., Commented Jul 12, 2012 at 15:47
I tried your suggestion but my INSERT is failing. Is my code correct? — ellis
– ellis, Commented Jul 12, 2012 at 19:25
Even though you're doing some processing, you're still inserting almost-raw text into your query strings, meaning you're vulnerable to SQL injection attacks. Before you go any further with this code, you should read up about them. — Marc B
– Marc B, Commented Jul 12, 2012 at 19:27
I'm not as concerned about the injection attacks as the 'input string' is built within a script from a secured source object. But, point taken. Thanks! — ellis
– ellis, Commented Jul 12, 2012 at 19:30

Razvan · Accepted Answer · 2012-07-12 15:48:34Z

1

foreach($array as $val){
    $sqlVals[]="'".$val."'";
}
$sqlValsStr = implode(",",$sqlVals);
$sql = "insert into table values(".$sqlValsStr.")";

answered Jul 12, 2012 at 15:48

Razvan

10.1k6 gold badges41 silver badges51 bronze badges

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

Comments

quickshiftin · Accepted Answer · 2012-07-12 15:46:12Z

0

Just read any basic PHP/MySQL introduction page on the internet and you'll have the code finished in no time. Assuming you've already got the database setup :)

answered Jul 12, 2012 at 15:46

quickshiftin

70.3k12 gold badges72 silver badges99 bronze badges

Comments

ellis · Accepted Answer · 2012-07-13 14:02:49Z

0

OK, i got it.

$pushFinal = $_POST["pushFinal"];
$lines = array();
$lines = explode("|",$pushFinal); 
$lineItems = array();

foreach ($lines as $val){
    $lineItems = explode("$",$val);
    $temp = "";
    foreach($lineItems as $val2){
        $temp = $temp."'".$val2."',";   
    }
    $temp = substr($temp, 0, -1);
    $sql ="insert into table values($temp)";
    $stmt = sqlsrv_query($conn,$sql);
}

I had to remove the trailing "," from each $temp row. Thanks "lc" for helping me with delimiting rows separately from the values. That worked like a charm.

answered Jul 13, 2012 at 14:02

ellis

531 gold badge2 silver badges5 bronze badges

Collectives™ on Stack Overflow

PHP - loop through array and insert values to SQL

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related