Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    New Coder
    Join Date
    May 2010
    Posts
    79
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Multiple Query changes

    Hello CodingForums!
    I'm working on a web application which displays a grid of results. I want to have a sidebar which filters these results based on what the user clicks on in the sidebar. Here is the code I have so far:

    PHP Code:
    $dbc mysqli_connect(DB_HOSTDB_USERDB_PASSWORDDB_NAME//connect to db
        
    or die('Error connecting to MySQL server. Maybe it went to buy doughnuts.');

    $query "SELECT * FROM ff_faces";
    if (
    $_GET['gender']) {
    $gender $_GET['gender'];
    $query $query " WHERE " $gender;
    }
    if (
    $_GET['ethnicity']) {
    $ethnicity $_GET['ethnicity'];
    $query $query " WHERE ethnicity_id=" $ethnicity;
    }
    $result mysqli_query($dbc$query); 
    When the user clicks the link, the information to augment the query is passed by a GET (i.e. ethnicity or gender). The query is then reloaded with the filtered content.

    But I have no way to make the filter work for both ethnicity AND gender. In other words, if the user filters by male, it will show all male, but then if she clicks 'caucasian', for example, it will return all results that are caucasian of both genders. I need a way to have these queries stack on top of each other on the query so that the correct results are returned.

    Hopefully that was clear, if not, please ask me. Thanks!
    Andrew

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    The problem is the where. You cannot have multiple WHERE within a query. The easiest change is a little hacky, but you can modify the query to SELECT * FROM ff_faces WHERE 1=1, then use AND instead of WHERE within the $query concatination.
    Edit:
    BTW, the gender handling will need to change. Its not addressing any property.

  • #3
    New Coder
    Join Date
    Jul 2011
    Location
    Kediri - Indonesia
    Posts
    61
    Thanks
    2
    Thanked 19 Times in 19 Posts
    like as Fou-Lu's clue, I try to make it code
    PHP Code:

    $dbc 
    mysqli_connect(DB_HOSTDB_USERDB_PASSWORDDB_NAME//connect to db 
        
    or die('Error connecting to MySQL server. Maybe it went to buy doughnuts.'); 

    $query "SELECT * FROM ff_faces where 1=1 "
    if (
    $_GET['gender']) { 
    $gender $_GET['gender']; 
    $query .= " and WHERE GENDER='".$gender."'"//i don't know name of your gender field. so I asumme it fieldname is "GENDER"

    if (
    $_GET['ethnicity']) { 
    $ethnicity $_GET['ethnicity']; 
    $query .=" and ethnicity_id=" $ethnicity

    $result mysqli_query($dbc$query); 
    hope it helps

  • #4
    New Coder
    Join Date
    May 2010
    Posts
    79
    Thanks
    8
    Thanked 0 Times in 0 Posts
    OK, I think I understand.

    Fou-Lu, for some reason I have the GET for gender pass as gender=1, but the ethnicity pass only as 1; it works but they're different :P

    My problem is that the user won't always click Gender then Ethnicity. They may only click Ethnicity. Or maybe they'll only click gender. But if they click both, I need to be able to adjust to that :P

    AA

  • #5
    New Coder
    Join Date
    May 2010
    Posts
    79
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Here is my optimized code, but which still only performs a single search with no nesting:

    PHP Code:
    if (isset($_GET['gender'])) {
    $gender $_GET['gender'];
    $where_clause .= "gender = " $gender;    
    }
    if (
    $_GET['ethnicity']) {
    $ethnicity $_GET['ethnicity'];
    $where_clause .= "ethnicity_id = " $ethnicity;
    }
    if (isset(
    $where_clause)) {
    $query .= " WHERE " $where_clause;

    Thanks!
    Andrew

  • #6
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Quote Originally Posted by authorandrew View Post
    Here is my optimized code, but which still only performs a single search with no nesting:

    PHP Code:
    if (isset($_GET['gender'])) {
    $gender $_GET['gender'];
    $where_clause .= "gender = " $gender;    
    }
    if (
    $_GET['ethnicity']) {
    $ethnicity $_GET['ethnicity'];
    $where_clause .= "ethnicity_id = " $ethnicity;
    }
    if (isset(
    $where_clause)) {
    $query .= " WHERE " $where_clause;

    Thanks!
    Andrew
    Still won't work. You need to add an AND or OR clause to separate multiple conditions depending on if you want to drill lower or open wider.

  • #7
    New Coder
    Join Date
    May 2010
    Posts
    79
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Question

    I realize that. My problem is that I'm not sure how to do that while still allowing the user to only select one of the two narrowing down options (gender and ethnicity)

    Andrew

  • #8
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    If you can only select one of the two, the easiest thing to do is manufacture a select or radio set to force it on HTML side. From PHP side, you can use an else if to dictate that, but it means the first will always prevail.

  • #9
    New Coder
    Join Date
    May 2010
    Posts
    79
    Thanks
    8
    Thanked 0 Times in 0 Posts
    I don't understand what you're saying.

    Here's all I need to do:
    The sidebar is a filter application for the entire database. One can sort only by gender, or one can sort only by ethnicity. Also, one can be able to sort by male and then further narrow down male results by an ethnicity. Is it possible to do this?

    Andrew

  • #10
    New Coder
    Join Date
    Jul 2011
    Location
    Kediri - Indonesia
    Posts
    61
    Thanks
    2
    Thanked 19 Times in 19 Posts
    Quote Originally Posted by authorandrew View Post
    Here is my optimized code, but which still only performs a single search with no nesting:

    PHP Code:
    if (isset($_GET['gender'])) {
    $gender $_GET['gender'];
    $where_clause .= "gender = " $gender;    
    }
    if (
    $_GET['ethnicity']) {
    $ethnicity $_GET['ethnicity'];
    $where_clause .= "ethnicity_id = " $ethnicity;
    }
    if (isset(
    $where_clause)) {
    $query .= " WHERE " $where_clause;

    Thanks!
    Andrew
    how if there is ethnicity AND gender?
    or without ethenicity and gender?

    it will be error on queries.
    I am sorry my english is very bad. But I am very interest to discusse here :-)

  • #11
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Quote Originally Posted by authorandrew View Post
    I don't understand what you're saying.

    Here's all I need to do:
    The sidebar is a filter application for the entire database. One can sort only by gender, or one can sort only by ethnicity. Also, one can be able to sort by male and then further narrow down male results by an ethnicity. Is it possible to do this?

    Andrew
    Are you sorting or filtering? You are interchanging the words. The sort means you only sort the resultset by whatever you have provided, and does not require a WHERE clause. If you need to add a filter, but only allow one, then you should write it as a select or radio menu instead to prevent multiples. I don't see a reason to code a filter without allowing all potential options to drill through it.

  • #12
    New Coder
    Join Date
    May 2010
    Posts
    79
    Thanks
    8
    Thanked 0 Times in 0 Posts
    I need to filter the results. I need to be able to filter using 1 criteria, but also possibly 2, depending on user input.

    Andrew
    Last edited by authorandrew; 11-28-2011 at 06:21 PM.

  • #13
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Okay, then we go back to post #2. You simply manufacture the query where if a condition exists you append an AND statement to the query.
    PHP Code:
    $query "SELECT * FROM ff_faces WHERE 1=1"
    if (isset(
    $_GET['gender']))
    {
        
    $query .= ' AND gender="' mysql_real_escape_string($_GET['gender']) . '"';
    }
    if (isset(
    $_GET['ethnicity']))
    {
        
    $query .= ' AND ethnicity="' mysql_real_escape_string($_GET['ethnicity']) . '"';

    That assumes that both of these HTML fields are now just the value, not 'gender=m' for example (which you sorta indicated you did previously).

  • #14
    New Coder
    Join Date
    May 2010
    Posts
    79
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Again, this doesn't allow for nested searches. If I click to filter by 'male' for example, the faces all show up only those listed in the DB as male. But then if I click ethnicity: caucasian, for example, it lists results from both genders.

    The problem is that when the user clicks a button, the page reloads, thus resetting the query to WHERE 1 (and nothing else) every time. I need some way to preserve the previous query across the board.

  • #15
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Quote Originally Posted by authorandrew View Post
    Again, this doesn't allow for nested searches. If I click to filter by 'male' for example, the faces all show up only those listed in the DB as male. But then if I click ethnicity: caucasian, for example, it lists results from both genders.

    The problem is that when the user clicks a button, the page reloads, thus resetting the query to WHERE 1 (and nothing else) every time. I need some way to preserve the previous query across the board.
    How are you submitting this data?


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •