SitePoint Sponsor

User Tag List

Results 1 to 18 of 18
  1. #1
    SitePoint Wizard co.ador's Avatar
    Join Date
    Apr 2009
    Posts
    1,054
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    array condition at WHERE clause..

    PHP Code:
    <pre>Array
    (
        [
    frmSearch] => Array
            (
                [
    zipcode] => 
                [
    food_types] => Array
                    (
                        [
    0] => 5
                    
    )

                [
    submit] => Submit
       
    </pre
    above is the print_r of the POST global variable coming from page1

    in page2 I want to display three fields from table stores based on some condition met with the form values.

    The thing is that foodtypes input field is an array and I am trying to fit the results of foodtypes inside the query where the fields from table store are to be display. I am trying to script it at the WHERE clause.


    so far I have this.

    PHP Code:
    // Extract the form POST variables and escape them

    $strZipCode = isset($_REQUEST['frmSearch']['zipcode'])? mysql_real_escape_string($_REQUEST['frmSearch']['zipcode']):'';
        
    $arrFoodTypes = isset($_REQUEST['frmSearch']['food_types'])? ($_REQUEST['frmSearch']['food_types']):array();

    //query to display three fields from stores based on the form input.
    $query4 "SELECT state, zip, county,restaurantname,restaurant_id
    FROM stores
    WHERE zip= '
    $strZipCode' OR restaurantname='$strName' OR state='$strState' OR restaurant_id="$arrFoodTypes[0].""
    the above condition at the WHERE zip is not working


    help please.....

  2. #2
    I solve practical problems. bronze trophy
    Michael Morris's Avatar
    Join Date
    Jan 2008
    Location
    Knoxville TN
    Posts
    2,053
    Mentioned
    66 Post(s)
    Tagged
    0 Thread(s)
    If you're always looking for the first element use array_pop

    Code:
    if (isset($_REQUEST['frmSearch']['food_types']) && is_array($_REQUEST['frmSearch']['food_types'])) {
    
    $arrFoodTypes = array_pop($_REQUEST['frmSearch']['food_types']);
    }else{
    $arrFoodTypes = '';
    }
    That's a start. However, are you wanting the query to try to match those fields to an empty result? If not you need to omit that section of the query when the search term is blank.

  3. #3
    SitePoint Wizard co.ador's Avatar
    Join Date
    Apr 2009
    Posts
    1,054
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Not, at the moment, right now I want the query to match a value..

    i haven't think about empty result sound like an idea to think about, but for the moment i want the query to match a result, not a empty one.

  4. #4
    SitePoint Wizard co.ador's Avatar
    Join Date
    Apr 2009
    Posts
    1,054
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    array_pop will return the last value of an array right? is the users input the last value of an array?

  5. #5
    rajug.replace('Raju Gautam'); bronze trophy Raju Gautam's Avatar
    Join Date
    Oct 2006
    Location
    Kathmandu, Nepal
    Posts
    4,013
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    First of all, since you are having the data from posted form then why don't you directly use $_POST instead of $_REQUEST? I would recommend to use $_POST. Secondly I personally could not understand what exactly you mean by 'the above condition at the WHERE zip is not working'? What is not working? Is there any error message you got or it is just not returning the expected data from the table?

    In such cases i would do something like this. Maybe this can give some sight:
    Code PHP:
    // Extract the form POST variables and escape them
    $_POST['frmSearch']['zipcode'] = '';
    $_POST['frmSearch']['food_types'][] = 5;
     $where = array();
    $where[] = isset($_POST['frmSearch']['zipcode']) ? "zip='" . ($_POST['frmSearch']['zipcode']) . "'" : ""; 
    $where[] = isset($_POST['frmSearch']['food_types'][0]) ? "restaurant_id=" . ($_POST['frmSearch']['food_types'][0]) : ''; 
    $where[] = "restaurantname='$strName'";
    $where[] = "state='$strState'";
     
    //query to display three fields from stores based on the form input. 
    $query4 = "SELECT state, zip, county,restaurantname,restaurant_id 
    FROM stores 
    WHERE " . implode(" OR ", $where);
    echo $query4;
    Mistakes are proof that you are trying.....
    ------------------------------------------------------------------------
    PSD to HTML - SlicingArt.com | Personal Blog | ZCE - PHP 5

  6. #6
    SitePoint Wizard co.ador's Avatar
    Join Date
    Apr 2009
    Posts
    1,054
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    it's just the way i have it structures was not working, and its supposed to display the fields in the query if users select a food type, but again the was I was structuring the array in the query so it works based on the users data was simply not showing the fields. There was not error at all or any notices.

  7. #7
    rajug.replace('Raju Gautam'); bronze trophy Raju Gautam's Avatar
    Join Date
    Oct 2006
    Location
    Kathmandu, Nepal
    Posts
    4,013
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Logically, since there is no zip code according your print_r output, so the query will try to fetch the records which have the zip field blank. If you just dont want to have zip field in the query if there is no zip code selected then above my example should work for you. Did you try once the way what i have shown you above?
    Mistakes are proof that you are trying.....
    ------------------------------------------------------------------------
    PSD to HTML - SlicingArt.com | Personal Blog | ZCE - PHP 5

  8. #8
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,192
    Mentioned
    17 Post(s)
    Tagged
    4 Thread(s)
    I believe what you are trying to accomplish is exactly what this piece of code I provided you with a while back was meant to do.

    PHP Code:
        // Food types filter
        
    if(!empty($arrFoodTypes) && !empty($arrFoodTypes[0])) {
        
    $arrSQLFilters[] = sprintf(
              
    'r.restaurants_id IN
                   (SELECT
                         restaurants_id
                      FROM
                         restaurants_restaurant_food_types
                     WHERE
                         restaurants_food_types_id IN (%s)
                     GROUP
                        BY
                         restaurants_id
                    HAVING 
                         COUNT(*) = %u)'
                
    ,/*mysql_real_escape_string(*/ implode(',',$arrFoodTypes/*)*/
                
    ,count($arrFoodTypes)
            );
        } 
    That query will find all restaurants that have the given food types. Later in the code it is then embedded into the where clause if needed.

    The same hold true for the zip codes with this piece of code.

    PHP Code:
        // Zipcode filter
        
    if(!empty($strZipCode)) {
            
    $boolIncludeZipCodes true;
        
            
    $arrSQLFilters[] = sprintf(
                
    "z.zip LIKE '%s'"
                
    ,"%$strZipCode%"
            
    );
        } 
    Same concept different application.

  9. #9
    SitePoint Wizard co.ador's Avatar
    Join Date
    Apr 2009
    Posts
    1,054
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok,

    @rajug, I was planning to give priority to the fields that users select then the empty ones. For example if there are five fields, The strZipCode,strState, strName and, two arrays $arrFoodTypes and $arrOfferings. Right now the way is set up below it will query the fields if strZipCode,strState, strName but i have set it up to $arrFoodTypes and $arrOfferings. I will implement the code in post #5.

    @Oddz This is a bit of ne html I have created to query and display the fields County, State and zipcode of the table restaurant, That's has been an extra piece of code I have put and it goes as follows.

    I didn't know the code you have provided would have some piece of script for this purpose.

    as I said in different words the query below will display in page2.php and the $strZipCode, $strState and $strName comes from the form in page1.php. then I have tried to put $arrFoodTypes and $arrOfferings at the WHERE clause the rest of the five input field form in page1.php so it can display when those input fieldds at the form are filled as well.

    Right now as you can see in the WHERE clause $strZipCode, $strState and $strName are being used and the fields will display in the browser when users input values but what about for $arrFoodTypes and $arrOfferings?

    PHP Code:
    $query4 "SELECT state, zip, county,restaurantname
    FROM restaurants  
    WHERE zip= '
    $strZipCode' OR restaurantname='$strName' OR state='$strState'";
    $result mysql_query($query4);
    $arrstate mysql_fetch_array($result);
    echo 
    '<div class="information"><label>County:</label>
            <div>'
    $arrstate['county']. '</div>
          <label>State:</label>
                 <div>'
    $arrstate['state']. '</div>
               <label>Zip Code:</label>
                 <div>'
    $arrstate['zip']. '</div></div> <br><br>'
    Thank you guys...

  10. #10
    SitePoint Wizard PHPycho's Avatar
    Join Date
    Dec 2005
    Posts
    1,201
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    // Extract the form POST variables and escape them
    $_POST['frmSearch']['zipcode'] = '';
    $_POST['frmSearch']['food_types'][] = 5;
     
    $where = array();
    $where[] = isset($_POST['frmSearch']['zipcode']) ? "zip='" . ($_POST['frmSearch']['zipcode']) . "'" "";
    $where[] = isset($_POST['frmSearch']['food_types'][0]) ? "restaurant_id=" . ($_POST['frmSearch']['food_types'][0]) : '';
    $where[] = "restaurantname='$strName'";
    $where[] = "state='$strState'";
     
    //query to display three fields from stores based on the form input.
    $query4 "SELECT state, zip, county,restaurantname,restaurant_id
    FROM stores
    WHERE " 
    implode(" OR "$where);
    echo 
    $query4
    If we have different sort of conditions like mixture of AND or OR, I used to perform as:
    PHP Code:
    // Extract the form POST variables and escape them
    $_POST['frmSearch']['zipcode'] = '';
    $_POST['frmSearch']['food_types'][] = 5;
    $where_sql null;
    $where_sql .= isset($_POST['frmSearch']['zipcode']) ? " AND zip='" . ($_POST['frmSearch']['zipcode']) . "'" "";
    $where_sql .= isset($_POST['frmSearch']['food_types'][0]) ? " AND restaurant_id=" . ($_POST['frmSearch']['food_types'][0]) : '';
    $where_sql .= " OR restaurantname='$strName'";
    $where_sql .= " AND state='$strState'";
     
    //query to display three fields from stores based on the form input.
    $query4 "SELECT state, zip, county,restaurantname,restaurant_id
    FROM stores
    WHERE 1=1"
    .$where_sql;
    echo 
    $query4

  11. #11
    SitePoint Wizard
    Join Date
    Nov 2005
    Posts
    1,191
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    if food types is an array like (mexican, chinese, thai) or even (1, 4, 9) then you should use sql IN

  12. #12
    SitePoint Wizard
    Join Date
    Nov 2005
    Posts
    1,191
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by co.ador View Post
    Right now as you can see in the WHERE clause $strZipCode, $strState and $strName are being used and the fields will display in the browser when users input values but what about for $arrFoodTypes and $arrOfferings?
    You have variables that may or not be set. You want to use them in a query only if they are set. Pretty simple really:
    1. check they are set
    2. if so concatenate another clause to the $query string (or $where string, or $array to be imploded, or object ...).

  13. #13
    I solve practical problems. bronze trophy
    Michael Morris's Avatar
    Join Date
    Jan 2008
    Location
    Knoxville TN
    Posts
    2,053
    Mentioned
    66 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by co.ador View Post
    array_pop will return the last value of an array right? is the users input the last value of an array?
    Ya. It was late at night.

  14. #14
    SitePoint Wizard co.ador's Avatar
    Join Date
    Apr 2009
    Posts
    1,054
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is what I have so far, I have made a adaptation of both, But I am not sure about setting this part like that..

    PHP Code:
    $_POST['frmSearch']['food_types'][]='';
    $_POST['frmSearch']['offerings'][]=''
    in the script below. Should I put ='array' instead in both food_types and offerings indexes?


    PHP Code:
    <?php 
    $_POST
    ['frmSearch']['name'] = ''
    $_POST['frmSearch']['zipcode'] = ''
    $_POST['frmSearch']['state'] = ''
    $_POST['frmSearch']['food_types'][]='';
    $_POST['frmSearch']['offerings'][]=''
    $where_sql null
    $where_sql .= isset($_POST['frmSearch']['name']) ? " OR restaurantname='" . ($_POST['frmSearch']['name']) . "'" ""
    $where_sql .= isset($_POST['frmSearch']['zipcode']) ? " OR zip='" . ($_POST['frmSearch']['zipcode']) . "'" ""
    $where_sql .= isset($_POST['frmSearch']['state']) ? " OR state='" . ($_POST['frmSearch']['state']) . "'" ""
    $where_sql .= isset($_POST['frmSearch']['food_types'][0]) ? " OR restaurant_id=" . ($_POST['frmSearch']['food_types'][0]) : '';
    $where_sql .= isset($_POST['frmSearch']['offerings'][0]) ? " OR restaurant_id=" . ($_POST['frmSearch']['offerings'][0]) : ''
    $where_sql .= " restaurantname='$strName'"
    $where_sql .= " zip='$strZipCode'"
    $where_sql .= " state='$strState'"
    $where_sql .= " restaurant_id='$arrFoodTypes'"
    $where_sql .= " restaurant_id='$arrOfferings'";
     
    //query to display three fields from stores based on the form input. 
    $query4 "SELECT state, zip, county,restaurantname,restaurant_id 
    FROM restaurants
    WHERE 1=1"
    .$where_sql
    echo 
    $query4
     

    ?>
    and it echo

    SELECT state, zip, county,restaurantname,restaurant_id
    FROM restaurants
    WHERE 1=1 OR restaurantname='' OR zip='' OR state='' OR restaurant_id= OR restaurant_id= restaurantname='' zip='' state='' restaurant_id='Array' restaurant_id='Array'

    it works ok with restaurantname, zip and state, but when I input a foodtype or a offerings then it won't echo anything but 'Array'. One of the thing I have to say is that foodtype build based on a many-to-many relationship database scheme. I compare with restaurant_id because restaurant_id is a common field in restaurants_to_restaurant_foodtypes and restaurants_to_restaurant_offering`tables. Both tables has the restaurants_id field and one has restaurants_foodtypes_id and another restaurants_offerings_id. So that was the reason why I have compare this two array $arrFoodTypes and $arrOfferings to restaurants_id field at the query. But it is not working.

    Do I have to approach it in another way, in order the query understand or will I have to JOIN the restaurants_to_restaurant_foodtypes and restaurants_to_restaurant_offering`tables to the query so the form can properly direct this fields?


    Thank you guys...

  15. #15
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,192
    Mentioned
    17 Post(s)
    Tagged
    4 Thread(s)
    A join or subquery is required to resolve the available food types at any given restaurant.

    Let me break it down for you a little more here. The below code will locate all restaurants with food types 1,2 OR 3.

    PHP Code:
        // Food types filter
        
    if(!empty($arrFoodTypes) && !empty($arrFoodTypes[0])) {
        
    $arrSQLFilters[] = sprintf(
              
    'r.restaurants_id IN
                   (SELECT
                         restaurants_id
                      FROM
                         restaurants_restaurant_food_types
                     WHERE
                         restaurants_food_types_id IN (%s)
                     GROUP
                        BY
                         restaurants_id'
                
    , array(1,2,3/*mysql_real_escape_string( implode(',',$arrFoodTypes) )*/
            
    );
        } 

    The keyword there is OR. This means that the previous code will return any restaurant that has any of those food types not all. In order to get back only restaurants that have 1,2 AND 3 the having clause is needed.

    PHP Code:
        // Food types filter
        
    if(!empty($arrFoodTypes) && !empty($arrFoodTypes[0])) {
        
    $arrSQLFilters[] = sprintf(
              
    'r.restaurants_id IN
                   (SELECT
                         restaurants_id
                      FROM
                         restaurants_restaurant_food_types
                     WHERE
                         restaurants_food_types_id IN (%s)
                     GROUP
                        BY
                         restaurants_id
                    HAVING 
                         COUNT(*) = %u)'
                
    , array(1,2,3/*mysql_real_escape_string( implode(',',$arrFoodTypes) )*/
                
    3   // count($arrFoodTypes)
            
    );
        } 
    Now the trick behind how the having clause works starts with a bit of logical reasoning. Once the where clause has been resolved no more than 3 duplicate restaurants can exist in the result set (assuming a unique constraint exist on restaurants_id and food_types_id). Now by dropping out all restaurants that do not have 3 duplicates those that have all the given food types can be resolved instead of all restaurants with either food type.

    The entire thing is tad more complex then how your trying to approach it hence the subquery which gives back only the applicable restaurants and nothing else.

    Furthermore, breaking everything down into a conditional array where one condition is not dependent on the next makes the code much easier to follow and extend ie. add new conditions/take way conditions.

  16. #16
    SitePoint Wizard PHPycho's Avatar
    Join Date
    Dec 2005
    Posts
    1,201
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    @co.ador:
    do you think this will work?
    PHP Code:
    $where_sql .= " restaurantname='$strName'"
    $where_sql .= " zip='$strZipCode'"
    $where_sql .= " state='$strState'"
    $where_sql .= " restaurant_id='$arrFoodTypes'"
    $where_sql .= " restaurant_id='$arrOfferings'"

  17. #17
    SitePoint Wizard co.ador's Avatar
    Join Date
    Apr 2009
    Posts
    1,054
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    @oddz

    Now that I am analyzing the code The query and this conditions are in the code already.

    This is the query that will display all the restaurants based on users input and conditions

    PHP Code:
    $strSQL sprintf(
        
    'SELECT
             r.restaurants_id
            ,r.restaurantname
            ,r.image
            ,r.description
            ,r.address
            ,r.zip
            ,r.state
        FROM
            restaurants r
            %s
            %s
            %s
            LIMIT %d, %d'
        
    ,$boolIncludeZipCodes === true?'INNER JOIN restaurants_to_zip_codes rz ON r.restaurants_id = rz.restaurants_id ':''
        
    ,empty($arrSQLFilters)?'':' WHERE 
    '
    .implode(' OR ',$arrSQLFilters)   
        ,
    $boolIncludeZipCodes === true?'GROUP BY r.restaurants_id':''
        
    ,$offset$rowsperpage
    );
    $arrResult mysql_query($strSQL) or die("Cannot execute:"mysql_error());
        
        while(
    $arrRow mysql_fetch_assoc($arrResult)) {
            
    $arrRestaurants[] = $arrRow;
        
        } 

    this code here
    PHP Code:
    '.implode(' OR ',$arrSQLFilters) 
    I think will do the same effect we are dicussing here, for the ones that doesn't know the code the $arrSQLFilters contain the $strname, $strzipcode, $strstate, $arrOfferings and $arrFoodTypes

    so above the final output is $arrRestaurants that variable will contain all the results we need.

    As I have said the code I want to display as below won't need a query then I can use the query above to display the fields based on the search?

    fields I want to display.

    PHP Code:
    $result mysql_query($arrRestaurant);
    $arrstate mysql_fetch_array($result);
    echo 
    '<div class="information"><label>County:</label>
            <div>'
    $arrstate['county']. '</div>
          <label>State:</label>
                 <div>'
    $arrstate['state']. '</div>
               <label>Zip Code:</label>
                 <div>'
    $arrstate['zip']. '</div></div> <br><br>'
    The filters goes are as below. As you can see below all the five form fields are inside the filters and used at the query above.

    PHP Code:
     $arrSQLFilters = array();
        
        
    // whether or not zip codes table needs to be included
        
    $boolIncludeZipCodes false;

        
    // Zipcode filter
        
    if(!empty($strZipCode)) {
            
    $boolIncludeZipCodes true;
        
            
    $arrSQLFilters[] = sprintf(
                
    "r.zip LIKE '%s'"     
                
    ,"%$strZipCode%"
            
    );
        }
        
        
    // State filter
        
    if(!empty($strState)) {
            
    $boolIncludeZipCodes true;
        
            
    $arrSQLFilters[] = sprintf(
                
    "r.state = '%s'"
                
    ,$strState
            
    );
     
        }

        
    // Restaurants name filter
        
    if(!empty($strName)) {
            
    $arrSQLFilters[] = sprintf(
                
    "r.restaurantname LIKE '%s'"
                
    ,"%$strName%"
            
    );
        }

        
    // Food types filter
        
    if(!empty($arrFoodTypes) && !empty($arrFoodTypes[0])) {
        
    $arrSQLFilters[] = sprintf(
              
    'r.restaurants_id IN
                   (SELECT
                         restaurants_id
                      FROM
                         restaurants_restaurant_food_types
                     WHERE
                         restaurants_food_types_id IN (%s)
                     GROUP
                        BY
                         restaurants_id
                    HAVING 
                         COUNT(*) = %u)'
                
    ,/*mysql_real_escape_string(*/ implode(',',$arrFoodTypes/*)*/
                
    ,count($arrFoodTypes)
            );
        }

        
    // Offerings Filter ie. eat-in, lunch, dinner, etc
        
    if(!empty($arrOfferings)) {
          
    $arrSQLFilters[] = sprintf(
              
    'r.restaurants_id IN
                   (SELECT
                         restaurants_id 
                     FROM
                         restaurants_to_restaurant_offering
                     
                     WHERE
                         restaurants_offerings_id IN (%s)
                     GROUP
                        BY
                         restaurants_id
                    HAVING 
                         COUNT(*) = %u)'
                
    ,/*mysql_real_escape_string(*/ implode(',',$arrOfferings/*)*/
                
    ,count($arrOfferings) );
         } 
    @PHPycho

    They are working for the first three conditions but then when it comes to the conditional arrays

    PHP Code:
    $where_sql .= " restaurant_id='$arrFoodTypes'"
    $where_sql .= " restaurant_id='$arrOfferings'"
    it won't display the fields below, Which I am trying to display them whenever one of the five inputs are set $strname OR $strzipcode OR $strstate OR $arrOfferings OR $arrFoodTypes. Even $arrOfferings and $arrFoodTypes.

  18. #18
    SitePoint Wizard co.ador's Avatar
    Join Date
    Apr 2009
    Posts
    1,054
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    this is the solution, as Oddz has said the $arrSQLFilters are set up in the query to implode the OR condition with the $arrSQLFilters as above and below, I just need to place the html code and the $strSQL variable containing the query. The $arrSQLFilters for foodTypes and Offerings will meet this conditions and will display the county, zip and state when users input are submited. Also I just needed to add the county field to the SELECT statatment like:

    PHP Code:
    $strSQL sprintf(
        
    'SELECT
             r.restaurants_id
            ,r.restaurantname
            ,r.image
            ,r.description
            ,r.address
            ,r.zip
            ,r.state
            ,r.county
        FROM
            restaurants r
            %s
            %s
            %s
            LIMIT %d, %d'
        
    ,$boolIncludeZipCodes === true?'INNER JOIN restaurants_to_zip_codes rz ON r.restaurants_id = rz.restaurants_id ':''
        
    ,empty($arrSQLFilters)?'':'WHERE 
    '
    .implode(' OR ',$arrSQLFilters)   
        ,
    $boolIncludeZipCodes === true?'GROUP BY r.restaurants_id':''
        
    ,$offset$rowsperpage
    );
    $arrResult mysql_query($strSQL) or die("Cannot execute:"mysql_error());
        
        while(
    $arrRow mysql_fetch_assoc($arrResult)) {
            
    $arrRestaurants[] = $arrRow;
        
        }
    $result mysql_query($strSQL);
    $arrstate mysql_fetch_array($result);
    echo 
    '<div class="information"><label>County:</label>
            <div>'
    $arrstate['county']. '</div>
          <label>State:</label>
                 <div>'
    $arrstate['state']. '</div>
               <label>Zip Code:</label>
                 <div>'
    $arrstate['zip']. '</div></div> <br><br>'
    Thank you guys.


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
  •