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?
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
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.
i used your data and it’s working just fine
first, search for singers
SELECT cd_id, cd_number, cd_title FROM cds
WHERE cd_title LIKE [COLOR="red"]'%singers%'[/COLOR]
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
SELECT cd_id, cd_number, cd_title FROM cds
WHERE cd_title LIKE [COLOR="Red"]'%cambridge%'[/COLOR]
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
Are you using php? If so:
$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.
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.
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.