PHP MySql Search

[BNSchnurr]

Hey guys, I'm a newbie to PHP and MySql, and I need some help!

 

For the record, I am a web developer and designer, who always put off learning  , due to fear, but realized I had to do it.

 

Anyways, my problem is this. I am creating a website, where there has to be a seach box where people can lookup names, and or keywords, that are stored into my db.

 

 

I've googled it a billion times, and got everything to almost work. I know how to set-up a database, tables, insert info, and all the real basics of mysql.

 

 

I am running my site on MAMP right now, and here is the code that I am trying to use.

 

 

 

<?php

/* DON'T DISPLAY ANY PHP ERRORS OR WARNINGS

  (optional) comment out in development

-----------------------------------------------*/

error_reporting(0);

 

/*DATABASE INFO

--------------------------*/

$hostname_logon = 'localhost'; //Database server LOCATION

$database_logon = ''; //Database NAME

$username_logon = ''; //Database USERNAME

$password_logon = ''; //Database PASSWORD

 

 

/*MYSQL TABLE TO SEARCH

--------------------------*/

//update these values to table you want to search

$db_table = ''; //TABLE NAME

$row_id = ''; //TABLE ROW ID

$title_field = ''; //FIELD CONTAINING ITEM TITLE

$content_field = ''; //FIELD CONTAINING ITEM CONTENT

$link_field = ''; //FIELD CONTAINING ITEM URL

$limit = 10; //NUMBER OF RESULTS PER PAGE

 

/*SEARCH SCRIPT FILENAME

--------------------------*/

//get the name of this file,so you can name this file whatever you want. This is used in the action parameter of the search form

$currentFile = $_SERVER["SCRIPT_NAME"];

$parts = explode('/', $currentFile);

$currentFile = $parts[count($parts) - 1];

 

/*CONNECT TO THE DATABASE

--------------------------*/

//open database connection

$connections = mysql_connect($hostname_logon, $username_logon, $password_logon) or die ( "Unabale to connect to the database" );

//select database

mysql_select_db($database_logon) or die ( "Unable to select database!" );

 

/*GET SEARCH PARAMETERS

--------------------------*/

//get the search parameter from URL

$var = mysql_real_escape_string(@$_REQUEST['q']);

 

//get pagination

$s = mysql_real_escape_string(@$_REQUEST['s']);

 

//set keyword character limit

if(strlen($var) < 3){

$resultmsg =  "<p>Search Error</p><p>Keywords with less then three characters are omitted...</p>" ; 

}

//trim whitespace from the stored variable

$trimmed = trim($var);

$trimmed1 = trim($var);

//separate key-phrases into keywords

$trimmed_array = explode(" ",$trimmed);

$trimmed_array1 = explode(" ",$trimmed1);

 

// check for an empty string and display a message.

if ($trimmed == "") {

$resulterror =  "<p>Search Error</p><p>Please enter a search...</p>" ;

}

 

// check for a search parameter

if (!isset($var)){

$resulterror =  "<p>Search Error</p><p>We don't seem to have a search parameter! </p>" ;

}

 

//make sure search keyword is at least 2 characters. (buggy if not)

if(strlen($trimmed) < 2){

$resulterror =  "<p>Search Error</p><p>Your keyword has to be at least two characters long! </p>" ;

}

 

/*CREATE SEARCH FORM

--------------------------*/

$search_form = '<form class="search-form" action="'.$currentFile.'" method="GET">

<div>

<input name="q" type="text" value="'.$q.'"><input name="search" type="submit" value="Search">

</div>

</form>';

 

 

/*DISPLAY QUERY ERRORS OR DO THE SEARCH

--------------------------------------------*/

if($resulterror){

$resultmsg = $resulterror;

}else{

 

// Build SQL Query for each keyword entered

foreach ($trimmed_array as $trimm){

 

// MySQL "MATCH" is used for full-text searching. Please visit mysql for details.

$query = "SELECT *, MATCH (".$title_field.",".$content_field.") AGAINST ('".$trimm."') AS score FROM ".$db_table." WHERE MATCH (".$title_field.",".$content_field.") AGAINST ('+".$trimm."' IN BOOLEAN MODE) HAVING score > 0.01 ORDER BY score DESC";

// Execute the query to  get number of rows that contain search kewords

$numresults=mysql_query ($query)  or die ("Error in query: $query. " . mysql_error());

$row_num_links_main =mysql_num_rows ($numresults);

 

//If MATCH query doesn't return any results due to how it works do a search using LIKE

if($row_num_links_main < 1){

$query = "SELECT * FROM ".$db_table." WHERE ".$title_field." LIKE '%".$trimm."%' OR ".$content_field." LIKE '%".$trimm."%' ORDER BY ".$row_id." DESC" ;

$numresults=mysql_query ($query);

$row_num_links_main =mysql_num_rows ($numresults);

}

// next determine if 's' has been passed to script, if not use 0.

// 's' is a variable that gets set as we navigate the search result pages.

if (empty($s)) {

$s=0;

}

 

// now let's get results.

$query .= " LIMIT $s,$limit" ;

$numresults = mysql_query ($query) or die ("Error in query: $query. " . mysql_error());

$row= mysql_fetch_array ($numresults);

 

 

//store record id of every item that contains the keyword in the array we need to do this to avoid display of duplicate search result.

do{

  $adid_array[] = $row[ 'news_id' ];

}while( $row= mysql_fetch_array($numresults));

} //end foreach

 

 

 

/*Display a message if no results found

--------------------------*/

if($row_num_links_main == 0 && $row_num_links_main1 == 0){

$resultmsg = "<p>Search results for: ". $trimmed."</p><p>Sorry, your search returned zero results</p>" ;

}

 

 

//delete duplicate record id's from the array. To do this we will use array_unique function

$tmparr = array_unique($adid_array);

$i=0;

foreach ($tmparr as $v) {

  $newarr[$i] = $v;

  $i++;

}

 

 

//total number of results

$row_num_links_main = $row_num_links_main + $row_num_links_main1;

 

 

if($resultmsg != ""){

$resultmsg = "<p>Search results for: <strong>" . $var."</strong></p>";

}

 

/* FETCH RESULTS BASED ON THE ID

--------------------------------------------*/

foreach($newarr as $value){

 

// EDIT HERE and specify your table and field unique ID for the SQL query

$query_value = "SELECT * FROM ".$db_table." WHERE ".$row_id." = '".$value."'";

$num_value=mysql_query ($query_value);

$row_linkcat= mysql_fetch_array ($num_value);

$row_num_links= mysql_num_rows ($num_value);

 

//create summary of the long text. For example if the field2 is your full text grab only first 130 characters of it for the result

$introcontent = strip_tags($row_linkcat[$content_field]);

$introcontent = substr($introcontent, 0, 130)."...";

 

//now let's make the keywods bold. To do that we will use preg_replace function.

//Replace field

  $title = preg_replace ( "'($var)'si" , "<strong>\\1</strong>" , $row_linkcat[ $title_field] );

  $desc = preg_replace ( "'($var)'si" , "<strong>\\1</strong>" , $introcontent);

 

  //COMMENT OUT THIS LINE: If using database ID field to drive links

  $link = preg_replace ( "'($var)'si" , "<strong>\\1</strong>" ,  $row_linkcat[$link_field]  );

 

  //UNCOMMENT and EDIT THIS LINE: If using database ID field to drive links

  //$link = 'http://yourdomain.com/pageid='. $row_linkcat[$link_field];

 

foreach($trimmed_array as $trimm){

if($trimm != 'b' ){

$title = preg_replace( "'($trimm)'si" ,  "<strong>\\1</strong>" , $title);

$desc = preg_replace( "'($trimm)'si" , "<strong>\\1</strong>" , $desc);

$link = preg_replace( "'($trimm)'si" ,  "<strong>\\1</strong>" , $link);

}//end highlight

}//end foreach $trimmed_array

//format and display search results

$resultmsg.= '<div class="search-result">';

$resultmsg.= '<div class="search-title"><a href="'.$link.'">'.$title.'</a></div>';

$resultmsg.= '<div class="search-text">';

$resultmsg.= $desc;

$resultmsg.= '</div>';

$resultmsg.= '<a href="'.$link.'" class="search-link">';

$resultmsg.= $link;

$resultmsg.= '</a>';

$resultmsg.= '</div>';

 

}  //end foreach $newarr

 

/* CREATE PAGINATION

--------------------------------------------*/

if($row_num_links_main > $limit){

// next we need to do the links to other search result pages

if ($s >=1) { // do not display previous link if 's' is '0'

$prevs=($s-$limit);

$resultmsg.= '<div class="search-previous"><a href="'.$PHP_SELF.'?s='.$prevs.'&q='.$var.'">Previous</a></div>';

}

// check to see if last page

$slimit =$s+$limit;

if (!($slimit >= $row_num_links_main) && $row_num_links_main!=1) {

// not last page so display next link

$n=$s+$limit;

$resultmsg.= '<div  class="search-next"><a href="'.$PHP_SELF.'?s='.$n.'&q='.$var.'">Next</a></div>';

}

}//end if $row_num_links_main > $limit

}//end if no errors

?>

<?php

 

//display form

echo $search_form;

 

//display results

echo $resultmsg;

?>

 

I know that this might be asking a lot, but can someone who is smarter than me please give me instructions on how to set this up.

 

I am at a complete loss. I've tried everything. I can connect to the db, and all, but it returns errors, and or blank screens.

 

I will owe everyone. Thank you.

 

 

 

[voip03]

What type of error, your getting?

[JKG]

for starters, you are in development so comment out

 

error_reporting(0);

[BNSchnurr]

Thanks for the replies.

 

The error I get is :

 

Error in query: SELECT *, MATCH (,) AGAINST ('david') AS score FROM names WHERE MATCH (,) AGAINST ('+david' IN BOOLEAN MODE) HAVING score > 0.01 ORDER BY score DESC. 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 ') AGAINST ('david') AS score FROM names WHERE MATCH (,) AGAINST ('+david' IN BOO' at line 1

 

I inserted the name "david", as a test.

 

I am not sure what "table id, title_field, etc is.

 

I can connect to the db right, but I think something might be off.

 

 

My new db name is doctornames, my table is names, and there are two fields. ID and name