Create dropdown list from database on dynamically added row

[Senthilkumar]

Hi Team,

I created code for add the table row dynamically using add button and insert the values which is entered on that row. I configured the input on my row. 

 

I want to add the drop down option which is pull the dat from databse. 

My entire code is 

<div class="home-content">

            <div>
                <?php
                if (isset($_POST["addInvoice"]))
                {
                    $month = $_POST["month"];

                    $newDate = date("M-Y", strtotime($month));

                    for ($a = 0; $a < count($_POST["brand"]); $a++)
                    {
                        $sql = "INSERT INTO tiv (Month, Brand, Category, Sub_Category, Model, Quantity) VALUES ('$newDate', '" . $_POST["brand"][$a] . "', '" . $_POST["category"][$a] . "', '" . $_POST["sub_category"][$a] . "', '" . $_POST["model"][$a] . "', '" . $_POST["qty"][$a] . "')";
                        mysqli_query($conn, $sql);
                    }
                }
                ?>

                <form method="POST" action="">
                    <input type="month" name="month" placeholder="Select Month" required style="width:auto;border:solid; border-color:black;font-family:Cambria;border-radius:5px;height:30px" />

                    <table style="margin-top:10px; height:15px">
                        <tr>
                            <th>#</th>
                            <th>Brand</th>
                            <th>Category</th>
                            <th>Sub Category</th>
                            <th>Model</th>
                            <th>Quantity</th>
                        </tr>
                        <tbody id="tbody"></tbody>
                    </table>

                    <p>
                        <button type="button" onclick="addItem();" style="font-family:Cambria;background: #0A2558;color:white;cursor:pointer; margin-top:10px;height:30px; border-radius:5px; padding:5px">Add New  +</button>
                    </p>

                    <input type="submit" name="addInvoice" value="Submit" style="width:150px; margin-top:10px;font-family:Cambria;border-radius:5px;height:30px;" />
                </form>

                <style type="text/css">
	                    table {
		                    width: 100%;
		                    border-collapse: collapse;
                            font-family:Cambria;
                                            border-radius:10px;
	                    }
	                    table tr th {
		                    border: 1px solid black;
		                    padding: 5px;
                            font-family:Cambria;
                            background: #0A2558;
                            color:white;
                            border-color:white;

	                    }
                         table tr td {
		                    border: 1px solid black;
		                    padding: 5px;
                            font-family:Cambria;
                            color:#0A2558;
                            border-color:#0A2558;

	                    }
                </style>

                <script>
	                        var items = 0;
	                        function addItem() {
		                        items++;

		                        var html = "<tr>";
			                        html += "<td>" + items + "</td>";
			                        html += "<td><input type='text' name='brand[]'></td>";
                                    html += "<td><input type='text' name='category[]'></td>";
                                    html += "<td><input type='text' name='sub_category[]'></td>";
                                    html += "<td><input type='text' name='model[]'></td>";
                                    html += "<td><input type='number' name='qty[]'></td>";
		                            html += "</tr>";

		                        var row = document.getElementById("tbody").insertRow();
		                        row.innerHTML = html;
	                        }
                </script>
            </div>

        </div>

I am trying to use the bellow code on my <td>

<select class="background" name="Department" onchange="submit()">
                                            <option value="">Department</option>
                                                <?php
                                                $qry    =   mysqli_query($conn,"SELECT DISTINCT Brand FROM model ORDER BY Brand ASC");
                                                while($row = $qry->fetch_assoc()) {
                                                    echo "<option";
                                                    if(isset($_REQUEST['Department']) and $_REQUEST['Department']==$row['Brand']) echo ' selected="selected"';
                                                    echo ">{$row['Brand']}</option>\n";
                                                }
                                                ?>
                                        </select>

But it is not working.

 

Can anyone tell me how to use the select option on my code

[Senthilkumar]

Dear Team,

 

Please help me to solve my issue

[mac_gyver]

On 4/8/2023 at 1:05 PM, Senthilkumar said:

But it is not working

you must tell us what result you did get, even if the result was a blank page, and tell us what result you expected.

On 4/8/2023 at 1:05 PM, Senthilkumar said:

SELECT DISTINCT Brand FROM model ...

 

your data is not normalized, making every query and operation on the data harder. you should have a brand table, with id (auto-increment primary index) and name columns. this will establish brand_id values. you would store the brand_id in any related table, such as the model table. would then simply query the brand table to get a list of the brand names and their ids for the select/option menu.

[Senthilkumar]

This is my page

 

When i am clicking add button I want to create the drop down like bellow picture

But witht the following code if I press add button, it is not creating row.

<script>
	                        var items = 0;
	                        function addItem() {
		                        items++;

		                        var html = "<tr>";
			                        html += "<td>" + items + "</td>";
			                        html += "<td><select name='tb1' >
                                            <?php
                                            $qry    =   mysqli_query($conn,"SELECT Brand_Name FROM brand ORDER BY Brand_Name ASC");
                                            while($row = $qry->fetch_assoc()) {
                                                echo "<option";
                                                if(isset($_REQUEST['tb1']) and $_REQUEST['tb1']==$row['Brand_Name']) echo ' 		selected="selected"';
                                                echo ">{$row['Brand_Name']}</option>\n";
                                            }
                                            ?>
                	                    </select></td>";
                                    html += "<td><input type='text' name='category[]'></td>";
                                    html += "<td><input type='text' name='sub_category[]'></td>";
                                    html += "<td><input type='text' name='model[]'></td>";
                                    html += "<td><input type='number' name='qty[]'></td>";
		                            html += "</tr>";

		                        var row = document.getElementById("tbody").insertRow();
		                        row.innerHTML = html;
	                        }
                </script>

My table structure is 

Please correct my code

Edited April 10, 2023 by Senthilkumar

[kicken]

20 hours ago, Senthilkumar said:

But witht the following code if I press add button, it is not creating row.

That code is doing things the hard way.  HTML has a template tag for a reason.  Use your PHP code to generate a template row in a template tag.  The your JS only needs to clone that row and add it to the table.  For example:

Template:

<template id="row-template">
  <tr>
    <td></td>
    <td>
    <select class="background" name="Department[]">
      <option value="">Department</option>
      <?php
  $qry    =   mysqli_query($conn,"SELECT DISTINCT Brand FROM model ORDER BY Brand ASC");
  while($row = $qry->fetch_assoc()) {
    echo "<option";
    if(isset($_REQUEST['Department']) and $_REQUEST['Department']==$row['Brand']) echo ' selected="selected"';
    echo ">{$row['Brand']}</option>\n";
}
      ?>
      </select>
    </td>
    <td><input type='text' name='category[]'></td>
    <td><input type='text' name='sub_category[]'></td>
    <td><input type='text' name='model[]'></td>
    <td><input type='number' name='qty[]'></td>
  </tr>
</template>

JS:

const template=document.getElementById('row-template');
const tbody=document.getElementById('tbody');
let itemCount=0;
  
function addItem(){
  	const newRow = template.content.cloneNode(true);
    const firstTd = newRow.querySelector('td:first-child');
    firstTd.textContent=++itemCount;
    tbody.appendChild(newRow);
}

 

[Senthilkumar]

Thanks for your reply. It is working now