CFitz Posted July 8, 2013 Share Posted July 8, 2013 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! Link to comment Share on other sites More sharing options...
Antonio Conte Posted July 8, 2013 Share Posted July 8, 2013 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. 1 Link to comment Share on other sites More sharing options...
HartleySan Posted July 9, 2013 Share Posted July 9, 2013 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. Link to comment Share on other sites More sharing options...
CFitz Posted July 9, 2013 Author Share Posted July 9, 2013 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!) Link to comment Share on other sites More sharing options...
HartleySan Posted July 9, 2013 Share Posted July 9, 2013 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? 2 Link to comment Share on other sites More sharing options...
CFitz Posted August 13, 2013 Author Share Posted August 13, 2013 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! Link to comment Share on other sites More sharing options...
StephenM Posted August 13, 2013 Share Posted August 13, 2013 Hi, Can you post the html for the form? Thanks 1 Link to comment Share on other sites More sharing options...
CFitz Posted August 13, 2013 Author Share Posted August 13, 2013 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! Link to comment Share on other sites More sharing options...
HartleySan Posted August 14, 2013 Share Posted August 14, 2013 What is $where_n_s equal to? Edit: You have $where_y_s and $where_n_s? What are these variables? Link to comment Share on other sites More sharing options...
CFitz Posted August 14, 2013 Author Share Posted August 14, 2013 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! Link to comment Share on other sites More sharing options...
HartleySan Posted August 14, 2013 Share Posted August 14, 2013 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. Link to comment Share on other sites More sharing options...
CFitz Posted August 14, 2013 Author Share Posted August 14, 2013 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? Link to comment Share on other sites More sharing options...
HartleySan Posted August 14, 2013 Share Posted August 14, 2013 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. 2 Link to comment Share on other sites More sharing options...
StephenM Posted August 14, 2013 Share Posted August 14, 2013 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. 1 Link to comment Share on other sites More sharing options...
Recommended Posts