PHP, AJAX & MySQL - updating page from multiple <select>

Question

I have a current set up of being able to select a minimum price to display from, and the page will update accordingly with no issues. However, I also want to be able to set a maximum price to display from a different <select> (and in future, other options such as product types too). However, the method that I am using (http://www.w3schools.com/php/php_ajax_database.asp) does not work when I modify it to pass through 2 values, it will not display any results. I think it is failing when trying to actually get hold of the maximum value, but I do not know at which point (or why) this is failing. Any help would be greatly appreciated! I have spent quite some time with trial and error, and nothing else tutorial-wise has been of much use. If you are able to help, please explain your answer as I am new to AJAX/JS in general.

HTML:

<select id="price-from" onchange="setPrice(this.value)">
    <option selected value="500">£500</option>
    <option value="1000">£1,000</option>
    <option value="2000">£2,000</option>
    <option value="3000">£3,000</option>
    <option value="4000">£4,000</option>
    <option value="5000">£5,000</option>
    <option value="6000">£6,000</option>
    <option value="7000">£7,000</option>
    <option value="8000">£8,000</option>
    <option value="9000">£9,000</option>
    <option value="10000">£10,000</option>
    <option value="20000">£20,000</option>
</select>
<p id="to">To</p>
<select id="price-to" onchange="setPrice(this.value)">
    <option value="500">£500</option>
    <option value="1000">£1,000</option>
    <option value="2000">£2,000</option>
    <option value="3000">£3,000</option>
    <option value="4000">£4,000</option>
    <option value="5000">£5,000</option>
    <option value="6000">£6,000</option>
    <option value="7000">£7,000</option>
    <option value="8000">£8,000</option>
    <option value="9000">£9,000</option>
    <option value="10000">£10,000</option>
    <option selected value="20000">£20,000</option>
</select>

JavaScript:

function setPrice(str) {
if (str == "") {
    document.getElementById("section1").innerHTML = "<p>No Results</p>";
    return;
    alert("error 1");
} else { 
    if (window.XMLHttpRequest) {
        // code for IE7+, Firefox, Chrome, Opera, Safari
        xmlhttp = new XMLHttpRequest();
    } else {
        // code for IE6, IE5
        xmlhttp = new ActiveXObject("Microsoft.XMLHTTP");
    }
    xmlhttp.onreadystatechange = function() {
        if (this.readyState == 4 && this.status == 200) {
            document.getElementById("section1").innerHTML = this.responseText;
        }
    };

    xmlhttp.open("GET","priceQuery.php?min=" +str + "max=" +str, true);
    xmlhttp.send();
  }
}

PHP:

<?php
    $con = mysqli_connect('localhost','root','','nbgardens');
    if (!$con) {
    die('Could not connect: ' . mysqli_error($con));
    }

    mysqli_select_db($con,"nbgardens");
    $min = (isset($_GET['min']) ? $_GET['min'] : null);
    $max = (isset($_GET['max']) ? $_GET['max'] : null);
    $sql="SELECT * FROM products WHERE product_price >= '".$min."' AND product_price<= '".$max."'";
    $result = mysqli_query($con,$sql);

    while($row = mysqli_fetch_assoc($result)){
        echo"<div class='browse-image'>
            <a href='#'>
            <p class='price'>£" . $row['product_price'] . "</p>
            <img src='" . $row['product_img'] . "' width='200px' class='thumb'>
            <figcaption>" . $row['product_title'] . "</figcaption></a></div>";
        }                           
    mysqli_close($con);
?>

use mysqli_real_escape_string to avoid sql injection or use PDO — Shibon
– Shibon, Commented Sep 23, 2016 at 9:50

Mouradif · Accepted Answer · 2016-09-23 10:25:34Z

1

There are a few problems with your current code :

Set two different variables

When you select price-from or when you select price-to you launch the same function with the same one-parameter. The function has no idea if the parameter you give it is a price-from or a price-to. One of many solutions for that would be not to give it any parameter and ask it to fetch for the selected values

Separate URL parameters with a `&`

With the current url you give your XHR Object, your request looks like this (let's say str = "1000") :

priceQuery.php?min=1000max=1000

In your PHP code, your $_GET['min'] equals the string "1000max=1000" and $_GET['max'] is never set

For your current PHP code, I see nothing wrong so here's

My fix :

HTML :

<select id="price-from" onchange="setPrice()">
    <option selected value="500">£500</option>
    <option value="1000">£1000</option>
    <option value="2000">...</option>
</select>
<p id="to">To</p>
<select id="price-to" onchange="setPrice()">
    <option value="8000">...</option>
    <option value="10000">£10,000</option>
    <option selected value="20000">£20,000</option>
</select>

Javascript :

function setPrice() {
    var minField = document.getElementById('price-from'),
        maxField = document.getElementById('price-to');

    var min = minField.options[minField.selectedIndex].value,
        max = maxField.options[maxField.selectedIndex].value;

    // We make sure we have both values and that max is greater than min
    if (min == "" || max == "" || parseInt(max) < parseInt(min)) {
        document.getElementById("section1").innerHTML = "<p>No Results</p>";
        return;
        alert("error 1");
    } else { 
        if (window.XMLHttpRequest) {
            // code for IE7+, Firefox, Chrome, Opera, Safari
            xmlhttp = new XMLHttpRequest();
        } else {
            // code for IE6, IE5
            xmlhttp = new ActiveXObject("Microsoft.XMLHTTP");
        }
        xmlhttp.onreadystatechange = function() {
            if (this.readyState == 4 && this.status == 200) {
                document.getElementById("section1").innerHTML = this.responseText;
            }
        };

    // Don't forget the '&' to separate the two URL parameters
    xmlhttp.open("GET","priceQuery.php?min=" + min + "&max=" + max, true);
    xmlhttp.send();
    }
}

answered Sep 23, 2016 at 10:25

Mouradif

2,7342 gold badges26 silver badges40 bronze badges

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

3 Comments

Mcar49 Over a year ago

I think I understand what you have done here, however it does not appear to work. PHP is not throwing up any errors so I assume it may be just an issue with passing the values into the query string?

Mcar49 Over a year ago

Nevermind, it is working now. However it does not display anything until the values are changed, how would I set it to display everything by default?

Mouradif Over a year ago

You since by default the minimum price is set as price-from and the maximum price is set as price-to you could simply call setPrice() on page load (<body onload="setPrice()"> or in JS : window.addEventListener('load', function() { setPrice(); });)

Kacper Polak · Accepted Answer · 2016-09-23 09:45:06Z

0

You overwrite min/max with next selection. Here's right code:

function setPrice() {
  var min = parseInt(document.getElementById('price-from').value),
      max = parseInt(document.getElementById('price-to').value);
  if(max < min) {
    var maxLocal = max;
    max = min;
    min = maxLocal;
  }
    if (window.XMLHttpRequest) {
        // code for IE7+, Firefox, Chrome, Opera, Safari
        xmlhttp = new XMLHttpRequest();
    } else {
        // code for IE6, IE5
        xmlhttp = new ActiveXObject("Microsoft.XMLHTTP");
    }
    xmlhttp.onreadystatechange = function() {
        if (this.readyState == 4 && this.status == 200) {
            document.getElementById("section1").innerHTML = this.responseText;
        }
    };
    xmlhttp.open("GET","priceQuery.php?min=" +min + "max=" +max, true);
    xmlhttp.send();
}

<select id="price-from" onchange="setPrice()">
    <option selected value="500">£500</option>
    <option value="1000">£1,000</option>
    <option value="2000">£2,000</option>
    <option value="3000">£3,000</option>
    <option value="4000">£4,000</option>
    <option value="5000">£5,000</option>
    <option value="6000">£6,000</option>
    <option value="7000">£7,000</option>
    <option value="8000">£8,000</option>
    <option value="9000">£9,000</option>
    <option value="10000">£10,000</option>
    <option value="20000">£20,000</option>
</select>
<p id="to">To</p>
<select id="price-to" onchange="setPrice()">
    <option value="500">£500</option>
    <option value="1000">£1,000</option>
    <option value="2000">£2,000</option>
    <option value="3000">£3,000</option>
    <option value="4000">£4,000</option>
    <option value="5000">£5,000</option>
    <option value="6000">£6,000</option>
    <option value="7000">£7,000</option>
    <option value="8000">£8,000</option>
    <option value="9000">£9,000</option>
    <option value="10000">£10,000</option>
    <option selected value="20000">£20,000</option>
</select>

answered Sep 23, 2016 at 9:45

Kacper Polak

1,41115 silver badges24 bronze badges

3 Comments

Mouradif Over a year ago

You wont get the select's value like this

Kacper Polak Over a year ago

@KiJéy Are you sure? w3schools.com/jsref/prop_select_value.asp

Mouradif Over a year ago

Oh weird... I'm sure I tried this and it didn't work. but I guess you're right : jsfiddle.net/L62dwcsb

Pranav Bhatt · Accepted Answer · 2016-09-23 09:49:06Z

You should try This:

<select id="price-from" onchange="setPrice(this.value)">
    <option selected value="500">£500</option>
    <option value="1000">£1,000</option>
    <option value="2000">£2,000</option>
    <option value="3000">£3,000</option>
    <option value="4000">£4,000</option>
    <option value="5000">£5,000</option>
    <option value="6000">£6,000</option>
    <option value="7000">£7,000</option>
    <option value="8000">£8,000</option>
    <option value="9000">£9,000</option>
    <option value="10000">£10,000</option>
    <option value="20000">£20,000</option>
</select>
<p id="to">To</p>
<select id="price-to" onchange="setPrice(this.value)">
    <option value="500">£500</option>
    <option value="1000">£1,000</option>
    <option value="2000">£2,000</option>
    <option value="3000">£3,000</option>
    <option value="4000">£4,000</option>
    <option value="5000">£5,000</option>
    <option value="6000">£6,000</option>
    <option value="7000">£7,000</option>
    <option value="8000">£8,000</option>
    <option value="9000">£9,000</option>
    <option value="10000">£10,000</option>
    <option selected value="20000">£20,000</option>
</select>

For Javascript you should try jQuery like this:

<script type="text/javascript">
$(document).ready(function(){
var minValue,maxValue;
    $("#price-from").on("change",function(){
    minValue=$(this).val();
    });
    $("#price-to").on("change",function(){
    maxValue=$(this).val();
    });
    if(minValue!='')
        $.post( "priceQuery.php", { min: minValue, max: maxValue  } );
});
</script>

And your PHP Code goes like this:

<?php
    $con = mysqli_connect('localhost','root','','nbgardens');
    if (!$con) {
    die('Could not connect: ' . mysqli_error($con));
    }

    mysqli_select_db($con,"nbgardens");
    $min = (isset($_POST['min']) ? $_POST['min'] : null);
    $max = (isset($_POST['max']) ? $_POST['max'] : null);
    $sql="SELECT * FROM products WHERE product_price >= '".$min."' AND product_price<= '".$max."'";
    $result = mysqli_query($con,$sql);

    while($row = mysqli_fetch_assoc($result)){
        echo"<div class='browse-image'>
            <a href='#'>
            <p class='price'>£" . $row['product_price'] . "</p>
            <img src='" . $row['product_img'] . "' width='200px' class='thumb'>
            <figcaption>" . $row['product_title'] . "</figcaption></a></div>";
        }                           
    mysqli_close($con);
?>

Collectives™ on Stack Overflow

PHP, AJAX & MySQL - updating page from multiple <select>

3 Answers 3

Set two different variables

Separate URL parameters with a `&`

My fix :

3 Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Set two different variables

Separate URL parameters with a &

My fix :

3 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related

Separate URL parameters with a `&`