MySQL, PHP, Ajax to filter result set?
I'm new to MySQL & would like to "filter" a result set by region. I have previously asked a similar question but I don't know if I'm meant to create a new question or continue that one, sorry if I've got this wrong.
I've looked at the http://www.w3schools.com/ajax/tryit.asp?filename=tryajax_database & think it would be ideal, but I'm not sure how to get it working, or how I can get it to "update" the results.
Ideally I suppose a drop down box below the region would look tidy - but, I'm not having any success - I really am totally limited to my understanding, can anyone help? (thank you to previous folks who helped with the first part too!!)
This is all I have so far, (to give an idea of what I'd like to filter).
Many thanks, scotia - below is the 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<开发者_如何学C;/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've stripped out bits. I hope this is OK.
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>"; ?>
Into:
$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>";
?>
Ok, since you're not answering I'll put a little script which I often use and have a neat effect.
It needs jQuery to be present in 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 may see this working here http://jsfiddle.net/6psNF/1/
You should adapt your table code so, for example table could have an ID and the row with the code could have a class. Tiny code with 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 could end 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();
}
});
});
Increasing performance in pages with a lot of content or super big tables!
加载中,请稍侯......
精彩评论