concatenating? Using one search field to search two data fields?

[Newsgroup_User]

Here is our site:
http://www.tradmusic.com/

Top right, is a search form. If you type in "Nathon Jones" you won't see me
listed in the results (although you'll see people who maybe have my name in
their description).
However, if you type in "Nathon" or "Jones" I do appear.

Now, this is because I store artists first name and surname in two different
database fields, so when I'm doing the search, I'm searching for "Nathon
Jones" in the firstname field and "Nathon Jones" in the surname field.
Because my database only has "Nathon" in the firstname field and "Jones" in
the surname field, it therefore doesn't find a match.

How do I take the search form value (a variable called "search") and use it
to search the firstname and surname database fields combined?
Is this what is called "concatenating" or to "concatenate"? (I'm terrible
at Scrabble by the way!)

Thank you.
Nath.

[Newsgroup_User]

"tradmusic.com" <sales@NOSHPAMtradmusic.com> wrote in message
news:ej4buj$pf7$1@forums.macromedia.com...
> How do I take the search form value (a variable called "search") and use
> it to search the firstname and surname database fields combined?
> Is this what is called "concatenating" or to "concatenate"? (I'm
> terrible at Scrabble by the way!)

Yep. SQL Server uses + to concatenate two strings.

[userxtian]

Hi , I had the same problem , solved it using the full text search feature in mysql

firstly I had to create the search index for the relevent fields in my table :

CREATE FULLTEXT INDEX full_index ON mytable(
field_name1,
field_name2
);

got that little bit from http://www.phpfreaks.com/tutorials/129/2.php

Then I deleted all the dreamweaver generated code and did my select statement on the results page like this:

$keyword = $_GET['nameofsearchformtextfield'];
mysql_select_db($database_b_connect, $b_connect);
$query_searchset = "SELECT * FROM table WHERE MATCH(field_name1, field_name2) AGAINST ('$keyword')";
$searchset = mysql_query($query_searchset, $b_connect) or die(mysql_error());
$row_searchset = mysql_fetch_assoc($searchset);
$totalRows_searchset = mysql_num_rows($searchset);
?>
Hope this helps