Last active
December 17, 2015 00:48
-
-
Save phirework/5523188 to your computer and use it in GitHub Desktop.
Look up values in a MySQL table using PHP/AJAX with two dropdown menus
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| +-------+----------+----------+----------+ | |
| | route | first | second | distance | | |
| +-------+----------+----------+----------+ | |
| | 1 | London | Madrid | 3 | | |
| | 2 | London | New York | 8 | | |
| | 3 | London | Chicago | 7 | | |
| | 4 | Madrid | New York | 9 | | |
| | 5 | Madrid | Chicago | 6 | | |
| | 6 | New York | Chicago | 5 | | |
| +-------+----------+----------+----------+ | |
| The Database, for Reference's sake. I decided to go with this because the traditional | |
| Excel method of having cities down the first column as well as across the top would | |
| generate too much duplicate data.... and I couldn't think of anything more clever. |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <html> | |
| <head> | |
| <title>Lane mapping</title> | |
| </head> | |
| <body> | |
| <?php | |
| require_once('config.php'); // This file contains all the db connection data | |
| ?> | |
| <!-- This is the actual AJAX function --> | |
| <script type="text/javascript"> | |
| // Create empty variables in preparation for the two values from the two dropdown menus | |
| var first; | |
| var second; | |
| function firstly(x) { | |
| // Grab the value from the first dropdown and set it to 'first' | |
| first = x.value; | |
| if (typeof second != 'undefined') { // If 'second' isn't undefined, run the 'route' function with the two variables | |
| route(first, second); | |
| } // Otherwise do nothing | |
| } | |
| // This is the same function but for the second variable | |
| function secondly(x) { | |
| second = x.value; | |
| if (typeof first != 'undefined') { | |
| route(first, second); | |
| } | |
| } | |
| // This is the AJAX function that takes two parameters | |
| function route(first, second) { | |
| var xmlhttp; | |
| 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 the XMLHttpRequest is successful | |
| if (xmlhttp.readyState==4 && xmlhttp.status==200) | |
| { | |
| // Then populate div id="distance" with the responseText | |
| document.getElementById("distance").innerHTML=xmlhttp.responseText; | |
| } | |
| } | |
| // Actually send the data using the variables assigned with firstly() and secondly() | |
| xmlhttp.open("GET","route.php?first="+first+"&second="+second,true); | |
| xmlhttp.send(); | |
| } | |
| </script> | |
| <?php | |
| // This creates the two dropdown menus based on what's available in the database | |
| $select = "SELECT DISTINCT(first) AS first FROM distances"; | |
| $result = mysql_query($select); | |
| if (!$result) { die ('Error: ' . mysql_error()); }; | |
| // Store a distinct list of all cities in the table in an array | |
| $cities = array(); | |
| $count = 0; | |
| while ($row = mysql_fetch_array($result)) { | |
| $count++; | |
| $cities[$count] = $row['first']; | |
| } | |
| ?> | |
| <form> | |
| <!-- When an option is selected, it runs firstly() --> | |
| <select name="first" onchange="firstly(this)"> | |
| <option selected value=""></option> | |
| <?php | |
| // Loop through all the entries in $cities and create an option for each one | |
| for ($i = 1; $i <= count($cities); ++$i) { | |
| echo "<option value='" . $cities[$i] . "'>" . $cities[$i] . "</option>"; | |
| } | |
| ?> | |
| </select> | |
| <br /> | |
| <!-- Repeat the process for the destination city --> | |
| <select name="second" onchange="secondly(this)"> | |
| <option selected value=""></option> | |
| <?php | |
| for ($i = 1; $i <= count($cities); ++$i) { | |
| echo "<option value='" . $cities[$i] . "'>" . $cities[$i] . "</option>"; | |
| } | |
| ?> | |
| </select> | |
| <br /> | |
| </form> | |
| <div id="distance"></div> | |
| </body> | |
| </html> | |
| <?php mysql_close($connected); ?> |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <?php | |
| require_once('config.php'); | |
| // Grab the data from GET and assign them to variables | |
| $first = $_GET['first']; | |
| $second = $_GET['second']; | |
| if ($first === $second) { | |
| echo "These two cities are the same: '" . $first . "' and '" . $second . "'."; | |
| } else { | |
| // Look up entries in the database --> this looks convoluted because I wanted | |
| // to make sure it worked bi-directionally - ie I only wanted one entry between | |
| // each pair of cities, but still be able to look up its inverse relationship | |
| $select = "SELECT * FROM distances WHERE (first='$first' OR second='$first') AND (first='$second' OR second='$second');"; | |
| $result = mysql_query($select); | |
| if (!$result) { die ('Error: ' . mysql_error()); }; | |
| // Print out the result in a human-readable format | |
| while($row = mysql_fetch_array($result)) { echo $first . " to " . $second . " will take " . $row['distance'] . " hours.<br />"; } | |
| } | |
| ?> |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment