[Resolved] k ima n00b. i need some help with searching a DB.

[Kinsbane]

Okay so Ive written a basic search script where the user can only type in one keyword to search by - and it only searches one column in the DB. This is for an image database containing assorted columns of data. Right now, they can only search for one word in the image's description field - that's not good. I gotta allow them to use multiple words in their search, and let them search almost every column for anything about an image. I just don't know how, have gotten told a number of things with nothing concrete - I want to try to avoid using indexing with multiple pages, since the db will eventually grow to hold about 3,000 records - it's not only to save space, but also to eliminate the number of clicks required by the user; our construction guys arent the most computer / interweb savvy. If youd like to see what I have done already, click on: http://www.darwenstheory.net/intranet_test/search.php - type into the keyword box 'test' and you'll get returned 10 results, 5 per page.

And, here's my search code:

<?php

$User="*"; 
$Pass="*"; 
$Host="localhost"; 
$DB="database"; 
$Table="images"; 
$Table2="user";
$Per_Page="5";
$sql_conn=mysql_connect($Host, $User, $Pass) or die("MySQL Error #".mysql_errno().": ".mysql_error());
$sql_db=mysql_select_db($DB);
$SQL="SELECT COUNT(*) AS Total FROM images WHERE s_Description LIKE '%".$_REQUEST['keyword']."%'";
$SQL_Result=mysql_db_query($DB, $SQL);
$SQL_Result_Array=mysql_fetch_array($SQL_Result);
$Total=$SQL_Result_Array['Total'];
$SQL="SELECT * FROM images WHERE s_Description LIKE '%".$_REQUEST['keyword']."%' ORDER BY p_Site";
if (empty($_GET['Result_Set']))
	{
		$Result_Set=0;
		$SQL.=" LIMIT $Result_Set, $Per_Page";
	}
	else
	{
		$Result_Set=$_GET['Result_Set'];
		$SQL.=" LIMIT $Result_Set, $Per_Page";
	}
$SQL_Result=mysql_db_query($DB, $SQL);
$SQL_Rows=mysql_num_rows($SQL_Result) or die(mysql_errno() . mysql_error());
?>

And, here's the code for how the results get returned and formatted (I would very much like to keep this format):

<?php
for ($a=0; $a < $SQL_Rows; $a++)
	{
		$SQL_Array=mysql_fetch_array ($SQL_Result);
		$parksite=$SQL_Array['p_Site'];
		$parkproject=$SQL_Array['p_Project'];
		$sheettype=$SQL_Array['s_Type'];
		$sheetdesc=$SQL_Array['s_Description'];
		$sheetiteration=$SQL_Array['s_Iteration'];
		$imagepath=$SQL_Array['image_path'];
		print "<table  code>";
	}
?>

I just don't know what to do to get myself a better search 🙁

Thanks in advance for help provided

[EricC]

If you're using MySQL 3.23.23 or better, then you may want to look at using MySQL's FullText indexing and searches to see if maybe they'll be useful for you.

[Kinsbane]

Originally posted by EricC
If you're using MySQL 3.23.23 or better, then you may want to look at using MySQL's FullText indexing and searches to see if maybe they'll be useful for you.

I am having quite a bit of difficulty with this - it won't return my results the way I already have done it thus far - am I missing something?

And why are sometimes the manual's queries like this:

SELECT * FROM table WHERE MATCH (body, title) AGAINST ('keyword');

some are like this:

SELECT id, MATCH(body, title) AGAINST ('keyword') FROM table;

and some are like this:

SELECT id, body, MATCH (body, title) AGAINST ('keyword') AS score FROM table WHERE MATCH (body, title) AGAINST ('keyword') ;

😕 😕 😕

I'm not understanding this at all.

[stolzyboy]

yours could be something like

<?
$SQL = "SELECT * FROM images MATCH (s_Description) AGAINST ('" . $_REQUEST['keyword'] . "') ORDER BY p_Site;";
?>

[EricC]

You might want to also read this article over at DatabaseJournal.com that is a very good primer for using MySQL's fulltext capabilities.

[Kinsbane]

Whew guys thanks for all the good info. I got something worked out, however, paginating my results has turned into a nightmare.

Could you take a gander at the following blocks of code and try and find out with me where I'm going wrong as far as paginating the rows returned?

$word=$_REQUEST['keyword'];
$sql_conn=mysql_connect($Host, $User, $Pass) or die("MySQL Error #".mysql_errno().": ".mysql_error());
$sql_db=mysql_select_db($DB);
$SQL="SELECT *, MATCH (s_Description) AGAINST ('$word') AS Total FROM ".$Table." WHERE MATCH (s_Description) AGAINST ('$word') ORDER BY p_Site DESC";
$SQL_Result=mysql_db_query($DB,$SQL);
$SQL_Result_Array=mysql_fetch_array($SQL_Result);
$Total=$SQL_Result_Array['Total'];
echo $Total;
if (empty($_GET['Result_Set']))
	{
		$Result_Set=0;
		$SQL.=" LIMIT $Result_Set, $Per_Page";
	}
	else
	{
		$Result_Set=$_GET['Result_Set'];
		$SQL.=" LIMIT $Result_Set, $Per_Page";
	}
$SQL_Result=mysql_db_query($DB, $SQL);
$SQL_Rows=mysql_num_rows($SQL_Result) or die(mysql_errno() . mysql_error());
?>

<?php
for ($a=0; $a < $SQL_Rows; $a++)
	{
		$SQL_Array=mysql_fetch_array ($SQL_Result);
		$parksite=$SQL_Array['p_Site'];
		$parkproject=$SQL_Array['p_Project'];
		$sheettype=$SQL_Array['s_Type'];
		$sheetdesc=$SQL_Array['s_Description'];
		$sheetiteration=$SQL_Array['s_Iteration'];
		$imagepath=$SQL_Array['image_path'];
		print "<table></table><br>";
	}
?>

<?php
	if ($Total > 0)
	{
		if ($Result_Set < $Total && $Result_Set > 0)
			{
				$Res1=$Result_Set-$Per_Page;
				echo "<a href=\"searchresults2.php?Result_Set=$Res1&keyword=$word\"><< Previous Page </a> ";
			}
		$Pages=$Total / $Per_Page;
		if ($Pages > 1)
			{
				for ($b=0, $c=1; $b < $Pages; $b++, $c++)
					{
						$Res1 = $Per_Page * $b;
						echo "<a href=\"searchresults2.php?Result_Set=$Res1&keyword=$word\">$c</a> \n";
					}
				}
			if ($Result_Set >= 0 && $Result_Set<$Total)
				{
					$Res1=$Result_Set + $Per_Page;
					if ($Res1<$Total)
						{
						echo "<a href=\"searchresults2.php?Result_Set=$Res1&keyword=$word\"> Next Page >></a>";
						}
				}
		}
	?>

The above blocks are all the meat and bones of it all, but I dont get errors or anything when trying to execute the pagination section (3rd code block) - they just don't show up at all.

[Kinsbane]

Bump - anyone able to help out with pagination?

[Kinsbane]

Yay! Resolved!

Here is how I had to change my first block of code w/queries to get a proper total count using MATCH AGAINST clauses:

$SQL="SELECT COUNT(*) AS Total, MATCH (s_Description) AGAINST ('$word') FROM ".$Table." GROUP BY p_Site";
$SQL_Result=mysql_query($SQL, $sql_conn);
$SQL_Result_Array=mysql_fetch_array($SQL_Result);
$Total=$SQL_Result_Array['Total'];
$SQL="SELECT *, MATCH (s_Description) AGAINST ('$word') FROM ".$Table." WHERE MATCH (s_Description) AGAINST ('$word') ORDER BY p_Site DESC";
if (empty($_GET['Result_Set']))
	{
		$Result_Set=0;
		$SQL.=" LIMIT $Result_Set, $Per_Page";
	}
	else
	{
		$Result_Set=$_GET['Result_Set'];
		$SQL.=" LIMIT $Result_Set, $Per_Page";
	}
$SQL_Result=mysql_db_query($DB, $SQL);
$SQL_Rows=mysql_num_rows($SQL_Result) or die(mysql_errno() . mysql_error());
?>

Previously, I had been naming a column Total for the relevancy match, and not the count like above. Now it all works 🙂