SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Feb 2001
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Having problems getting this to work. If I leave my form blank and hit submit I get the entire database.

    If I put in "Cotten" as a lastname I get nothing returned. I have added print statements to the bottom so I know $item and $column are getting returned.

    What am I missing?


    FORM

    <h2>Search for info</h2>

    Enter any one of the below. Leave blank for entire database.
    <P>Select one or more sections to search:
    <form method=post action="querysql.php">
    <SELECT NAME=column MULTIPLE>
    <OPTION value=emp_comp_name>Computer Name</OPTION>
    <OPTION value=emp_last_name>Last Name</OPTION>
    <OPTION value=emp_first_name>First Name</OPTION>
    <OPTION value=ser_number>Serial Number</OPTION>
    <OPTION value=ser_manufacturer>Manufacturer</OPTION>
    </SELECT>
    </P>


    <p>

    Search for: <input type=text size=20 name=item><br><p>
    <input type=submit>
    </form></h2>


    QUERYSQL.PHP

    <?php

    $db_host = 'localhost';
    $db_user = 'fee';
    $db_pass = 'foo';
    $db_name = 'phone';

    $db_table = 'EMPLOYEE';

    mysql_connect("$db_host","$db_user","$db_pass");

    mysql_select_db("$db_name") ;

    $result = mysql_query("SELECT * FROM $db_table WHERE '$column' LIKE '$item%'")
    or die(mysql_error());;

    while($row = mysql_fetch_object($result)) {


    $tmp .= "Computer Name: $row->emp_comp_name <br>\n";
    $tmp .= "Last Name: $row->emp_last_name <br>\n";
    $tmp .= "First Name: $row->emp_first_name <br>\n";
    $tmp .= "Phone: $row->emp_phone <br><hr>\n";
    }
    $header = "<html> <body>";
    Print $header;
    Print $tmp;
    Print $column;
    Print $item;
    ?>


    Appreciate any help I can get on this.

    Thank you in advance,

    Ray
    raycotten@telocity.com

  2. #2
    Serial Publisher silver trophy aspen's Avatar
    Join Date
    Aug 1999
    Location
    East Lansing, MI USA
    Posts
    12,937
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    where '$column' like '$item%'

    First of all why is the column name is quotes?

    Now if you dont fill in item the DB will see this:

    like '%'

    And it'll return everything.

    They way you have it if $item was 'Er' it'd return Error, Eroneous, Erogonious, Erotic, Er. Also realize its case sensitive - if you don't want it to be this way use LCASE() on both the search term and the column.

    If you coded it like this '%$item%' it would return peer, beer, mutterer, perspiration, etc.

    if you coded it like this '% $item %' it would only return 'er' because you include the spaces around your name.

    And thats all the different ways to use like - I wasn't sure what your exact question was since you didn't include your DB structure but I hope that helps.
    Chris Beasley - I publish content and ecommerce sites.
    Featured Article: Free Comprehensive SEO Guide
    My Guide to Building a Successful Website
    My Blog|My Webmaster Forums

  3. #3
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    If the column being searched is a TEXT type (TINYTEXT, SHORTTEXT, TEXT, MEDIUMTEXT, LONGTEXT) then the LIKE search is case-insensitive without the use of LCASE().
    Kevin Yank
    CTO, sitepoint.com
    I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
    Baby’s got back—a hard back, that is: The Ultimate CSS Reference

  4. #4
    SitePoint Member
    Join Date
    Feb 2001
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok, looks like the problem isn't the LIKE statement but the WHILE loop.

    For somereason it does not run when a single variable is input.

    Researching:

    while($row = mysql_fetch_object($result)) {

    Print "In the while loop<BR>";
    $tmp .= "Computer Name: $row->emp_comp_name <br>\n";
    $tmp .= "Last Name: $row->emp_last_name <br>\n";
    $tmp .= "First Name: $row->emp_first_name <br>\n";
    $tmp .= "Phone: $row->emp_phone <br><hr>\n";
    }


    Thanks,

    Ray

  5. #5
    SitePoint Member
    Join Date
    Feb 2001
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks all that was it!!!


    Remove the quotes from 'column' and it works now.

    Thanks all!

    Ray


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
  •