SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Guru ripcurlksm's Avatar
    Join Date
    Aug 2004
    Location
    San Clemente, CA
    Posts
    859
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Checkbox array/looping SQL query

    Hello, I have a form with multiple checkbox's and if a user selects the box, a dropdown menu appears. Based on what options they pick, I want to create an array of their selections and assemble a SQL query (where $x=1 AND $y=2 AND $z=3, etc..).

    Here is my form which have 3 drop down menus:

    HTML Code:
    	<form name="form1" method="post" action="">
    		Difficulty: <input type="checkbox" onclick="document.getElementById('difficulty').disabled=!this.checked" />
    		<select disabled="disabled" id="difficulty">
      			<option value="hard">Hard</option>
      			<option value="easy">Easy</option>
    		</select>
    		<br>
    		Direction: <input type="checkbox" onclick="document.getElementById('direction').disabled=!this.checked" />
    		<select disabled="disabled" id="direction">
      			<option value="left">Left</option>
      			<option value="right">Right</option>
    		</select>
    		<br>
    		Color: <input type="checkbox" onclick="document.getElementById('color').disabled=!this.checked" />
    		<select disabled="disabled" id="color">
      			<option value="left">Red</option>
      			<option value="right">Green</option>
    		</select>		
        </form>	

    Pending on which variables are selected in the checkboxs above, it should create an array to be assembled to query the database. If all three variables were selected from the focm above, my SQL would look like this:
    PHP Code:
    SELECT FROM resort WHERE direction='left' AND difficulty='easy' AND color='red' 
    Here is my php statement which is essentially trying to assemble the above SQL statement. This is what I need help with. I'm not sure how to pass the variables so it would know that that "difficulty" box had "Easy" selected and the "direction" box had "right" selected. the array would loop and assemble a SQL statement so that it would say WHERE difficulty='easy' AND direction='right' for example. If only one checkbox was selected, it would only print one WHERE statement, or if three was selected it would print WHERE and two AND statements..
    PHP Code:
    <?php
        
    $arr 
    $_POST['value'];
    $id =  $_POST['id'];

    $select="SELECT * FROM resort";

    $where="";

        foreach (
    $arr as &$value
        {
        echo 
    "WHERE $id='$value' AND";
        }
    ?>
    Can you help explain to me how to pass the specific variable $difficulty='easy' into a while statement so I can build my SQL query?

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    First, give your form elements names, or you won't be able to receive the values in PHP.

    PHP Code:
    $sql "SELECT * FROM resort WHERE 1=1";

    if (!empty(
    $_GET['difficulty'])) {
      
    $sql .= " AND difficulty = '" mysql_real_escape_string($_GET['difficulty']) . "'";
    }

    if (!empty(
    $_GET['direction'])) {
      
    $sql .= " AND direction = '" mysql_real_escape_string($_GET['direction']) . "'";
    }

    if (!empty(
    $_GET['color'])) {
      
    $sql .= " AND color = '" mysql_real_escape_string($_GET['color']) . "'";

    Please excuse any typos... my shift key is broken.

  3. #3
    SitePoint Guru ripcurlksm's Avatar
    Join Date
    Aug 2004
    Location
    San Clemente, CA
    Posts
    859
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Dan, that works wonderfully!

  4. #4
    . shoooo... silver trophy logic_earth's Avatar
    Join Date
    Oct 2005
    Location
    CA
    Posts
    9,013
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)
    I would do something like this just to reduce code repeat.
    PHP Code:
    $sql 'SELECT * FROM resort WHERE 1=1';

    $fields = array('difficulty''direction''color');
    foreach (
    $fields as $field) {

        if (!isset(
    $_GET[$field]) || empty($_GET[$field])) {
            continue;
        }

        
    $_GET[$field] = mysql_real_escape_string($_GET[$field])
        
    $sql .= sprintf(" AND %s = '$s'"$field$_GET[$field]);


    Doesn't matter tho does the same thing.
    Logic without the fatal effects.
    All code snippets are licensed under WTFPL.


  5. #5
    SitePoint Guru ripcurlksm's Avatar
    Join Date
    Aug 2004
    Location
    San Clemente, CA
    Posts
    859
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you very much I will check it out


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
  •