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

[mospeed]

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]

please post your code

[mospeed]

<?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]

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]

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

[mospeed]

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]

$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]

That did it, thank you very much.

[mospeed]

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

[andrewgauger]

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]

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]

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]

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]

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]

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]

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]

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]

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){