Home Php Mysql Insert Array in columns
Reply: 2

Php Mysql Insert Array in columns

Marco Raganelli
1#
Marco Raganelli Published in 2018-02-14 18:33:29Z

I have the follow problem: I have differents arrays, each array contain a list of items. I try to insert in database but each item of list result togheter.

This is my code: (i use preg_replace for delete
between items)

$codice = preg_replace('/(<br>)+$/', '', $_POST['jcitemcodice']);
$prodotto = preg_replace('/(<br>)+$/', '', $_POST['jcitemname']);
$quantita = preg_replace('/(<br>)+$/', '', $_POST['jcitemqty']);
$prezzo = preg_replace('/(<br>)+$/', '', $_POST['jcitemprezzo']);

$a1 = array("$codice","$prodotto","$quantita","$prezzo");

$res = implode("','" ,$a1);

$sql = "INSERT INTO test (codice,prodotto,quantita,prezzo) VALUES ('$res')";
mysql_query($sql);

Making echo of query the result is:

INSERT INTO test (codice,prodotto,quantita,prezzo) VALUES ('SUT03M SUT02M','Arrabbiata Albahaca','12 6','1.25 1.3')

but for to be correct i need the result:

INSERT INTO test (codice,prodotto,quantita,prezzo) VALUES ('SUT03M','Arrabbiata','12','1.25'), VALUES ('SUT02M','Albahaca','6','1.3')

$codice contain: SUT03M SUT02M
$prodotto contain: Arrabbiata Albahaca
$quantita contain: 12 6
$prezzo contain: 1.25 1.3

I have try a lot of codes looking around but always same result.

Thanks you.

4givN
2#
4givN Reply to 2018-02-14 23:16:45Z

Assuming codice, prodotto, quantita, prezzo don't contain value with _ (underscore), you can explode their content separated by it:

//Change existing tag with defined delimiter
$d = "_";//delimiter
$codice = preg_replace('#<[^>]+>#', $d, $_POST['jcitemcodice']);
$prodotto = preg_replace('#<[^>]+>#', $d, $_POST['jcitemname']);
$quantita = preg_replace('#<[^>]+>#', $d, $_POST['jcitemqty']);
$prezzo = preg_replace('#<[^>]+>#', $d, $_POST['jcitemprezzo']);

//replace space in quantita and in prezzo with the delimiter
$quantita = preg_replace(' ', $d, $quantita);
$prezzo = preg_replace(' ', $d, $prezzo);

//Separate data
$tCodice = explode($d, $codice);
$tProdotto = explode($d, $prodotto);
$tQuantita = explode($d, $quantita);
$tPrezzo = explode($d, $prezzo);

//Formulate values string
$values = "";
foreach($tCodice as $key => $_codice){
    if($_codice > ""){
         $a1 = array(trim($tCodice[$key]), trim($tProdotto[$key]), trim($tQuantita[$key]), trim($tPrezzo[$key]));
         $res = implode("','" ,$a1);
         if($values != ""){
             $values .= ", ";
         }
         $values .= "('".$res."')";
    }
}
$sql = "INSERT INTO test (codice,prodotto,quantita,prezzo) VALUES $values";
echo $sql;

Please improve this for more custom data from you $_POST

Mohammed Shafeek
3#
Mohammed Shafeek Reply to 2018-02-14 20:56:32Z

This is a generalized code and no matter how many variables u have and it will add null if no match found in the strings u were already split.

$codice = "SUT03M SUT02M";
$prodotto = "Arrabbiata";
$quantita = "12 6";
$prezzo = "1.25 1.3";
$column_keys = array("codice","prodotto","quantita","prezzo");

$res = array();
$c = 0;
$sql = "";
$max = array("k" => 0,"v" => "");
foreach ($column_keys as $key => $value) {
    $res[$value] = explode(" ",${$value});
    $c = count($res[$value]);
    if($c > $max["k"])
       $max = array("k" => $c,"v" => $value); 
}
if($max["k"] > 0){
    $sql = "INSERT INTO test (".implode($column_keys,",").") VALUES ";
    for ($i=0; $i < $max["k"] ; $i++) { 
        $ar = array();
        foreach ($column_keys as $key => $value)
            $ar[] = isset($res[$value][$i]) ? $res[$value][$i] : "NULL";
        $adstrng = $i>0 ? ",":"";
        $sql.= $adstrng."(".implode($ar,",").")";
    }    
}
echo $sql;

As like @tadman commented don't use deprecated codes and try to do in updated standards like PDO

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.766912 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO