Jump to content

remove commas from search string and pass results to a query

XandarX

By XandarX
in PHP Coding Help

 Share

Recommended Posts

XandarX Newbie

XandarX

Posted
Posted

I have some data in a table and some of it is Artist names stored as "Last, First"

I need to be able to have the script search weather or not someone types "last, first" or "first last".

Any ideas?

 

Here's my code:

 

<html>
<head>
<title>search script</title>
</head>
<body>

<form name="form" action="search.php" method="get">
  <input type="text" name="q" />
  <input type="submit" name="Submit" value="Search" />
</form>

<?php

  // Get the search variable from URL

  $var = @$_GET['q'] ;
  $trimmed = trim($var); //trim whitespace from the stored variable

// rows to return
$limit=100; 

// check for an empty string and display a message.
if ($trimmed == "")
  {
  echo "<p>Please enter a search...</p>";
  exit;
  }

// check for a search parameter
if (!isset($var))
  {
  echo "<p>We dont seem to have a search parameter!</p>";
  exit;
  }

//connect to your database ** EDIT REQUIRED HERE **
mysql_connect("localhost","username","password"); //(host, username, password)

//specify database
mysql_select_db("mydb") or die("Unable to select database"); //select which database we're using

// Build SQL Query  
$query = "select * from songs where Title like \"%$trimmed%\" or Artist like \"%$trimmed%\"  
  order by Title"; // EDIT HERE and specify your table and field names for the SQL query

$numresults=mysql_query($query);
$numrows=mysql_num_rows($numresults);

// If we have no results, offer a google search as an alternative

if ($numrows == 0)
  {
  echo "<h4>Results</h4>";
  echo "<p>Sorry, your search: "" . $trimmed . "" returned zero results</p>";
  }

// next determine if s has been passed to script, if not use 0
  if (empty($s)) {
  $s=0;
  }

// get results
  $query .= " limit $s,$limit";
  $result = mysql_query($query) or die("Couldn't execute query");

// display what the person searched for
echo "<p>You searched for: "" . $var . ""</p>";

// begin to show results set
echo "Results";
$count = 1 + $s ;

// now you can display the results returned
echo "<table border=1>";
  while ($row= mysql_fetch_array($result)) {
  $title = $row["Title"];
  $artist = $row["Artist"];
  $number = $row["Number"];

  echo "<tr><td>$count.)</td><td>$title</td><td>$artist</td><td>$number</td></tr>" ;
  $count++ ;
  }

  echo "</table>";
$currPage = (($s/$limit) + 1);

//break before paging
  echo "<br />";

  // next we need to do the links to other results
  if ($s>=1) { // bypass PREV link if s is 0
  $prevs=($s-$limit);
  print " <a href=\"$PHP_SELF?s=$prevs&q=$var\"><< 
  Prev 10</a>&nbsp ";
  }

// calculate number of pages needing links
  $pages=intval($numrows/$limit);

// $pages now contains int of pages needed unless there is a remainder from division

  if ($numrows%$limit) {
  // has remainder so add one page
  $pages++;
  }

// check to see if last page
  if (!((($s+$limit)/$limit)==$pages) && $pages!=1) {

  // not last page so give NEXT link
  $news=$s+$limit;

  echo " <a href=\"$PHP_SELF?s=$news&q=$var\">Next 20 >></a>";
  }

$a = $s + ($limit) ;
  if ($a > $numrows) { $a = $numrows ; }
  $b = $s + 1 ;
  echo "<p>Showing results $b to $a of $numrows</p>";
  
?>

</body>
</html>

Link to comment
Share on other sites
bibby Newbie

bibby

Posted
Posted

Long answer, sorry..

coupla things.

first I'll answer the question, then offer what might be a better solution (though it takes more work).

 

To detect the presence of a comma, you can use strpos or preg_match (and other fxns).

strpos returns the position of the first occurrence of a string within a string, which could be 0. This is not to be mistaken for FALSE (return for no match), but in the "last, first" scenario it's probably not applicable.

 

$hasComma = FALSE != strpos($trimmed, ',');
# or
$hasComma = preg_match('/,/',$trimmed,$m);

 

or, you could just explode on the comma and count the result.

$parts = explode(',', $trimmed);
$hasComma = count($parts)>1;

 

if there was a comma, and you're ok with assuming the user put in "last, first"  (also assuming/checking that count($parts)==2 ),  you can flip like so.

 

list( $first, $last ) = $parts;
if( $hasComma )
   list($last, $first) = array($first, $last);

 

doing any exploding at all, you might want to trim all the parts so that you might reconstruct them in a controlled manner.

 

$parts = array_map("trim", $parts);
// I also like to filter empty parts
$parts = array_values( array_filter( $parts ));

 

Then your search term can be reconstructed

 $term = join(' ',array($first, $last));

 

----------------

 

Now, I've gotta say that I don't think you've got an ideal search. What you might want to is break the search term apart (by space, comma, etc) and query an index of individual words. This is how you can rank of "relevance" and offer more results than you would have if the user misspelled their search term (which 5%-15% of users will always do).

 

By "index" I mean a new table containing "terms" and their associative artist_ids.

Let's take an example data set of three artists:

 

1- Johnny Cash

2- Sweep the Leg Johnny

3- Clean Sweep

 

Ignoring "the", our index of unique words is:

Johnny

Cash

Sweep

Leg

Clean

 

The linking table could look like:

_term_|_artist_id_

Johnny | 1

Cash | 1

Sweep | 2

Leg | 2

Johnny | 2

Clean | 3

Sweep | 3

 

Gather up possible matches for each word, paying special attention to when the same records appear. That should increase their relevance. Misses should decrease the "score". Then present your results in order of score. It's often better to show more results than fewer.

 

Before, a search for 'Sweep Johnny' would net 0 results, but we've all come to expect the search to be more lenient with our brevity. If we searched an index for each word, I'd get all three results, but I'd get exactly what I was looking for on the top.

 

#1  Sweep the Leg Johnny  (100% 2/2)

#2  Johnny Cash  (50% 1/2)

#3  Clean Sweep (50% 1/2)

 

Link to comment
Share on other sites
XandarX Newbie

XandarX

Posted
  • Author
Posted

I worded my question wrong.  For that I apologize.  What I meant was this:

 

User types in "Faith Hill"

I need the search script to return all records with "Faith Hill" AND "Hill, Faith"

 

Though looking at the examples you gave I'm starting to get a better understanding of what might be going wrong.  The problem is that I really don't know how to implement it.

 

I changed my text fields in the database to add fulltext to them.  All my internet searches on the subject state that I need to do something in a fulltext method, even though they don't quite tell you how.

Link to comment
Share on other sites
QuickOldCar Regular Member

QuickOldCar

Posted
Posted

http://dev.mysql.com/doc/refman/5.5/en/fulltext-boolean.html

 

It takes some work, and why most sites just have a simple search.

 

But you should prob be looking into exact phrase, or can just add a + or - before each search word for include and exclude.

 

So for the search word would be +faith +hill

 

Comma's are usually not used in mysql for in the searches, maybe is a way to add it, but I wouldn't.

 

I do have this clump of code I made, maybe will help, maybe not, but this is for a multiple search select. of course you don't have same values, also I have the start and stop rows to limit mysql for pagination. Just be sure to have the full text index made for each field or would be very slow.

 

<?php
$display = mysql_real_escape_string($_GET['display']);
$order = mysql_real_escape_string($_GET['order']);
$search_name = mysql_real_escape_string($_GET['search_name']);
$search_words = mysql_real_escape_string($_GET['search_words']);

//search get variables from search form
if ($search_name == "first_begins_characters") {
    $result = mysql_query("SELECT * FROM users WHERE firstname LIKE '".$search_words."%' ORDER BY $display $order LIMIT $startrow,$posts_per_page" );
$total_count = mysql_query("SELECT * FROM users WHERE firstname LIKE '".$search_words."%'");
} elseif ($search_name == "first_contains_characters") {
    $result = mysql_query("SELECT * FROM users WHERE firstname LIKE '%"."$search_words"."%' ORDER BY $display $order LIMIT $startrow,$posts_per_page" );
$total_count = mysql_query("SELECT * FROM users WHERE firstname LIKE '%"."$search_words"."%'");
} elseif ($search_name == "last_begins_characters") {
    $result = mysql_query("SELECT * FROM users WHERE lastname LIKE '".$search_words."%' ORDER BY $display $order LIMIT $startrow,$posts_per_page" );
$total_count = mysql_query("SELECT * FROM users WHERE lastname LIKE '".$search_words."%'");
} elseif ($search_name == "last_contains_characters") {
    $result = mysql_query("SELECT * FROM users WHERE lastname LIKE '%"."$search_words"."%' ORDER BY $display $order LIMIT $startrow,$posts_per_page" );
$total_count = mysql_query("SELECT * FROM users WHERE lastname LIKE '%"."$search_words"."%'");
} elseif ($search_name == "all") {
$result = mysql_query("SELECT * FROM users ORDER BY $display $order LIMIT $startrow,$posts_per_page" );
$total_count = mysql_query("SELECT * FROM users");
} else {

//if anything goes wrong above or nothing selected, this will be used as the default query instead
$result = mysql_query("SELECT * FROM users ORDER BY $display $order LIMIT $startrow,$posts_per_page" );
$total_count = mysql_query("SELECT * FROM users");
}

?>

<form name="input" action="" method="get">
<?php
if (!$_GET['display']) {
$display = "firstname";
}
?>
Display:<Select style="color: #FFFFFF; font-family: Verdana; font-weight: bold; font-size: 12px; background-color: #000000;" name="display">
<option "Input" style="color: #FFFFFF; font-family: Verdana; font-weight: bold; font-size: 12px; background-color: #000000;" size="15" value="<?php echo $display; ?>"><?php echo $display; ?></option>
<option style="color: #FFFFFF; font-family: Verdana; font-weight: bold; font-size: 12px; background-color: #000000;" size="15" value="firstname">firstname</option>
<option style="color: #FFFFFF; font-family: Verdana; font-weight: bold; font-size: 12px; background-color: #000000;" size="15" value="lastname">lastname</option>
</select>
<?php
if (!$_GET['order']) {
$order = "ASC";
}
?>
Order:<Select style="color: #FFFFFF; font-family: Verdana; font-weight: bold; font-size: 12px; background-color: #000000;" name="order">
<option style="color: #FFFFFF; font-family: Verdana; font-weight: bold; font-size: 12px; background-color: #000000;" size="15" value="<?php echo $order; ?>"><?php echo $order; ?></option>
<option style="color: #FFFFFF; font-family: Verdana; font-weight: bold; font-size: 12px; background-color: #000000;" size="15" value="ASC">Ascending</option>
<option style="color: #FFFFFF; font-family: Verdana; font-weight: bold; font-size: 12px; background-color: #000000;" size="15" value="DESC">Descending</option>
</select>

<?php
if (!$_GET['search_name']) {
$search = "all";

}
?>
Search Type:<Select style="color: #FFFFFF; font-family: Verdana; font-weight: bold; font-size: 12px; background-color: #000000;" name="search">
<option style="color: #FFFFFF; font-family: Verdana; font-weight: bold; font-size: 12px; background-color: #000000;" size="15" value="<?php echo $search; ?>"><?php echo $search; ?></option>
<option style="color: #FFFFFF; font-family: Verdana; font-weight: bold; font-size: 12px; background-color: #000000;" size="15" value="all">all</option>
<option style="color: #FFFFFF; font-family: Verdana; font-weight: bold; font-size: 12px; background-color: #000000;" size="15" value="first_begins_characters">User Begins Character(s)</option>
<option style="color: #FFFFFF; font-family: Verdana; font-weight: bold; font-size: 12px; background-color: #000000;" size="15" value="first_contains_characters">User Contains Character(s)</option>
<option style="color: #FFFFFF; font-family: Verdana; font-weight: bold; font-size: 12px; background-color: #000000;" size="15" value="last_begins_characters">Last Begins Character(s)</option>
<option style="color: #FFFFFF; font-family: Verdana; font-weight: bold; font-size: 12px; background-color: #000000;" size="15" value="last_contains_characters">Last Contains Character(s)</option>
</select>
<br />
Search Word(s) or Char(s):<input size="40"type="text" name="search_words" style="color: #FFFFFF; font-family: Verdana; font-weight: bold; font-size: 12px; background-color: #000000;" size="15" value="<?php echo $_GET['search_words']; ?>">
<input type="submit" style="color: #FFFFFF; font-family: Verdana; font-weight: bold; font-size: 12px; background-color: #000000;" size="15" value="Search Name" />

</form>

Link to comment
Share on other sites
XandarX Newbie

XandarX

Posted
  • Author
Posted

Wasn't able to do anything with that bit, though it's a good piece of code for what it does.  Might use something like that in another application I'm making!

 

Where I'm having trouble is that MySQL returns results only for the string of words that are found IN ORDER. But if the database has the item in reversed order, it does not return it.

 

Example:  Search string "David Bowie" only returns results with "David Bowie", NOT "Bowie David"

 

I need it to return the results no matter the order they appear in within the database.

I'm using mySQL 5.0.91 and php 5.2.9 if that makes a difference.

 

I tried using BOOLEAN and NATURAL LANGUAGE modes in my query, but the results were the same.  Still the same order.

 

What I think I need is to split the search string up into parts, pass each one to the query individually to build some sort of a recordset, then return only the results that have all of the keywords entered.  I've done this exact same type of search in asp, but php doesn't seem to have the same functionality when it comes to data recordsets.

 

I'm learning, but I need some help.  I researched fulltext and figured out that was somewhere in the neighborhood, but when I implemented it the results never changed.  Keyword order seems to matter and I can't figure out how to get around that.

Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
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.