[SOLVED] search results not what they should be

[jasonc]

I have been trying desperately to fix a problem with either my code or the mysql DB!

 

I am using PHP.

 

I have a lot of rows about 4000 or so, all set as 'text' dont know why but still.

 

I have a search page i wrote but it does not perform correctly, it does not find words i search for.

 

i have been told to change to using a 'full text search' method but this seems too much work, or have i got the wrong idea? is it easy to convert?

 

it was suggested that the mysql DB could be corrupt but i have checked i think it is ok, but how can i be sure?

 

to test further i backed up everything and create a new DB and tables and inserted manually entries with a few words for me to search for and then tested my scripts and all seems ok, it worked. but my DB put back in place caused problems again.

 

just thinking about it are there any special characters that i should NOT use that may cause the DB to seem corrupt?

 

i am going to create a subdomain and setup a new DB so it can be tested again by maybe someone who could help solve this problem i am having. any offers?

 

also how easy is it to convert my DB to using the full text search method and what examples of code can anyone give me to explain how this actually works so that the exact phrase being searched for is shown and not all that look like the phrase, as in the .... like %word% .... method

 

 

thanks in advance for your help as i am now going mad with frustrastion as it should not be so hard to do this should it. but i seem to be getting it all wrong!

 

thanks

[fenway]

Post your table structure, some same data, and the query you are using.

[jasonc]

the query..

 

$query = "SELECT * FROM `info` WHERE `desc` LIKE '%".$search."%'";

 

the tables..

 

-- Table structure for table `info`

 

CREATE TABLE `info` (

  `id` int(11) NOT NULL auto_increment,

  `desc` text,

  PRIMARY KEY  (`id`)

) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3613 ;

 

-- Dumping data for table `info`

 

INSERT INTO `info` VALUES (3374, 'Click Monkeys - Google''s arch enemies I''m guessing\r\n');

INSERT INTO `info` VALUES (3375, 'Coathanger Bench');

INSERT INTO `info` VALUES (3376, 'Tapedeck.Org - Celebrating the gentle art of the cassette tape');

INSERT INTO `info` VALUES (3377, 'Offensive Drawings - NSFW, as an artist draws the rude thing but then changes into something safe');

INSERT INTO `info` VALUES (3378, 'Best Crappy Stuff');

INSERT INTO `info` VALUES (3379, 'Kinetic Energy - Energy saving household items');

INSERT INTO `info` VALUES (3380, 'National Lampoon Covers - I loved NatLamp and just sold my collection from 1984 \r\n');

INSERT INTO `info` VALUES (3381, 'Be Net-aware! - SexyAmber might not be quite your type\r\n');

INSERT INTO `info` VALUES (3382, 'TAKE THAT, PLUTO! - Serves him right for chasing the kid');

INSERT INTO `info` VALUES (3383, 'Rolled-up Newspaper Dog Sculpture\r\n');

INSERT INTO `info` VALUES (3384, 'Urban Furniture - The brilliant Spanish designer Spy''s website holds these marvellous redesigns of everyday street architecture (such as fences and traffic signals)');

INSERT INTO `info` VALUES (3385, 'Darth Vader Helmet Redesigns');

INSERT INTO `info` VALUES (3386, 'Rotten Neighbour - It''s newish at the moment but this could either be a valuable tool to avoid horrible neighbours when moving or a bitchfest...\r\n');

INSERT INTO `info` VALUES (3387, 'Depth Of Field - Pretty and cool\r\n');

INSERT INTO `info` VALUES (3388, 'Golf Drive - Use your catapult to hole the boulder. Golf prehistoric-style');

INSERT INTO `info` VALUES (3389, 'Reuben Miller');

INSERT INTO `info` VALUES (3390, 'Simpsons Scenes And Their References - This is a labour of love. So many of these passed me by');

INSERT INTO `info` VALUES (3391, 'Teller Speaks - We knew Penn Jillette''s magic partner spoke. Here''s the proof from Miami Vice\r\n');

INSERT INTO `info` VALUES (3392, 'Topless Mug Shots - The Smoking Gun with some nice (and, quite frankly, not so nice) topless shots\r\n');

INSERT INTO `info` VALUES (3393, 'Dancing Hotdogs');

INSERT INTO `info` VALUES (3394, 'Cheetos Art ');

INSERT INTO `info` VALUES (3395, 'Frezenda - How to add adult decor with child elements, plus other interesting design');

INSERT INTO `info` VALUES (3396, 'The Best And Worst Logo Redesigns - It makes you question my own logo but a fantastic, objective article showing what should and should not be changed\r\n');

INSERT INTO `info` VALUES (3397, 'Universal Decision Maker - Way more entertaining than flipping a coin ');

INSERT INTO `info` VALUES (3401, 'Random Good Stuff');

INSERT INTO `info` VALUES (3400, 'Droll Things');

INSERT INTO `info` VALUES (3402, 'Spiderman Lamp - Where was all this comic stuff when I was a kid?');

INSERT INTO `info` VALUES (3403, 'The Economist Advertising Campaign - They used some really creative graphic design studios. Very nice (I still wouldn''t buy it though)\r\n');

INSERT INTO `info` VALUES (3404, 'Moonwalking Bird - Jesus that is amazing\r\n');

INSERT INTO `info` VALUES (3405, 'Mouse Jiggler - USB plug-in which keeps your cursor moving so it doesn''t go to screensaver. There is a point to screensaver you know, guys\r\n');

 

[fenway]

Great... now what things don't work?

[jasonc]

the search!

 

i search for a word from the entries but it sometime only returns the top three or 5 rows of the DB and not ones that contain the word searched for.

 

[fenway]

Give specific examples.

[jasonc]

searching for 'art' does not return results with 'art' in them it shows the first three in the DB

[fenway]

Post the actual query... I can't guess why it's not working.

[PFMaBiSmAd]

Well, I created a table with the posted rows and putting various strings into $search does return the expected results.

 

I would guess that your form is not setting the $_POST variable that you think it is with what you think or something else in your form processing code is modifying or overwriting it.

 

You can echo out the $query variable, which is what fenway has asked, to see what it actually contains.

 

Otherwise, to get specific help with what your form and form processing code is doing you will need to post them.

[jasonc]

i have also done a search in phpmyadmin and copied the code from there to the search script and changed the bit that adds the word being looked for and still the same.

 

SELECT * FROM `info` WHERE `desc` LIKE CONVERT( _utf8 '%word%' USING latin1 ) COLLATE latin1_swedish_ci

 

if i was to search for 'cats' i get 4 results

also 'dogs' returns 4

'art' returns something like the first 350 results !!

 

 

i also had an idea, i just put the same code phpmyadmin created in to my search script and this too showed the same results as if i entered them myself!

 

this is my search script.

 

 

<table width="100%">
  <tr> 
    <td> <form name="find" method="post" action="?ac=s">
        <div align="center"><font face="Tahoma">Search for link: </font>
	<input type="text" name="search"> <input type="submit" name="findlink" value="Find link">
        </div>
      </form></td>
  </tr>
</table>
<?
					if ($_POST['search']) { // show link to select from
							$query = "SELECT * FROM `sites` WHERE `desc` LIKE '%".stripstring($_POST[search])."%'";
							echo("'".$query."'");
							$getsites = mysql_query($query);
						$numberofrows = mysql_num_rows($getsites) or die(mysql_error());
						?>
						<p align="center"><strong><em><font size="4" face="Tahoma">Showing links found</font></em></strong></p>
						<table width="100%">
						  <? for ($i = 0; $i < $numberofrows; $i++) {
																	$id = mysql_result($links, $i, "id"); $submitgmtdatetime = mysql_result($links, $i, "submitgmtdatetime");
																	$url = mysql_result($links, $i, "url"); $desc = mysql_result($links, $i, "desc");
																	$sourceurl = mysql_result($links, $i, "sourceurl"); $sourcedesc = mysql_result($links, $i, "sourcedesc");
																	$clicks = mysql_result($links, $i, "clicks"); $clicksviarss = mysql_result($links, $i, "clicksviarss");
																	$submittedby = mysql_result($links, $i, "submittedby");
																	?>
						  <tr>
							<td> 
							  <a class="one" href="?h=s&v=<?=$id;?>" target="_blank"><?=$desc;?></a> submitted on <? echo ( date('d-m-Y', strtotime($submitgmtdatetime)) );
								if ($sourceurl) { echo('  Via <a class="one" href="http://' . $sourceurl . '" target="_blank">' . $sourcedesc . '</a>'); } ?>
						</td>
					  </tr>
					  <? } ?>
					</table>
					<? } else { ?>
					<p> </p><p> </p><p> </p><p> </p><p> </p><p> </p><p> </p>
					<? }
?>

[jasonc]

another thing that came to mind is...

 

are there characters that i might have (i should not due to my stripstring routine that removes special characters and extra spaces before being entered in the DB) that could be messing up the rest of the DB ?

[fenway]

Why the collation stuff?

[PFMaBiSmAd]

Since you are getting your same results by putting the query into phpmyadmin, let us just confirm what the expected results should be -

 

LIKE '%art%' will match "Artist", "whereartthou", and anything else that has three consecutive characters "art" in it.

 

Do the results you get actually contain entries with the character sequences "cats", "dogs", or "art" anywhere in them?

 

We have been asking you for specific examples of what you are getting, because the only thing we can see is what you write in your post. You are saying that the results are not correct, but you are not posting the actual results. This makes if very difficult to duplicate your problem or to help you.

 

If you enter "art" for a term, post the first 5 or so results so that someone can "see" what you are seeing.

[jasonc]

searching for 'and' returns of all these results...  (all results shown)

 

Click Monkeys - Google's arch enemies I'm guessing

Coathanger Bench

Tapedeck.Org - Celebrating the gentle art of the cassette tape

Offensive Drawings - NSFW, as an artist draws the rude thing but then changes into something safe

Best Crappy Stuff

Kinetic Energy - Energy saving household items

National Lampoon Covers - I loved NatLamp and just sold my collection from 1984

 

 

 

 

 

searching for 'but' returns of all these results...  (all results shown)

 

Click Monkeys - Google's arch enemies I'm guessing

Coathanger Bench

Tapedeck.Org - Celebrating the gentle art of the cassette tape

 

 

 

 

 

searching for 'rat' returns of all these results...  (all results shown)

 

Click Monkeys - Google's arch enemies I'm guessing

 

 

 

 

 

it has been suggested that the DB could be corrupt or something so i done an 'optimise' but do not know how i check for bugs that could be causing the results to be wrong.

 

also if i search for words that are not in the DB at all i get no results as expected.

 

 

 

[fenway]

What if you do this without the collations?

[PFMaBiSmAd]

From the code you have posted in this thread, you are operating on different tables. In the earlier post the table is 'info' and in the later post is it 'sites'. In your last piece of code, the mysql_query() is using $getsites for the result resource but your are fetching data using $links so there is no way it is producing anything having to do with reality.

 

We are not getting accurate information on what your code is, what the query is, and what the results are. Based on the last code you posted, you are probably doing a query earlier in the code and then it is outputting those results instead of what the last query got.

[jasonc]

all information is correct, apart from that, being that i forgot to change the sites to info but the script is still the same otherwise.

 

i created copies of the scripts and have been using them to test the scripts out.

 

i also created copies of the data too, and have been using the copies which is called 'info'

 

sorry for any confusion.

 

 

[jasonc]

Why the collation stuff?

 

i thought i'd try something and use the code produced by phpmyadmin.

 

i also copied the full code into my scripts leaving the word i was searching for in the code too, and not having what was put in the form added to the code.

 

and still the same thing, the wrong results.

 

so this rules out POST sending the wrong info to the database.

[jasonc]

the mysql_query() is using $getsites for the result resource but your are fetching data using $links

 

this is what was causing it to fail

 

it has been causing me problems ever since i first wrote the code.

 

thanks for your help.

[fenway]

Glad you got it working.... but this is why I always stress that you run the code directly, no fancy php -- if it works in mysql and it doesn't work in your script, there's something wrong with the latter.  Too bad it took 18 posts to clear this up... and is why always as for vanilla sql statements and output.