PHP/mySQL Search Engine Help

[djmac14]

Hey hows it going?  This is my first post here.  I was referred by a friend, he told me you were the go to guys (and gals)!  First off, I have very little experience with PHP, I do have quite a bit of knowledge about HTML and CSS, as well as a tiny bit of mySQL experience.  So when you offer your help make it as clear as possible, dont assume I know anything! haha.

 

Anyway, heres as much detail as I can give you...

 

I'm trying to build a PHP/mySQL search engine.  I want it to search only pages I submit, pages that are located only on my site.  Heres what I have so far thats not working out.

 

I have a "Search.php" which is just the search form.  I have the form "Get" from "Search_Results.php".  The Search.php is very basic, Google-esque.  Now on my "Search_Results.php" page is where all my PHP is.  Since I dont know PHP I basically copy/pasted a bunch of tutorial codes.  But now I dont know how to modify it really.

 

I have a mySQL database setup, with a table titled "news".  That table consists of:

Field: id, Type: int11, no null, auto increment, Primary key

Field: name, Type: varchar(20), null

Field: message, Type: varchar(255), null

Field: keywords, Type: varchar(255), null

Field: address, Type: varchar(255), null

Field: phone, Type: varchar(255), null

Name: I want to just display the name of the site

Message: A description of the site

Keywords: keywords that would link the "search" to the site. (I DONT WANT THIS DISPLAYED IN RESULTS)

Address: Site address

Phone: Phone number

 

 

My PHP code on "Search_results.php" consists of:

//connect to mysql

 

mysql_connect("server","dbuser","dbtest");

mysql_select_db("dbname");

 

$search=$_POST["search"];

 

$result = mysql_query("SELECT * FROM news WHERE message LIKE '%$search%'");

 

 

while($row=mysql_fetch_array($result))

{

 

 

  $title=$row["title"];

  $message=$row["message"];

  $who=$row["who"];

  $date=$row["date"];

  $time=$r["time"];

  $id=$r["id"];

 

  echo "$title <br> $message <br> $who <br> $date | $time <br>";

}

?>

I have inserted a bunch of information into the tables on the mySQL database, but when I search, they all display, regardless of what I searched for, even if I leave the search box blank.  But whats even worse, only the "Message" mySQL field is displaying.

 

So what I'm asking for help on first off is:

-How can I make the search results display only relevant searchs (by looking at the keywords field)

-How can I get the mySQL table to show all the fields, except "Keywords" when searched for.

 

Thanks for your help! I'm trying to use this as sort of an introduction to PHP so please be kind! haha

[djmac14]

Anyone?  :-\

I've looked over the code quite a bit, tried a lot of stuff with the mySQL table.  But now I'm at work, rereading my post, I think my problem might be this line of php:

$result = mysql_query("SELECT * FROM news WHERE <b>message</b> LIKE '%$search%'");

"News" is the name of the table, and "message" is the name of 1 field, the 1 that is showing up.  As I said, I'm at work so I'm unable to test anything, but would I be able to include all the fields by seperating with a comma? Like this:

$result = mysql_query("SELECT * FROM news WHERE <b>message, phone, name, address</b> LIKE '%$search%'");

Or is that gonna mess up more?

 

 

[affordit]

Change this

$result = mysql_query("SELECT * FROM news WHERE message LIKE '%$search%'");

 

To This

$result = mysql_query("SELECT * FROM news WHERE keywords LIKE '%$search%'");

[djmac14]

ah thanks for the help! I guess I should have figured that one out.  I was getting more wrapped up in trying to figure out why only my "Message" field from the mySQL table displays in "Search_results.php", and its not actually searching, its just displaying everything

[djmac14]

Alright I've done some more work on it and I'm starting to get the hang of things.

I've now got my mySQL tables working good, I've got every field displayed correctly.  Now my final question is (well for now haha):

 

Why wont my "Fetch" or search actually work?  I can type anything in, and instead of searching the "Keywords" mySQL table, it just automatically displays everything I've inputted into the tables.  Heres my complete php snippet from my "Search_Results.php" page.

<?php

//connect to mysql

 

mysql_connect("server","dbname","dbpass");

mysql_select_db("dbname");

 

$search=$_POST["search"];

 

$result = mysql_query("SELECT * FROM news WHERE keywords LIKE '%$search%'");

 

 

while($row=mysql_fetch_array($result))

{

 

 

  $name=$row["name"];

  $message=$row["message"];

  $website=$row["website"];

  $keywords=$row["keywords"];

  $address=$row["address"];

  $phone=$row["phone"];

 

 

  echo "$name <br> $message <br> $website <br> $keywords <br> $address <br> $phone <br><br>";

}

?>

 

Any clue why it doesnt actually "search"?  It just displays everything.

[djmac14]

I'm still trying to get this solved.  I worked out almost everything.  I just cant get it to actually search.

When I click "Search" it just displays every mySQL table I have inputed, instead of searching "keywords" and only listing the relevent tables.  By deductive reasoning, I am wondering if its this line of coding.  Its using "$result" for something, but I don't a table or anything for "$result".  What would I input there to make it display or relevent results?

 

while($row=mysql_fetch_array($result))

[revraz]

echo $search

 

If its empty, it will return everything.

[djmac14]

echo $search

 

If its empty, it will return everything.

I'm sorry, I'm such a noob to PHP stuff that I'm not on the same brainwave as you.

 

What I've learned is "$search" would be looking at a mySQL table?  I have tables set up for:

$name - Displays the name of the search result

$message - a description of the search

$website - website of the results

$keywords - the keywords associated to the results

$address - the physical street address of the results

$phone - Phone # to the result location

 

Those all display in the "search_results.php".  But I dont have a input table or anything for "$search".  If you dont mind steering me a little, where would this be?  Would this be included in the "news" table, which includes all the previously mentioned inputs?  Or would it be a seperate table?

 

 

[revraz]

$search=$_POST["search"];

 

$result = mysql_query("SELECT * FROM news WHERE keywords LIKE '%$search%'");

 

 

[djmac14]

$search=$_POST["search"];

 

$result = mysql_query("SELECT * FROM news WHERE keywords LIKE '%$search%'");

 

This is already in my PHP code, or is this a hint as to this is where my problem lies? You mentioned that if $search is empty it will display everything, which it is.  From what I learned "$ANYTHING" draws from mySQL input fields, which are inside of a mySQL table.  For instance, my "$keywords" draws from my input field "KEYWORDS" which is inside my mySQL table titled "NEWS"

 

But I dont know where to build a "$search" field, or and what type of settings the input would need.

[djmac14]

Some more input, I'm trying to find a resolution to this problem still.

 

My mySQL includes a table titled "NEWS", where I draw all my input fields used in the search results from.  Please doublte check my fields and tell me if anythings wrong.

My fields include:

ID: Int11, NOT NULL, Auto Increment, and Primary Key

Name: Varchar(255), NULL

Message: Varchar(255), NULL

Website: Varchar(255), NULL

Keywords: Varchar(255), NULL

Address: Varchar(255), NULL

Phone: Varchar(255), NULL

 

I also moved to a whole new PHP Snippet I got from a "Tutorial".

However, now I come across the error:

Parse error: parse error, unexpected '}' in /xxxxx/xxxx/xxxx/html/xxxx/search_result2.php on line 1

 

Here is the complete snippet.  I've done my best to edit the default snippet to make my information.  However my limited knowledge on the subject is hindering me haha

 

<?php

//connect to mysql

 

mysql_connect("server","dbname","dbpass");

mysql_select_db("dbname");

 

//specify how many results to display per page

$limit = 10;

 

// Get the search variable from URL

  $var = @$_GET['q'] ;

//trim whitespace from the stored variable

  $trimmed = trim($var);

//separate key-phrases into keywords

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

 

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

if ($trimmed == "") {

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

  }

// check for a search parameter

if (!isset($var)){

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

  }

// Build SQL Query for each keyword entered

foreach ($trimmed_array as $trimm){

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

    $query = "SELECT  FROM news WHERE keywords LIKE '%$trimm%' OR name like '%$trimm%' OR message like '%$trimm%' ORDER BY keywords*DESC" ;

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

    $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 ( "Couldn't execute query" );

    $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[ 'id' ];

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

} //end foreach

 

if($row_num_links_main == 0 && $row_set_num == 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++;

  }

// now you can display the results returned. But first we will display the search form on the top of the page

?>

 

<form action="search2.php" method="get" name="search">

  <div align="center">

    <input name="q" type="text" value=" <?php echo $q; ?> " size="15">

      <input name="search" type="submit" value="Search">

  </div>

</form>

 

<?php

// display what the person searched for.

if( isset ($resultmsg)){

  echo $resultmsg;

  exit();

}else{

  echo "Search results for: " . $var;

}

 

foreach($newarr as $value){

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

$query_value = "SELECT * FROM*news WHERE*keywords = '$value'";

$num_value=mysql_query ($query_value);

$row_linkcat= mysql_fetch_array ($num_value);

$row_num_links= mysql_num_rows ($num_value);

 

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

//Replace field

  $titlehigh = preg_replace ( "'($var)'si" , "<b> //1</b>" , $row_linkcat[ 'keywords' ] );

  $linkhigh = preg_replace ( "'($var)'si" , "<b> //1</b>" , $row_linkcat[ 'name' ] );

  $linkdesc = preg_replace ( "'($var)'si" , "<b> //1</b>" , $row_linkcat[ 'message' ] );

foreach($trimmed_array as $trimm){

    if($trimm != 'b' ){

      $titlehigh = preg_replace( "'($trimm)'si" ,* "<b> //1</b>" , $titlehigh);

      $linkhigh = preg_replace( "'($trimm)'si" ,*"<b> //1</b>" , $linkhigh);

      $linkdesc = preg_replace( "'($trimm)'si" ,* "<b> //1</b>" , $linkdesc);

    }

//end highlight

?>

<p>

<?php echo $titlehigh; ?><br>

<?php echo $linkhigh; ?><br>

<?php echo $linkhigh; ?>

</p>

 

<?php

}  //end foreach $trimmed_array

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

          echo "<div align='left'><a href='$PHP_SELF?s=$prevs&q=$var&catid=$catid'>Previous " .$limit. "</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;

            echo "<div align='right'><a href='$PHP_SELF?s=$n&q=$var&catid=$catid'>Next " .$limit. "</a></div>";

      }

    }

}  //end foreach $newarr

?>

[djmac14]

Edited some errors in the code, now I get this error:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/content/d/j/m/djmac14/html/mcpin/search_result2.php on line 37

Couldn't execute query

 

Where line 37 is:

$row_num_links_main =mysql_num_rows ($numresults);

 

[/b]Up to date code:[/b]

<?php

//connect to mysql

 

mysql_connect("10.6.171.154","test401","Naqt1762dj");

mysql_select_db("test401");

 

//specify how many results to display per page

$limit = 10;

 

// Get the search variable from URL

  $var = @$_GET['q'] ;

//trim whitespace from the stored variable

  $trimmed = trim($var);

//separate key-phrases into keywords

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

 

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

if ($trimmed == "") {

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

  }

// check for a search parameter

if (!isset($var)){

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

  }

// Build SQL Query for each keyword entered

foreach ($trimmed_array as $trimm){

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

    $query = "SELECT  FROM news WHERE keywords LIKE '%$trimm%' OR name like '%$trimm%' OR message like '%$trimm%' ORDER BY keywords*DESC" ;

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

    $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 ( "Couldn't execute query" );

    $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[ 'id' ];

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

} //end foreach

 

if($row_num_links_main == 0 && $row_set_num == 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++;

  }

// now you can display the results returned. But first we will display the search form on the top of the page

?>

 

<form action="search2.php" method="get" name="search">

  <div align="center">

    <input name="q" type="text" value=" <?php echo $q; ?> " size="15">

      <input name="search" type="submit" value="Search">

  </div>

</form>

 

<?php

// display what the person searched for.

if( isset ($resultmsg)){

  echo $resultmsg;

  exit();

}else{

  echo "Search results for: " . $var;

}

 

foreach($newarr as $value){

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

$query_value = "SELECT * FROM*news WHERE*keywords = '$value'";

$num_value=mysql_query ($query_value);

$row_linkcat= mysql_fetch_array ($num_value);

$row_num_links= mysql_num_rows ($num_value);

 

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

//Replace field

  $titlehigh = preg_replace ( "'($var)'si" , "<b> //1</b>" , $row_linkcat[ 'keywords' ] );

  $linkhigh = preg_replace ( "'($var)'si" , "<b> //1</b>" , $row_linkcat[ 'name' ] );

  $linkdesc = preg_replace ( "'($var)'si" , "<b> //1</b>" , $row_linkcat[ 'message' ] );

foreach($trimmed_array as $trimm){

    if($trimm != 'b' ){

      $titlehigh = preg_replace( "'($trimm)'si" ,  "<b> //1</b>" , $titlehigh);

      $linkhigh = preg_replace( "'($trimm)'si" , "<b> //1</b>" , $linkhigh);

      $linkdesc = preg_replace( "'($trimm)'si" ,  "<b> //1</b>" , $linkdesc);

    }

//end highlight

?>

<p>

<?php echo $titlehigh; ?><br>

<?php echo $linkhigh; ?><br>

<?php echo $linkhigh; ?>

</p>

 

<?php

}  //end foreach $trimmed_array

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

          echo "<div align='left'><a href='$PHP_SELF?s=$prevs&q=$var&catid=$catid'>Previous " .$limit. "</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;

            echo "<div align='right'><a href='$PHP_SELF?s=$n&q=$var&catid=$catid'>Next " .$limit. "</a></div>";

      }

    }

}  //end foreach $newarr

?>