Populating dropdown menu with mysql data problem

[bambinou1980]

Hello,

 

I am really struggling with this.....

 

I have a Mysql table called "Products" with the fields:

id

name

price1

price2

price3

 

Each product as you can see has 3 prices.

 

 

What I am trying to achieve is this horizontally in the form

 

Dropdown1[name], Radial button 1[price1], radial button 2[price2], radial button 3[price3], quantity_field

 

I would like to have the radial buttons to populate the right prices of the product selected from the dropdown menu, I am completely mixed up here.....

 

From what I understand I cannot achieve this with php alone but PHP and jqeury, would anyone knows how please?

 

Here is my current code:

						<?php 
			$query2 = "SELECT name, price1, price2, price3 FROM products ORDER BY name desc LIMIT 3";
                        $result2 = mysqli_query($connection, $query2);
                        while($row = mysqli_fetch_assoc($result2)){
						?>

     <!--Product 1-->
     <div class="form-inline well">
     <label for="product1_id">Choose Product 1 *</label>
     <div class="form-group">
     <select name="product1" id="product1"  class="form-control">
     <option value="<?php echo $row['name'];?>"><?php echo $row['name'];?></option>
     </select> 
     <label class="btn btn-default">
     <input type="radio" id="price1_id" name="price1" value="4" value="<?php echo $row['price1'];?>" /> €<?php echo $row['price1'];?>
     </label> 					
     <label class="btn btn-default">
     <input type="radio" id="price2_id" name="price2" value="4" value="<?php echo $row['price2'];?>"/> €<?php echo $row['price2'];?>
     </label> 			
     <label class="btn btn-default">
     <input type="radio" id="price3_id" name="price3" value="4" value="<?php echo $row['price3'];?>"/> €<?php echo $row['price3'];?>
     </label> 					  
     <input name="quantity1" id="quantity1" type="text" class="form-control" maxlength="4" size="4"><!--disabled="disabled"-->
     </div>
     </div>
                         <!--Product 1-->
						<?php } ?>

 Also my other problem is that I am actually not just populating the dropdown menus but also multiplying the whole dropdown html components which is wrong.

 

What is the best method to have let's say 3 horizontal dropdown lines(up to 5 product selection). Do I need to re-add an sql query each time for each of the lines?

 

 

Thank you so much for your help in advance!

 

Ben

[Barand]

If I understand correctly what you want, you need to call an AJAX request when the dropdown selection changes. Send the selected product id and get back the 3 product prices. Then set the price values of the 3 buttons from the returned data.

[bambinou1980]

Hi Barand,

 

Yes you are right,( I had to blurr the product names because those are my friends products).

 

I have attached a pic here:

 

 

 

As you can see, I have created a table with 5 fields, table called "products" with product_id, product_name, price1, price2, price3 and quantity.

 

When the person in front of the pc passes an order from his desk, he will choose a due date,choose the customer name from the dropdown that should populate automatically the 3 prices radio buttons(from the products table), he will then input a quantity and click send. This data will then go to a new table called "Orders" this way I keep everything statically in one table where I will then be able to pull statistics in the future.

 

What I wanted first was a (add) button to add a new line for the order each time but I checked it online and did find the logic quiet complicated for me so for now I was thinking about having 5 static dropdown fields.

 

Regarding calender, what plugin would you suggest please? Something easy/simple to use.

I am thinking about taking jquery courses online because I have not palyed much with it.

 

 

 

Thank you so much as always:-)

Edited July 31, 2015 by bambinou1980

[bambinou1980]

I have found this little pieve of code that seems to work pretty well with Jqeury to add new fields, my problem is, how to add my html and php code without breaking the js code?

I tired to put them together but it failed:

		<script>				
$(document).ready(function() {
    var max_fields      = 10; //maximum input boxes allowed
    var wrapper         = $(".input_fields_wrap"); //Fields wrapper
    var add_button      = $(".add_field_button"); //Add button ID
    
    var x = 1; //initlal text box count
    $(add_button).click(function(e){ //on add input button click
        e.preventDefault();
        if(x < max_fields){ //max input box allowed
            x++; //text box increment
            $(wrapper).append('<div><input type="text" name="mytext[]"/><a href="#" class="remove_field">Remove</a></div>'); //add input box
        }
    });
    
    $(wrapper).on("click",".remove_field", function(e){ //user click on remove text
        e.preventDefault(); $(this).parent('div').remove(); x--;
    })
});
</script>

<div class="input_fields_wrap">
    <button class="add_field_button">Add More Fields</button>
    <div><input type="text" name="mytext[]"></div>
</div>

[mac_gyver]

What I wanted first was a (add) button to add a new line for the order each time but I checked

 

 

dynamically adding form fields is typically used when adding empty fields for data entry, such as adding a set of fields to enter the data for each additional person in a family or adding a new car to your insurance coverage...

 

it is not used to reveal existing information, as that just adds extra clicks to the process and makes for a bad user experience on your site. if what you are wanting to do is make the select/option menu contain all the possible products (less ones that have already been picked up to that point), that's not what your code is currently doing and still makes for a 'click happy' bad user experience on your site.

 

if what you are wanting to do is provide a way of selecting among all your products, see my reply in your other thread.

Edited July 31, 2015 by mac_gyver

[Barand]

this code will demonstrate how to use ajax to change the three prices when a different product is selected

 

The form

<?php
$db = new mysqli(HOST,USERNAME,PASSWORD,'test');

function productOptions($db) 
{
    $sql = "SELECT id, name
            FROM product
            ORDER BY name";
    $res = $db->query($sql);
    $opts = "<option value=''>- choose -</option>\n";
    while (list($id,$name) = $res->fetch_row()) {
        $opts .= "<option value='$id'>$name</option>\n";
    }
    return $opts;
}


?>
<html>
<head>
<title>Sample</title>
<script src="//ajax.googleapis.com/ajax/libs/jquery/1.11.0/jquery.min.js"></script>
<script type='text/javascript'>
    $().ready(function() {
        $("#product").change(function() {
            var pid = $(this).val();
            $.get(
                "get_prod_prices.php" ,
                {"pid":pid} ,
                function(data) {
                    $.each(data, function(k,v) {
                        $("#price"+k).val(v);
                        if (v)
                            $("#label"+k).html("€"+v);
                        else
                            $("#label"+k).html("");
                    })
                },
                "JSON"
            )
        })
    })
</script>
</head>
<body>
<form>
    <select name="product" id="product" >
        <?=productOptions($db)?>
    </select>
    <input type="radio" name="price" id="price1" value="0"><label for="price1" id="label1">
    </label> 
    <input type="radio" name="price" id="price2" value="0"><label for="price2" id="label2">
    </label> 
    <input type="radio" name="price" id="price3" value="0"><label for="price2" id="label3">
    </label> 
</form>
</body>
</html>

the script call with AJAX request (get_prod_prices.php)

<?php
$db = new mysqli(HOST,USERNAME,PASSWORD,'test');


if (!isset($_GET['pid'])) {
    $prices = [1=>'','',''];
    exit(json_encode($prices));
}
$pid = intval($_GET['pid']);
$sql = "SELECT price1, price2, price3
        FROM product
        WHERE id = $pid";
$res = $db->query($sql);
list($p1, $p2, $p3) = $res->fetch_row();
$prices = [1=>$p1, $p2, $p3];
echo json_encode($prices); 
?>

[bambinou1980]

Thanks Barand I have unfortunately changed the way my script worked as I did not receive any email notifications of your reply :-(

 

I have opened another thread regarding the new logic I will use.

 

 

Thanks again for your hard work!

 

Ben