How do you get results to show up from a Full Text Search? (mysql)

mospeed · April 16, 2010

So have my $results variable that contains all of the keys id's of the rows that match the search query but I'm not exactly sure what to do from here.

How do I use this array to unload each key ID individually onto a search result page? Can anyone please help me with this?

l0ve2hat3 · April 16, 2010

please post your code

mospeed · April 16, 2010

<?php require_once('Connections/mysql_connect.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  if (PHP_VERSION < 6) {
    $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
  }

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}

mysql_select_db($database_mysql_connect, $mysql_connect);
$query_Recordset1 = "SELECT gif.id FROM gif";
$Recordset1 = mysql_query($query_Recordset1, $mysql_connect) or die(mysql_error());
$results = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);
?>
<?php

require_once 'dbconnect.php';

$table = 'gif';
$fulltext = 'title, meta_key';


$LookFor = $_GET['LookFor'];
// From the form above
$exploded_LookFor = explode(' ', $LookFor); 
// We're using the space character to split the words into an array
$num = count($exploded_LookFor);
// how many words do we have ?
$num = $num -1; // Why ? - because arrays start at zero & count starts at one !!! 

for ( $counter = 0 ; $counter <= $num; $counter += 1) 

{

	$exploded_LookFor[$counter] = mysql_real_escape_string($exploded_LookFor[$counter]);			// the use of mysql escape to clean it up						

	if (strlen($exploded_LookFor[$counter]) > 3)
  // if >3, we can use fulltext search 
		{

			$LongEnough = 1;
  // we have a word >3 characters long 
			if ($Search == "")

				{

					$Search = '+'.$exploded_LookFor[$counter].'*';

				}

			else

				{

					$Search = $Search.' +'.$exploded_LookFor[$counter].'*';

				}

		}
// adding in the + and * to the search words
	elseif (strlen($exploded_LookFor[$counter]) > 2) // catching words > 2 Characters for a column search  

		{

			if ($SearchDesc == "") 

				{

					$SearchDesc = " AND (title LIKE '%$exploded_LookFor[$counter]%'";

					$SearchDesc = $SearchDesc." OR meta_key LIKE '%$exploded_LookFor[$counter]%')";



				}

				else

				{

					$SearchDesc = $SearchDesc." AND (title LIKE '%$exploded_LookFor[$counter]%'";

					$SearchDesc = $SearchDesc." OR meta_key LIKE '%$exploded_LookFor[$counter]%')";



				}

		}

}


// That's the searching through. Words of 4 characters or more are put to the fulltext search, words of 3 characters
// are going to be done via a column search 

if ($LongEnough > 0) 

{

   $q= "SELECT * FROM $table WHERE match $fulltext against ('$Search' IN BOOLEAN MODE) $SearchDesc";

}


// If we haven't had a word of >3 characters, we're going to cry & do it the slow way – make sure that both 'Title' and
// 'Content' are indexed columns (Another way to get bad marks from the db administrator)

if ($LongEnough != 1)

{

	for ( $counter = 0 ; $counter <= $num; $counter += 1) 

	{

		$exploded_LookFor[$counter] = mysql_real_escape_string($exploded_LookFor[$counter]);									

		if (strlen($exploded_LookFor[$counter]) > 2)

		{

			if ($counter <= 0) 

			{

				$Search = " (title LIKE '%".$exploded_LookFor[$counter]."%'" ;

				$Search = $Search." OR meta_key LIKE '%".$exploded_LookFor[$counter]."%' )" ;


			}

		else

			{

				$Search = $Search."  AND ( title LIKE '%".$exploded_LookFor[$counter]."%'" ;

				$Search = $Search." OR meta_key LIKE '%".$exploded_LookFor[$counter]."%' )";


			}

		}

	}

}

if ($q == '') 
// we haven't got a fulltext search to do – Cries as knows dbadmin person is not going to be happy
{

if ($Search != '')

	{

		$q= "SELECT * FROM $table WHERE $Search";
  // this is a real slow way of doing it
	}

}

if ($q !='')

{		

	// echo $q,'<BR /><BR />';

	$rs=mysql_query($q) or die(mysql_error());

	while ($row = mysql_fetch_array($rs))

	{

		$result[$count] = $row['id'];  

		$count++; // Total Number of rows returned 

	}	 

}

mysql_close($link);

if ($count == 0) {
echo "Sorry, no GIFs were found in your search.";
} else {
foreach($result as $results) {
echo $results . ", ";
}
}
?>

At the end there I was just fooling around with different things I can do with the results as I'm very new to PHP and MySQL. The code was borrowed from a tutorial on codecall.net.

mospeed · April 16, 2010

Okay so if I echo $result[1] It's giving me the 2nd number in the array but if I echo $result[0] it gives me nothing. I'm still not sure how to get these results to show up on the search result forum.

andrewgauger · April 16, 2010

do a print_r of the results to see how it is mapped

mospeed · April 16, 2010

Okay so I did a random search and used print_r($result) and it gave me this.

Array ( [] => 8 [1] => 11 [2] => 34 [3] => 35 [4] => 36 [5] => 46 [6] => 51 [7] => 58 [8] => 62 )

So does this mean my array is messed up in some way? Seeing as the first key in the array has no number to access it?

andrewgauger · April 16, 2010

$result[$count] = $row['id'];

$count++; // Total Number of rows returned

$count is undefined before this, go ahead and put a $count=0 above the line:

while ($row = mysql_fetch_array($rs))

{

$result[$count] = $row['id'];

$count++; // Total Number of rows returned

}

mospeed · April 16, 2010

That did it, thank you very much.

mospeed · April 16, 2010

Hmm I'm still not sure how to display the results with just the $results array though.

andrewgauger · April 16, 2010

You cant. Modify this line:

while ($row = mysql_fetch_array($rs))

to:

while ($row = mysql_fetch_assoc($rs))

Then instead of assigning results[count]=row['id']

You can do something meaningful with the results.. The problem is I don't know your table structure. If you could put:

print_r($row);

after $result[$count]=$row['id'];

and make the fix I advised at the top. Then the results will look like:

array([id] => 8, => "images/picture01.gif")

that will tell us about what the next step is.

mospeed · April 16, 2010

Alright here is what it looks like with your requested fix.

Array ( [id] =>8 [gif_id] => 24fd7ac [meta_key] => shaq, laugh, happy, funny [meta_desc] => Shaq laughing at Finals press conference. [title] => Shaq [desc] => Shaquille O'Neal laughing at a Finals press conference. [key] => shaq, laugh, happy, funny, shaquille, pointing => http://i49.tinypic.com/24fd7ac.jpg [date] => 2010-03-11 )

This is awesome, I had no idea all this information was contained within the row variable. I use another set of pictures that are linked to every table by the table key and I just bring it up by using the key in the url link like this: <img src="assets/images/stills/<?php echo $id; ?>.gif" width="155" height="155" />

I'd prefer to have a table with this 155 by 155 image to the left and just the title and the description to the right of it for each of the results found in the search.

mospeed · April 16, 2010

If this is the information in $row, shouldn't I be able to access it like this? $row[0]['id']. I've been playing around with it some more but nothing I've learned on multidimensional arrays seems to want to work.

andrewgauger · April 17, 2010

just replace this:

while ($row = mysql_fetch_array($rs))

{

$result[$count] = $row['id'];

$count++; // Total Number of rows returned

}

with this:

$result=array();
while ($row = mysql_fetch_assoc($rs))
      {
         $result[] = $row;  
      }

then you can:

echo "<img src=\"$result[0]['url']\">";

mospeed · April 17, 2010

Excellent. And if I wanted to echo $result[0]['url']; for each of the results in the array how would I go about doing that?

andrewgauger · April 17, 2010

foreach ($result as $key => $arg){
echo "<img src=\"".$arg['url']."\" />";
}

But what is more efficient is to not even use a $result (s) array and to just:

while ($row = mysql_fetch_assoc($rs))

{

echo "<img src=\"".$rs['url']."\" />";

}

mospeed · April 17, 2010

while ($row = mysql_fetch_assoc($rs))
      {
         echo "<img src=\"".$rs['url']."\" />";
      }

I couldn't get this to work but foreach seems to work like a charm.

foreach ($result as $key => $arg){
echo "<img src=\"".$arg['url']."\" />";
}

foreach ($result as $key => $arg){
echo '<table>
    <tr>
      <th rowspan="2"><a href="view.php?id=' .$arg['id']. '"><img src="assets/images/stills/' .$arg['id']. '.gif" width="155" height="155" /></a></th>
      <td><h3>' .$arg['title']. '</h3> <br /> ' .$arg['desc']. '</tr>
    <tr>
      <td> </td>
    </tr>
  </table>';
}

This is how I envisioned displaying the results and it looks alright but not great. I will continue to work with it to try and make it look better but I cannot thank you enough for helping me with this. Thank you so much Mr. Gauger.

mospeed · April 17, 2010

Last question, I promise.

if ($count == 0) {
echo "Sorry, no GIFs were found in your search.";
} else {

foreach ($result as $key => $arg){
echo '<table>
    <tr>
      <th rowspan="2"><a href="view.php?id=' .$arg['id']. '"><img src="assets/images/stills/' .$arg['id']. '.gif" width="155" height="155" /></a></th>
      <td><h3>' .$arg['title']. '</h3> <br /> ' .$arg['desc']. '</tr>
    <tr>
      <td> </td>
    </tr>
  </table>';
}
}

After changing while ($row = mysql_fetch_array($rs)) to while ($row = mysql_fetch_assoc($rs)) and removing

{

         $result[$count] = $row['id'];  

         $count++; // Total Number of rows returned 

      }

The $count variable no longer works but if the search comes back with no results I want to be able to let the user know somehow.

andrewgauger · April 17, 2010

You are right $rs wouldn't work... its supposed to be $row. I guess I was in a hurry when I posted that, sorry for the typo.

Last question, I promise.

Don't promise && don't worry.

take $count out and replace:

if ($count == 0) {

with any ONE of these:

if(!mysql_num_rows($rs)){
if(!count($result)){
if(!$result){

Sign In

How do you get results to show up from a Full Text Search? (mysql)

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information