Using FULLTEXT Searches with PHP and MySQL

[phillw]

Fulltext Searching with MySQL

This is my understanding of what it is, how it works and some general guidence on usage.
It is a very condensed version of what I have learned in 12 weeks of hard study on 'best-practices' - And I stress, it is only my honest opinion of how
to use fulltext searches whilst not causing undue searches on the database server.

Firstly a bit of background. There are times when you need to search accross a couple (or more) columns withing your MySQL Database. You can, of course, tell MySQL to do a full column search for the required word(s) on the required columns - This will take some time in a large database and have the db admin person delete you from their Christmas card list (And possibly from the list of users as well !!).

FULLTEXT (hereafter called FT) searching is designed to help out in these situations. However, think long and hard before you use FT searches - they are, by their very nature, server instensive !!

A couple of rules.

1) By default, words of less than 4 characters are not searched for.

2) There is a default 'stop-list' of common words that are excluded from a FT index. This is to keep the FT Index as small as possible. The default English StopList is here...

MySQL :: MySQL 5.0 Reference Manual :: 11.8.4 Full-Text Stopwords

If you are running your own MySQL Server you can alter both of the above, but that is beyond the scope of this introductory tutorial.

For this tutorial, I am going to use the example from the MySQL site - that is a list of articles, much like this forum, with an ID, author, title & content.

You can create an FT index when you create your database, however, having read various forums on FT it is apparent that this is not the best way to go about it.

Instead, the general advice is to get the data into your database and THEN create the FT index. The reason for this is that otherwise the FT index needs to be updated everytime you add a record (This is another good way to upset the db admin person).

Our database is called forumdatabase and the articles are stored in the table articles.

So, you have your articles stored like this ...

ID = 1 (AUTO INCREMENTING, UNIQUE, ID NUMBER - MySQL puts this record in for you)
Author = Fred Blogs
Title = How to upset database administrators.
Content = Ensure that you are forcing the Fulltext index to completely update itself everytime you add a record. Another good way is to use LIMIT when paging up and down - this makes the server go do a complete Fulltext search up to the higher limit, throwing away more and more records as it goes.

ID = 2
Author = Database Administrator
Title = How to have your user privalidges removed by the Database Administrator.
Content = See the article of How to upset database administrators by the incorrect usage of the Fulltext search function.

ID = 3
Author = Fred Blogs
Title = How to earn brownie points from the database administrator.
Content = Ensure that you only ever do the fulltext search the once, storing the ID numbers of the search result in an array, to be called up in your paging display module later.

ID = 4
Author = John Smith
Title = I had my user privalidges removed - why ?
Content = I was writing a script to query all the databases for if a word appeared in several fields - the search would take a while, but it did work. All of a sudden I've had a nasty note saying I was exceeding 'fair-usage' and have had my rights revoked. What did I do wrong ?

ID = 5
Author = Fred Blogs
Title = Re: I had my user privalidges removed - why ?
Content = Have a look at postings in the 'how-to' on the MySQL forum


.... I think you get the drift ;-)

What we will now do, is create the fulltext index of the column "Title" and "Content". Our database is called forumdatabase and the articles are stored in the table articles.

You can, alegedly, do this in phpmyadmin (I couldn't get it to work) - but you also need to drop the index if it exists, rather than update it. Why ?

[QUOTE]After changing the stopword file it is not too wise to use the REPAIR TABLE tablename QUICK as mentioned in the documentation when you have lots of records.
I had a table with ca 4 million records, and first I fell for this trap. The repair took more than 10 days. After this I tried DROP INDEX and CREATE INDEX. That took only 40 minutes![\QUOTE]

So, having been warned by that kind person - we're going to drop the table 1st !!!

mysql>  DROP INDEX ft_Index ON articles;

mysql>  CREATE INDEX ft_Index ON articles (Title, Content);  

mysql> quit

Obviously, the 1st time you do this, you'll get an error saying the index does not exist.

So, let's look for the word 'database' in our FT index.

mysql> Select * FROM articles WHERE MATCH +database* AGAINST ('Title, Content') IN BOOLEAN MODE

WHAT ?!!!!!!

Okay - firstly I'm using a boolean search, 2 main reasons

1) you can use the + and - operators on words.
+ = MUST be there
- = must NOT be there
(There are exceptions to this rule ...

2) When you don't use boolean search, if a search were to return more than 50% of your records in your table it would return nothing.

And, there is a 3rd reason (One that was a subject of two requests within CodeCall for help) - and that is this

Keep in mind that although MATCH() AGAINST() is case-insensitive, it also is basically **accent-insensitive**. In other words, if you do not want _mangé_ to match with _mange_ (this example is in French), you have no choice but to use the BOOLEAN MODE with the double quote operator. This is the only way that MATCH() AGAINST() will make accent-sensitive matches.

E.g.:

SELECT * FROM quotes_table WHERE MATCH (quote) AGAINST ('"mangé"' IN BOOLEAN MODE)

For multiword searches:

SELECT * FROM quotes_table MATCH (quote) AGAINST ('"mangé" "pensé"' IN BOOLEAN MODE)

SELECT * FROM quotes_table MATCH (quote) AGAINST ('+"mangé" +"pensé"' IN BOOLEAN MODE)

Although the double quotes are intended to enable phrase searching, just like any web search engine for example, you can also use them to signify single words where accents and other diacritics matter.

The only drawback to this method seems to be that the asterisk operator is mutually exclusive with the double quote. Or I just haven't been able to combine both effectively.

So, as the above says, perversely, if you DO want it to be accent-insensitive - simply do not use the double quote operator .... Kinda cute, yes ?

But, enough digression, back to

mysql> Select * FROM articles WHERE MATCH +database* AGAINST ('Title, Content') IN BOOLEAN MODE

this will match against database and databases (the * at the end of database) and, just for good measure (we need this when we are looking for more than one word to be a match) we put the + before the word.

So, for ID = 1 - we have a match within the Title
ID = 2 - we have a match both within the Title & Content
ID = 3 - we have a match within the Content
ID = 4 - we have a match within the Content
ID = 5 - we have no match.

For the next bit, I'm going to move over to php for the querying, and start to replace the hand written variables with strings.

I have a library routine called dbconnect.php - It's quite innofensive & every home should have one ...

<?php
/////////////////////////////////////////////////////////////////////////////////
    //Database connection
    /////////////////////////////////////////////////////////////////////////////////
    $host="localhost";  // the path to the database, usually localhost, 
    $user="user-name";  // your login name, for this part set up a 'select' only user 
    $password="your password"; // the password you set - no password is dangerous !!! 
    $database='your database';  // The database you want to read
	 $link = mysql_connect($host,$user,$password);
		if (!$link) 
		{
			$host="the full host name of your server MySQL database";
			$link = mysql_connect($host,$user,$password);
		}
		if (!$link) 
		{
	    	die("Unable to Connect to Server");
	   }
mysql_select_db ($database)or die ('Unable to Select Database '); 
/////////////////////////////////////////////////////////////////////////////////////////////

?>

As most of us have LAMP servers on our computers, the above module simply tries to connect via 'localhost' (Your computer) and if
that fails, it uses the Server to connect. It's quite simple, you must have the database, user & password the same on both systems.

***INTERLUDE*****
MySQL Injection:
There are various horror stories & code available to 'hack' systems via search entry by users. Trust NO-ONE and NOTHING.
I may be paranoid, but I even check paramatised (stuff I declare via code and drop down windows) search stuff sent to my search query.
Do NOT use magic-quotes - they are old, being phased out and not good.
Do NOT use the likes of strip-slashes etc to clean things up - Mr O'Riley will get well fed up when his name doesn't work.
MySQL and php has a nice, easy command - it is called mysql_real_escape_string() It is there to look after your MySQL database - let it do the work.

It is not in the scope of this tutorial to deal with safety - there are plenty that deal with just that.

***END OF INTERLUDE***

I am assuming you know how to write the html for a user to input what they're looking for.

FindIt ...

<form id="input" action="search.php" method="get" />
I am Looking For:  
<input type="text" size="40" name="LookFor" />
<input type="submit" value="Go Find It !!" />
<br /><br />

$table - name of table within the database opened by dbconnect.php
$fulltext - the list of columns we are searching accross using the fulltext query
$lookfor - the list of words to look for, from the user
$exploded_LookFor – an array containing each word the user input
$Search - build the search up.
$SearchDesc – either a modifier to fine tune our search, or we only had words of 3 characters long.

search.php

$table = 'articles';
$fulltext = 'Title, Content';

$LookFor = $_GET['LookFor'];
 // From the form above
$exploded_LookFor = explode(' ', $LookFor); 
 // We're using the space character to split the words into an array
$num = count($exploded_LookFor);
 // how many words do we have ?
$num = $num -1; // Why ? - because arrays start at zero & count starts at one !!! 

for ( $counter = 0 ; $counter <= $num; $counter += 1) 

	{

		$exploded_LookFor[$counter] = mysql_real_escape_string($exploded_LookFor[$counter]);			// the use of mysql escape to clean it up						

		if (strlen($exploded_LookFor[$counter]) > 3)
  // if >3, we can use fulltext search 
			{

				$LongEnough = 1;
  // we have a word >3 characters long 
				if ($Search == "")

					{

						$Search = '+'.$exploded_LookFor[$counter].'*';

					}

				else

					{

						$Search = $Search.' +'.$exploded_LookFor[$counter].'*';

					}

			}
 // adding in the + and * to the search words
		elseif (strlen($exploded_LookFor[$counter]) > 2) // catching words > 2 Characters for a column search  

			{

				if ($SearchDesc == "") 

					{

						$SearchDesc = " AND (Title LIKE '%$exploded_LookFor[$counter]%'";

						$SearchDesc = $SearchDesc." OR Content LIKE '%$exploded_LookFor[$counter]%')";

					}

 				else

					{

						$SearchDesc = $SearchDesc." AND (Title LIKE '%$exploded_LookFor[$counter]%'";

						$SearchDesc = $SearchDesc." OR Content LIKE '%$exploded_LookFor[$counter]%')";

					}

			}

	}


// That's the searching through. Words of 4 characters or more are put to the fulltext search, words of 3 characters
// are going to be done via a column search 

if ($LongEnough > 0) 

	{

	   $q= "SELECT * FROM $table WHERE match $fulltext against ('$Search' IN BOOLEAN MODE) $SearchDesc";

	}


// If we haven't had a word of >3 characters, we're going to cry & do it the slow way – make sure that both 'Title' and
// 'Content' are indexed columns (Another way to get bad marks from the db administrator)

if ($LongEnough != 1)

	{

		for ( $counter = 0 ; $counter <= $num; $counter += 1) 

		{

			$exploded_LookFor[$counter] = mysql_real_escape_string($exploded_LookFor[$counter]);									

			if (strlen($exploded_LookFor[$counter]) > 2)

			{

				if ($counter <= 0) 

				{

					$Search = " (Title LIKE '%".$exploded_LookFor[$counter]."%'" ;

					$Search = $Search." OR Content LIKE '%".$exploded_LookFor[$counter]."%' )" ;

				}

			else

				{

					$Search = $Search."  AND ( Title LIKE '%".$exploded_LookFor[$counter]."%'" ;

					$Search = $Search." OR Content LIKE '%".$exploded_LookFor[$counter]."%' )";

				}

			}

		}

	}

if ($q == '') 
 // we haven't got a fulltext search to do – Cries as knows dbadmin person is not going to be happy
	{

	if ($Search != '')

		{

			$q= "SELECT * FROM $table WHERE $Search";
  // this is a real slow way of doing it
		}

	}

if ($q !='')

	{		

		// echo $q,'<BR /><BR />';
 - my debug line – to see what is about to be done – you'll find it useful
		$rs=mysql_query($q) or die(mysql_error());

		while ($row = mysql_fetch_array($rs))

		{

			$result[$count] = $row['ID'];  

			$count++; // Total Number of rows returned 

		}	 

	}

mysql_close($link);

include 'display.php'

Okay, so there's a heck of a lot going on up there !!!
The % are added to front and back of search words to allow database to match with plurals etc. (My searches are a bit more involved, so I need them front & back)




If the sentence the user input was 'I am having a problem with fulltext searrches'

The search would be …

SELECT * FROM articles WHERE match Title, Content  against ('+having* +problems* +with* +fulltext* +searches*' IN BOOLEAN MODE) ;

The 'I is too short, as is the 'am' and 'a'

If they tried 'I think I know a bit about fulltext searches'

It would return

SELECT * FROM articles WHERE match Title, Content against ('+think* +know* +about* +fulltext* +searches*' IN BOOLEAN MODE) AND (Title LIKE '%bit%' OR Content LIKE '%bit%')

So, the faster fulltext search would be done 1st, then it would be sub checked to see if it had 'bit' in it, as that was a 3 character word.

And now, just to make the db administrator cry ….

'the cat sat on the mat'

SELECT * FROM articles WHERE (Title LIKE '%the%' OR Content LIKE '%the%' ) AND ( Title LIKE '%cat%' OR Content LIKE '%cat%' ) AND ( Title LIKE '%sat%' OR Content LIKE '%sat%' ) AND ( Title LIKE '%the%' OR Content LIKE '%the%' ) AND ( Title LIKE '%mat%' OR Content LIKE '%mat%' )

I know that the above is a lot to take in. Take your time, read it – don't be worried about things like
Title LIKE '%".$exploded_LookFor[$counter]."%'" - It means put a % at the start and end of the, say, 3rd word.

If there is any interest, I'll sit down and pop on a tutorial for display.php, the module that the above calls once it is done.

If nothing else, the above should go some way to show why you shouldn't being using LIMIT on searches that are server intesive.

I look forward to comments, good & 'constructive' – This is my 1st article for CodeCall.

Regards,

Phill.

[matthanc]

Excellent tutorial you have here but how would a beginner at this like myself implement this onto a site? Would I need to create a recordset to display the results in a rowspan?

[phillw]

Hi and welcome to CodeCall.

search.php calls display .php, this means the array containing the ID numbers of the records found ($result[]), along with the number found ($count). display.php then gets the results to display them. I'll have to re-write the information on display.php, in the meantime, I'll post below additions to search.php. If you pop over to the 'live' site and try a few parts out with the debug turned on you will see how it is building up the array $result[] via the MySQL SELECT command.

[phillw]

As noted elsewhere, I have re-written a lot of code for 2009 - 2010 M.G. Judd Ltd Front Page The little switch on the lower left of the main menu system toggles FT output on & off. I've added some more output to it. As I will be covering how the 'display.php' modules work, I have included the filenames of the pic files. Also added is a count of records retrieved and an attempt to show un-sanitised input that has not been put through mysql_real_escape_string, although it does seem to be catching the single apostrophe before I do :? I will investigate that a bit further. There are more parts on, and you can still see how it gets on if you use the search for parts system, as it splits what it can use for full-text, and what it cannot.

Phill.

[phillw]

One of the things about programming is that you get to hate certain symbols - as they haunt you. My usual one is the dreaded ; that has to go at the end each line of PHP code. But, today, courtesy of the way MySQL does its FULLTEXT indexes, the symbol - goes into my Room 101.

Scenario .. A manufacturers part numbering system is 123 45 67-89 It has a logic in why. However, even though 67-89 is more than 3 character in length and should go into the FULLTEXT index, it does not. Instead, My SQL treats it as 67 89, that is two 'words' of two characters in length. This is not good when you are searching for 67-89, as it will not find a match. This also means that I need to add OEM_Part_No into my sub-string build for when there is not a four, or more, character 'word'.

Now, if you have your own hosting server you can simply

Modify a character set file: This requires no recompilation. The true_word_char() macro uses a “character type” table to distinguish letters and numbers from other characters. . You can edit the contents of the <ctype><map> array in one of the character set XML files to specify that '-' is a “letter.” Then use the given character set for your FULLTEXT indexes.

But, most of us don't. So, we have to 'work around' it. Below is my 'hack'..
1) in the string you are going to split into words, replace all instances of - with a space, as that is how we split our words up. For this we use str_replace
So....

$LookFor=$_GET['LookFor'];
$exploded_LookFor=explode(' ', $LookFor); // Now split it into bite sized bits to build the search 
$num=count($exploded_LookFor);

Becomes ...

$LookFor=$_GET['LookFor'];
$LookFor=str_replace("-"," ",$LookFor); // Replace -'s with spaces as MySQL doesn't like looking for them 
$exploded_LookFor=explode(' ', $LookFor); // Now split it into bite sized bits to build the search 
$num=count($exploded_LookFor);

All done ? :roll: Nope .....

In the original search routine, I only looked for character of a length of 3 characters for the secondary search, we now need to drop this to two characters. If you have been paying attention you'll re-call this line ...

$SearchDesc=''; // trapping words less than 4 characters - in my case I only trap words with more
    						 // 2 characters in them (i.e. 3 !!!, but you can alter it to 1, or 0 if you want)

Gee, I'm real glad I wrote it that way !!!
So, the test for >2 becomes >1

Here ...

		elseif (strlen($exploded_LookFor[$counter])>1) // catching words > 1 Characters for a Sub Search

Now, in the original code, I also rebuilt the search string if we did not have a word of four characters, or more, in it as we cannot use a FULLTEXT search on it. (Remember the cat sat on the mat). Looking more closely at the code reveals I don't need to do that. If you read the section

if ($SearchDesc=="") 
					{
						$SearchDesc=" AND (Description LIKE '%$exploded_LookFor[$counter]%'";
						$SearchDesc=$SearchDesc." OR Suitable_For LIKE '%$exploded_LookFor[$counter]%'";
						$SearchDesc=$SearchDesc." OR OEM_Part_No LIKE '%$exploded_LookFor[$counter]%')";
					}
 				else
					{
						$SearchDesc=$SearchDesc." AND (Description LIKE '%$exploded_LookFor[$counter]%'";
						$SearchDesc=$SearchDesc." OR Suitable_For LIKE '%$exploded_LookFor[$counter]%'";
						$SearchDesc=$SearchDesc." OR OEM_Part_No LIKE '%$exploded_LookFor[$counter]%')";
					}

and compare it to the rebuilding section....

if ($Search=="") 
					{
						$Search="  (Description LIKE '%$exploded_LookFor[$counter]%'";
						$Search=$Search." OR Suitable_For LIKE '%$exploded_LookFor[$counter]%'";
						$Search=$Search." OR OEM_Part_No LIKE '%$exploded_LookFor[$counter]%')";
					}
 				else
					{
						$Search=$Search." AND (Description LIKE '%$exploded_LookFor[$counter]%'";
						$Search=$Search." OR Suitable_For LIKE '%$exploded_LookFor[$counter]%'";
						$Search=$Search." OR OEM_Part_No LIKE '%$exploded_LookFor[$counter]%')";
					}

The difference ? The word AND is present at the start of the first routine, and missing from the second. (Okay so the first string is $SearchDesc and the second is $Search).

Again, there is a string command to sort this ....

$Search=ltrim($SearchDesc, " AND");

ltrim does what it says and removes " AND" from the start of the string, so now we aren't building it twice.

The revised version is below, it also includes the little 'hooks' for the FULLTEXT debug mode that I've put onto 2009 - 2010 M.G. Judd Ltd Front Page - Look for code with FT in !!

<?php
include './phplibrary/include-mime.php';
include './phplibrary/dbconnect.php';
	 $q='';
    $count=0;   //Used to build the display array 
    $table='MGJ_Parts';		// The MAIN (Or only table within the database you want to read) 
    $fulltext='MGJ_Part_No, OEM_Part_No, Suitable_For, Master_Group, Sub_Group, Description, Dimensions';
    				 // for the fulltext search of the parts database
    $display=array(); // initialise the array
    $result=array(); // initialise the array
    $LongEnough=0; // FULLTEXT searches require 4 or more characters in a search - so this means that
    						// hot, cat, dog, oil, red, pie   and so on won't be checked.
    						// there is also a stop-list of prohbited words available from here ...
    						//    http://dev.mysql.com/doc/refman/5.0/en/fulltext-stopwords.html   
     						// love them, or hate them - they're there !!!!
    $SearchSize=''; // my paramatised stuff
    $SearchDesc=''; // trapping words less than 4 characters - in my case I only trap words with more
    						 // 1 characters in them (i.e. 2 !!!, but you can alter it to 2, or 0 if you want)
  
$LookFor=$_GET['LookFor'];
$LookFor=str_replace("-"," ",$LookFor); // Replace -'s with spaces as MySQL doesn't like looking for them 
$exploded_LookFor=explode(' ', $LookFor); // Now split it into bite sized bits to build the search 
$num=count($exploded_LookFor);
$num=$num -1; // Why ? - because arrays start at zero & I want count to start at one !!! 
for ($counter=0; $counter<=$num; $counter+=1) 
	{
		if ($_SESSION['FT']=='FT')
			{ 
				
				$ShowSearch=$ShowSearch.' '.$exploded_LookFor[$counter].' ';
			}
		$exploded_LookFor[$counter]=mysql_real_escape_string($exploded_LookFor[$counter]);	
		if ($_SESSION['FT']=='FT')
			{ 
				
				$ShowSearch=$ShowSearch.' '.$exploded_LookFor[$counter].' <br />';
			}								
		if (strlen($exploded_LookFor[$counter])>3)
			{
				$LongEnough=1;
				if ($Search=="")
					{
						$Search='+'.$exploded_LookFor[$counter].'*';
					}
				else
					{
						$Search=$Search.' +'.$exploded_LookFor[$counter].'*';
					}
			}
		elseif (strlen($exploded_LookFor[$counter])>1) // catching words > 1 Characters for a Sub Search 
			{
				if ($SearchDesc=="") 
					{
						$SearchDesc=" AND (Description LIKE '%$exploded_LookFor[$counter]%'";
						$SearchDesc=$SearchDesc." OR Suitable_For LIKE '%$exploded_LookFor[$counter]%'";
						$SearchDesc=$SearchDesc." OR OEM_Part_No LIKE '%$exploded_LookFor[$counter]%')";
					}
 				else
					{
						$SearchDesc=$SearchDesc." AND (Description LIKE '%$exploded_LookFor[$counter]%'";
						$SearchDesc=$SearchDesc." OR Suitable_For LIKE '%$exploded_LookFor[$counter]%'";
						$SearchDesc=$SearchDesc." OR OEM_Part_No LIKE '%$exploded_LookFor[$counter]%')";
					}
			}
	}
$MyImpSize=$_GET['MyImpSize'];
$MyImpSize=mysql_real_escape_string($MyImpSize);  

$MyMetSize=$_GET['MyMetSize'];
$MyMetSize=mysql_real_escape_string($MyMetSize);  

$SearchSize='';
if ($MyImpSize<>"NULL")
    {
        $SearchSize=" AND (Size LIKE '%$MyImpSize%' )";
    }

if ($MyMetSize<>"NULL")
    {
        $SearchSize=$SearchSize." AND (Size LIKE '%$MyMetSize%' )";
    }
if ($LongEnough>0) 
	{
		$SearchSize=$SearchSize.$SearchDesc;	
	   $q= "SELECT * FROM $table WHERE match $fulltext against ('$Search' IN BOOLEAN MODE) $SearchSize";
	}
	
if ($LongEnough!=1)
	{
		$Search=ltrim($SearchDesc, " AND");
	}
if ($q=='') 
	{
	if ($Search!='')
		{
			$Search=$Search.$SearchSize;
			$q="SELECT * FROM $table WHERE $Search";
		}
	}
if ($q!='')
	{		
		$rs=mysql_query($q) or die(mysql_error());
		while ($row=mysql_fetch_array($rs))
		{
			$result[$count]=$row['ID'];  
			$count++; // Total Number of rows returned 
		}	 
	}
mysql_close($link);
include 'display.php';
?>

and there you have it - all revised, tidied up & shortened - although the MySQL server now has to take a 'hit' at two characters, instead of the previous three.

Go over to the site, pop it into FULLTEXT mode and have a look !!

Regards,

Phill.

[phillw]

::sigh:: You can always trust a 'user' ... according to my stats, someone searched for "Clutch and Bearing" So, it took the literally and looked for all parts containing the words "Clutch" "and" "bearing" - oddly enough, it didn't find any matches. Soooo... you remember that str_replace function ?...
(Note the space each side of the word, else "stand" would become "st" !!!)

$LookFor=$_GET['LookFor'];
$LookFor=str_replace(" AND "," ",$LookFor); // Strip AND
$LookFor=str_replace(" and "," ",$LookFor); // Strip and
$LookFor=str_replace(" OR "," ",$LookFor); // Strip OR
$LookFor=str_replace(" or "," ",$LookFor); // Strip or 
$LookFor=str_replace("-"," ",$LookFor); // Replace -'s with spaces as MySQL doesn't like looking for them 
$exploded_LookFor=explode(' ', $LookFor); // Now split it into bite sized bits to build the search

Yay !!!!!

Phill.

[phillw]

Hmmmm....

Something amiss, mysql_real_escape_string and php seem to be having a disagreement...

If you input

'E'

into Search For Part via

echo '<form id="input" action="search.php" method="get" />';
echo 'I am Looking For:  ';
echo '<input type="text" size="40" name="LookFor" />';
echo '<input type="submit" value="Go Find It !!" />';

it is returning, in search.php (the module I've been explaining, above)

$LookFor=$_GET['LookFor'];

returns

\'E\'

BEFORE is run it through the mysql_real_escape_string command, which then thoroughly enjoys itself and returns

\\\'E\\\'

Not what I want !!!

I'm looking into it.

A little while later, I found the cause.

Well, the problem was being caused by magic_quotes_gpc being turned on on my local server - I'm a little angry that this is the default as it is a new installation and we've been told for years not to use them !! - Anyways, they're now off !!

If you are having odd things happen with escaping, like above, then check your php.ini file for the entry

; Magic quotes for incoming GET/POST/Cookie data.
magic_quotes_gpc = On

And set it to Off. File-Save then restart Apache2

$ sudo /etc/init.d/apache2 restart

Because of the escaping characters being added to the search string, I've had to modify the test and building of the search string, thus ...

for ($counter=0; $counter<=$num; $counter+=1) 
	{
		$esc_exploded_LookFor[$counter]=mysql_real_escape_string($exploded_LookFor[$counter]);
		if ($_SESSION['FT']=='FT')
			{ 
				
				$ShowSearch=$ShowSearch.' '.$exploded_LookFor[$counter].' '.$esc_exploded_LookFor[$counter].' <br />';
			}
		if (strlen($exploded_LookFor[$counter])>3)
			{
				$LongEnough=1;
				if ($Search=="")
					{
						$Search='+'.$esc_exploded_LookFor[$counter].'*';
					}
				else
					{
						$Search=$Search.' +'.$esc_exploded_LookFor[$counter].'*';
					}
			}
		elseif (strlen($exploded_LookFor[$counter])>1) // catching words > 1 Characters for a Sub Search 
			{
				if ($SearchDesc=="") 
					{
						$SearchDesc=" AND (Description LIKE '%$esc_exploded_LookFor[$counter]%'";
						$SearchDesc=$SearchDesc." OR Suitable_For LIKE '%$esc_exploded_LookFor[$counter]%'";
						$SearchDesc=$SearchDesc." OR OEM_Part_No LIKE '%$esc_exploded_LookFor[$counter]%')";
					}
 				else
					{
						$SearchDesc=$SearchDesc." AND (Description LIKE '%$esc_exploded_LookFor[$counter]%'";
						$SearchDesc=$SearchDesc." OR Suitable_For LIKE '%$esc_exploded_LookFor[$counter]%'";
						$SearchDesc=$SearchDesc." OR OEM_Part_No LIKE '%$esc_exploded_LookFor[$counter]%')";
					}
			}
	}

Quite a simple change, I've just added the variable esc_exploded_LookFor to hold the escaped string, so the check for the length of the string does not have to take into account the possibility of escape characters.

The easy way to see this in operation is to head over to the 2009 - 2010 M.G. Judd Ltd Front Page site, turn on FT and search for

'E'

Phill.

[matthanc]

Hello again, I got your search engine working pretty well and I've been messing around with it quite a bit for the last couple of days but I still can't seem to figure out what to do with $result[].

If I do a random search on my database with your engine I can see that $result is holding the database keys to the proper articles, I checked this with print_r($result) and got this.

Array ( [0] => 8 [1] => 11 [2] => 34 [3] => 35 [4] => 36 [5] => 46 [6] => 51 [7] => 58 [8] => 62 )

So I get 8 matching results with the id keys which is great but I haven't been able to figure out how to use this array to display results.

In other words I'm still stuck and none of my newbie ideas seem to be working.

[phillw]

I have a routine called display.php which allows you to display a set number of records per page, I'll get it commented up and posted. Basically it opens up the dbase again, then does a loop using

 Select * with ID=$result[array offset]

It depends on what you want to do with the returned record numbers, I just display them as 7 records per page (the number can be changed), 2009 - 2010 M.G. Judd Ltd Front Page is the 'live' site, you can switch it to tutorial mode by clicking the button on the bottom left. If you use the "Search for Parts" you will see how the array is used to display the returned $result array. If this is the sort of thing you're wanting to do I'll write some additional comments for the display.php routine and post it up.
If you ask it to look for spanner you will be able to see the offset in the array as you page up & down, if you hover over the MGJudd Part Number on the results page, you will see the Record ID Number; this is 'clickable' to take you to a single part view which pulls in the image(s) associated with each part.

Regards,

Phill.

[phillw]

Hi, Sorry, I've been a bit busy. I've thrown up a draft 'how-to' for display.php over on my baby forum, once it's in a fit state I'll transfer it over here. Just do bear in mind that it is a draft & bit rough round the edges, hopefully it give you some pointers PhillW.net on the net &bull; View topic - display.php

Regards,

Phill.
P.S. to the mods - I promise I'll get it all tidied up and posted !!