Page 1 of 1

database query to perform search

Posted: Thu Aug 15, 2002 12:10 pm
by ssand
I am trying to build a simple search function that searches keywords for classified ads.

I currently have two tables one Classifieds (which has the client companyID, adID, adtext, etc.) and then table Keywords (which has ref to adID, keywords, and keywordID).

I am stuck on trying to build the query so the when a client searches they don't pull ads from other clients.

Should I scrap the two tables method and just ad a column in the Classifieds table that contains the one field for the list of keywords??

Code: Select all

It is a basic search. From the submit form I have: 

if ($keyword)
	{
	$k = split(" ", $keyword);
	$num_keywords = count ($k);
	for ($i=0; $i<$num_keywords; $i++)
		&#123;
		if ($i)
			$k_string .= "or k.keyword = '".$k&#1111;$i]."' ";

		else
			$k_string .= "k_keyword = '".$k&#1111;$i]."' ";
		&#125;
		$and .="and ($k_string) ";
	&#125;

$sql = SELECT from classifieds ????s on the MySQL query

$result = mysql_query($sql);

etc... to display the results.

If I add a column to Classifieds for keywords will the $and string look for any part in the keywords field or just ads that contain only one keyword that matches???
I am learning as I build this. Any help is greatly appreciated.

Thanks - Steve

Figured it out!!!

Posted: Thu Aug 15, 2002 5:03 pm
by ssand
I got it to work :lol:

If anyone is interested....

Code: Select all

if ($keyword)
	&#123;
	$k = split(" ", $keyword);
	$num_keywords = count ($k);
	for ($i=0; $i<$num_keywords; $i++)
		&#123;
		if ($i)
			$k_string .= "or k.kywd = '".$k&#1111;$i]."' ";

		else
			$k_string .= "k.kywd = '".$k&#1111;$i]."' ";
		&#125;

		$and .="and ($k_string) ";
	&#125;

$sql = "SELECT c.adID, c.run_date, c.ad_name, c.approved, c.approvedby, k.companyID
		FROM classifieds c, keywords k
 		WHERE c.adID = k.adID
		$and
		GROUP BY c.adID, c.ad_name
		ORDER BY c.ad_name desc";

$result = mysql_query ($sql);

etc... then displays the results filtered by the user's companyID