MySQL, PHP, Ajax to filter result set?

Question

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</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.

good call on making this a new question. We need to see the relevant code in regionbox.php in order to answer your question, please remove the link in order to not have people hack your site. — Johan
– Johan, Commented Jul 14, 2011 at 11:39
Oh, thank you I really appreciate it...where should I add the regionbox.php code? (sorry!) — scotia
– scotia, Commented Jul 14, 2011 at 11:45
Copy paste the relevant portions of it in your question., then select the code and press CTRL+K to make it look like code. — Johan
– Johan, Commented Jul 14, 2011 at 11:47
What do you want to do? Only show the elements that match the Dropdown values? Or "refetch" data from your database each time you pick a filter? — Antonio Laguna
– Antonio Laguna, Commented Jul 14, 2011 at 13:00
Sorry @Antonio - I had mmissed your post - yes, Id like to "refetch" Im osrry for my question not being clear!!! — scotia
– scotia, Commented Jul 14, 2011 at 13:45

Johan · Accepted Answer · 2011-07-14 13:06:30Z

3

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>"; 
?>

edited Jul 14, 2011 at 13:06

answered Jul 14, 2011 at 12:27

Johan

77.4k28 gold badges204 silver badges346 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

scotia Over a year ago

...Ive had a few error messages but Im certain Im just doing it wrong! - so am just working through them, I really appreciate your help...back soon!

scotia Over a year ago

...sorry - I cant see where Imm going wrong, the error message seems to refer to the echo at the end? Parse error: syntax error, unexpected T_ECHO, expecting T_WHILE in /home/palegall/public_html/Reassessment/regionbox.php on line 108 By putting the WHILE back at the end I can get the headers to show up with the drop down, but no initial data shows - sorry to be so stupid.

Johan Over a year ago

@scotia, left a do that doesn't belong in there, sorry 'bout that.

scotia Over a year ago

By removing the AND variable the data will show as before, but no functionality of the drop down?

Johan Over a year ago

@Scotia, the AND region = '$region' limits the result to only those regions. I don't know the layout and fieldnames in your database so you need to tweak the query to match your database, also you need to make sure the values in your dropdownlist are identical to the regions use in your database.

|

Antonio Laguna · Accepted Answer · 2011-07-14 13:50:13Z

3

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!

edited Jul 14, 2011 at 13:50

answered Jul 14, 2011 at 13:40

Antonio Laguna

9,3107 gold badges38 silver badges72 bronze badges

9 Comments

scotia Over a year ago

Dreadfully sorry - I had missed posts abovve & was trying to make sure I hadnt mixed up names etc - I really do appreciate everyone's help - scotia

Antonio Laguna Over a year ago

Oh don't worry @scotia I just wanted to write an useful reply :-)

scotia Over a year ago

I think I understand the purpose of the above code, but Im not sure about the jquery & am worried if I try something too different from what Im working with just now I'll confuse myself further - sorry guys you ended up with the plonker. I need all the data to show first, then have the dropdown filter the results. :)

Antonio Laguna Over a year ago

That's what this answer does! First fetch all the content and then filter it. Try it here jsfiddle.net/6psNF/1 and you'll see that. I ended with a jquery solution because it needs few lines of code and have a great performance and also because your question was tagged as jquery!

scotia Over a year ago

Oh, I wonder how that happened? I didnt tag it as such since I kno nothing about them? Apologies. I had a look at the link you suggested so will go back & now try to make it for my tables & try it out. Many thanks again ot everyone trying to help me with this.

|

Collectives™ on Stack Overflow

MySQL, PHP, Ajax to filter result set?

2 Answers 2

7 Comments

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related