SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Member
    Join Date
    Jun 2002
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Any in WHERE Clause

    Hi,

    I've got this code:

    $sql_select = "SELECT * FROM `ad2` WHERE f8 = \"$locationform\" and sitecatid = \"$categoryform\" LIMIT $limitnumber" ;

    How can I select "Any" as a value for \"$locationform\" so that mysql returns all data in the fields. (in this case the field would be f8)

    Thanks in advanced
    Thomas Boepple
    Emerald Computers

  2. #2
    Super Ninja Monkey Travis's Avatar
    Join Date
    Dec 2001
    Location
    Sioux City, Iowa
    Posts
    691
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    just use $sql_select = "SELECT * FROM `ad2`" and it will retrieve all the results. Example:

    PHP Code:
    <?php
    mysql_connect
    ('localhost''blah''easypass');
    mysql_select_db('blah');

    $sql_select "SELECT * FROM ad2";
    $result mysql_query($sql_select);

    while (
    $row mysql_fetch_assoc($result)) {
        echo(
    '<pre>');
        
    print_r($row);
        echo(
    '</pre><br>');
    }
    ?>
    Travis Watkins - Hyperactive Coder
    My Blog: Realist Anew
    Projects: Alacarte - Gnome Menu Editor

  3. #3
    SitePoint Guru okrogius's Avatar
    Join Date
    Mar 2002
    Location
    US
    Posts
    622
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Any in WHERE Clause

    Originally posted by emerald
    Hi,

    I've got this code:

    $sql_select = "SELECT * FROM `ad2` WHERE f8 = \"$locationform\" and sitecatid = \"$categoryform\" LIMIT $limitnumber" ;

    How can I select "Any" as a value for \"$locationform\" so that mysql returns all data in the fields. (in this case the field would be f8)

    Thanks in advanced
    PHP Code:
    if ( empty($where) ) {
    $where 'id>0';   


  4. #4
    SitePoint Member
    Join Date
    Jun 2002
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the replies. That wasn't exactly what I was looking for unfortuntally.

    I want the term used to display all. Example to select all rows is * but this won't work for fetching all data. I want to have the any term in the where clause.

    Something like:
    PHP Code:
    $sql_select "SELECT * FROM `ad2` WHERE f8 = * and sitecatid = \"$categoryform\" LIMIT $limitnumber
    but obviously this doesn't work

    I have a form called Location. This is a drop down with all these locations and they all have a value. But I want to have an option in the drop down to select ALL Locations. I want to know how to do this using the script logic above.

    Thanks in advanced once again
    Thomas Boepple
    Emerald Computers

  5. #5
    SitePoint Wizard Chris82's Avatar
    Join Date
    Mar 2002
    Location
    Osnabrück
    Posts
    1,003
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This won't work in MySQL I think. You would need to use a subquery, maybe like this:

    Code:
    SELECT * FROM ad8 WHERE sitecatid = $categoryform
    AND f8 IN (SELECT f8 FROM ad8);
    What you could try is:

    - SELECT f8 FROM ad8
    - join the results with OR
    - try it with the query:

    Code:
    SELECT * FROM ad8 WHERE 
    (f8 = 1 OR f8 = 2 ...) 
    AND sitecatid = $categoryform;
    There is also a note on how to rewrite IN subqueries in the manual.

  6. #6
    SitePoint Enthusiast aivarannamaa's Avatar
    Join Date
    Sep 2002
    Location
    Estonia
    Posts
    36
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    But I want to have an option in the drop down to select ALL Locations.
    Let's pretend the value of this special option is 'all_locations'. Then you should check the value of $locationform before creating the query string and omit location condition when user asked for all locations:

    PHP Code:
    if ($locationform == 'all_locations') {
        
    $sql_select "SELECT * FROM `ad2` WHERE sitecatid = \"$categoryform\" LIMIT $limitnumber;
    }

    else {
        
    $sql_select "SELECT * FROM `ad2` WHERE f8 = \"$locationform\" and sitecatid = \"$categoryform\" LIMIT $limitnumber;
    }

    // execute the query 
    Maybe this is what you had in mind.

    Aivar

  7. #7
    SitePoint Member
    Join Date
    Jun 2002
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks a million aivarannamaa! this is exactly what i had in mind.


    Thomas Boepple
    Emerald Computers

  8. #8
    SitePoint Member
    Join Date
    Jun 2002
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by Chris82
    This won't work in MySQL I think. You would need to use a subquery, maybe like this:

    Code:
    SELECT * FROM ad8 WHERE sitecatid = $categoryform
    AND f8 IN (SELECT f8 FROM ad8);
    What you could try is:

    - SELECT f8 FROM ad8
    - join the results with OR
    - try it with the query:

    Code:
    SELECT * FROM ad8 WHERE 
    (f8 = 1 OR f8 = 2 ...) 
    AND sitecatid = $categoryform;
    There is also a note on how to rewrite IN subqueries in the manual.
    Thank you too Chris, that explained another problem I had.
    Thomas Boepple
    Emerald Computers

  9. #9
    Mlle. Ledoyen silver trophy seanf's Avatar
    Join Date
    Jan 2001
    Location
    UK
    Posts
    7,168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Moved to a more appropriate forum

    Sean
    Harry Potter

    -- You lived inside my world so softly
    -- Protected only by the kindness of your nature


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
  •