SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Evangelist JordashTalon's Avatar
    Join Date
    Dec 2008
    Posts
    505
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Search through MySQL Database for names

    Ok,

    One of my rows is named 'Name' which has a string of the name of a person e.g. 'John Doe'

    I made a form which does this
    <input type="text" name="search">
    <input type="submit">

    $result=mysql_query("SELECT * FROM table WHERE Name LIKE Search")

    Which I thought would work but in the search field I have to type out the entire string

    e.g. John Doe

    instead of only John

    What would be a good way to do this?

    Thanks

  2. #2
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT * FROM table WHERE Name LIKE '&#37;Search%'
    note that any search term using % at the beginning will not allow an index to be used on that column thus slowing the search down significantly as your data grows.

    Best idea is to split a field like this into first and last name fields. if this is just an example and you are searching through a bunch of text (i.e. a whole article for a group of words) then look into FULLTEXT INDEXES.

  3. #3
    SitePoint Evangelist JordashTalon's Avatar
    Join Date
    Dec 2008
    Posts
    505
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok, adding % to the beginning and end worked,

    Thanks

    BTW what does the % mean?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    &#37; is the standard SQL wildcard character for LIKE searches, representing zero or more characters

    the other wildcard character is _ which stands for exactly one character
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Evangelist JordashTalon's Avatar
    Join Date
    Dec 2008
    Posts
    505
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok thanks for the help everyone!

  6. #6
    SitePoint Zealot
    Join Date
    Dec 2008
    Posts
    120
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    One more idea
    you can split search sting on empty space
    and make sql query like this

    select * from table where
    name like '&#37;first search word%'
    OR name like '%second search word%'
    ...

  7. #7
    SitePoint Evangelist JordashTalon's Avatar
    Join Date
    Dec 2008
    Posts
    505
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Good idea


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
  •