Geek Answers Handbook

MySQL, PHP, Ajax to filter a result set?

I am new to MySQL and would like to "filter" the result set by region. I asked a similar question earlier, but I do not know if I should create a new question or continue it, sorry if I am wrong.

I looked at http://www.w3schools.com/ajax/tryit.asp?filename=tryajax_database and I think it will be perfect, but I'm not sure how to make it work, or how I can make it "update" the results.

Ideally, I assume that the drop-down box under the region will look neat - but I have not succeeded - I am really completely limited by my understanding, can anyone help? (thanks to the previous people who also helped with the first part!)

This is all I have so far (to give an idea of ​​what I would like to filter out).

Thanks a lot, scotia - below - regionbox.php file

...

<script type="text/javascript"> function selectRegion(str) { var xmlhttp; if (str=="") { document.getElementById("region").innerHTML=""; return; } 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 (xmlhttp.readyState==4 && xmlhttp.status==200) { document.getElementById("region").innerHTML=xmlhttp.responseText; } } xmlhttp.open("GET","regionbox.php?q="+str,true); xmlhttp.send(); } </script> </head> <body> <P> <table class="dbTable"> <tr> <tr><th>Commodity</th> <th><form action="regionbox.php"> <select name="region" onchange="selectRegion(this.value)"> <option value="">Select Region</option> <option value="E. Midlands">E. Midlands</option> <option value="Gtr. London">Gtr. London</option> <option value="North East">North East</option> <option value="North West">North West</option> <option value="Scotland">Scotland</option> <option value="South East">South East</option> <option value="South West">South West</option> <option value="W. Midlands">W. Midlands</option> <option value="Wales">Wales</option> </select> </form></th> <th>Member</th> <th>Size</th> <th>Price</th> <th>Date Posted</th> </tr> <?php $link = mysql_connect('localhost', '', ''); $db_selected = mysql_select_db('palegall_newTrader', $link); if (!$db_selected) { die ('cant find newTrader' . mysql_error()); } $region = mysql_real_escape_string($_POST['region']); $query = mysql_query("SELECT * FROM `sell` WHERE `commodity` = 'paper' ORDER BY `price`") or die( mysql_error() ); echo '<table class="dbTable">'; while ($row = mysql_fetch_assoc($query)) { echo '<tr><td>'.$row['commodity'].'</td> <td>'.$row['region'].'</td> <td>'. $row['member'].'</td> <td>'.$row['size'].'</td> <td>'. $row['price'].'</td> <td>'.$row['posted'].'</td> </tr>'; } echo "</table>"; ?> </body></html>

I deleted the bits. I hope everything is in order.

+4
source share
2 answers

Change this code

 $query = mysql_query("SELECT * FROM `sell` WHERE `commodity`='Paper' ORDER BY `price`") or die( mysql_error() ); $row=mysql_fetch_assoc($query); do { echo'<table class="dbTable">'; echo '<tr><td>'.$row['commodity'].'</td> <td>'.$row['region'].'</td> <td>'. $row['member'].'</td> <td>'.$row['size'].'</td> <td>'. $row['price'].'</td> <td>'.$row['posted'].'</td> </tr>'; } while($row = mysql_fetch_assoc($query)); echo "</table>"; ?>

IN:

 $region = mysql_real_escape_string($_POST['region_Name']); //For debugging: echo $region $query = mysql_query("SELECT * FROM sell WHERE commodity = 'paper' AND region = '$region' ORDER BY price") or die( mysql_error() ); echo '<table class="dbTable">'; //echo the rows in a while loop while ($row = mysql_fetch_assoc($query)) { echo '<tr><td>'.$row['commodity'].'</td> <td>'.$row['region'].'</td> <td>'. $row['member'].'</td> <td>'.$row['size'].'</td> <td>'. $row['price'].'</td> <td>'.$row['posted'].'</td> </tr>'; } echo "</table>"; ?>
+3
source

Well, since you are not responding, I will put in a little script that I often use and get a neat effect.

He needs jQuery to be present on your website

 $('select').change(function(){ var region = $(this).val(); $("tr").each(function () { if ($(this).text().search(new RegExp(region, "i")) < 0) { $(this).hide(); } else { $(this).show(); } }); });

You can see how this works here http://jsfiddle.net/6psNF/1/

You must adapt your table code so, for example, a table can have an identifier, and a line with a code can have a class. A little code with a tiny example:

 <table id="trader"> <tbody> <tr> <td>Random</td> <td class="region">E. Midlands</td> <td>Member</td> <td>Size</td> <td>Price</td> <td>Date</td> <td>Posted</td> </tr> </tbody>

So, the code I wrote may end up like

 $('select').change(function(){ var region = $(this).val(); $("#trader tr").each(function () { if ($(this).text().find(".region").search(new RegExp(region, "i")) < 0) { $(this).hide(); } else { $(this).show(); } }); });

Increase productivity on pages with a lot of content or super large tables!

+3
source

More articles:


All Articles