Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    New to the CF scene
    Join Date
    Mar 2007
    Location
    wycombe
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    searching the db with optional values

    I need to create a program that will allow the user to search the mysql database using around 15 values. The values are optional.

    It is a gardening site, so I want users to be able to search for only 'green' and skill level '1' plants for example.

    There are about 15 optional values that they can choose from.

    I know how to search the db for multiple values using SQL queries but Im stuck when it comes to searching the database if the user for varying numbers of values depending on whether they entered a value in the HTML search form.

    So I only want the SQL to search for values which have been inputted by the user in the HTML form.

    Does anyone have any helpful tips ?
    Thanks
    Last edited by simstar; 03-12-2007 at 08:02 PM. Reason: spelling mistake

  • #2
    Regular Coder devinemke's Avatar
    Join Date
    Dec 2004
    Location
    NYC
    Posts
    443
    Thanks
    0
    Thanked 12 Times in 11 Posts
    when constructing your SQL string simply leave out the fields that the user left empty in the form

  • #3
    New to the CF scene
    Join Date
    Mar 2007
    Location
    wycombe
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by devinemke View Post
    when constructing your SQL string simply leave out the fields that the user left empty in the form
    How do I do that?

    I only know how to write simple SQL statements...I dont know how to create ones using php, depending on what the user has entered.

    There has got to be a more simple way than writing 100 -150 SQL statements for each possible combination

  • #4
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    The ideal thing to do is to use "fulltext" indices on the fields that you deem searchable. Depending on how your database is designed, however, this could lead to some overhead that may impact performance.

    Anyways... you can get acquainted with fulltext indexing in the MySQL manual here.

  • #5
    Regular Coder devinemke's Avatar
    Join Date
    Dec 2004
    Location
    NYC
    Posts
    443
    Thanks
    0
    Thanked 12 Times in 11 Posts
    PHP Code:
    <?php
    if (!isset($_POST['submit']))
    {
        echo 
    '
        <form action="" method="POST">
        first name: <input type="text" name="fields[first_name]" value=""><br>
        last name: <input type="text" name="fields[last_name]" value=""><br>
        email: <input type="text" name="fields[email]" value=""><br>
        <input type="submit" name="submit" value="submit">
        </form>
        '
    ;
    }
    else
    {
        
    $sql_array = array();
        foreach (
    $_POST['fields'] as $key => $value)
        {
            
    $value trim($value);
            if (
    $value) {$sql_array[] = $key  " = '" $value "'";}
        }
        
        if (
    $sql_array)
        {
            
    $sql 'SELECT * FROM table WHERE ' implode(', '$sql_array);
            echo 
    $sql;
        }
        else
        {
            echo 
    'all fields blank';
        }
    }
    ?>

  • #6
    New to the CF scene
    Join Date
    Mar 2007
    Location
    wycombe
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for the code, it works, but I am only used to using

    Code:
    while($row = mysql_fetch_array( $result )) {
    to display the data.. is there another method to display the sql results?

    cheers for the help

  • #7
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    That method should still work...


  •  

    Posting Permissions

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