Jump to content

Searching multiple words

Katie

By Katie
in PHP Coding Help

Recommended Posts

Katie Newbie

Katie

Posted
Posted

Hello people, sorry to bother you all.

 

I'm kinda new to php and I've been editing this search engine. I've already done a few things but I really want to add one more additional feature - Multiple word search. At present it only searches for one word. I've been editing the code and trying to insert the following segment inside the script but keep getting syntax errors:

 

$sql = "SELECT whatever, more, stuff FROM table WHERE ";

 

$keywordArray = explode(" ", $_POST['keyword']);

 

foreach ($keywordArray As $val) {

    $sql .= "(keyword LIKE '".$val."') ";

}

 

 

 

----the SQL is either doing an AND or OR search by replacing the )( that are rendered into ) AND ( or ) OR ( ------

 

$sql = str_replace(") (", ") AND (", $sql);

 

$sql .= "LIMIT 25;";

 

 

 

 

-----------THIS IS ORGINAL SCRIP SEARCH SCRIPT BELOW--------------

 

<?

print "<html><head><title>My Search Engine</title></head><body>\n";

 

 

 

if( $_POST['keyword'] )

 

{

 

  include("dbconnect.php");

 

 

 

  /* Get timestamp before executing the query: */

 

  $start_time = getmicrotime();

 

 

 

  /* Set $keyword and $results, and use addslashes() to

 

    *  minimize the risk of executing unwanted SQL commands: */

 

  $keyword = addslashes( $_POST['keyword'] );

 

  $results = addslashes( $_POST['results'] );

 

 

/* Execute the query that performs the actual search in the DB: */

 

$result = mysql_query(" SELECT p.page_url AS url,

 

                          COUNT(*) AS occurrences

 

                          FROM page p, word w, occurrence o

 

                          WHERE p.page_id = o.page_id AND

 

                          w.word_id = o.word_id AND

 

                          w.word_word = \"$keyword\"

 

                          GROUP BY p.page_id

 

                          ORDER BY occurrences DESC

 

                          LIMIT $results" );

 

 

 

/* Get timestamp when the query is finished: */

 

  $end_time = getmicrotime();

 

 

 

  $number = mysql_num_rows($result);

 

if ($number != "0") {

/* Present the search-results: */

 

print "<h2>Search results for '".$_POST['keyword']."':</h2>\n";

 

for( $i = 1; $row = mysql_fetch_array($result); $i++ )

 

{

 

print "$i. <a href='".$row['url']."'>".$row['url']."</a>\n";

 

print "(Index count: ".$row['occurrences'].")<br><br>\n";

 

}

 

} else {

print "<p>No matching results found/p>";

}

 

  /* Present how long it took the execute the query: */

 

  print "query executed in ".(substr($end_time-$start_time,0,5))." seconds.";

 

}

 

else

 

{

 

  /* If no keyword is defined, present the search page instead: */

 

}

 

print "</body></html>\n";

 

 

/* Simple function for retrieving the current timestamp in microseconds: */

 

function getmicrotime()

 

{

 

  list($usec, $sec) = explode(" ",microtime());

 

  return ((float)$usec + (float)$sec);

 

}

 

?>

 

-------------------END OF SEARCH SCRIPT-----------------------------

 

 

 

Could somebody please please help, I've editing this script I'm going crazy!!!

 

Thanks people

 

Link to comment
https://forums.phpfreaks.com/topic/42533-searching-multiple-words/
Share on other sites
obsidian Member

obsidian

Posted
Posted

Here's my quickie go at it (keep in mind I'm trying to keep words within double quotes grouped for matching):

<?php
$sql = "SELECT * FROM myTable WHERE ";

// clean up your search first
$search = trim($_POST['search']);
$search = strip_tags($search);
$search = mysql_real_escape_string($search);

preg_match_all('|(")([^"]+)\\1|', $search, $groups, PREG_PATTERN_ORDER);
preg_match_all('|\b([a-z\d_-]+)\b|i', $search, $words, PREG_PATTERN_ORDER);

$like = array();
if (count($groups) > 0) {
  foreach ($groups[2] as $x) {
    $like[] = "searchCol LIKE \"%$x%\"";
  }
}

if (count($words) > 0) {
  foreach ($words[1] as $x) {
    $like[] = "searchCol LIKE \"%$x%\"";
  }
}

$sql .= '(' . implode(') OR (', $like) . ')';

// Now $sql holds your query string
?>

 

Hope this helps. May need a little tweaking, but I think the idea is there for you.

obsidian Member

obsidian

Posted
Posted

Sorry, I should have explained myself a bit better... that's what I get for posting in a rush ;) My code is simply taking a POST variable named "search" and breaking it up to generate the SQL to run your search query. I'm not taking into account any of the error checking, and I'm not even running the query. What you use it for is entirely up to you. I was just trying to show you one option of how to set up a dynamic query for a multi word search. You'll still want to run your checks to see if the search was empty, etc, but I'll leave that up to your discretion.

obsidian Member

obsidian

Posted
Posted

...my problem is I'm not sure exactly where to place the segement of code inside the script. Oh well I'll guess I keep trying

 

I'm not positive without being a little more familiar with your script, but I believe you could replace this segment of code:

<?php
/* Execute the query that performs the actual search in the DB: */
$result = mysql_query(" SELECT p.page_url AS url,
                         COUNT(*) AS occurrences
                         FROM page p, word w, occurrence o
                         WHERE p.page_id = o.page_id AND
                         w.word_id = o.word_id AND
                         w.word_word = \"$keyword\"
                         GROUP BY p.page_id
                         ORDER BY occurrences DESC
                         LIMIT $results" );
?>

 

with this:

<?php
/* Execute the query that performs the actual search in the DB: */
$sql = "SELECT p.page_url AS url, COUNT(p.page_url) AS occurrences ".
       "FROM page p, word w, occurrence o ".
       "WHERE p.page_id = o.page_id AND w.word_id = o.word_id ".
       "AND ";

// clean up your search first
$search = trim($_POST['search']);
$search = strip_tags($search);
$search = mysql_real_escape_string($search);

preg_match_all('|(")([^"]+)\\1|', $search, $groups, PREG_PATTERN_ORDER);
preg_match_all('|\b([a-z\d_-]+)\b|i', $search, $words, PREG_PATTERN_ORDER);

$like = array();
if (count($groups) > 0) {
  foreach ($groups[2] as $x) {
    $like[] = "w.word_word = \"$x\"";
  }
}

if (count($words) > 0) {
  foreach ($words[1] as $x) {
    $like[] = "w.word_word = \"$x\"";
  }
}

$sql .= "((" . implode(") OR (", $like) . ")) ".
        "GROUP BY p.page_url ORDER BY occurrences DESC LIMIT $results";

$result = mysql_query($sql);
?>

 

One thing to note, though: since you're querying against a keyword column, you most likely won't get any results from the quoted string matches. Also, you may want to loop through your keyword array and strip out any duplicates before you actually run your query.

 

Hope this helps.

Katie Newbie

Katie

Posted
  • Author
Posted

Hiya, this is the code I pasted

 

http://www.student.city.ac.uk/~jf958/final/searcht.php

 

keep getting n error on line 70

 

<?

print "<html><head><title>My Search Engine</title></head><body>\n";

 

 

 

if( $_POST['keyword'] )

 

{

 

  include("dbconnect.php");

 

 

 

  /* Get timestamp before executing the query: */

 

  $start_time = getmicrotime();

 

 

 

  /* Set $keyword and $results, and use addslashes() to

 

    *  minimize the risk of executing unwanted SQL commands: */

 

  $keyword = addslashes( $_POST['keyword'] );

 

  $results = addslashes( $_POST['results'] );

 

 

/* Execute the query that performs the actual search in the DB: */

 

$sql = "SELECT p.page_url AS url, COUNT(p.page_url) AS occurrences ".

      "FROM page p, word w, occurrence o ".

      "WHERE p.page_id = o.page_id AND w.word_id = o.word_id ".

      "AND ";

 

// clean up your search first

$search = trim($_POST['search']);

$search = strip_tags($search);

$search = mysql_real_escape_string($search);

 

preg_match_all('|(")([^"]+)\\1|', $search, $groups, PREG_PATTERN_ORDER);

preg_match_all('|\b([a-z\d_-]+)\b|i', $search, $words, PREG_PATTERN_ORDER);

 

$like = array();

if (count($groups) > 0) {

  foreach ($groups[2] as $x) {

    $like[] = "w.word_word = \"$x\"";

  }

}

 

if (count($words) > 0) {

  foreach ($words[1] as $x) {

    $like[] = "w.word_word = \"$x\"";

  }

}

 

$sql .= "((" . implode(") OR (", $like) . ")) ".

        "GROUP BY p.page_url ORDER BY occurrences DESC LIMIT $results";

 

$result = mysql_query($sql);

 

 

 

/* Get timestamp when the query is finished: */

 

  $end_time = getmicrotime();

 

 

 

  $number = mysql_num_rows($result);

 

if ($number != "0") {

/* Present the search-results: */

 

print "<h2>Search results for '".$_POST['keyword']."':</h2>\n";

 

for( $i = 1; $row = mysql_fetch_array($result); $i++ )

 

{

 

print "$i. <a href='".$row['url']."'>".$row['url']."</a>\n";

 

print "(Index count: ".$row['occurrences'].")<br><br>\n";

 

}

 

} else {

print "<p>No matching results found/p>";

}

 

  /* Present how long it took the execute the query: */

 

  print "query executed in ".(substr($end_time-$start_time,0,5))." seconds.";

 

}

 

else

 

{

 

  /* If no keyword is defined, present the search page instead: */

 

}

 

print "</body></html>\n";

 

 

/* Simple function for retrieving the current timestamp in microseconds: */

 

function getmicrotime()

 

{

 

  list($usec, $sec) = explode(" ",microtime());

 

  return ((float)$usec + (float)$sec);

 

}

Archived

This topic is now archived and is closed to further replies.

Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.