How to save/update all selected multiple dropdown selected values into myphpadmin database Codeignitor

[gidgor256]

How to save/update all selected multiple dropdown selected values into myphpadmin database with the table above (using Ajax or better recommendation)
When the 'save all' button is clicked all individual selected values in the dropdown are inserted into the dB and when 'update-all' is clicked the dB values are all updates.
For all the search, I get for only one dropdown and I don't know to use that for all dropdown at ago.
I really do not know how to go about it.

links, demos and examples will be good

 

[Barand]

Your form markup code and form processing code would be good

[gidgor256]

On 3/31/2021 at 10:22 AM, Barand said:

Your form markup code and form processing code would be good

so far this is what i have got

   $('.remsubmit').click(function() {
                            var indexid= "<?=$indexid?>";
                            var name = $(this).prop('id');
                            var emailid = "<?=$emailid?>";
			    var remarks="";
                            
                            
                           var x = document.getElementById("mySelect").selectedIndex;
                             var y = document.getElementById("mySelect").options;
                            remarks = y[x].text;

                   

 this insert the selected value of the first dropdown only;
that is when submit for the index 2 or 40 is clicked the value selected in the remark dropdown of the index 1 is reapeated for them
how can i make it a loop

Edited April 2, 2021 by gidgor256

[Barand]

The simplest way is to name your selects using the id of the related item. In my example below, the product codes are the ids.

        TABLE: product;
        +--------------+------------------------------------------+------+-----+---------+-------+
        | Field        | Type                                     | Null | Key | Default | Extra |
        +--------------+------------------------------------------+------+-----+---------+-------+
        | product_code | varchar(5)                               | NO   | PRI | NULL    |       |
        | product_name | varchar(45)                              | YES  |     | NULL    |       |
        | price        | decimal(8,2)                             | YES  |     | NULL    |       |
        | rating       | enum('vpoor','poor','ok','good','vgood') | YES  |     | NULL    |       |
        +--------------+------------------------------------------+------+-----+---------+-------+

        +--------------+--------------+-------+--------+
        | product_code | product_name | price | rating |
        +--------------+--------------+-------+--------+
        | A001         | Widget       | 10.99 | vpoor  |
        | B002         | Gizmo        | 3.49  | ok     |
        | C003         | Thingy       | 56.25 | good   |
        | D444         | Wotsit       | 2.25  | vgood  |
        +--------------+--------------+-------+--------+

and therefore the selects are named like this...

<select  name="rating[A001]"> ... </select>
<select  name="rating[B002]"> ... </select>
etc

Whn POSTed the post data looks like

   [rating] => Array
        (
            [A001] => 4
            [B002] => 3
            [C003] => 2
            [D444] => 1
        )

and processing is simply

foreach ($_POST['rating'] as $prodid => $rating)
   update product setting rating to $rating where product code is $prodid 
end foreach

Here's my example code

//
// PROCESS POSTED DATA
//
if ($_SERVER['REQUEST_METHOD'] == 'POST') {

    $updt = $pdo->prepare("UPDATE product
                          SET rating = ?
                          WHERE product_code = ?
                          ");
    foreach ($_POST['rating'] as $code => $rating) {
        $updt->execute( [ $rating, $code ] );
    }

    header("Location: #");         // reload page
    exit;
    
}

//
// MAIN PROCESSING
//
$res = $pdo->query("SELECT product_code
                        , product_name
                        , price
                        , rating+0 as rating         -- force numeric value
                   FROM product
                   ORDER BY product_code
                  ");
$products = '';
foreach ($res as $r) {
    $products .= "<tr>
                      <td>{$r['product_code']}</td>
                      <td>{$r['product_name']}</td>
                      <td>&pound;{$r['price']}</td>
                      <td>
                      <select class='w3-input w3-border' name='rating[{$r['product_code']}]' >
                      " . ratingOptions($r['rating']) .
                      "</select>
                      </td>
                      <td>" . stars($r['rating']) . "</td>
                  </tr>\n";
}

//
//  FUNCTIONS
//

function ratingOptions($current)
{
    $ratings = [1 => 'vpoor', 'poor', 'ok', 'good', 'vgood'];
    $opts = "<option value=''>- select -</option>\n";
    foreach ($ratings as $r => $rdesc) {
        $sel = $r == $current ? 'selected' : '';
        $opts .= "<option $sel value='$r'>$rdesc</option>\n";
    }
    return $opts;
}

function stars($n)
{
    if ($n > 5) $n = 5;
    return "<span style='color:gold'>" . str_repeat("<i class='fas fa-star'></i>", $n) . "</span>" .
           "<span style='color:#e7e7e7'>" . str_repeat("<i class='fas fa-star'></i>", 5-$n) . "</span>";
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<title>Example</title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css">
<link rel="stylesheet" href="https://use.fontawesome.com/releases/v5.15.1/css/all.css">
<style type='text/css'>
   
</style>
</head>
<body>
<div class="w3-content">
    <div class="w3-panel w3-black w3-padding">
        <h1>Example</h1>
    </div>
    <form method='POST'>
        <table class='w3-table-all'>
            <tr class='w3-dark-gray'>
                <th>Code</th>
                <th>Product</th>
                <th>Price</th>
                <th colspan="2">Rating</th>
            </tr>
            <?=$products?>
        </table>
        <br>
        <input class="w3-button w3-blue w3-right" type="submit" value="Save">
    </form>
</div>
</body>
</html>

 

[Barand]

If you want to use AJAX, here is the same example using that method.

The main difference is that instead of using the array naming convention for the selects (name="rating[A001]") it uses class and data attributes

<select class="rating" data-id="A001"> ... </select>

Again, the "stars" are used as visual confirmation that the update was successful.

Code

//
// PROCESS POSTED DATA
//
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    if ($_POST['ajax'] == 'update-all') {
        $updt = $pdo->prepare("UPDATE product
                              SET rating = ?
                              WHERE product_code = ?
                              ");

        foreach ($_POST['rating'] as $rdata) {
            $updt->execute( [ $rdata['rate'], $rdata['id'] ] );
            $stars[] = ["id" => $rdata['id'], "stars" => stars($rdata['rate'])];
        }
        exit(json_encode($stars));
    }
}

//
// MAIN PROCESSING
//
$res = $pdo->query("SELECT product_code
                        , product_name
                        , price
                        , rating+0 as rating         -- force numeric value
                   FROM product
                   ORDER BY product_code
                  ");
$products = '';
foreach ($res as $r) {
    $products .= "<tr>
                      <td>{$r['product_code']}</td>
                      <td>{$r['product_name']}</td>
                      <td>&pound;{$r['price']}</td>
                      <td>
                      <select class='w3-input w3-border rating' data-id='{$r['product_code']}' >
                      " . ratingOptions($r['rating']) .
                      "</select>
                      </td>
                      <td class='stars' data-id='{$r['product_code']}'>" . stars($r['rating']) . "</td>
                  </tr>\n";
}

//
//  FUNCTIONS
//

function ratingOptions($current)
{
    $ratings = [1 => 'vpoor', 'poor', 'ok', 'good', 'vgood'];
    $opts = "<option value=''>- select -</option>\n";
    foreach ($ratings as $r => $rdesc) {
        $sel = $r == $current ? 'selected' : '';
        $opts .= "<option $sel value='$r'>$rdesc</option>\n";
    }
    return $opts;
}

function stars($n)
{
    if ($n > 5) $n = 5;
    return "<span style='color:gold'>" . str_repeat("<i class='fas fa-star'></i>", $n) . "</span>" .
           "<span style='color:#e7e7e7'>" . str_repeat("<i class='fas fa-star'></i>", 5-$n) . "</span>";
}

?>
<!DOCTYPE html>
<html lang="en">
<head>
<title>Example</title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css">
<link rel="stylesheet" href="https://use.fontawesome.com/releases/v5.15.1/css/all.css">
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.5.0/jquery.min.js"></script>
<script type='text/javascript'>
    $().ready( function() {
        $("#btnSave").click( function() {
            var rating = []
            $("select.rating").each( function(k,v) {
                var id = $(v).data("id")
                var rate = $(v).val()
                rating.push( { "id":id, "rate":rate } )
            })
            
            $.post (
                "",
                {"ajax":"update-all", "rating":rating},
                function(resp) {
                    $.each(resp, function(k, v) {
                        $(".stars[data-id="+v.id+"]").html(v.stars)
                    })
                },
                "JSON"
            )
        })
    })
</script>
</head>
<body>
<div class="w3-content">
    <div class="w3-panel w3-black w3-padding">
        <h1>Example</h1>
    </div>
    
        <table class='w3-table-all'>
            <tr class='w3-dark-gray'>
                <th>Code</th>
                <th>Product</th>
                <th>Price</th>
                <th colspan="2">Rating</th>
            </tr>
            <?=$products?>
        </table>
        <br>
        <button class="w3-button w3-blue w3-right" id="btnSave">Save</button>
    
</div>
</body>
</html>

 

Edited April 3, 2021 by Barand