How to make a MySQL search engine?

[Jaxeed]

Hey again everyone!

This post here is one I bet you guys will hate. But anyway, the thing is that I wanted to make a search engine for my database, and thought that this would be easy peasy lemon squeezy. After looking through my PHP book and the php.net webpage, I found that there was no mysql_search_db() command. ARG! I went pale wondering how they could leave out such an obvious command?

lol... So I figured I'd ask you ppl for any good tips on how exactly to make a simple search engine for a MySQL account. This is not for a public website but more as a tool, so I simply need something where I can enter a word/digits/short sentence/etc, and it will search for matches in the database fields and records making the results into variables I could echo in a table. Basically I would need to know where it found matches, so a table name, fieldname, and row number would be nice.

Hope you are not sighing too loudly over me now...lol. I promise I wont ask stupid questions like this again.

[mistafeesh]

check out http://www.phpdig.net

[topfloorstudio]

Try this code for a quick and dirty solution to a search engine
<?
$choice = "your desired fieldname to be searched";
$search = "your desired search";
$order = "will be asc from this row";
$sql= "SELECT * FROM tablename Where ".$choice." LIKE '%".$search."%' AND type =\"$type\" ORDER BY $order ASC" ;
?>

[Jaxeed]

Thx topfloorstudio, but:
I used your example to write this code:

#In HTML part:
<form action="monitor.php" method="post" target="monitor">
<input name="tablesearch" type="text" size="10">
<input name="fieldsearch" type="text" size="10">
<input name="rowsearch" type="text" size="10">
<input name="keyword" type="text" size="10">
          <br>
<input name="search" type="submit" value="Search" class="form">

</form>

#In PHP part:
$tablesrc = $_POST["tablesearch"];
$fieldsrc = $_POST["fieldsearch"];
$rowsrc = $_POST["rowsearch"];
$keyword = $_POST["keyword"];

if($tablesrc != NULL and $fieldsrc != NULL and $rowsrc != NULL and $keyword != NULL)
{
$searchcode = "SELECT * FROM ".$tablesrc." Where ".$fieldsrc." LIKE '%".$keyword."%' AND type =\"$type\" ORDER BY $rowsrc ASC" ;
$search = mysql_query($searchcode, $conn) or die(mysql_error());
}

And it gave me this result:

"Unknown column 'type' in 'where clause'"

Anyway, it would be really neat if all I input is one keyword, and it searches everything for that one keyword, outputting tablename, fieldname and rownumber + maybe the record in the cell containing the match into variables or one (.=) variable that is listing everything.

If I already know the table and the fieldname and the row number, it seems rather pointless to do a search for it in the first place, if you know what I mean.

Oh, and what would be really neat is if you could put a into the keyword if there is something you are not sure about. As for example if you are searching for a user named Michael or Micheal or something, you can type Mich. Meaning * to be anything.

But thanks for your input mate! 🙂

[PyroX]

To use * as a wildcard something like this will do:

// Prepare Wildcards
$query=str_replace('*','%',$query);

// Build Query
$sql = "SELECT * FROM tablename WHERE yourcolumn1 like '$query' or yourcolumn2 like '$query'";

// Execute Query
$search = mysql_query($searchcode, $conn) or die(mysql_error());

The % in a like comparison is a wilcard, using this example will:

Search for Ted* Returns anything that starts with Ted such as Teddy

Etc, Etc,

Search for 44 returns anything with 44 in it.

[Jaxeed]

Thats really neat PyroX, thanks a lot for thatone.

There is one thing I dont get though. What am I supposed to fill in into yourcolumn1 and yourcolumn2? My first guess would be the keyword I'm searching for, but its two of them so I'm not so certain its that.

But thanks loads for the input, I think I get the picture on how such a search engine would look like soon. 🙂

[Jaxeed]

Ok, as far as I undrestand so far, to do a MySQL search one needs to know tablename and field(column) name. Hence I made these loops so this code would go through all the tables and fields looking for the keyword.

$keyword = $_POST["keyword"];
if($keyword != NULL)
	{
	$tbl = mysql_list_tables($jaxeed, $conn);
	for($tbsr=0; $tbsr < mysql_num_rows($tbl); $tbsr++)
	$tablesrc = mysql_tablename($tbl, $tbsr);
		$fldnm = mysql_list_fields($jaxeed, $tablesrc, $conn);
		$clmn = mysql_num_fields($fldnm);
		for ($cl = 0; $cl < $clmn; $cl++) 
		$fieldsrc = mysql_field_name($fldnm, $cl);
			$searchcode = "SELECT * FROM ".$tablesrc." WHERE ".$fieldsrc." LIKE '%".$keyword."%' ";
			$search .= mysql_query($searchcode, $conn) or die(mysql_error());

}

Seems to be working without error messages, though I'm not certain how to extract the information this script can get out.
Hopefully table name and field name would be possible to echo.

[Jaxeed]

Where testing :

$tbl = mysql_list_tables($jaxeed, $conn);
for($tbsr=0; $tbsr < mysql_num_rows($tbl); $tbsr++)
$tablesrc = mysql_tablename($tbl, $tbsr);

$fldnm = mysql_list_fields($jaxeed, $tablesrc, $conn);
$clmn = mysql_num_fields($fldnm);
for ($cl = 0; $cl < $clmn; $cl++) 
$fieldsrc .= mysql_field_name($fldnm, $cl).",";

$searchcode = "SELECT * FROM ".$tablesrc." WHERE MATCH (".$fieldsrc.") AGAINST ('".$keyword."');";

..recently. Seemed like an obvious answer to my problem. But no luck unfortunately, even though I've been though the code should be correct.

Please enlight me 😃

[leatherback]

Hi,

I could be mistaken, but it looks to me like you are 'overquoting' your searchstring.

Have you tried:

searchcode = "SELECT * FROM $tablesrc WHERE MATCH ($fieldsrc) AGAINST ($keyword)";

[PyroX]

WHERE MATCH (".$fieldsrc.") AGAINST ('".$keyword."');";

You will have to set the columns up for this type of matching.

[Jaxeed]

Hi again guys!

Thanks for all your replies. I've played around be bit more with the code and pretty much moved away from the WHEN MATCH() AGAINST('') tag since I never got anything usefull out of it.

However, I'm half way there now I think, since the new code now is able to find matches and render at least the table name and all the fields in the hits.

There is no valuable information in there yet, as I'm trying to get this working before I start using it. As you can see I still need a way to get the row records displayed of the hit rows.

The code looks like this so far:

$keyword = $_POST["keyword"];
if($keyword != NULL)
	{	
	$tbl = mysql_list_tables($jaxeed, $conn);
	for($tbsr=0; $tbsr < mysql_num_rows($tbl); $tbsr++)
		{
		$tablesrc = mysql_tablename($tbl, $tbsr);
		$fldnm = mysql_list_fields($jaxeed, $tablesrc, $conn);
		$clmn = mysql_num_fields($fldnm);
		for ($cl = 0; $cl < $clmn; $cl++)
			{
			$fieldsrc = mysql_field_name($fldnm, $cl);
			$searchcode = "SELECT * FROM $tablesrc WHERE $fieldsrc LIKE '$keyword'";
			$search = mysql_query($searchcode, $conn) or die(mysql_error());
			#Dealing with hits:
			if(mysql_fetch_assoc($search) == TRUE)
				{
				$hittable = $tablesrc;
				$hitfield = $fieldsrc;
				$allfields = mysql_list_fields($jaxeed, $hittable, $conn);
				$allcolumns = mysql_num_fields($allfields);
				$colspan = $allcolumns + 2;
				$showfields .="<td bordercolor=\"#000080\"><div align=center class=\"monitor\" colspan=\"$colspan\"> $hittable </td></tr><tr>";
				for ($af = 0; $af < $allcolumns; $af++) 
					{
					$showfields .= "<td bgcolor=\"#00C000\"><div align=center class=\"mon2\">" .mysql_field_name($allfields, $af) . ": </div></td>"; $stopper++;
					#where the tag has to go in for getting the records of each column
					if($stopper == $allcolumns)
						{
						$showfields .= "</tr></table><br><table border=\"1\" cellspacing=\"0\" cellpadding=\"2\" bordercolor=\"#00C000\"><tr>"; $stopper = 0;
						}
					}
				#simple output		
				$hitlist.= " Table: $tablesrc   Field: $fieldsrc  |";
				}
			}
		}
	}

I've tried putting in the below tag to get the records, but the code went balistic as soon as I did this - all sorts of errors.

while($conn = mysql_fetch_array($searchcode)) 
{
foreach ($conn as $hit_info) $searchresult .= "<td><div class=\"monitor\">$hit_info</div></td>"; $br++; if($br >= $allcolumns) {$searchresult .= "</tr><tr>"; $br = 0;}
}

So, dear friends, do you have any better ideas of what tag I should try? Also, when it finds more then one hit in one table it displays the table twice, which isnt really necessary (though no big issue really).

Thanks for all your support so far. When I'm done with this manager thingy I'll make it available, hopefully making someones MySQL managements easier.

[Jaxeed]

ok, maybe you need some more input.... hmmmm
I have also tried now:

while($hit_info = mysql_fetch_array($searchcode)) 
{
$searchresult .= "<td><div class=\"monitor\">$hit_info</div></td>"; $br++; if($br >= $allcolumns) {$searchresult .= "</tr><tr>"; $br = 0;}
}

But it returns this error when run:
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource...

As far as I can see, $searchcode should be a perfectly good argument to fetch the correct rows from the hit table(s). Any idea how come this argument isnt accepted? or any better idea on how to write this part of the code?

It is pretty much the final part of the search engine code that I need working. After this is done, the search engine is done. I hope therefore someone will be able to end my frustration, so I can get on with the rest of the codes.

[Jaxeed]

FINALLY!!!

I got everything working, and I found out what I did wrong. The above statement is using a MySQL query indeed, but I forgot to put in mysql_query($thequery, $conn). In other words the script never even contacted MySQL with this query I wrote. How silly am I???

Anyway, I thought I might do someones life easier by posting the finished search engine script.

$keyword = $_POST["keyword"];
if($keyword != NULL)
	{	
	$tbl = mysql_list_tables($jaxeed, $conn);
	for($tbsr=0; $tbsr < mysql_num_rows($tbl); $tbsr++)
		{
		$tablesrc = mysql_tablename($tbl, $tbsr);
		$fldnm = mysql_list_fields($jaxeed, $tablesrc, $conn);
		$clmn = mysql_num_fields($fldnm);
		for ($cl = 0; $cl < $clmn; $cl++)
			{
			$fieldsrc = mysql_field_name($fldnm, $cl);
			$searchcode = "SELECT * FROM $tablesrc WHERE $fieldsrc LIKE '$keyword'";
			$search = mysql_query($searchcode, $conn) or die(mysql_error());

		#Dealing with hits:
		if(mysql_fetch_assoc($search) == TRUE)
			{
			$hitcount++;
			$hittable = $tablesrc;
			$hitfield = $fieldsrc;
			$allfields = mysql_list_fields($jaxeed, $hittable, $conn);
			$allcolumns = mysql_num_fields($allfields);
			$colspan = $allcolumns + 2;
			$showfields .="<td bordercolor=\"#000080\"><div align=center class=\"monitor\" colspan=\"$colspan\"> $hittable </td></tr><tr>";
			$rendercontent = mysql_query("SELECT * FROM $hittable WHERE $hitfield LIKE '$keyword'", $conn);
				while($hit = mysql_fetch_array($rendercontent, MYSQL_ASSOC)) 
					{
     				foreach ($hit as $hit_info) $searchresult .= "<td><div class=\"monitor\">$hit_info</div></td>"; $br++; if($br >= $allcolumns) {$searchresult .= "</tr><tr>"; $br = 0;}
					}
			for ($af = 0; $af < $allcolumns; $af++) 
				{
				if(mysql_field_name($allfields, $af) != $hitfield) 
					{
					$showfields .= "<td bgcolor=\"#00C000\"><div align=center class=\"blass\">" .mysql_field_name($allfields, $af) . ": </div></td>"; $stopper++;
					}
				else
				if(mysql_field_name($allfields, $af) == $hitfield)
					{
					$showfields .= "<td bgcolor=\"#00C000\"><div align=center class=\"mon2\">" .mysql_field_name($allfields, $af) . ": </div></td>"; $stopper++;
					}
				if($stopper == $allcolumns)
					{
					$showfields .= "</tr> $searchresult </table><br><table border=\"1\" cellspacing=\"0\" cellpadding=\"2\" bordercolor=\"#00C000\"><tr>"; $stopper = 0; $searchresult = NULL;
					}
				}
			}
		}
	}
}
if($hitcount == NULL){
		$found = "Found no matches...<br>";
		}
if($hitcount != NULL){
		$found = "Found $hitcount matches...<br>";
		}

This script searches and posts the findings into the multipart variable $showfields, and the count of hits into $hitcount as well as making the hit statements in $found.
To render the findings you could as me put it into the HTML part of the page like this:

if($keyword != NULL){
		echo "$found <br>";
		echo "<table border=\"1\" cellspacing=\"0\" cellpadding=\"2\" bordercolor=\"#00C000\">";
		echo "<tr> $showfields </tr>";
		echo "</table>";
		}

There! It wasnt really THAT hard to make, but I stumbled a LOT, so it tood a good while longer then I would have expected. Anyway, ideas to improvement are always welcome.

Cheers