-3

I want to insert all values with just a function and I don't want to rewrite the same code many times but I have the problem that this function just inserts the first values (I checked the input name and it's set correctly).

$name = htmlspecialchars($_POST["name"]);  
$prix = htmlspecialchars($_POST["prixing"]); 
$prixn = htmlspecialchars($_POST["quantite"]);  
$uniteing = $_POST['unite']; 
$date = date('Y-m-d');

<?php

$servername = "localhost"; 
$username = "root"; 
$password = "test"; 
$dbname = "test";

// Create connection $conn = mysqli_connect($servername, $username, $password, $dbname);

// Check connection if (!$conn) {   die("Connection failed: " . mysqli_connect_error());    }  // 
  variable $date = date('Y-m-d');

$name = htmlspecialchars($_POST["name"]);      
$name1 = htmlspecialchars($_POST["name1"]); 
$prix = htmlspecialchars($_POST["prixing"]);   
$prix1 = htmlspecialchars($_POST["prixing1"]); 
$prixn = htmlspecialchars($_POST["quantite"]); 
$prixn1 = htmlspecialchars($_POST["quantite1"]); 
$uniteing = $_POST['unite'];    
$uniteing1 = $_POST['unite1'];

$name2 = htmlspecialchars($_POST["name2"]);          
$name3 = htmlspecialchars($_POST["name3"]); 
$prix2 = htmlspecialchars($_POST["prixing2"]);       
$prix3 = htmlspecialchars($_POST["prixing3"]); 
$prixn2 = htmlspecialchars($_POST["quantite2"]);     
$prixn3 = htmlspecialchars($_POST["quantite3"]); 
$uniteing2= $_POST['unite2'];   
$uniteing3 = $_POST['unite3'];

$name4 = htmlspecialchars($_POST["name4"]);          
$name5 = htmlspecialchars($_POST["name5"]); 
$prix4 = htmlspecialchars($_POST["prixing4"]);       
$prix5 = htmlspecialchars($_POST["prixing5"]); 
$prixn4 = htmlspecialchars($_POST["quantite4"]);     
$prixn5 = htmlspecialchars($_POST["quantite5"]); 
$uniteing4 = $_POST['unite4'];  
$uniteing5 = $_POST['unite5'];

$name6 = htmlspecialchars($_POST["name6"]);          
$name7 = htmlspecialchars($_POST["name7"]); 
$prix6 = htmlspecialchars($_POST["prixing6"]);       
$prix7 = htmlspecialchars($_POST["prixing7"]); 
$prixn6 = htmlspecialchars($_POST["quantite6"]);     
$prixn7 = htmlspecialchars($_POST["quantite7"]); 
$uniteing6 = $_POST['unite6'];  
$uniteing7 = $_POST['unite7'];

$name8 = htmlspecialchars($_POST["name8"]);          
$name9 = htmlspecialchars($_POST["name9"]); 
$prix8 = htmlspecialchars($_POST["prixing8"]);       
$prix9 = htmlspecialchars($_POST["prixing9"]); 
$prixn8 = htmlspecialchars($_POST["quantite8"]);     
$prixn9 = htmlspecialchars($_POST["quantite9"]); 
$uniteing8 = $_POST['unite8'];  
$uniteing9 = $_POST['unite9'];

$name10 = htmlspecialchars($_POST["name10"]); 
$prix10 = htmlspecialchars($_POST["prixing10"]); 
$prixn10 = htmlspecialchars($_POST["quantite10"]); 
$uniteing10 = $_POST['unite10'];

//end variable 2

function insert($namex, $prixx,$prixnx, $datex, $uniteingx,$conn)
{
    $sql = "INSERT INTO ingredient 
                VALUES ('$namex','$prixx','$prixnx','$datex','$uniteingx')"; 
    $res = mysqli_query($conn, $sql);

    if ($res) { 
        echo "New record created successfully"; 
        mysqli_error($conn); 
    } else {   
        echo "_error_: " . $sql . "<br>" . mysqli_error($conn);
    }
} 

insert($name, $prix,$prixn, $date, $uniteing,$conn); 
insert($name1, $prix1,$prixn1, $date1, $uniteing1,$conn); 
insert($name2, $prix2,$prixn2, $date2, $uniteing2,$conn); 
insert($name3, $prix3,$prixn3, $date3, $uniteing3,$conn); 
insert($name4, $prix4,$prixn4, $date4, $uniteing4,$conn); 
insert($name5, $prix5,$prixn5, $date5, $uniteing5,$conn); 
insert($name6, $prix6,$prixn6, $date6, $uniteing6,$conn); 
insert($name7, $prix7,$prixn7, $date7, $uniteing7,$conn); 
insert($name8, $prix8,$prixn8, $date8, $uniteing8,$conn); 
insert($name9, $prix9,$prixn9, $date9, $uniteing9,$conn); 
insert($name10, $prix10,$prixn10, $date10, $uniteing10,$conn);

header('Location: ../index.html'); ?>

Here is my form:

<!DOCTYPE html>
<html>
<head>
  <link rel="stylesheet" type="text/css" href="css/style.css">
<meta charset="utf-8">
</head>

<body>
<form action="php/insert-multi-ing.php" method="POST">

<table>
  <tr>
    <th>Nom Ingrédient</th>
    <th>Prix Ingrédient</th>
    <th>Quantite Ingrédient</th>
    <th>Unite</th>
  </tr>
  <tr>
    <td><input type="text" name="name"></td>
    <td><input type="text" name="prixing"></td>
     <td><input type="text" name="quantite"></td>
     <td>

        <select name="unite" id="unites">
          <option value="kg">kg</option>
          <option value="G">G</option>
          <option value="L">L</option>
          <option value="ml">Ml</option>
          <option value="cl">Cl</option>
          <option value="Piece">Piece</option>
        </select>
      </td>
    </tr>
    <tr>
        <td><input type="text" name="name1"></td>
        <td><input type="text" name="prixing1"></td>
        <td><input type="text" name="quantite1"></td>
        <td>

            <select name="unite1" id="">
                <option value="kg">kg</option>
                <option value="G">G</option>
                <option value="L">L</option>
                <option value="ml">Ml</option>
                <option value="cl">Cl</option>
                <option value="Piece">Piece</option>
            </select>
        </td>
    </tr>
    <tr>
        <td><input type="text" name="name2"></td>
        <td><input type="text" name="prixing2"></td>
        <td><input type="text" name="quantite2"></td>
        <td>

            <select name="unite2" id="">
                <option value="kg">kg</option>
                <option value="G">G</option>
                <option value="L">L</option>
                <option value="ml">Ml</option>
                <option value="cl">Cl</option>
                <option value="Piece">Piece</option>
            </select>
        </td>
    </tr>
    <tr>
        <td><input type="text" name="name3"></td>
        <td><input type="text" name="prixing3"></td>
         <td><input type="text" name="quantite3"></td>
        <td>
    
            <select name="unite3" id="">
                <option value="kg">kg</option>
                <option value="G">G</option>
                <option value="L">L</option>
                <option value="ml">Ml</option>
                <option value="cl">Cl</option>
                <option value="Piece">Piece</option>
            </select>
        </td>
    </tr>
    <tr>
        <td><input type="text" name="name4"></td>
        <td><input type="text" name="prixing4"></td>
        <td><input type="text" name="quantite4"></td>
        <td>

            <select name="unite4" id="">
                <option value="kg">kg</option>
                <option value="G">G</option>
                <option value="L">L</option>
                <option value="ml">Ml</option>
                <option value="cl">Cl</option>
                <option value="Piece">Piece</option>
            </select>
        </td>
    </tr>
    <tr>
        <td><input type="text" name="name5"></td>
        <td><input type="text" name="prixing5"></td>
        <td><input type="text" name="quantite5"></td>
        <td>

            <select name="unite5" id="">
              <option value="kg">kg</option>
              <option value="G">G</option>
              <option value="L">L</option>
              <option value="ml">Ml</option>
              <option value="cl">Cl</option>
              <option value="Piece">Piece</option>
            </select>
        </td>
    </tr>
    <tr>
        <td><input type="text" name="name6"></td>
        <td><input type="text" name="prixing6"></td>
        <td><input type="text" name="quantite6"></td>
        <td>

            <select name="unite6" id="">
              <option value="kg">kg</option>
              <option value="G">G</option>
              <option value="L">L</option>
              <option value="ml">Ml</option>
              <option value="cl">Cl</option>
              <option value="Piece">Piece</option>
            </select>
        </td>
    </tr>
    <tr>
        <td><input type="text" name="name7"></td>
        <td><input type="text" name="prixing7"></td>
        <td><input type="text" name="quantite7"></td>
        <td>

            <select name="unite7" id="">
              <option value="kg">kg</option>
              <option value="G">G</option>
              <option value="L">L</option>
              <option value="ml">Ml</option>
              <option value="cl">Cl</option>
              <option value="Piece">Piece</option>
            </select>
        </td>
    </tr>
    <tr>
        <td><input type="text" name="name8"></td>
        <td><input type="text" name="prixing8"></td>
        <td><input type="text" name="quantite8"></td>
        <td>
                
            <select name="unite8" id="">
              <option value="kg">kg</option>
              <option value="G">G</option>
              <option value="L">L</option>
              <option value="ml">Ml</option>
              <option value="cl">Cl</option>
              <option value="Piece">Piece</option>
            </select>
        </td>
    </tr>
    <tr>
        <td><input type="text" name="name9"></td>
        <td><input type="text" name="prixing9"></td>
         <td><input type="text" name="quantite9"></td>
         <td>

            <select name="unite9" id="">
              <option value="kg">kg</option>
              <option value="G">G</option>
              <option value="L">L</option>
              <option value="ml">Ml</option>
              <option value="cl">Cl</option>
              <option value="Piece">Piece</option>
            </select>
        </td>
    </tr>
    <tr>
        <td><input type="text" name="name10"></td>
        <td><input type="text" name="prixing10"></td>
        <td><input type="text" name="quantite10"></td>
        <td>
            
            <select name="unite10" id="">
              <option value="kg">kg</option>
              <option value="G">G</option>
              <option value="L">L</option>
              <option value="ml">Ml</option>
              <option value="cl">Cl</option>
              <option value="Piece">Piece</option>
            </select>
        </td>
    </tr>
    </table>

    <button>Ajouter ingrédient</button>
</form>
</body>
</html>
Improve this question
13
  • 3
    Warning!!! Your code is open for SQL injection, please use prepared statements preferably with PDO or mysqli. Commented Aug 15, 2020 at 11:41
  • Show us your HTML form yet. Commented Aug 15, 2020 at 11:46
  • Let me understand you have table ingredient with columns like name, prix, prixn, uniteing and date and you want to create several records within one form? Commented Aug 15, 2020 at 11:55
  • yesss truee sir Commented Aug 15, 2020 at 11:57
  • 1
    Good code indentation would help us read the code and more importantly it will help you debug your code Take a quick look at a coding standard for your own benefit. You may be asked to amend this code in a few weeks/months and you will thank me in the end. Commented Aug 15, 2020 at 13:11

3 Answers 3

Reset to default
3

You shouldn't repeat your form fields as a list name0, name1 .. name99 instead you need to send them as an array like: data[0][name] .. data[99][name]

Also better generate your HTML with PHP for not violating DRY rule, you'll apreciate that when will need to edit the form with reperating fields in the future:

<!DOCTYPE html>
<html>
<head>
    <link rel="stylesheet" type="text/css" href="css/style.css">
    <meta charset="utf-8">
</head>
<body>
<form action="php/insert-multi-ing.php" method="POST">

    <table>
        <tr>
            <th>Nom Ingrédient</th>
            <th>Prix</th>
            <th>Prix Ingrédient</th>
            <th>Quantite Ingrédient</th>
            <th>Unite</th>
        </tr>
        <?php
        for ($i = 0; $i < 10; $i++) {
            echo "
                <tr>
                    <td><input type='text' name='data[{$i}][name]'></td>
                    <td><input type='text' name='data[{$i}][prix]'></td>
                    <td><input type='text' name='data[{$i}][prixn]'></td>
                    <td><input type='text' name='data[{$i}][quantite]'></td>
                    <td>
                        <select name='data[{$i}][unite]' id='unite_{$i}'>
                            <option>kg</option>
                            <option>G</option>
                            <option>L</option>
                            <option>Ml</option>
                            <option>Cl</option>
                            <option>Piece</option>
                        </select>
                    </td>
                </tr>
            ";
        }
        ?>
    </table>
    <button>Ajouter ingrédient</button>
</form>
</body>
</html>

Here's the sample for accessing it as a multidimensional array in PHP and inserting to DB with prepared statement. Keep in mind that I use PDO instead of mysqli here which I advice you to:

<?php
$data = $_POST['data'] ?? null;
if (!is_null($data)) {

    $pdo = new PDO("mysql:host=127.0.0.1;dbname=test;charset=utf8", "yourusername", "yourpassword");
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $stmt = $pdo->prepare("
        INSERT 
        INTO ingredient (name, prix, prixn, unite, quantite, date) 
        VALUES (:name, :prix, :prixn, :unite, :quantite, NOW())
        ");

    $stmt->bindParam('name', $name);
    $stmt->bindParam('prix', $prix);
    $stmt->bindParam('prixn', $prixn);
    $stmt->bindParam('quantite', $quantite);
    $stmt->bindParam('unite', $unite);

    foreach ($data as $item) {
        
        // Adds some data validation to make sure you won't save million empty rows,
        // also add custom validation for other fields (if any)
                 
        $name = checkValue($item['name']);
        $prix = checkValue($item['prix']);
        $prixn = checkValue($item['prixn']);
        $quantite = floatval($item['quantite']);
        $unite = checkValue($item['unite']);

        if (!is_null($name) && !is_null($prix) && !is_null($prixn) && $quantite > 0) {
            $stmt->execute();
        }
    }
}
/**
 * check if the string value is not null and not empty
 *
 * @param $value
 *
 * @return string|null
 */
function checkValue($value)
{
    return (is_null($value) || trim($value) == '') ? null : $value;
}

Note your code is messy and it's quite possible that I used wrong column names or field names in form, just fix it. In general that works.

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

10 Comments

MYSQL is perfectly capable of generating DATE and DATETIME stamps without passing them in as php variables.
@mickmackusa probably, however this is not the topic in this case.Id set NOW() as default val in DB.
Why not bind $item['name'] etc. To avoid the declarations inside the loop? E.g. $stmt->bindParam('name', $item['name']);
We must always give our very best advice -- even if it is not asked for. We give because we want to help people grow -- not because we want to farm unicorn points while exerting the least effort necessary. In pdo, I'd probably just drop the data inside the execute() calls.
The null coalescing operator followed by the is_null() check is double handling. Just call isset() in this case.
|
0

Your code is subject to SQL injection. It's better to use parameterized queries. They take care of quoting, fixing data that might include SQL injection, etc. They also execute faster when executing the same statement repeatedly.

Note: Code not tested, may require some syntax checking and other corrections

<?php

//  Create PDO connection
$dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass);

//  Create list of base column names
$colList = array(
    'name',
    'prix',
    'prixn',
    'uniteing'
);
//  Create array to hold column values from $_POST
$val = Array(null,null,null,null);

//  Prepare SQL statement with place holders, be sure to explicitly list the table column names. Substitute the actual column names for those below.
$stmt = $dbh->prepare("INSERT INTO ingredient (`namex`,`prixx`,`prixnx`,`uniteingx`) VALUES (:namex,:prixx,:prixnx,:uniteingx)");

//  Bind each array value to a place holder
$stmt->bindParam(':namex',$val[1]);
$stmt->bindParam(':prixx',$val[2]);
$stmt->bindParam(':prinx',$val[3]);
$stmt->bindParam(':uniteingx',$val[4]);

//  Step through the column name suffix values from the form
for($i=0;$i<=10;$i++) {
    //  Special case for the first (0) value (i.e. no suffix)
    $suffix = $i > 0 ? $i : '';
    //  Load the 5 column values from the post variables into the $val array
    foreach($colList as $colNum, $colName) {
        $val[$colNum] = $_POST[$colName . $suffix];
    }
    //  Execute the SQL statement above with the current values in $val
    $stmt->execute();
}


?>
Improve this answer

4 Comments

11 iterations might not be enough -- use a foreach.
I don't see how $val[4] is the date value. I think the CURRENT_DATE should be hardcoded into the table schema.
@mickmackusa, true, but just following OPs originale post. Loop could be modified to handle any number (if fixed size) or to get the size from an array of fields (foreach) or a value passed from the page.
@mickmackusa My mistake. Too many hours awake and not enough coffee. Presumed it was part of each row.
0

To begin, craft your html form so that it is fit for purpose and doesn't violate html document standards.

  • You should be generating the rows of input fields inside of a loop.

  • You should declare the name attributes of each field using array syntax so that row data is submitted in grouped subarrays -- this will make subsequent processes much easier. By simply trailing the field name with [], you can avoid cluttering your file with unnecessary php syntax.

  • You must not have duplicated id attributes in a single document. You could append a counter to the end of the id strings, but there is a high probability that you don't need the id declarations at all -- I'll omit them.

  • There is zero benefit in duplicating an <option>'s text as its value value. Simply omit that attribute declaration as well.

  • Use a whitelist of measurement units so that you don't need to write out each <option> tag over and over. This will improve the maintainability of your script.

  • For improved UX, use field attributes such as: title, placeholder, pattern, minlength, maxlength, required, etc. as well as potentially type="number" to guide the users about how to form valid entries. These simple touches will not only help to prevent user frustration, they will spare your application from making fruitless trips to the database and/or only storing partial submissions.

    <table>
    <tr>
        <th>Nom Ingrédient</th>
        <th>Prix Ingrédient</th>
        <th>Quantite Ingrédient</th>
        <th>Unite</th>
    </tr>
    <?php
    $numberOfRows = 10;
    $units = ['kg', 'G', 'L', 'ml', 'Cl', 'Piece'];
    for ($i = 0; $i < $numberOfRows; ++$i) {
        ?>
        <tr>
            <td><input type="text" name="name[]"></td>
            <td><input type="text" name="price[]"></td>
            <td><input type="text" name="quantity[]"></td>
            <td>
                <select name="unit[]">
                    <option><?php echo implode('</option><option>', $units); ?></option>
                </select>
            </td>
        </tr>
        <?php
    }
    ?>
</table>

As for your database table setup, here are some tips:

  • Avoid vague column naming like date. Rename the column as insert_date or created_on or something similar so that the value is more informative to future readers of your scripts/project.
  • Modify the schema of your ingredients table to set the DEFAULT value of insert_date as CURRENT_DATE. In doing so, you will never need to write this column into your INSERT queries -- the database will use the current date automatically when you do not pass a value for that column.
  • If this table doesn't have an AUTOINCREMENTing id column, you should add one and make it the PRIMARY KEY. This is a very basic technique to improve future interactions with the table and will eliminate possible confusion when you find that someone has submitted a duplicate name into the table.

As for processing your submitted data, there are only a few simple steps to follow:

  1. Iterate the $_POST array and isolate each row of data to be inserted into the database.

  2. Once isolated, you need to validate and optionally sanitize each row BEFORE executing the query so that you never store "bad data" in your table.

  3. You are using mysqli and that is just fine -- you don't need to switch to pdo to write secure/stable code.

  4. You will only need to generate a prepared statement once and bind variables to placeholders once. Only the (conditional) execution of the statement needs to be inside the loop. (some other examples: 1, 2, 3)

  5. I will recommend, however, that you switch from mysqli's procedural syntax to its object-oriented syntax. It is more concise and I find it simpler to read.

    // create a mysqli connection object e.g. $mysqli = new mysqli(...$credentials);
$sql = "INSERT INTO ingredients (`id`, `name`, `price`, `quantity`, `unit`)
        VALUES (NULL, ?, ?, ?, ?)"; 
$stmt = $mysqli->prepare($sql);
$stmt->bind_param('sdds', $name, $price, $quantity, $unit);  // Assumes price and quantity are float values

$failures = [];
$affectedRows = 0;
$units = ['kg', 'G', 'L', 'ml', 'Cl', 'Piece'];
foreach ($_POST['name'] as $index => $name) {
    // adjust the validation/sanitization processes as you wish
    $name = trim($name);
    $price = trim($_POST['price'][$index]);
    $quantity = trim($_POST['quantity'][$index]);
    $unit = trim($_POST['unit'][$index]);
    $rowNo = $index + 1;
    if (!strlen($name) || !strlen($price) || !strlen($quantity) || !in_array($unit, $units)) {
        $failures[] = "Missing/Invalid value on row $rowNo";
    } elseif (!$stmt->execute());
        $failures[] = "A syntax error has occurred";  // check your error logs
    } else {
        ++$affectedRows;
    }
}
echo "Affected Rows: $affectedRows";
if ($failures) {
    echo "<ul><li>" , implode('</li><li>', $failures) , "</li></ul>";
}

Some overall advice:

  1. Avoid mixing your French and your English. If you are going to use French variable names, then use ALL French variables. That said, I have read advice from native English speakers and English-as-a-Second-Language developers who state that you should always use ALL English in your code -- this is highly subjective advice.
  2. When to use htmlspecialchars() function? You will notice that at no point did I call this function in my answer. This is because at no point are we printing any of the user's input to screen. Validate? Yes. Sanitize? Sure. HTML Encode? Nope; not here, not now.
  3. If these rows of ingredients are meant to relate to specific recipe table rows, then you will need to establish a FOREIGN KEY relationship. The recipes table will need an id column and the ingredients table will need a column like recipe_id which stores that respective recipe id. Assuming your html form will already know which recipe is being referred to, you should include a <input type="hidden" name="recipe" value="<?php echo $recipeId; ?>"> field on the line under your <form> tag. Then when saving data, you must save the $_POST['recipe'] value with each ingredients row. Then you are making better use a of "relational database".
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.