How to pass multiple variables in foreach php

Question

I'd like to pass multiple variables in a foreach loop to add the value from $array_sma[] into my database. But so far I can only insert the value from $short_smas, while I'd also like to insert the values from $mid_smas. I have tried nested foreach but it's multiplying the values.

$period = array(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15);

$sma = array(6,9);

foreach ($sma as $range)    {


$sum = array_sum(array_slice($period, 0, $range));

$result = array($range - 1 => $sum / $range);


for ($i = $range, $n = count($period); $i != $n; ++$i) {

        $result[$i] = $result[$i - 1] + ($period[$i] - $period[$i - $range]) / $range;

}

$array_sma[] = $result;

}

list($short_smas,$mid_smas)=$array_sma;

foreach ($short_smas as $short_sma) {

$sql = "INSERT INTO sma (short_sma) 

  VALUES ('$short_sma') ";

if ($con->query($sql) === TRUE) {

    echo "New record created successfully<br><br>";

} else {

    echo "Error: " . $sql . "<br>" . $con->error;

}

}

The code in my question works fine i.e. the value from the first sub array ($short_smas) of $array_sma[] gets inserted into the column short_sma of my msql database. The problem I have is when I try to insert the second sub array $mid_smas (see list()) from $array_sma[] in my second column of my database call mid_sma.

I think this is closed to what I want to achieve but still nothing gets inserted in the DB, source: php+mysql: insert a php array into mysql

I don't have any mysql syntax error.

$array_sma[] = $result;

 $sql = "INSERT INTO sma (short_sma, mid_sma) VALUES ";
foreach ($array_sma as $item) {
 $sql .= "('".$item[0]."','".$item[1]."'),";
}

$sql = rtrim($sql,",");

What is the result you are looking for there might be an easier way to do this but I need to see what the end result is supposed to look like (if it was working). — Danny
– Danny, Commented Mar 23, 2018 at 17:33
@Seb your last comment belongs inside the question: I've copy-pasted into the body — lebelinoz
– lebelinoz, Commented Mar 23, 2018 at 20:59

szywo · Accepted Answer · 2018-03-24 12:40:02Z

0

Main problem is that $short_smas and $mid_smas have different size. Moreover they are associative arrays so either you pick unique keys from both and will allow for empty values for keys that have only one value available or you pick only keys present in both arrays. Code below provides first solution.

// first lets pick unique keys from both arrays
$uniqe_keys = array_unique(array_merge(array_keys($short_smas), array_keys($mid_smas)));
// alternatively we can only pick those present in both
// $intersect_keys = array_intersect(array_keys($short_smas),array_keys($mid_smas));

// now lets build sql in loop as Marcelo Agimóvel sugested
// firs we need base command:
$sql = "INSERT INTO sma (short_sma, mid_sma) VALUES ";

// now we add value pairs to coma separated list of values to
// insert using keys from prepared keys array
foreach ($uniqe_keys as $key) {
    $mid_sma = array_key_exists($key, $mid_smas)?$mid_smas[$key]:"";
    $short_sma = array_key_exists($key, $short_smas)?$short_smas[$key]:"";
    // here we build coma separated list of value pairs to insert
    $sql .= "('$short_sma', '$mid_sma'),";
}
$sql = rtrim($sql, ",");

// with data provided in question $sql should have string:
// INSERT INTO sma (short_sma, mid_sma) VALUES, ('3.5', ''), ('4.5', ''), ('5.5', ''), ('6.5', '5'), ('7.5', '6'), ('8.5', '7'), ('9.5', '8'), ('10.5', '9'), ('11.5', '10'), ('12.5', '11')

// now we execute just one sql command
if ($con->query($sql) === TRUE) {
    echo "New records created successfully<br><br>";
} else {
    echo "Error: " . $sql . "<br>" . $con->error;
}

// don't forget to close connection

Marcelo Agimóvel also suggested that instead of multiple inserts like this:

INSERT INTO tbl_name (a,b,c) VALUES (1,2,3);

its better to use single:

INSERT INTO tbl_name
    (a,b,c)
VALUES
    (1,2,3),
    (4,5,6),
    (7,8,9);

That's why I append value pairs to $sql in foreach loop and execute query outside loop.

Also its worth mentioning that instead of executing straight sql its better to use prepared statements as they are less prone to sql injection.

edited Mar 24, 2018 at 12:40

answered Mar 23, 2018 at 17:46

szywo

861 silver badge6 bronze badges

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

7 Comments

Marcelo Agimóvel Over a year ago

Seb this is your answer, I would sugest you to build the query inside the foreach, but only execute outside foreach. This will improve your query performance. Never use queries in a loop. Nice code, btw.

szywo Over a year ago

@Seb I modified my answer based on what you included in comment. Do you need help with INSERT modification so it handles two fields instead of one?

Seb Over a year ago

Thanks. If you print $array_sma[] you will see that it contains two sub array which I have put into two variables (see list()) $short_smas and $mid_smas. These are the values I want to put in my DB. In my code I have only inserted short_sma in my DB, because I don't know how to insert more than one variable, and this is what I'm trying to find out i.e. How do I insert mid_sma in my DB bearing in mind that I have a specific column for that in my DB.

szywo Over a year ago

@Seb then You need construct: INSERT INTO tbl_name (a,b,c) VALUES (1,2,3); I edited my answer once more, please see the code, and note about building single INSERT as suggested by Marcelo Agimóvel

szywo Over a year ago

@Seb You got no INSERT because $item[0] and $item[1] are empty and your array has just two rows and many columns so you get: INSERT ... VALUES ('',''),('','')

|

Collectives™ on Stack Overflow

How to pass multiple variables in foreach php

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related