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!