www.webdeveloper.com
Results 1 to 2 of 2

Thread: Search for "blank" by "blank" (keyword with drop-down for category/column)

Hybrid View

  1. #1
    Join Date
    Dec 2012
    Posts
    1

    Talking Search for "blank" by "blank" (keyword with drop-down for category/column)

    Hey everyone!

    I'm having a really tough time figuring out what I would imagine is a pretty simple MySQL database search. I'd like to be able to search for a keyword and use a drop down to designate which column to look in. For example:

    Search for keyword: ________________ (text field) by Name, Policy #, or Email Address (drop-down).

    Here is the code that I'm currently using:

    HTML Form
    Code:
    <table border="0" cellpadding="0" cellspacing="0">
    <form action="search.php" method="post">		
    <tr>
    <td><input type="text" value="Search" name="term" onblur="if (this.value=='') { this.value='Search'; }" onfocus="if (this.value=='Search') { this.value=''; }" class="top-search-inp" /></td>
    <td>
    <select class="styledselect" name='searchtype'>
    <option value="*"> All</option>
    <option value="Recipient"> Name</option>
    <option value="Code"> Policy #</option>
    <option value="Email">Email</option>
    </select> 
    </td>
    <td>
    <input type="image" src="images/shared/top_search_btn.gif"  />
    </td>
    </tr>
    </form>		
    </table>
    MySQL Query & Display Results
    Code:
    				
    <?php
    mysql_connect("localhost", "username", "password") or die(mysql_error());
    mysql_select_db("database") or die(mysql_error());
    
    $term = $_POST['term'];
    $filter = $_POST['searchtype'];
    					
    $query = "SELECT * FROM wp_gcl_certificates where Recipient like '%$term%'"; 
    					
    $result = mysql_query($query) or die(mysql_error());
    
    while($row = mysql_fetch_array($result)){
    echo "<tr class=\"alternate-row\">";
    echo "<td><input  type=\"checkbox\"/></td>";
    echo "<td>{$row['recipient']}</td>";
    echo "<td><a href=\"mailto:{$row['email']}\">{$row['email']}</a>";
    echo "<td>\${$row['price']}</td>";
    echo "<td>{$row['status']}</td>";
    echo "<td><a href=\"/gift-certificates-lite/gcl_show.php?cid={$row['code']}\">{$row['code']}</a>";
    echo "<td class=\"options-width\">";
    echo "<a href=\"/gift-certificates-lite/gcl_show.php?cid={$row['code']}\" title=\"Edit\" class=\"icon-1 info-tooltip\"></a>";
    echo "<a href=\"\" title=\"Edit\" class=\"icon-2 info-tooltip\"></a>";
    echo "<a href=\"\" title=\"Edit\" class=\"icon-3 info-tooltip\"></a>";
    echo "<a href=\"\" title=\"Edit\" class=\"icon-4 info-tooltip\"></a>";
    echo "<a href=\"\" title=\"Edit\" class=\"icon-5 info-tooltip\"></a>";
    echo "</td>";
    echo "</tr>";
    }
    ?>
    This works great for searching the table 'wp_gcl_certificates' by 'Recipient' but I can't seem to figure out how to make the column I search by adjustable.

    I tried this line:
    HTML Code:
    $query = "SELECT * FROM wp_gcl_certificates where '%$filter%' like '%$term%'";
    with no luck

    I'm new to this forum, so I'm sorry if I posted in the wrong category. Any help would be GREATLY appreciated!
    Thanks in advance.

  2. #2
    Join Date
    May 2003
    Posts
    599
    If your MySql DB is using case sensitive table and column names, you need to make sure to pass the appropriate column name filter...so if your column name is 'email' you need to do either:

    HTML Code:
    <option value="email">Email</option>
    or:

    PHP Code:
    $filter strtolower($_POST['searchtype']);//of course this method wont work if some column names use caps and others dont 
    Then:

    PHP Code:
    $query "SELECT * FROM wp_gcl_certificates where $filter like '%$term%'"

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles