Jump to content
Larry Ullman's Book Forums
Sign in to follow this  
CFitz

What'S The Best Way To Filter Out Certain Query Results?

Recommended Posts

My brain must be dead, because while I read the book, I am having trouble devising a solution to this problem.

 

I would like to include a button on a page of query results that allows users to filter the results to those that match only certain criteria. I realize that it's not a good idea to run a whole new query just for this filtering, so I have been trying  to look for a solution, but to no avail.

 

Any tips? I'm sorry for being vague, I'd just rather not give away too much about the site's structure. Thank you!

Share this post


Link to post
Share on other sites

You need to give us some more if you need specialized help. Change data to something similar if you think it's sensitive.

 

Other than that, you might use simple solutions such a in_array() or other solutions to check the data. Just save the results you fetch to an array and filter that.

  • Upvote 1

Share this post


Link to post
Share on other sites

CFitz, hello, and welcome to the forums.

I more or less agree with Antonio that without more info, it's hard for us to help. For one, you question is not very clear.

 

From what (I think) I can understand though, I will say that performing an extra query to apply an additional filter is not bad or inappropriate. If that will work in your situation, then I say go with it.

Share this post


Link to post
Share on other sites

CFitz, hello, and welcome to the forums.

I more or less agree with Antonio that without more info, it's hard for us to help. For one, you question is not very clear.

 

From what (I think) I can understand though, I will say that performing an extra query to apply an additional filter is not bad or inappropriate. If that will work in your situation, then I say go with it.

 

 

You need to give us some more if you need specialized help. Change data to something similar if you think it's sensitive.

 

Other than that, you might use simple solutions such a in_array() or other solutions to check the data. Just save the results you fetch to an array and filter that.

 

I appreciate the responses. I see that it is unreasonable to ask for assistance with such a vague question, so I shall elaborate!

 

I am currently running the following query (I sort the results via a pulldown menu):

 

$q = "SELECT title, description, image, x_id FROM x_representatives ORDER BY $order_by LIMIT $start, $display";
$r = @mysqli_query ($mysqli, $q);
 
Before navigating to this web page, users fill out a form that sets their own "x_id" to a certain value. I would like them to be able to hit a checkbox that allows them to see only results that match their "x_id" to those of the query. I was thinking along the lines of a prepared statement but am having trouble.
 
I appreciate the help, and please let me know if I need to further explain.  :) (HartleySan, thank you for the welcome, and if running multiple queries will have no noticeable effect on performance I may just go that route!)

Share this post


Link to post
Share on other sites
Thank you for the clarification.

In this case, using prepared statements will not give you any performance benefit, because whether the check box is checked or not, you'll only need to perform one query. The query you perform though will differ, depending on whether the check box was selected or not.

 

Without seeing your actual code, I'm kind of guessing here, but I think the following will suffice:

 



<?php
  
  // Establish DB connection here.
  // Also, you should validate all received values. I am leaving that out for brevity though.
  
  $order_by = $_POST['order_by'];
  
  $start = $_POST['start'];
  
  $display = $_POST['display'];
  
  $filter_on_own_id = isset($_POST['show_results_for_only_own_id']) ? true : false; // This is for the check box.
  
  if ($filter_on_own_id) {
    
    $x_id = $_POST['x_id'];
    
    $q = "SELECT title, description, image FROM x_representatives WHERE x_id = $x_id ORDER BY $order_by LIMIT $start, $display";
    
  } else {
    
    $q = "SELECT title, description, image, x_id FROM x_representatives ORDER BY $order_by LIMIT $start, $display";
    
  }
  
  $r = mysqli_query($mysqli, $q);
  
  // Etc.


 

I think the good thing about the above is that in either case, only one query is required; a simple if-else statement handles it all.

 

What do you think?

Is that what you want?

  • Upvote 2

Share this post


Link to post
Share on other sites

Thank you for the clarification.

In this case, using prepared statements will not give you any performance benefit, because whether the check box is checked or not, you'll only need to perform one query. The query you perform though will differ, depending on whether the check box was selected or not.

 

Without seeing your actual code, I'm kind of guessing here, but I think the following will suffice:

 

 

<?php
  
  // Establish DB connection here.
  // Also, you should validate all received values. I am leaving that out for brevity though.
  
  $order_by = $_POST['order_by'];
  
  $start = $_POST['start'];
  
  $display = $_POST['display'];
  
  $filter_on_own_id = isset($_POST['show_results_for_only_own_id']) ? true : false; // This is for the check box.
  
  if ($filter_on_own_id) {
    
    $x_id = $_POST['x_id'];
    
    $q = "SELECT title, description, image FROM x_representatives WHERE x_id = $x_id ORDER BY $order_by LIMIT $start, $display";
    
  } else {
    
    $q = "SELECT title, description, image, x_id FROM x_representatives ORDER BY $order_by LIMIT $start, $display";
    
  }
  
  $r = mysqli_query($mysqli, $q);
  
  // Etc.
 

I think the good thing about the above is that in either case, only one query is required; a simple if-else statement handles it all.

 

What do you think?

Is that what you want?

 

 

Thank you very much for the response and help! I'm sorry for the rather late reply, but I was just getting back to that particular problem in my site! This leads me to my next minor issue...

 

I have incorporated a bit of that code as follows:

$filter_on_own_id = isset($_POST['checkbox_county']) ? true : false;

if ($filter_on_own_id) {
    $s = "SELECT county_id FROM counties WHERE county_name = '$where_n_s'";
    $t = @mysqli_query ($mysqli, $s);
    $tresult = mysqli_fetch_assoc($t);
    $q = "SELECT title, description, image, x_id FROM x_representatives WHERE county_id = '$tresult' ORDER BY $order_by LIMIT $start, $display";
    } else {
    $q = "SELECT title, description, image, x_id FROM x_representatives ORDER BY $order_by LIMIT $start, $display";        
    }
    
$r = @mysqli_query ($mysqli, $q);

I have a checkbox that lets the user filter the results down to only those that match his county_id (defined earlier). When a user clicks the checkbox and submits the form, the page simply returns the same list of query results as before. Do you think this is an html error or something to do with the php?

 

I appreciate the help folks!

Share this post


Link to post
Share on other sites

Hi,

Can you post the html for the form?

Thanks

 

Sure!

echo '
<form method="post" action="#">
<label id="exampleid"><input type="checkbox" name="checkbox_county" value="checkbox_county" ';
 
    if (isset($_POST['checkbox_county'])) {
        echo "checked=\"checked\" />Only $where_y_s.";
        } else {
		echo ">Only $where_y_s.";
        }
        
echo '
</label>
<input class="exampleclass" type="submit" value="Go" />
</form>

Note: I defined the $where_y_s via a session variable above the form. Thanks!

Share this post


Link to post
Share on other sites

What is $where_n_s equal to?

 

Edit: You have $where_y_s and $where_n_s? What are these variables?

 

Above the code I presented earlier:

$where_y_s = $_SESSION['where'];

Below the code I presented earlier:

$where_n_s = str_replace(' ', '_', $where_y_s);

The user provides the "where," but in this site's case, "where" could include spaces, which I replace with underscores below before running the variable through a query!

 

I hope this helps the understanding of my problem!

 

Share this post


Link to post
Share on other sites

Well, does your query work if you run a static query on the DB?

Trying echoing out the value of $where_n_s, and make sure it's what you want/expect.

 

The query runs fine both ways, only with the checkbox clicked it still doesn't filter.

I believe the issue is that I am not getting anything into the variable $tresult from "$tresult = mysqli_fetch_assoc($t);"

Have any ideas?

Share this post


Link to post
Share on other sites

Well, your $tresult = mysqli_fetch_assoc($t); line is always going to only grab one row from the DB no matter what.

You should be using a while loop to loop through all of the rows returned from the DB.

From there, you can do whatever you have to.

  • Upvote 2

Share this post


Link to post
Share on other sites

But there should be only one row of results since this user has selected just one county --> i'm not sure if that is the problem.

 

$tresult is an associative array but you have not referenced any values in the array.

 

$q = "SELECT title, description, image, x_id FROM x_representatives WHERE county_id = '$tresult' ORDER BY $order_by LIMIT $start, $display";

 

perhaps it should be something like:

 

$county = $tresult['county_id'];

 

$q = "SELECT title, description, image, x_id FROM x_representatives WHERE county_id = '$county' ORDER BY $order_by LIMIT $start, $display";

 

I could have some syntax errror here above but you see the idea I'm getting at.

  • Upvote 1

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
Sign in to follow this  

×
×
  • Create New...