Create SQL Query with <options> tag and echo result?

user_16315703 0
Viewed 143

I want to set up a page where you can see how much it will cost to repair your broken mobile phone. I have a database with all the issues, phones and prices. Now I want to print the price after the user seletected a phone and the issue.

So this is my question: How do I now get the selection the user has done and use it in php to create the correct sql query in order to echo the price?

Database (Numbers are the price):

issue iphone_8 iphone_xs
battery 25 50
waterdamage 60 100

Design:

This is how it is supposed to look like. So you can better understand what I am trying to achieve.

HTML:

Currently looks like this:

<select>
    <option>Select device</option>
    <option>iPhone 8</option>
    <option>iPhone XS</option>
</select>
<select>
    <option>Select issue</option>
    <option>Battery</option>
    <option>Water Damage</option>
</select>

PHP:

Selecting data works fine. I am using the default w3c layout to select data with php out of my database: https://www.w3schools.com/php/php_mysql_select.asp

mysql sql html php database
1 Answers

You can do this by adding OnChange event() on <select> tag, and submit() the form. Next do the query, get the price and update your price value.

This is a sample code:

  1. PHP Code
//If phone and screen both selected
if (!empty($_POST['phone']) && !empty($_POST['issue']) ){

    /* DO YOUR QUERY */

    $price = ...;

}

What this sample code do: As soon as the form is submitted, it will go through the PHP from top of the page, check if both "phone" and "issue" are selected, if yes, do PHP Query and get resulting price.

  1. HTML Form
<form method="post" action="">
    <select name="phone" >
        <option></option>
        <option value="samsung" <?php if($_POST["phone"]=="samsung")echo "selected"; ?>>Samsung</option>
        <option value="sony" <?php if($_POST["phone"]=="sony")echo "selected"; ?>>Sony</option>
    </select>

    <select name="issue" onchange="this.form.submit()">
        <option></option>
        <option value="screen" <?php if($_POST["issue"]=="screen")echo "selected"; ?> >Screen</option>
            <option value="battery" <?php if($_POST["issue"]=="battery")echo "selected"; ?>>Battery</option>
    </select>
</form>

What this sample code do: As soon as the user select an issue, it will trigger OnChange() event and submit() the form to the same page. The php code inside <option> tag is used to maintain the selected option after form is submitted.

  1. JavaScript
<script type="text/javascript">
    var price = <?php echo $price ?>;
    document.getElementById('price').innerHTML = price;
</script>

What this sample code do: You can pass PHP variable to JavaScript by using echo. Then, set innerHTML to price.

To understand it better, the order of the event is 2, 1, 3.

  • First: user select an option from HTML form.
  • Second, form submit, will check the PHP on top of your code.
  • Last, JavaScript will change your Price label to the value you want.

Note: This is just a sample, hope it gives you an idea how to achieve this, let me know if this is what you wanted.

To achieve this with AJAX, use this method:

this is how your form.php should look like:

<!DOCTYPE html>
<html>
<head>
    <title>Form Select with AJAX</title>
    <script src="https://code.jquery.com/jquery-3.6.0.js" integrity="sha256-H+K7U5CnXl1h5ywQfKtSj8PCmoN9aaq30gDh27Xc0jk=" crossorigin="anonymous"></script> 
</head>
<body>
    <select name="phone" id="phone">
        <option value="">Select device</option>
        <option value="iPhone 8">iPhone 8</option>
        <option value="iPhone XS">iPhone XS</option>
    </select>
    <select name="issue" id="issue">
        <option value="">Select issue</option>
        <option value="Battery">Battery</option>
        <option value="Water Damage">Water Damage</option>
    </select>

    <!-- DIV to show the Price-->
    <div id="price"></div>

    <script type="text/javascript">
        function myAjaxCall (dataString){
            $.ajax({ 
              type: "POST", 
              url: "get-data.php", 
              data: dataString, 
              success: function(result){ 
                $("#price").html(result); 
              }
            });
        }

      $(document).ready(function(){ 
        $("#issue, #phone").change(function(event){ /* WHEN YOU CHANGE/SELECT ISSUE OR PHONE */
            //Phone select changed
            if ($(event.target).attr('id') == 'phone'){
                // alert('phone changed');
                var phone = $(this).val();
                var issue = document.getElementById('issue').value;
            }
            //Issue select changed
            else if ($(event.target).attr('id') == 'issue'){
                // alert ('issue changed');
                var issue= $(this).val(); 
                var phone = document.getElementById('phone').value;
            } 
            var dataString = "issue="+issue+"&phone="+phone; 
            myAjaxCall(dataString);
        });
      });
    </script>

</body>
</html>

And this is how your get-data.php should look like:

<?php
if(!empty($_POST["phone"]) && !empty($_POST["issue"])){
  /* DO YOUR QUERY HERE AND GET THE OUTPUT YOU WANT */
  
  if ($_POST['phone'] == 'iPhone 8' && $_POST['issue'] == 'Battery'){
    $price = 5;
  }
  else if ($_POST['phone'] == 'iPhone 8' && $_POST['issue'] == 'Water Damage'){
    $price = 28;
  }
  else if ($_POST['phone'] == 'iPhone XS' && $_POST['issue'] == 'Water Damage'){
    $price = 10;
  }
  else if ($_POST['phone'] == 'iPhone XS' && $_POST['issue'] == 'Battery'){
    $price = 25;
  }
  else $price = "";

  echo $price;
}
else echo "";
?>

Refer to this answer: Do php query on select change.

user_93879840
Related