Search Question? can someone point me in the right direction

[aperales10]

I know some get fed up w/ simple q's like this but I've already search and could not find what I was looking for:

I need to find a tutorial that shows me how to do a search for a specific word or words using php and mysql tables.

This is what I am trying to do:
If someone searches for 'wh' I only want to display results with the word 'wh'. The code I have now displays results with the letters 'wh' in them. for example, I get a row with the word 'white' and I don't want this. I only want rows with content such as this '5 WH Blades'
Here is my code:

if(isset($HTTP_POST_VARS['search']))
{
$search = $HTTP_POST_VARS['search'];
}

if(!isset($search))
{
 die("Search data not submitted!<br>Please click back and try again");
}

if(empty($search))
{
 die("Search data empty!");
}



$host = "host"; 
$user = "user"; 
$password = "password"; 
$database = "database"; 
$dbtable = "table"; 
$limit = "12"; // rows per page to return 


// connect to database 
$link = @ mysql_connect($host,$user,$password) or die ("Sorry <BR>\n Could not connect. Please try later"); 

// next determine if itemresults has been passed to script, if not use 0 
if (empty($itemresults)) 
    { 
    $itemresults=0; 
    } 





//query 
$query = "SELECT * from $dbtable WHERE description LIKE '%$search%' ORDER BY item limit $itemresults, $limit"; 



// check to see if the query was successful 
if (!mysql_db_query($database, $query, $link))  

{ 
    echo ("Sorry <br>\n Could not connect!<BR>\n"); 
}      

else 
{ 

$result = mysql_query($query); 
$num_results = mysql_num_rows($result); 
echo " There are <b>$num_results</b> Results <br>\n<br>\n"; 

if ($itemresults >1) 
{ // bypass PREV link if itemresults is 0 
    $previtemresults=$itemresults-12; 
    echo "<a href=\"$PHP_SELF?itemresults=$previtemresults&search=$search\">PREV</a> &nbsp; \n"; 
} 
// check to see if last page 
if (!($num_results < $limit)) {
    // not last page so give NEXT link 
    $newitemresults=$itemresults+$limit; 
    echo "<a href=\"$PHP_SELF?itemresults=$newitemresults&search=$search\">NEXT</a><p>\n"; 
} 



$a = array(); 
while ($row = mysql_fetch_array($result)) 
{ 
    $a[] = '<font size="2" face="Arial, Helvetica, sans-serif">'.'<a href="http://localhost/lightingfixturesonsale/item_details.php?item='.($row['item']).'">'.'<img src=\'images/'.($row['item']).'.jpg\' border=0>'.'</a>'.'<br>'.'<a href="http://localhost/lightingfixturesonsale/item_details.php?item='.($row['item']).'">'.$row['item'].'</a>'.'<br>'.$row['description'].'</font><br><br><br>'; 
} 

echo '<table width="558" border="0" cellspacing="0" cellpadding="0" bordercolor="#CCCCCC">'; 
for ($i = 0; $i < $num_results; $i++) 
{ 
    echo '<tr align="center"><td width="186">' . $a[$i] . '</td>'; 
    $i++; 
    echo '<td width="186">' . $a[$i] . '</td>'; 
    $i++; 
    echo '<td width="186">' . $a[$i] . '</td></tr>'; 
} 
echo '</table>'; 

// next we need to do the links to other results 

if ($itemresults >1) 
{ // bypass PREV link if itemresults is 0 
    $previtemresults=$itemresults-12; 
    echo "<a href=\"$PHP_SELF?itemresults=$previtemresults&search=$search\">PREV</a> &nbsp; \n"; 
} 

// calculate number of pages needing links 
$pages = ceil($numrows / $limit);

// $pages now contains int of pages needed unless there is a remainder from division 
if ($numrows%$limit) { 
    // has remainder so add one page 
    $pages++; 
} 

for ($i=1;$i<=$pages;$i++) { // loop thru 
    $newitemresults=$limit*($i-1); 
    echo "<a href=\"$PHP_SELF?itemresults=$newitemresults\">$i</a> &nbsp; \n"; 
} 

// check to see if last page 
if (!($num_results < $limit)) {
    // not last page so give NEXT link 
    $newitemresults=$itemresults+$limit; 
    echo "<a href=\"$PHP_SELF?itemresults=$newitemresults&search=$search\">NEXT</a><p>\n"; 
} 
} 
// Close the database connection 
if ($link = mysql_connect($host,$user,$password)) 
    { 
    mysql_close ($link); 
    } 

[sidney]

youll probably have to us regex in the query to match whole word

$query = "SELECT * from $dbtable WHERE description REGEXP '[^{[:alnum:]]?".$search."[[:space:][:punct:]]'} ORDER BY item limit $itemresults, $limit";

http://dev.mysql.com/doc/mysql/en/Regexp.html

[aperales10]

thanks Sidney, it worked...somewhat. The only thing now is that when I do a search for the last word in the description, it is not finding it. Do you know why?

Appreciate your help!
Armando

[sidney]

that whould require a slighly differnet query

query for word anywhere

$query = "SELECT * from $dbtable WHERE description REGEXP '[^{[:alnum:]]?".$search."[[:space:][punct:]]'} ORDER BY item limit $itemresults, $limit";

query for last word
$query = mysql_query("SELECT * from $dbtable WHERE description REGEXP '[[:space:]]".$search."[[:punct:]]?\$' ORDER BY item limit $itemresults, $limit");

[aperales10]

the first query you suggested only works when there is a punctuation in that field and the second doesn't work for what I'm trying to do. I am trying the other options on the link you recommended 'http://dev.mysql.com/doc/mysql/en/Regexp.html' but I can't seem to get any to work. I am stumped. Any other suggestions?

Thanks!

[aperales10]

I used this query and got it to work.

$query = "SELECT * from $dbtable WHERE description REGEXP '[[:<:]]".$search."[[:>:]]' ORDER BY item limit $itemresults, $limit";

Thanks for your help sidney!!!

[sidney]

try this

$query = mysql_query("SELECT * from $dbtable WHERE description REGEXP '[^{[:alnum:]]".$search."([[:space:][:punct:]]|\$)'} ORDER BY item");

edit i was looking for them [[:<:]] [[:>:]] i knew the pcre \b

but did see them on the link i gave must be blind

use your example as mine was a work around as i didnt see the above

[aperales10]

That worked too. Thanks a million!

Armando