Help with a query

[voyde]

Currently:

$sql = "SELECT * FROM lyrics WHERE lyric LIKE '%$q%' ORDER BY id DESC limit $startat,$search_perpage";

 

Its ordered by descending id, i need it to order by relevance. so the more times $q is found the closer it put it to the top of the results.

 

any ideas?

[corbin]

I personally haven't ever massed searched text like that, but based on a few other threads, this could help:

 

http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

 

Sorry if that page doesn't help you at all x.x.

[voyde]

thanks but I have tried that already with no luck.

 

 

its mysql 5.0.45 and i cant figure it out on it.

[corbin]

Hmmm.....  The way I understand fulltext searching as working, it should work perfectly for this case.

 

Maybe try googling around and seeing how it works if you don't understand it, or maybe someone else can suggest a better solution.

[cyrixware]

<FORM ACTION="searchtest.php" METHOD="POST" NAME="ArticlesSearch" class="style1">
  <strong><INPUT TYPE="TEXT" NAME="SearchString">
<INPUT TYPE="SUBMIT" NAME="SUBMIT" VALUE="Search">
  </strong>
</FORM>

 

Then...

<?php
//Connect to DB
include("connDB.php");	
$Limit = 5; //Number of results per page
$SearchString=$_POST["SearchString"]; // Get the search tearm
If($SearchString == "") $SearchString=$_GET["SearchString"]; // Get the search tearm
If($SearchString == "") {
Echo"Nothing to Search For";
exit();
}
$page=$_GET["page"]; //Get the page number to show
If($page == "") $page=1; //If no page number is set, the default page is 1

//Get the number of results
$SearchResult=mysql_query("SELECT * FROM updates WHERE updateMessage LIKE '%$SearchString%' ORDER BY updateId") or die(mysql_error());
$NumberOfResults=mysql_num_rows($SearchResult);

//Get the number of pages
$NumberOfPages=ceil($NumberOfResults/$Limit);

$SearchResult=mysql_query("SELECT * FROM updates WHERE updateMessage LIKE '%$SearchString%' ORDER BY updateId LIMIT " . ($page-1)*$Limit . ",$Limit") or die(mysql_error());
$x=0;
While($row = mysql_fetch_object($SearchResult)) {
$x++;
?>

<?php Echo "<b>$x.</b> ".$row->updateMessage . "</span><BR><BR>";?>


<?php
}
$Nav="";
If($page > 1) {
$Nav .= "<A HREF=\"searchtest.php?page=" . ($page-1) . "&SearchString=" .urlencode($SearchString) . "\"><< Prev</A>";
}
For($i = 1 ; $i <= $NumberOfPages ; $i++) {
If($i == $page) {
$Nav .= "<B>$i</B>";
}Else{
$Nav .= "<A HREF=\"searchtest.php?page=" . $i . "&SearchString=" .urlencode($SearchString) . "\">$i</A>";
}
}
If($page < $NumberOfPages) {
$Nav .= "<A HREF=\"searchtest.php?page=" . ($page+1) . "&SearchString=" .urlencode($SearchString) . "\">Next >></A>";
}

Echo "<BR><BR>" . $Nav;
?> 

[cyrixware]

Or this one:

 

<select name="searchType">
    <option value="SuID">ID Number</option>
    <option value="SuLastname">Lastname</option>
    <option value="SuFirstname">Firstname</option>
</select>
     <input name="searchTerm" type="text">
     <input type="submit" name="Submit" value="Search">

 

<?php
if($_REQUEST[submit] == "Search")
{
$searchType = $_REQUEST['searchType'];
$searchTerm = $_REQUEST['searchTerm'];

include("../connDB.php");


$sql_search_student = "SELECT student.*, studsubj.* FROM student, studsubj WHERE 
			student.SuID = studsubj.SuID AND student." . $searchType . " = '$searchTerm' ORDER BY student.SuLastname, student.SuFirstname";
if(!$q_search_student = mysql_query($sql_search_student))
{
	echo "<font color=white></font>";
}

elseif(!mysql_num_rows($q_search_student))
{
	echo "<center>Search Results... No record found!</center>";
}

else
{

	while($r_search_student = mysql_fetch_assoc($q_search_student))
	{
                 ................ displyed info .............
                }
        }
}		
?>

[voyde]

I dont see how those will improve search results.

[voyde]

Nevermind, I got it.