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.
Results 1 to 6 of 6
  1. #1
    New to the CF scene
    Join Date
    May 2011
    Location
    NY, NY
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    dynamic MySQL select statement

    Hello,

    I'm putting together an internal company website. Right now its taking two fields firstName and lastName but I will be adding more fields (address,city,state,zip..etc). I want to create a dynamic select statement that will look at what fields the user has entered and use those in the select statement. I did some research and found two query generators/builders. I tried applying them to my code with no success. Is this the approach I should be taking or something else ? I have attached my two files along with two query generators/builders into one zip. Thanks in advance

    -Chris
    Attached Files Attached Files

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    This is really a PHP question, because the easy way to do this is with a little bit of PHP magic.

    It's not really magic.

    The idea is to build your query up one piece at a time using PHP's string glue, the period (.).

    First, create your basic query:
    PHP Code:
    $query "SELECT * FROM table1 WHERE 1"
    Note the "WHERE 1" is merely a tricky way to get the "WHERE" clause into the query. The reason for this is our query is now ready for any number of modifiers to the "WHERE" clause. We can add, 3, 4 or 0. (BTW, "WHERE 1" will always evaluate to "true".)

    To add a modifier:
    PHP Code:
    if (!empty($_POST['city']))
    {
        
    $query .= " AND city_column = '".mysql_real_escape_string($_POST['city'])."'";

    Do this with each item you want to add to the query as a possible filter, and then call mysql_query($query), check for errors, fetch the data, so on and so forth.

  • #3
    New to the CF scene
    Join Date
    May 2011
    Location
    NY, NY
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Fumigator,

    I get what you are saying and like this approach. I have a question. I noticed that $query starts with AND so there has to be one required field correct ? What if I didn't want a required field. For example say I just wanted to look at a date range rather then names. I know I am jumping ahead but I'm just anticipating management requests

  • #4
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    Quote Originally Posted by cthorn112 View Post
    Fumigator,

    I get what you are saying and like this approach. I have a question. I noticed that $query starts with AND so there has to be one required field correct ? What if I didn't want a required field. For example say I just wanted to look at a date range rather then names. I know I am jumping ahead but I'm just anticipating management requests
    Not so. Each qualifier starts with AND, but $query starts with SELECT. All fields are optional. If no fields are entered, you end up with this query:

    Code:
    SELECT * FROM mytable WHERE 1
    If 3 fields are entered, you end up with this query:

    Code:
    SELECT * FROM mytable WHERE 1
    AND field1 = 'field1value'
    AND field2 = 'field2value'
    AND field3 = 'field3value'

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,596
    Thanks
    80
    Thanked 4,633 Times in 4,595 Posts
    Read again what Fumigator said.

    Look at his initial sql string:
    Code:
    $query = "SELECT * FROM table1 WHERE 1";
    If you never added any more conditions to the query, the WHERE 1 would cause *ALL* records to be selected.

    If you just wanted a date range, you might then do
    Code:
    $query .= " AND dateField BETWEEN '2011-1-1' AND '2011-5-31' ";
    The operator used there is "dot equals", *NOT* just "equals". So that *APPENDS* to the initial value of $query and your SQL becomes
    Code:
    SELECT * FROM table1 WHERE 1 AND dateField BETWEEN '2011-1-1' AND '2011-5-31'
    See?

    ************************

    *SIGH* Old age is catching up with me. Two minutes too slow. <grin/>
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #6
    New to the CF scene
    Join Date
    May 2011
    Location
    NY, NY
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Oh ok I see what you are saying. Thank you very much for the information. I will be back with more questions after I apply some changes to the code.

    Thanks,

    -Chris


  •  

    Posting Permissions

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