SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Enthusiast
    Join Date
    Mar 2006
    Posts
    33
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    LIKE does not work properly

    I have set up a page with the following MySQL query:

    SELECT cd_id, cd_number, cd_title FROM cds WHERE cd_title LIKE '%$search%' ORDER BY cd_number ASC

    This finds some of the search terms that are there to be found, but others it does not find. Can anyone tell me why please?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Peter Finch View Post
    Can anyone tell me why please?
    because you've done something wrong in either the data in the table, or the data in the search term

    neither of which we can see

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Mar 2006
    Posts
    33
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here is the data from an Export. It goes id, number, cd_title, date inserted and date updated. There is more, but this makes the point:

    INSERT INTO `cds` VALUES (1, '201', 'Cambridge Singers a cappella, directed by John Rutter', '2010-01-27 11:07:57', '0000-00-00 00:00:00');
    INSERT INTO `cds` VALUES (2, '202', 'This Joyous Night, Music for Christmas, Cavendish Singers and Ensemble, Mavinder Ratten', '2010-01-26 12:28:21', '0000-00-00 00:00:00');
    INSERT INTO `cds` VALUES (3, '204', 'The Tallis Scholars sing Thomas Tallis - disc 2', '2010-01-24 00:00:00', '0000-00-00 00:00:00');
    INSERT INTO `cds` VALUES (7, '301', 'BOITO Mephistopheles Disc 1', '2010-01-25 17:30:24', '0000-00-00 00:00:00');
    INSERT INTO `cds` VALUES (8, '302', 'BOITO Mephistopheles Disc 2', '2010-01-27 11:20:00', '2010-01-27 12:36:34');
    INSERT INTO `cds` VALUES (9, '303', 'BOITO Mephistopheles Disc 3', '2010-01-25 17:49:00', '0000-00-00 00:00:00');

    In the first row, when I ask it to find 'Singers' it does it just like that, but it says that 'Cambridge' is nowhere to be found. It can find Sing and Si as well but not Cam.

    This is a home job, but it is useful practice and it would be a useful index to my CDs if only it would work.

    I would be grateful to be told that this certainly should not happen, if that is the case.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    i used your data and it's working just fine

    first, search for singers
    Code:
    SELECT cd_id, cd_number, cd_title FROM cds 
    WHERE cd_title LIKE '%singers%' 
    ORDER BY cd_number ASC
    
    cd_id	cd_number	cd_title
    1	201	Cambridge Singers a cappella, directed by John Rutter
    2	202	This Joyous Night, Music for Christmas, Cavendish Singers and Ensemble, Mavinder Ratten
    second, search for cambridge
    Code:
    SELECT cd_id, cd_number, cd_title FROM cds 
    WHERE cd_title LIKE '%cambridge%' 
    ORDER BY cd_number ASC
    
    cd_id	cd_number	cd_title
    1	201	Cambridge Singers a cappella, directed by John Rutter
    you must be doing something different
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    4,998
    Mentioned
    100 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Peter Finch View Post
    I have set up a page with the following MySQL query:

    SELECT cd_id, cd_number, cd_title FROM cds WHERE cd_title LIKE '%$search%' ORDER BY cd_number ASC

    This finds some of the search terms that are there to be found, but others it does not find. Can anyone tell me why please?
    Are you using php? If so:

    PHP Code:
    $sql="SELECT cd_id, cd_number, cd_title FROM cds WHERE cd_title LIKE  '%$search%' ORDER BY cd_number ASC";
    echo 
    $sql
    Post here the output of the echo $sql for when your trying to search for singers for example.
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  6. #6
    SitePoint Enthusiast
    Join Date
    Mar 2006
    Posts
    33
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you gentlemen for the advice, and especially you r937. I have got it now. These things are always all too obvious once you find them. My PHP included:

    $a = @mysql_num_rows($result);
    if ($a == 0) $b = ''; else if ($a == 1) $a = 'The search has found one CD:'; else if ($a > 1) $b = 'The search has found ' . $a . ' CDs:';

    echo "$b";

    if ($a >= 1) {echo '<table border=0 cellpadding=0 cellspacing=3 align=center width=800> etc etc

    The first bit was designed to write a suitable message to say how many CDs were found. However, since I had put $a instead of $b up there for $a == 1 it would not write out the result when there was only one. There was only one Cambridge, but several Singers. I suppose that should have been a clue as well.

    You could not have known that I had done that, but had you not said that it worked for you I might not have thought of this line of investigation. Many thanks.

  7. #7
    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)
    Simple thing to do and far too many people overlook it ... Don't use a front end application. Do your work in mysql. once you know that is working, then and only then, worry about putting it into the front end application.

    Then you aren't trying to trouble shoot something in mysql when (as in this case) the error is in the front end application code.

    Just my 2 cents.


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
  •