Results 1 to 4 of 4
  1. #1
    Join Date
    May 2011
    Posts
    4

    Unanswered: Using PHP to construct SQL

    Hey,

    I'm sure this is an easy question.

    If you want to produce SQL with php for a search query. So you have say 5 criteria which are all optional and may or may not be inputted by the user. You cannot guarantee any of them.

    When it comes to making the SQL in php you can use :

    So if they exist then you can use AND for the 4 last criteria.

    But for the first criteria if you have that as a WHERE if that one is not selected then the SQL just is a list of AND's with no starting WHERE.

    Am i being really stupid and is there an easy answer?

    Thanks

    Alex

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    there is a sneaky trick to set up your where clause using a quirk of SQL
    PHP Code:
    $WhereClause " where 1=1";
    if (
    abooleancondition)
    {  
    $WhereClause .= " and anumericcolumn = ".$anumericvalue;
    }
    if (
    anotherbooleancondition)
    {  
    $WhereClause .= " and astringcolumn = '".$astringcolumn."'";

    by using where 1=1 means you alwasy have something in your where clause, but it doesn't affect row selection.
    then add your if statements to build the fully formed (or not) where clause
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2011
    Posts
    4
    Your a genius!

    Thanks so much, worked a charm!

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by lecsiy View Post
    Your a genius!

    Thanks so much, worked a charm!
    not really
    I'd love to claim credit for that dodge
    I got that trick from Rudy (R937) on this site.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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