SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    Jun 2009
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question $query := SELECT... Need Logic in advance of knowing which checkboxes are selected...

    I am new to Php/MySQL programming, but have built a site using Kevin Yank's book and other sources for reference. I now have a problem that has me currently stopped in my tracks.

    My site allows for a visitor to select one or many checkboxes. The PHP code then converts the actual selections to "Yes" variables and the unchecked boxes to "No" variables. In this way a comparision can be made to the mySQL talbe field entries in search of all candidate records that match the checkbox requirements.

    In order to ensure that the search is accurate and even returns "overqualified" records:

    1) I need to build up a mySQL SELECT query based only on the checkboxes that have been checked.

    2) I do not want to include any conditions in the WHERE clause for those that have not been checked.

    The problem is that I do not know which checkboxes will be selected in advance for any given and susequent search.

    I do have lead on proper coding that I believe (with my limited knowledge), will work. However, I am unable to verify becasue of a problem with the final line of code - i.e., the actual SELECT statement that is at the heart of the potential solution.

    Thank you in advance for any assistance that can be extended. Please find attached a small section of the code that I am prepared to use if it will work after determining the reason for the terminal failure:


    <?php // SAMPLE CODE ; PLEASE SEE LAST LINE: "SELECT"

    // 1st checkbox attribute "$end_h" and 2nd Table field "Endors_h"

    $queryStr = '';
    if( $end_h =='Yes'
    ) $queryStr = $queryStr . " Endors_h='Yes'";

    // 2nd checkbox attribute "$end_s" and 2nd Table field "Endors_s"

    if( $end_s =='Yes'
    ){
    if( $queryStr != '' ) $queryStr = $queryStr . ' AND';
    $queryStr = $queryStr . " Endors_s='Yes'";
    }

    // 3rd checkbox attribute "$end_x" and 2nd Table field "Endors_x"

    if( $end_x =='Yes'
    ){
    if( $queryStr != '' ) $queryStr = $queryStr . ' AND';
    $queryStr = $queryStr . " Endors_x='Yes'";
    }

    // 4th checkbox attribute "$end_n" and 2nd Table field "Endors_n"

    if( $end_n =='Yes'
    ){
    if( $queryStr != '' ) $queryStr = $queryStr . ' AND';
    $queryStr = $queryStr . " Endors_n='Yes'";
    }

    // 5th checkbox attribute "$end_t" and 2nd Table field "Endors_t"

    if( $end_t =='Yes'
    ){
    if( $queryStr != '' ) $queryStr = $queryStr . ' AND';
    $queryStr = $queryStr . " Endors_t='Yes'";
    }

    // 6th checkbox attribute "$end_y" and 2nd Table field "Endors_y"

    if( $end_y =='Yes'
    ){
    if( $queryStr != '' ) $queryStr = $queryStr . ' AND';
    $queryStr = $queryStr . " Endors_y='Yes'";
    }

    // 7th checkbox attribute

    if( $end_p =='Yes'
    ){
    if( $queryStr != '' ) $queryStr = $queryStr . ' AND';
    $queryStr = $queryStr . " Endors_p='Yes'";
    }

    // 8th checkbox attribute

    if( $end_w =='Yes'
    ){
    if( $queryStr != '' ) $queryStr = $queryStr . ' AND';
    $queryStr = $queryStr . " Endors_w ='Yes'";
    }

    // 9th checkbox attribute

    if( $end_z =='Yes'
    ){
    if( $queryStr != '' ) $queryStr = $queryStr . ' AND';
    $queryStr = $queryStr . " Endors_z='Yes'";
    }

    $queryStr := 'SELECT * FROM $tbl_name WHERE' . $queryStr . ';';
    ?>
    Last edited by a4flix3; Jun 28, 2009 at 09:19. Reason: typos

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Code PHP:
    // query with first where condition (1=1 is always true), so all other
    // conditions can start with AND
    $queryStr = "
      SELECT * 
      FROM $tbl_name 
      WHERE 1 = 1 
    ";
     
    // 1st checkbox attribute "$end_h" and 2nd Table field "Endors_h"
    if ($end_h == 'Yes') $queryStr .= " AND Endors_h = 'Yes' ";
     
    // 2nd checkbox attribute "$end_s" and 2nd Table field "Endors_s"
    if ($end_s == 'Yes') $queryStr .= " AND Endors_s = 'Yes' ";
     
    // 3rd checkbox attribute "$end_x" and 2nd Table field "Endors_x"
    if ($end_x == 'Yes') $queryStr .= " AND Endors_x = 'Yes' ";
     
    // 4th checkbox attribute "$end_n" and 2nd Table field "Endors_n"
    if ($end_n == 'Yes') $queryStr .= " AND Endors_n = 'Yes' ";
     
    // 5th checkbox attribute "$end_t" and 2nd Table field "Endors_t"
    if ($end_t == 'Yes') $queryStr .= " AND Endors_t = 'Yes' ";
     
    // 6th checkbox attribute "$end_y" and 2nd Table field "Endors_y"
    if ($end_y == 'Yes') $queryStr .= " AND Endors_y = 'Yes' ";
     
    // 7th checkbox attribute
    if ($end_p == 'Yes') $queryStr .= " AND Endors_p = 'Yes' ";
     
    // 8th checkbox attribute
    if ($end_w == 'Yes') $queryStr .= " AND Endors_w = 'Yes' ";
     
    // 9th checkbox attribute
    if ($end_z == 'Yes') $queryStr .= " AND Endors_z = 'Yes' ";

  3. #3
    SitePoint Member
    Join Date
    Jun 2009
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    "guido2004" - thank you. Your solution is simplier, elegant, and it WORKS!


Bookmarks

Posting Permissions

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