5 Replies - 763 Views - Last Post: 19 April 2009 - 08:05 AM Rate Topic: -----

#1 iry  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 39
  • Joined: 17-September 07

[urgent!] need helps in writing query for building search engine

Post icon  Posted 18 April 2009 - 03:04 PM

at first i'm using $query(as below) to search the result from database(MySQL).
but this will only search the whole string that enter by user.

$var = @$_POST['q_box'];
$trimmed = trim($var);

$query = "select * from courseFinder where 
courseName like \"%$trimmed%\" 
or faculty like \"%$trimmed%\" 
or overview like \"%$trimmed%\" 
or outline like \"%$trimmed%\" 
or entryRequirement like \"%$trimmed%\"
or career like \"%$trimmed%\"
order by ID"; 


then i search for another solution to makes the Course Finder more useful which can:
i. search phrase by phrase
ii. list the most accurate one on top
iii. ignore the common word(stopword)

and i found the way by using $sql(as below).

$sql = "SELECT DISTINCT MATCH(courseCode, courseName, faculty, overview, outline, entryRequirement, career) Against ('$var' IN BOOLEAN MODE) as score, courseCode,  courseName, faculty, overview, outline, entryRequirement, career FROM courseFinder WHERE MATCH(courseCode, courseName, faculty, overview, outline, entryRequirement, career) Against ('$var' IN BOOLEAN MODE) ORDER BY score DESC";


but after i done all..and when the time before it goes to user testing..
i found that when i use $sql, i can't return the ID of the rows return (where ID is an INT that has set auto_increment), but if use $query, ID can be return.

the code for returning and passing the ID to url is as follows:
do
	{
	//bind data
	  $faculty = $row_sql["faculty"];
	$courseName = $row_sql["courseName"];
	$ID = $row_sql["ID"];	
	echo '<tr vAlign=top>';
	   //this if-else is to do alternate row for table
	   if ($altrow==0)
	   {
			echo '<td class="rowwhite" align="right">'.$count.'</td>';
			echo '<td class="rowwhite" align="left">&nbsp;<a href=view.php?ID='.$row_sql["ID"].'>'.$courseName.'</a><br/>&nbsp;&nbsp;'.$faculty.'</td>';
			$altrow++;
		}
	   else
	   {
			$altrow=0;
			echo '<td class="rowgrey2" align="right">'.$count.'</td>';
			echo '<td class="rowgrey2" align="left">&nbsp;<a href=view.php?ID='.$ID.'>'.$courseName.'</a><br/>&nbsp;&nbsp;'.$faculty.'</td>';
		}
	echo '</tr>';
	$count++;
	}while ($row_sql = mysql_fetch_assoc($query));


i'm finding the fastest ways to solve problem, because i only left 20 hours to done it.
how to solve this problem?
i need the ID for my dynamic page to be display...

this is the link to my "Course Finder" search page : http://what-o.com/it...nder/search.php

hope to hear from you all soon..
thanks a lot!!

Is This A Good Question/Topic? 0
  • +

Replies To: [urgent!] need helps in writing query for building search engine

#2 iry  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 39
  • Joined: 17-September 07

Re: [urgent!] need helps in writing query for building search engine

Post icon  Posted 18 April 2009 - 03:04 PM

at first i'm using $query(as below) to search the result from database(MySQL).
but this will only search the whole string that enter by user.

$var = @$_POST['q_box'];
$trimmed = trim($var);

$query = "select * from courseFinder where 
courseName like \"%$trimmed%\" 
or faculty like \"%$trimmed%\" 
or overview like \"%$trimmed%\" 
or outline like \"%$trimmed%\" 
or entryRequirement like \"%$trimmed%\"
or career like \"%$trimmed%\"
order by ID"; 


then i search for another solution to makes the Course Finder more useful which can:
i. search phrase by phrase
ii. list the most accurate one on top
iii. ignore the common word(stopword)

and i found the way by using $sql(as below).

$sql = "SELECT DISTINCT MATCH(courseCode, courseName, faculty, overview, outline, entryRequirement, career) Against ('$var' IN BOOLEAN MODE) as score, courseCode,  courseName, faculty, overview, outline, entryRequirement, career FROM courseFinder WHERE MATCH(courseCode, courseName, faculty, overview, outline, entryRequirement, career) Against ('$var' IN BOOLEAN MODE) ORDER BY score DESC";


but after i done all..and when the time before it goes to user testing..
i found that when i use $sql, i can't return the ID of the rows return (where ID is an INT that has set auto_increment), but if use $query, ID can be return.

the code for returning and passing the ID to url is as follows:
do
	{
	//bind data
	  $faculty = $row_sql["faculty"];
	$courseName = $row_sql["courseName"];
	$ID = $row_sql["ID"];	
	echo '<tr vAlign=top>';
	   //this if-else is to do alternate row for table
	   if ($altrow==0)
	   {
			echo '<td class="rowwhite" align="right">'.$count.'</td>';
			echo '<td class="rowwhite" align="left">&nbsp;<a href=view.php?ID='.$row_sql["ID"].'>'.$courseName.'</a><br/>&nbsp;&nbsp;'.$faculty.'</td>';
			$altrow++;
		}
	   else
	   {
			$altrow=0;
			echo '<td class="rowgrey2" align="right">'.$count.'</td>';
			echo '<td class="rowgrey2" align="left">&nbsp;<a href=view.php?ID='.$ID.'>'.$courseName.'</a><br/>&nbsp;&nbsp;'.$faculty.'</td>';
		}
	echo '</tr>';
	$count++;
	}while ($row_sql = mysql_fetch_assoc($query));


i'm finding the fastest ways to solve problem, because i only left 20 hours to done it.
how to solve this problem?
i need the ID for my dynamic page to be display...

this is the link to my "Course Finder" search page : http://what-o.com/it...nder/search.php

hope to hear from you all soon..
thanks a lot!!
Was This Post Helpful? 0
  • +
  • -

#3 BetaWar  Icon User is offline

  • #include "soul.h"
  • member icon

Reputation: 1104
  • View blog
  • Posts: 6,913
  • Joined: 07-September 06

Re: [urgent!] need helps in writing query for building search engine

Posted 18 April 2009 - 05:44 PM

Please don't post duplicate threads. Merged.
Was This Post Helpful? 0
  • +
  • -

#4 tivrfoa  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 9
  • View blog
  • Posts: 98
  • Joined: 25-January 09

Re: [urgent!] need helps in writing query for building search engine

Posted 18 April 2009 - 05:52 PM

Does the courseCode is unique?
If it is you can use it, instead of the id.

Else you make a query inside the do...while to get the
id, and you use the fields you already have as conditions to the new query, eg:
$newQuery = "select id from courseFinder where faculty = '$faculty' and ..."
Was This Post Helpful? 1
  • +
  • -

#5 iry  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 39
  • Joined: 17-September 07

Re: [urgent!] need helps in writing query for building search engine

Posted 19 April 2009 - 07:14 AM

View Posttivrfoa, on 18 Apr, 2009 - 04:52 PM, said:

Does the courseCode is unique?
If it is you can use it, instead of the id.

Else you make a query inside the do...while to get the
id, and you use the fields you already have as conditions to the new query, eg:
$newQuery = "select id from courseFinder where faculty = '$faculty' and ..."


yes. the courseCode is unique, but i do not have time to collect all the code for courses, thats why..

i will try the second solution, thanks


View PostBetaWar, on 18 Apr, 2009 - 04:44 PM, said:

Please don't post duplicate threads. Merged.


sorry. i don't know where i should post
Was This Post Helpful? 0
  • +
  • -

#6 iry  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 39
  • Joined: 17-September 07

Re: [urgent!] need helps in writing query for building search engine

Posted 19 April 2009 - 08:05 AM

THANKS A LOT!!!!

the second solution works!!
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1