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 4 of 4
  1. #1
    cen
    cen is offline
    New to the CF scene
    Join Date
    Jan 2006
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Selectively building an SQL query

    I have a form that has 4 select boxes out of which the first one is mandatory and other 3 may or may not be selected by the user. If one or more of them is selected the searching (or rather viewing) gets more specific according to that criterea.

    My problem is how you build the SQL for this kind of thing ? My problem is further complicated by the fact that if the user selects one or more selectives at most two tables have to be joined to get the results. So I have to get these tables to the from clause as well as in the where clause and for joining.

    Any pointers or ideas would be very much appreciated,

    Thanks all.

  • #2
    Regular Coder
    Join Date
    Oct 2004
    Location
    London E4 UK
    Posts
    320
    Thanks
    0
    Thanked 0 Times in 0 Posts
    some php, the selects give you values for your variables

    use some logic to supply you with whatever building blocks you need and then construct the query

  • #3
    $object->toCD-R(LP); vinyl-junkie's Avatar
    Join Date
    Jun 2003
    Posts
    3,083
    Thanks
    2
    Thanked 23 Times in 23 Posts
    I'm doing something like this on a database search page (here). Depending on the number of elements a user searches for, I use any one of about four or five queries that are all pre-built except for the where clause. I build the where clause using some fairly extensive logic in a PHP script.

    There's really no easy way to do this. Just brute force and a lot of judiciously applied code.
    Music Around The World - Collecting tips, trade
    and want lists, album reviews, & more
    SNAP to it!

  • #4
    Senior Coder
    Join Date
    Nov 2002
    Location
    North-East, UK
    Posts
    1,265
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You can do it with if statements or Select Case

    Here is a basic example

    PHP Code:
    <?php
    if(isset($_POST['Submit'])){
        
    $sql "SELECT * FROM table WHERE Gender = '" $_POST['Gender'] . "'";
        
        if(
    $_POST['Status'] != 'Choose') {
            
    $sql .= " AND Status = '" $_POST['Status'] . "'";
        }
        if(
    $_POST['Location'] != 'Choose'){
            
    $sql .= " AND Location = '" $_POST['Location'] . "'";
        }
        if(isset(
    $_POST['Photo'])){
            
    $sql .= " AND Photo=1";
        }
    }
    ?>
    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
    <html>
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
    <title>Untitled Document</title>
    </head>

    <body>
    <form name="form1" method="post" action="">
      <select name="Gender" id="Gender">
        <option value="Male">Male</option>
        <option value="Female">Female</option>
      </select>
      <select name="Status" id="Status">
        <option value="Choose">Choose Status</option>
        <option value="Single">Single</option>
        <option value="Married">Married</option>
      </select>
      <select name="Location" id="Location">
        <option value="Choose">Choose Location</option>
        <option value="UK">UK</option>
        <option value="US">US</option>
      </select> 
      Has Photo 
      <input name="Photo" type="checkbox" id="Photo" value="1">
      <input type="submit" name="Submit" value="Submit">
    </form>
    <p><?php if(isset($sql)){echo $sql;} ?></p>
    </body>
    </html>


  •  

    Posting Permissions

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