Hello,

I've been trying like crazy to figure out how to get my single word search to instead search for multiple words, but am no closer for my efforts.

As you can see here on line 18:
(at the top of my single word search page)
www.shopstrathroy.com/standardsearch.txt
I have the search set up to look through three fields(keywords, business_name, description) in one table(ss_dir_main).

This works fine for one word(or two words if they are in the exact order entered, in the same field, with the exact same spacing and punctuation). It will find the searched word in any one of those three fields. But now I need it to look for multiple words. So if someone types in the search box "Ford Mustang" I need the search to find those words even if in the "keywords" field they are listed as "mustang cars Ford". The search also needs to find those words if "Ford" is in the "business_name" field and "Mustang" is in the "description" or "keywords" field.

So basically, I need the search to find ALL words submitted, ALL in a single record, in any order, but not necessarily together or in the same field.

The code I've been working with is at:
www.shopstrathroy.com/multisearch.txt
But I am unclear as how to incorporate this into my current single word search page, or how to make it search the three fields necessary.

I'm using UD4, PHP4, and MySQL 3.22. I'm not sure what other info could help. But if anyone can offer pointers on integrating the multi-word-search code into my current page, the help would be appreciated. If there's anything else I can say to help clarify, please just yell!

Thank you all for your time.

Peter

The functional single word search page can be viewed if need be at:
www.shopstrathroy.com/directory.php
Enter the word "whatever" and click "go" to view results.

If your multisearch code works for you as is then you can easily incorporate it. Unfortunately the recordset won't remain editable within UltraDev, however, when coding at this level, I don't think that will be a great loss, as you can easily make any modifications directly.

Simply replace:-
<?php
$rskeywords=$connssdirectory->Execute("SELECT dir_id, business_name, address, city, province, postal_code, phone_number, phone_number_2, toll_free_phone, fax_number, email_address, website_local, website_world, operating_hours, ss_member, ss_details, description, keywords FROM ss_dir_main WHERE keywords LIKE '%" . ($rskeywords__txtSearchfield) . "%' OR business_name Like '%" . ($rskeywords__txtSearchfield) . "%' OR description like '%" . ($rskeywords__txtSearchfield) . "%' OR business_name like '" . ($rskeywords__mnuSearchLetter) . "%' ORDER BY business_name ASC" or DIE($connssdirectory->ErrorMsg());
$rskeywords_numRows=0;
$rskeywords__totalRows=$rskeywords->RecordCount();
?>

in your standardsearch file, for:-

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

//check to see if variable $term (keywords) has been defined and is not empty
if(isset($term) && $term != ""
{
$terms = split(" ",addslashes($term));
// Get all the words into an array
$addquery .= "(my_table.my_column LIKE '%$terms[0]%'";


$size = sizeof($terms);
for($i=1;$i<$size;$i++) {
$addquery.=" AND my_table.my_column LIKE '%$terms[$i]%'";
}
$addquery.="";

}

$sql .= $addquery;
$rskeywords=$connssdirectory->Execute($sql) or DIE($connssdirectory->ErrorMsg());
$rskeywords_numRows=0;
$rskeywords__totalRows=$rskeywords->RecordCount();
?>

Tim Green

Extension & PHP TalkZone Manager
<font size=1>-------------------------------------------
<i>Please read the Forum FAQ before posting
a question to this TalkZone.</i>
-------------------------------------------
www.UDzone.com : A dynamic Dreamweaver,
Ultradev and Fireworks site for developers
by developers.
-------------------------------------------</font id=size1>

I guess instead of saying "This is the code I'm working with", I should have said "This is the code I'm trying to get working"! <img src=../images/dmxzone/forum/icon_smile.gif border=0 align=middle>

As the code is written, below, it does not work. I get the error:
You have an error in your SQL syntax near '' at line 1

I'm not surprised it's not working because right now I don't see where the variables txtSearchfield/$Searchfield or mnuSearchLetter/$SearchLetter are being pulled into the SQL. Should I be replacing 1 or more instances of "$term" to $Searchfield ?

Also, right now it appears "ss_dir_main.keywords LIKE '%$terms[0]%'" that it will only search the field "keywords" in the directory "ss_dir_main". But it is essential that it searches three fields; keywords, description, and business_name in the directory ss_dir_main. At this "scripting level" as you put it, I'm not sure how to accomplish this.

I'm sorry if I appear lost, I pretty much am. But this is the last major step in the user end of my application, unfortunately it's proving to be the most difficult.

So a BIG thanks in advance for any additional help you can provide.

Peter

The code below can also be viewed at www.shopstrathroy.com/multisearch.txt

&lt;?php
// Copyright (c) Interakt Online 2001
// www.interakt.ro/
require("./adodb/adodb.inc.php";
require("./Connections/connssdirectory.php";
?&gt;&lt;?php
$rskeywords__txtSearchfield = "KeywordSearch";
if (isset($Searchfield))
{$rskeywords__txtSearchfield = $Searchfield;}
?&gt;&lt;?php
$rskeywords__mnuSearchLetter = "LetterSearch";
if (isset($SearchLetter))
{$rskeywords__mnuSearchLetter = $SearchLetter;}
?&gt;&lt;?php
$sql = "SELECT * FROM ss_dir_main WHERE ";
//check to see if variable $term (keywords) has been defined and is not empty
if(isset($term) && $term != ""
{
$terms = split(" ",addslashes($term));// Get all the words into an array
$addquery .= "(ss_dir_main.keywords LIKE '%$terms[0]%'";
$size = sizeof($terms);
for($i=1;$i&lt;$size;$i++) {
$addquery.=" AND ss_dir_main.keywords LIKE '%$terms[$i]%'";
}
$addquery.="";
}
$sql .= $addquery;
$rskeywords=$connssdirectory-&gt;Execute($sql) or DIE($connssdirectory-&gt;ErrorMsg());
$rskeywords_numRows=0;
$rskeywords__totalRows=$rskeywords-&gt;RecordCount();
?&gt;

OK, hopefully this will help. i've reworked your SQL generating code so that it does work:-

$sql = "SELECT * FROM ss_dir_main WHERE ";
// check for $term (keywords)
if (isset($term) && $term!="" {
$terms = split(" ",addslashes($term));
for ($k=0; $k &lt; count($terms); $k++) {
if ($k!=0) {
$sql.=" OR ";
}
$sql .= "keywords LIKE '%".$terms[$k]."%' OR business_name LIKE '%".$terms[$k]."%' OR description LIKE '%".$terms[$k]."%'";
}
echo $sql;
}

This will ensure that each word is compared against each field in the database.

Hope it helps

Tim Green

Extension & PHP TalkZone Manager
<font size=1>-------------------------------------------
<i>Please read the Forum FAQ before posting
a question to this TalkZone.</i>
-------------------------------------------
www.UDzone.com : A dynamic Dreamweaver,
Ultradev and Fireworks site for developers
by developers.
-------------------------------------------</font id=size1>

It's definitely getting closer thanks to your help Tim. With the modified code below, I'm no longer getting the "SQL Syntax" error. BUT(as always) it's been replaced with this error:
Fatal error: Call to a member function on a non-object in /usr/local/www/vhosts/shopstrathroy.com/htdocs/searchtest2.php on line 28

Line 28 in the below code is:
$rskeywords__totalRows=$rskeywords-&gt;RecordCount();

If I remove line 28, the same error would then appear for line 37(not included in the code below):
$rskeywords_total = $rskeywords-&gt;RecordCount();

Do these lines require something that's been changed in the SQL?


Peter


A longer version of the code can be seen at:
www.shopstrathroy.com/multisearch.txt

&lt;?php


// Copyright (c) Interakt Online 2001
// www.interakt.ro/

require("./adodb/adodb.inc.php";
require("./Connections/connssdirectory.php";
?&gt;&lt;?php
$rskeywords__txtSearchfield = "%";
if (isset($Searchfield))
{$rskeywords__txtSearchfield = $Searchfield;}
?&gt;&lt;?php
$sql = "SELECT * FROM ss_dir_main WHERE ";
// check for $term (keywords)
if (isset($term) && $term!="" {
$terms = split(" ",addslashes($term));
for ($k=0; $k &lt; count($terms); $k++) {
if ($k!=0) {
$sql.=" OR ";
}
$sql .= "keywords LIKE '%".$terms[$k]."%' OR business_name LIKE '%".$terms[$k]."%' OR description LIKE '%".$terms[$k]."%'";
}
echo $sql;
}
$sql .= $addquery;
$rskeywords_numRows=0;
$rskeywords__totalRows=$rskeywords-&gt;RecordCount();
?&gt;

Oops, my fault.

The reason you're getting the member function on a non-object error is that the recordset isn't actually being created. I forgot to remove the echo which basically outputs the sql to the page, and replace with the recordset generation code... sorry about that.

Try:-

&lt;?php


// Copyright (c) Interakt Online 2001
// www.interakt.ro/

require("./adodb/adodb.inc.php";
require("./Connections/connssdirectory.php";
?&gt;&lt;?php
$rskeywords__txtSearchfield = "%";
if (isset($Searchfield))
{$rskeywords__txtSearchfield = $Searchfield;}
?&gt;&lt;?php
$sql = "SELECT * FROM ss_dir_main WHERE ";
// check for $term (keywords)
if (isset($term) && $term!="" {
$terms = split(" ",addslashes($term));
for ($k=0; $k &lt; count($terms); $k++) {
if ($k!=0) {
$sql.=" OR ";
}
$sql .= "keywords LIKE '%".$terms[$k]."%' OR business_name LIKE '%".$terms[$k]."%' OR description LIKE '%".$terms[$k]."%'";
}

}
$rskeywords=$connssdirectory-&gt;Execute($sql) or DIE($connssdirectory-&gt;ErrorMsg());
$rskeywords_numRows=0;
$rskeywords__totalRows=$rskeywords-&gt;RecordCount();
?&gt;

Tim Green

Extension & PHP TalkZone Manager
<font size=1>-------------------------------------------
<i>Please read the Forum FAQ before posting
a question to this TalkZone.</i>
-------------------------------------------
www.UDzone.com : A dynamic Dreamweaver,
Ultradev and Fireworks site for developers
by developers.
-------------------------------------------</font id=size1>

Right you were, that error is now gone. But again it's been replaced with with:

You have an error in your SQL syntax near '' at line 1

The updated code can be seen again at:
www.shopstrathroy.com/multisearch.txt

Am I making you feel like you REALLY deserve a holiday now? <img src=../images/dmxzone/forum/icon_smile.gif border=0 align=middle>

Thanks for all your help Tim, and a very Merry Christmas to you!

Peter

Hope you enjoyed the holidays!

I spent some time over the past week trying to track down what was causing the error but was unsuccessful. I then posted to a different forum to give you a break <img src=../images/dmxzone/forum/icon_smile_wink.gif border=0 align=middle> but I don't think anyone there could find the problem.

One person responded with:
If $term is empty your query will say
SELECT * FROM ss_dir_main WHERE

I'm assuming he means that if someone submits an empty search box that this will create an error. But I'm submitting words in the search box from the search page so this shouldn't be the cause.

Hopefully you had a relaxing couple of days and can shine some new genius light on what could be causing the SQL syntax error.

Thanks again,

Peter

Tim i'am trying to use this code as well and get the error can you shed anymore light on this.
regards

"You have an error in your SQL syntax near '' at line 1"

&lt;?php

// Copyright (c) Interakt Online 2001
// www.interakt.ro/

require("./adodb/adodb.inc.php";
require("./Connections/RBconn.php";
?&gt;&lt;?php
$RSsearch__varkeyword = "%";
if (isset($txtKeyword))
{$RSsearch__varkeyword = $txtKeyword;}
?&gt;&lt;?php
$sql = "SELECT * FROM jobs_job WHERE ";
// check for $term (keywords)
if (isset($term) && $term!="" {
$terms = split(" ",addslashes($term));
for ($k=0; $k &lt; count($terms); $k++) {
if ($k!=0) {
$sql.=" OR ";
}
$sql .= "title_job LIKE '%".$terms[$k]."%' OR salary_job LIKE '%".$terms[$k]."%' OR qualifications_job LIKE '%".$terms[$k]."%'";
}

}
$RSsearch=$RBconn-&gt;Execute($sql) or DIE($RBconn-&gt;ErrorMsg());
$RSsearch_numRows=0;
$RSsearch__totalRows=$RSsearch-&gt;RecordCount();
?&gt;<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
OK, hopefully this will help. i've reworked your SQL generating code so that it does work:-

$sql = "SELECT * FROM ss_dir_main WHERE ";
// check for $term (keywords)
if (isset($term) && $term!="" {
$terms = split(" ",addslashes($term));
for ($k=0; $k &lt; count($terms); $k++) {
if ($k!=0) {
$sql.=" OR ";
}
$sql .= "keywords LIKE '%".$terms[$k]."%' OR business_name LIKE '%".$terms[$k]."%' OR description LIKE '%".$terms[$k]."%'";
}
echo $sql;
}

This will ensure that each word is compared against each field in the database.

Hope it helps

Tim Green

Extension & PHP TalkZone Manager
<font size=1>-------------------------------------------
<i>Please read the Forum FAQ before posting
a question to this TalkZone.</i>
-------------------------------------------
www.UDzone.com : A dynamic Dreamweaver,
Ultradev and Fireworks site for developers
by developers.
-------------------------------------------</font id=size1>
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

Here is the solution:
First let's say that the text field in the search page is named "keywords".
After submitting you will generate the sql query using the following code:

&lt;?php


// Copyright (c) Interakt Online 2001
// www.interakt.ro/

require("./adodb/adodb.inc.php";
require("./Connections/yourConn.php";
?&gt;&lt;?php
$fields = array("yourTable.field1", "yourTable.field2", "yourTable.field3";
?&gt;&lt;?php
function getKeywordsSQL($key) {
global $HTTP_POST_VARS;
global $fields;
$keywords = explode("+", $HTTP_POST_VARS[$key]);
//Removing spaces from every keyword
for($i=0; $i&lt;sizeof($keywords); $i++){
$keywords[$i] = trim($keywords[$i]);
}
$opt = "AND";
$sql = "";
for($i=0; $i&lt;sizeof($keywords); $i++) {
$sql = $sql . "(";
for($j=0; $j&lt;sizeof($fields); $j++) {
$sql = $sql . $fields[$j] . " LIKE " . "'%" . $keywords[$i] . "%' OR ";
}
$sql=substr($sql,0,strlen($sql)-4);
$sql=$sql." " . $opt . " ";
}
$sql=substr($sql,0,strlen($sql)-4);
return $sql;
}
?&gt;&lt;?php
if ($HTTP_POST_VARS["keywords"] == "" {
$sqlKeywords = "";
} else {
$sqlKeywords = getKeywordsSQL("keywords";
}
?&gt;&lt;?php

$sSQL = "SELECT * FROM yourTable WHERE (id_table &gt; 0)";
if($sqlKeywords != "" {
$sSQL = $sSQL . " AND (" . $sqlKeywords . "";
}
?&gt;&lt;?php
$rs = $yourConn-&gt;Execute($sSQL) or KT_DIE($yourConn-&gt;ErrorNo(),$yourConn-&gt;ErrorMsg());
$rs_numRows=0;
$rs__totalRows=$rs-&gt;RecordCount();
?&gt;
Now some explanations:
$fields is an array with the fields into you are looking. It can be customized by
adding or deleting fields to it.
$keywords is an array obtained from the text field in the search page using explode
command with "+" separator(this separator is used when you search between words; you can change it).
$opt is an option that means:
"AND":all words must be found
"OR" :any word can be found
id_table from the query is the id field of your table(you will change this according to your
id table field; I use this syntax to be easy to add the rest of the query)
Please let me know if this helped you.

-----------------------
Cirstoiu Aurel Sorin
InterAKT Support
www.interakt.ro <pre id=code><font face=courier size=2 id=code> </font id=code></pre id=code> <pre id=code><font face=courier size=2 id=code> </font id=code></pre id=code>

Edited by - csorin on 04 Jun 2002 10:23:28