[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

MYSQL/PHP Search function not working properly...

Posted on 2006-05-22
13
Medium Priority
?
332 Views
Last Modified: 2013-12-12
Hello everyone.  I'm having a problem with searching a MySQL table in a DB.  I have set up 2 queries, one is a full-text search:

SELECT mytable_id, mytable_title, mytable_caption, mytable_dts, MATCH(mytable_title, mytable_caption, mytable_full_body) AGAINST ('searchinfo') AS score FROM mytable WHERE MATCH(mytable_title, mytable_caption, mytable_full_body) AGAINST ('searchinfo')
ORDER BY score DESC

The other is just a straight LIKE match:

SELECT *
FROM mytable
WHERE mytable_full_body LIKE '%colname%'

Both queries run and work, but only return the 1st value found (usually found on the full_body of the 1st table entry).  I expect this of the LIKE query, but I would have thought the full-text search would display all the hits, in order of score.  I need to list all hits found, in order or relevance (score).

Here's my PHP script:

<?php require_once('../Connections/ccSQL.php'); ?>

<?php
$searchinfo_rsResults = "-1";
if (isset($_GET['search'])) {
  $searchinfo_rsResults = (get_magic_quotes_gpc()) ? $_GET['search'] : addslashes($_GET['search']);
}
mysql_select_db($database_ccSQL, $ccSQL);
$query_rsResults = sprintf("SELECT mytable_id, mytable_title, mytable_caption, mytable_dts,                  MATCH(mytable_title, mytable_caption, mytable_full_body)                  AGAINST ('%s') AS score FROM mytable WHERE MATCH(mytable_title, mytable_caption, mytable_full_body)                  AGAINST ('%s') ORDER BY score DESC ", $searchinfo_rsResults,$searchinfo_rsResults);
$rsResults = mysql_query($query_rsResults, $ccSQL) or die(mysql_error());
$row_rsResults = mysql_fetch_assoc($rsResults);
$totalRows_rsResults = mysql_num_rows($rsResults);

$colname_rsMatch = "-1";
if (isset($_GET['search'])) {
  $colname_rsMatch = (get_magic_quotes_gpc()) ? $_GET['search'] : addslashes($_GET['search']);
}
mysql_select_db($database_ccSQL, $ccSQL);
$query_rsMatch = sprintf("SELECT * FROM mytable WHERE mytable_full_body LIKE '%%%s%%'", $colname_rsMatch);
$rsMatch = mysql_query($query_rsMatch, $ccSQL) or die(mysql_error());
$row_rsMatch = mysql_fetch_assoc($rsMatch);
$totalRows_rsMatch = mysql_num_rows($rsMatch);
?>
<html>
 <TD vAlign=top><div align="left">
                        <p align="center"><span class="style7">Search Database</span></p>
                        <form action="search.php" method="get" class="style9">
                          <div align="center">
                            <input name="search" type="text">
                        &nbsp;&nbsp;
                            <input name="Submit" type="submit" value="Go">
                          </div>
                        </form>
                        <p class="style9">&nbsp;</p>
                        <p align="center" class="style9"><span class="style3">Search result</span><strong>:</strong></p>
                        <p align="left" class="style9"><br>
                          <span class="style10"><a href="displayInt.php?recordID=<?php echo $row_rsMatch['mytable_title']; ?>" class="style1"><?php echo htmlentities($row_rsMatch['mytable_caption']); ?>
                          </a></span></p>
                        <p align="left" class="style9"><span class="style10"><a href="displayInt.php?recordID=<?php echo $row_rsMatch['mytable_title']; ?>" class="style1">
                          <?php
 
   $search_key = $_GET['search'];
  $text = $row_rsMatch['mytable_full_body'];
 
  preg_match("/(^|\W)\w.{0,50}.{$search_key}.{0,50}\w($|\W)/i", $text, $subtext);
  $subtext[0] = preg_replace('/('.preg_quote($search_key).')/i','<b>\1</b>', $subtext[0]);
 
  ?>
                          </a></span> <font face="Arial" size="2" color="#FFFFFF"><? echo $subtext[0]; ?></font> </p>
                        <p align="left" class="style9">&nbsp;</p>
                        <p align="center" class="style9"><span class="style11">Alternate Search Results: </span></p>
                        <p align="left" class="style9"><span class="style10"><a href="displayInt.php?recordID=<?php echo $row_rsResults['mytable_title']; ?>" class="style1"><?php echo htmlentities($row_rsResults['mytable_caption']); ?></a> <span class="style1">&nbsp;Ranking:&nbsp;<strong><?php echo number_format($row_rsResults['score'],2,".",","); ?></strong></span></span><span class="style4"><span class="style1"><br>
                                </span></span><span class="style1"></p>
                      </div>
                        <p><B>                                  <BR>
                                  </B>                              </p>
                        <form action="index.php">
                                <div align="center"><b>
                                  <input name="Submit" type="submit" value="View Database">
                                </b> </div>
                              </form>
                      <B>                              </B></TD></TR></TBODY></TABLE></TD>
           
               
 
        width=468></TD></TR></TBODY></TABLE>
      <?php
mysql_free_result($rsResults);

mysql_free_result($rsMatch);
?></TD>
0
Comment
Question by:south_paw
  • 6
  • 4
  • 3
13 Comments
 
LVL 16

Assisted Solution

by:dr_dedo
dr_dedo earned 484 total points
ID: 16735665
give that a shot
SELECT * ,
MATCH ( mytable_title, mytable_caption, mytable_full_body )
AGAINST ( 'searchinfo') AS `score`,
FROM `mytable`
WHERE MATCH (  mytable_title, mytable_caption, mytable_full_body  )
AGAINST ( 'searchinfo' )
GROUP BY `id`
HAVING
MATCH (mytable_title, mytable_caption, mytable_full_body )
AGAINST ( 'searchinfo' ) AND `score` = '1'

don't ORDER BY
0
 

Author Comment

by:south_paw
ID: 16736259
Something's amiss.....

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM `mytable` WHERE MATCH ( mytable_title, mytable_caption, mytable_full_body' at line 1
0
 
LVL 20

Assisted Solution

by:Muhammad Wasif
Muhammad Wasif earned 1516 total points
ID: 16741448
a correction to dr_dedo's query
You don't need , (comma) after `score`

SELECT * ,
MATCH ( mytable_title, mytable_caption, mytable_full_body )
AGAINST ( 'searchinfo') AS `score`
FROM `mytable`
WHERE MATCH (  mytable_title, mytable_caption, mytable_full_body  )
AGAINST ( 'searchinfo' )
GROUP BY `id`
HAVING
MATCH (mytable_title, mytable_caption, mytable_full_body )
AGAINST ( 'searchinfo' ) AND `score` = '1'


How many records do you have in table?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:south_paw
ID: 16743451
Hey there wasifg,

No errors, but no results.  I only have 3 records in the table, so I suspect this has something to do with it (the 50% threshold rule maybe).  The full_text_body contains 200-300 lines of text though.... Thought it should be at least getting some hits?
0
 
LVL 20

Expert Comment

by:Muhammad Wasif
ID: 16743735
Insert some more records to the table, around 10 records. then execute your query. Hope this will work.
0
 

Author Comment

by:south_paw
ID: 16744246
Loaded it up with 9 entries, but still no luck.  Nothing is returned in in normal mode, and if I try a matach against 'searchinfo' in boolean mode i still only get the 1st result.

I can't figure out what could be going wrong....
0
 

Author Comment

by:south_paw
ID: 16747329
I am happy to use another type of search if I cannot get the full-text working, I am running out of time and really need a solution.  Any suggestions?
0
 
LVL 16

Assisted Solution

by:dr_dedo
dr_dedo earned 484 total points
ID: 16747762
did u index your table ?? it might help !!
ALTER TABLE mytable ADD FULLTEXT INDEX (mytable_title, mytable_caption, mytable_full_body );
0
 
LVL 20

Accepted Solution

by:
Muhammad Wasif earned 1516 total points
ID: 16750156
There is no problem with your query, the problem is the way you are displaying results. You need a while() to go through all the records. You need to look at the example provided at http://www.php.net/mysql_fetch_assoc

Try this code, i have not changed anything just introdiuced while()
<?php
require_once('../Connections/ccSQL.php');

$searchinfo_rsResults = "-1";
if (isset($_GET['search'])) {
  $searchinfo_rsResults = (get_magic_quotes_gpc()) ? $_GET['search'] : addslashes($_GET['search']);
}
mysql_select_db($database_ccSQL, $ccSQL);
$query_rsResults = sprintf("SELECT mytable_id, mytable_title, mytable_caption, mytable_dts,
                                          MATCH(mytable_title, mytable_caption, mytable_full_body)
                                          AGAINST ('%s') AS score FROM mytable WHERE MATCH(mytable_title, mytable_caption, mytable_full_body)
                                          AGAINST ('%s') ORDER BY score DESC ", $searchinfo_rsResults, $searchinfo_rsResults);

$rsResults = mysql_query($query_rsResults, $ccSQL) or die(mysql_error());
$totalRows_rsResults = mysql_num_rows($rsResults);
echo "Total records found: $totalRows_rsResults<br>";
?>
<table border="1" width="95%">
<tr>
      <td>ID</td>
      <td>Title</td>
      <td>Caption</td>
      <td>DTS</td>
      <td>Score</td>
</tr>
<?
while($row_rsResults = mysql_fetch_assoc($rsResults))
{?>
<tr>
      <td><?=$row_rsResults["mytable_id"]?></td>
      <td><?=$row_rsResults["mytable_title"]?></td>
      <td><?=$row_rsResults["mytable_caption"]?></td>
      <td><?=$row_rsResults["mytable_dts"]?></td>
      <td><?=$row_rsResults["score"]?></td>
</tr>
<?
}
?>
</table>
0
 

Author Comment

by:south_paw
ID: 16757025
Table has been indexed also.  wasifg, I tried tha above, but it is still returning zero results   :(

Will keep testing....
0
 
LVL 20

Assisted Solution

by:Muhammad Wasif
Muhammad Wasif earned 1516 total points
ID: 16758322
Did you inserted below code for search form? If not, add this code to the code i provided above and try again

 <p align="center"><span class="style7">Search Database</span></p>
                        <form action="search.php" method="get" class="style9">
                          <div align="center">
                            <input name="search" type="text">
                        &nbsp;&nbsp;
                            <input name="Submit" type="submit" value="Go">
                          </div>
                        </form>
0
 
LVL 16

Expert Comment

by:dr_dedo
ID: 16760371
maybe it is a good idea to post a sql that creates your db, so we can test it with you ? zip it, upload it to rapidshare or something, many testers for sure better than one!
0
 

Author Comment

by:south_paw
ID: 16770129
Thanks guys, I got it sorted by creating a different search function and placing all the urls in an array.   I think the full-search was being hindered by the format of the data in the table.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

These days socially coordinated efforts have turned into a critical requirement for enterprises.
There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to dynamically set the form action using jQuery.
Suggested Courses
Course of the Month18 days, 12 hours left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question