Update the content on the webpage to display the SQL data generated by selecting options from various dropdown

Question

Update the content on the webpage to display the SQL data generated by selecting options from various dropdown

My database table is structured like this:

  Name  │ Favorite Color │  Age  │  Pet
────────┼────────────────┼───────┼───────
 Rupert │     Green      │  21   │  Cat
  Mike  │      Red       │  19   │  Dog
 Rachel │     Purple     │  24   │  Cat
 Thomas │      Red       │  21   │  Fish
  ...   |      ...       |  ...  |  ...

I have two dropdowns:

<select>
    <option selected="selected" disabled="disabled">Choose Pet</option>
    <option>Cat</option>
    <option>Dog</option>
    <option>Fish</option>
</select>

<select>
    <option selected="selected" disabled="disabled">Choose Color</option>
    <option>Red</option>
    <option>Green</option>
    <option>Purple</option>
</select>

I am looking to display the individual who matches a specific combination from the two dropdowns.

//if cat and green selected, print Rupert
//if fish and red selected, print Thomas
//etc

I don't have a clear plan on how to approach this. I want to retrieve information through SQL rather than hardcoding it into the page so that any changes in names do not require rewriting code.

$name = $db->prepare("
    SELECT name
        FROM people
        WHERE color = :col
        AND pet = :pet
");
$name->execute(array(
    ':col' => value from one dropdown,
    ':pet' => value from another dropdown
));

This is an example of an SQL query that would be triggered on dropdown change, ensuring that the accurate data is fetched based on the selected combination.

javascript jquery html sql ajax

Answer 1

Answer №1

If you anticipate that the data set will grow significantly, it may be more efficient to implement a remote search feature. In this scenario, selections made by users can be sent to a PHP script which will then retrieve and display the corresponding name.

Alternatively, if the data set remains small, you can directly embed the rows into the page as a JSON object. JavaScript can then be used to lookup the selections and dynamically change the displayed names accordingly.

In the case of embedding the data set, you could structure your code like this:

Create JSON Object

var selections = {
  'Cat-Green': 'Rubert',
  'Dog-Red': 'Mike',
  ... 
};

To facilitate easy retrieval of values from selects, assign them IDs like so:

Add HTML Markup

<select id="pet" onchange="update();">
    <option selected="selected" disabled="disabled">Choose Pet</option>
    <option>Cat</option>
    <option>Dog</option>
    <option>Fish</option>
</select>

<select id="color" onchange="update();">
    <option selected="selected" disabled="disabled">Choose Color</option>
    <option>Red</option>
    <option>Green</option>
    <option>Purple</option>
</select>

<span id="result"></span>

Finally, define an update function in JavaScript to populate the result based on selections:

JavaScript Function

function update() {
  var key = $("#pet").val() + '-' + $("#color").val();
  $("#result").text(selections[key]);
}

On the server side, when serving the page, parse the JSON object as shown below:

Complete Script with PHP

<?php
// establish database connection, then
$query  = "SELECT * FROM `favorites` LIMIT 50"; // just to be safe
$result = mysqli_query($link, $query);
$array  = array(); // initialize empty array
while($row=mysqli_fetch_assoc($result)) {
  // Name  │ Favorite Color │  Age  │  Pet
  $array[$row['Pet'] . '-' . $row['Color']] = $row['Name'];
}
$json = json_encode($array); // convert array to JSON string
// closing PHP tag - now proceed to output HTML, JS, and JSON
?>
<!DOCTYPE html>
<html>
<head>
  ...
  <script src=" ... remember to include jQuery ... ></script>
  <script>
    var selections = <?php echo $json; ?>;
    ...
    function update ...
  </script>
</head>
<body>
  <select id="pet" onchange="update();">
  ...
</body>
</html>

Check out this jsbin link for hardcoded JSON data.

Answer 2

If you anticipate that the data set will grow significantly, it may be more efficient to implement a remote search feature. In this scenario, selections made by users can be sent to a PHP script which will then retrieve and display the corresponding name.

Alternatively, if the data set remains small, you can directly embed the rows into the page as a JSON object. JavaScript can then be used to lookup the selections and dynamically change the displayed names accordingly.

In the case of embedding the data set, you could structure your code like this:

Create JSON Object

var selections = {
  'Cat-Green': 'Rubert',
  'Dog-Red': 'Mike',
  ... 
};

To facilitate easy retrieval of values from selects, assign them IDs like so:

Add HTML Markup

<select id="pet" onchange="update();">
    <option selected="selected" disabled="disabled">Choose Pet</option>
    <option>Cat</option>
    <option>Dog</option>
    <option>Fish</option>
</select>

<select id="color" onchange="update();">
    <option selected="selected" disabled="disabled">Choose Color</option>
    <option>Red</option>
    <option>Green</option>
    <option>Purple</option>
</select>

<span id="result"></span>

Finally, define an update function in JavaScript to populate the result based on selections:

JavaScript Function

function update() {
  var key = $("#pet").val() + '-' + $("#color").val();
  $("#result").text(selections[key]);
}

On the server side, when serving the page, parse the JSON object as shown below:

Complete Script with PHP

<?php
// establish database connection, then
$query  = "SELECT * FROM `favorites` LIMIT 50"; // just to be safe
$result = mysqli_query($link, $query);
$array  = array(); // initialize empty array
while($row=mysqli_fetch_assoc($result)) {
  // Name  │ Favorite Color │  Age  │  Pet
  $array[$row['Pet'] . '-' . $row['Color']] = $row['Name'];
}
$json = json_encode($array); // convert array to JSON string
// closing PHP tag - now proceed to output HTML, JS, and JSON
?>
<!DOCTYPE html>
<html>
<head>
  ...
  <script src=" ... remember to include jQuery ... ></script>
  <script>
    var selections = <?php echo $json; ?>;
    ...
    function update ...
  </script>
</head>
<body>
  <select id="pet" onchange="update();">
  ...
</body>
</html>

Check out this jsbin link for hardcoded JSON data.

Update the content on the webpage to display the SQL data generated by selecting options from various dropdown

Answer №1

Similar questions

Axios - Error: Promise Rejected - The request was unsuccessful with a 500 status code

Guide on incorporating a thymeleaf custom dialect in an HTML script

Efficient Error Handling in Next.JS with Apollo GraphQL Client

Is Ursina the right choice for web development?

The Twitch API is providing inaccurate channel information

I'm having trouble getting rid of this stubborn loader on the website

Having trouble getting rid of the border-bottom?

Issue with login form in IONIC: Form only functions after page is refreshed

Skip nodes in Polymer 1.0 by using ExcludeLocalNames

Does the language setting on a browser always stay consistent?

Unraveling Complex JSON Structures in React

Conceal elements with a single click of a button

What is the best way to export all tables from MySQL to a CSV file?

Execute an AJAX call to remove a comment

Can you explain the purpose of the search_/> tag used in this HTML code?

Utilizing TypeScript namespaced classes as external modules in Node.js: A step-by-step guide

I'm perplexed as to why my JavaScript code isn't successfully adding data to my database

Simple method to send information from an MVC controller to jQuery.ajax

Why do I keep encountering a null window object issue while using my iPhone?

Is it possible to show elements from an ngFor loop just once when dealing with a two-dimensional string array in a display?