Write loop sql query from data array (php, mysql)

Question

I have a problem with php script.

I have an array, which is generated from form, where $_POST['store'] is an array from jQuery form with functionality to add multiple rows:

 Array
(
    [client] => 
    [darvad] => 
    [owca] => 
    [ldrive] => 
    [store] => Array
        (
            [product] => Array
                (
                    [0] => 430
                    [1] => 440
                    [2] => 430
                )

            [quantity] => Array
                (
                    [0] => 123
                    [1] => 1223
                    [2] => 232
                )

            [segums] => Array
                (
                    [0] => Mixed park
                    [1] => Light vehicle
                    [2] => Trucks
                )

            [deadline] => Array
                (
                    [0] => 2015-08-04
                    [1] => 
                    [2] => 
                )

            [renewal] => Array
                (
                    [0] => 1
                )

        )

)

And i need to get values from this array into sql insert statment and loop it.

$sql_rec = "INSERT INTO tsales_funnel_mrecord (product, quantity, segums, deadline) VALUES (...),(...),(...).... ";

HTML CODE:

            <div id="container">
            <div id="content" role="main">

            <?php
                echo "<pre>";
                print_r($_POST);
                echo "</pre>";
            ?>

<form action="<?= $_SERVER['REQUEST_URI'] ?>" method="post" id=multiForm>  
    <label for="client">Klients: *</label><input id="client" type="text" name="client" placeholder="Reg.nr | Pk.kods" value=""  /></br>
    <label for="selector1">Darījuma vadītājs: *</label>
    <select id="selector1" name="darvad" >
        <option value="">-Dar. vadītājs-</option>
<?php 
    $sql = "SELECT Vards_Uzvards, Tables_ID FROM users";
    $results = $wpdb->get_results($sql);  // return an object, not ARRAY_N
if ($results) {
    foreach ($results as $row) {
        echo "<option value = '".$row->Tables_ID."'>".$row->Vards_Uzvards."</option>"; 
}}
    echo "</select></br>";                          
?>
<label for="owcafind"><a href="<?php echo site_url('/sample-page/owca/'); ?>" target="_blank">Meklēt OWCA kodu:</a> *</label><input id="owcafind" type="text" name="owca" placeholder="OWCA Kods (8)" value=""  /></br>

<label for="ldrive">Mape L diskā:</label><input id="ldrive" type="text" name="ldrive" placeholder="Mape L diskā" value="" /></br>

Produkti:  <a href="#" class="addRow"><img src="<?php echo site_url('/img/plus-icon.png'); ?>" width="15px"></a><br/>
<table class="multi">
<!-- table title -->
<tr><th>Produkts</th><th>Vienību skaits</th><th>Riska segums:</th><th>Deadline:</th><th>Atjaunojums</th><th>[Option]</th></tr>
<!-- row template, when added new row -->
<tr style="display:none;" class="templateRow">
<td><select name="store[product][]">
<option value="" selected="selected">-Produkts-</option>
<option value="430">OCTA</option>
<option value="440">KASKO</option>
</select></td>
<td><input type="text" name="store[quantity][]" /></td>
<td><select name="store[segums][]">
<option value="" selected="selected">-Riska segums-</option>
<option value="Mixed park">Mixed park</option>
<option value="Light vehicle">Light vehicle</option>
<option value="Trucks">Trucks</option>
<option value="Buss">Buss</option>
</select></td>
<td><input type="date" name="store[deadline][]" class="datepicker" /></td>
<td><input type="checkbox" name="store[renewal][]" value="1" /></td>
<td><a class="del" href="#"><img src="<?php echo site_url('img/minus-icon.jpg'); ?>" width="15px"></a></td>
</tr>
<!-- default values -->
<tr>
<td><select name="store[product][]" >
<option value="" selected="selected">-Produkts-</option>
<option value="430">OCTA</option>
<option value="440">KASKO</option>
</select></td>
<td><input type="text" name="store[quantity][]"  /></td>
<td><select name="store[segums][]">
<option value="" selected="selected">-Riska segums-</option>
<option value="Mixed park">Mixed park</option>
<option value="Light vehicle">Light vehicle</option>
<option value="Trucks">Trucks</option>
<option value="Buss">Buss</option>
</select></td>
<td><input type="date" name="store[deadline][]" class="datepicker"  /></td>
<td><input type="checkbox" name="store[renewal][]" value="1" /></td>
<td></td>
</tr>
<!-- /default values -->
</table>

so what was the outcome of your attempts? any error messages that could help us? — DevDonkey
– DevDonkey, Commented Aug 14, 2015 at 8:02
How is the data stored? "VALUES (...),(...),(...).... ";" - could that just be the value of "0, 1, 2, 3, 4" in a big long string? — Christian
– Christian, Commented Aug 14, 2015 at 8:03
@АлексИльин check the bottom example in my answer. Should cover this. — Christian
– Christian, Commented Aug 14, 2015 at 8:11

phpchap · Accepted Answer · 2015-08-14 08:13:41Z

1

From your question, it looks like this is what you're after

$itemCount = sizeof($array['store']['product']);

for ($i = 0; $i < $itemCount; $i++) {
    $sql_rec = "INSERT INTO tsales_funnel_mrecord (product, quantity, segums, deadline) VALUES ("' . $array['store']['product'][$i] . '", "' . $array['store']['quantity'][$i] . '", "' . $array['store']['segums'][$i] . '", "' . $array['store']['deadline'][$i] . '");";

    // Run the sql statement on the database here
}

You'll need to ensure that all user-supplied values are properly escaped before storing in the database.

answered Aug 14, 2015 at 8:13

phpchap

3822 silver badges7 bronze badges

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

2 Comments

AlexIL Over a year ago

I have an error with that, $itemCount return 0, and code didn't work.

Example person Over a year ago

To ensure that user provided data is escaped. Use prepared statements :)

Christian · Accepted Answer · 2015-08-14 08:10:20Z

0

If Array is called $array, then you can access the arrays values like so;

// product;
$array['store']['product'];
// quantity;
$array['store']['quantity'];
// etc.

Then, if they are to go into a single column (which is bad form and I don't recommend, then you can do something like this;

// product;
$prod_string = '';
foreach ($array['store']['product'] as $key => $prod) {
  $prod_string .= $prod;
}

Then you can use $prod_string in your query.

OR, if you need to insert a row for EACH of the keys;

// We use the key from the product loop to get the others;
foreach ($array['store']['product'] as $key => $prod) {
  $prod_val = $prod;
  $qty_val = !empty($array['store']['quantity'][$key]) ? $array['store']['quantity'][$key] : '';
  $seg_val = !empty($array['store']['segums'][$key]) ? $array['store']['segums'][$key] : '';
  $dl_val = !empty($array['store']['deadline'][$key]) ? $array['store']['deadline'][$key] : '';
  // Right here create your query and insert.
  $sql_rec = "INSERT INTO tsales_funnel_mrecord (product, quantity, segums, deadline) VALUES ($prod_val, $qty_val, $seg_val, $dl_val);"
  // I'm not sure what library you're using for your db management, so will leave that out. 
}

Then you'll have the value of each.

NOTE - I have not checked for clean post values. Ie, sanitized input. Thats outside the scope of this question.

answered Aug 14, 2015 at 8:10

Christian

4,0082 gold badges25 silver badges43 bronze badges

7 Comments

AlexIL Over a year ago

Trying to run your code, have error: Warning: Invalid argument supplied for foreach()

Christian Over a year ago

Maybe you should update your question with the exact code you're running. Keep in mind that a warning is not an error and code execution will not halt.

AlexIL Over a year ago

i had update my question with html code. i have tried insert $array = $_POST; before your code. And almost done. Have error: INSERT INTO tsales_funnel_mrecord (Product_type, Vien_skaits, Riska_segums, Deadline) VALUES (440, 2321, Light vehicle, 2015-08-07); You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'vehicle, 2015-08-07)' at line 1

Christian Over a year ago

While my code may have an issue in the query I really recommend you search stack overflow for questions about inserting post data into the database. I fear you will insert unsanitized data. You're really asking for an entire how to and this has all been covered.

AlexIL Over a year ago

Had done with code, i posted it as answer. Thank you for help!

|

Termininja · Accepted Answer · 2016-12-17 10:24:39Z

0

Have done it:

if ($_SERVER['REQUEST_METHOD'] == 'POST') {     
// We use the key from the product loop to get the others;
$array = $_POST;
$itemCount = sizeof($array['store']['product']);
// Loop through all $itemCount
$values_string = '';
for ($i = 0; $i < $itemCount; $i++) {
    $prod = esc_sql($array['store']['product'][$i]);
    $quant = esc_sql($array['store']['quantity'][$i]);
    $seg = esc_sql($array['store']['segums'][$i]);
    $deadline = esc_sql($array['store']['deadline'][$i]);
    $renewal = esc_sql($array['store']['renewal'][$i]);
    if ($i < $itemCount - 1) {
    $new_str = "('".$prod."','".$quant."','".$seg."','".$deadline."','".$renewal."'),";
    } else{
    $new_str = "('".$prod."','".$quant."','".$seg."','".$deadline."','".$renewal."');";
    }
    $values_string .= $new_str;
}
// Run the sql statement on the database here
$sql_rec = "INSERT INTO tsales_funnel_mrecord (Product_type, Vien_skaits, Riska_segums, Deadline, Atjaunojums) VALUES $values_string";
$wpdb->query($sql_rec); 
}

edited Dec 17, 2016 at 10:24

Termininja

7,05412 gold badges51 silver badges50 bronze badges

answered Aug 14, 2015 at 9:35

AlexIL

5733 gold badges9 silver badges24 bronze badges

1 Comment

Christian Over a year ago

As I said in the comments on my answer, esc_sql() will not protect from injection attacks.

Collectives™ on Stack Overflow

Write loop sql query from data array (php, mysql)

3 Answers 3

2 Comments

7 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

7 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related