php and sql search engine script...

[scottstown]

Hello all,

 

I am having a small problem with getting the following code working... I am sure its an easy problem, just need another set of eyes on it..

THANKS for any help! Scott P.

 

if ($search == NULL or $search == ‘%’){

} else {

for ($i=0; $i<count($keywords); $i++) {

$query = "

SELECT link, description, title

FROM search_data

WHERE keyword LIKE \"%$keywords[$i]%\"  <--- I think the problem is in here somewhere...but may not be..

ORDER BY link";

}

 

Thanks Again.

[matto]

$query = "
SELECT link, description, title
FROM search_data 
WHERE keyword LIKE '%" . $keywords[$i] . "%'
ORDER BY link";

[scottstown]

thanks , but it didn't help, it must be something else...

[matto]

where does the variable $search come from ?

 

Can you explain what exactly is not working....maybe post more of the script

 

 

[scottstown]

Here is more of the script, it is different then the first one that I posted as I have been working on it.. but it still isn't working properly ...

 

The data from the POST is from a form of a search engine... any help would be great, thanks

 

$search_txt = $_POST['search_text'];

 

$search = $search_txt;

 

$keywords = explode(" ", $search);

 

$keywords = array_diff($keywords, array(""));

 

 

include('dbcreds.php');

 

 

 

 

if($search == NULL or $search == ‘%’){

}

else{

 

 

$query = "SELECT * FROM search_data WHERE";

 

for ($i=0; $i<count($keywords); $i++) {

 

$query = "keyword LIKE  '%" . $keywords[$i] . "%';

}

 

 

 

 

$result = mysql_query($query) or die(mysql_error());

 

 

if ($search == NULL or $search == '%'){

} else {

$count = mysql_num_rows($result);

$total_results = $count;

}

 

[scottstown]

This is the error im getting when i try to run it....

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'keyword LIKE '%car%'' at line 1

 

[matto]

when your query is executed using the mysql_query() function it is missing the "SELECT * FROM search_data WHERE" because you are reassigning the contents of $query rather than appending to it.

 

For example:

 

$sql = "SELECT * FROM search_data WHERE";

//append the where clause

$sql .= " keyword = 'whatever'";

 

The $sql string now = "SELECT * FROM search_data WHERE keyword = 'whatever'"

 

.....essentially what you have is the following:

 

$sql = "SELECT * FROM search_data WHERE";

$sql = " keyword = 'whatever'";

 

The $sql string now = " keyword = 'whatever'"

 

 

 

[scottstown]

I have tried to do what (matto) said in the post above, but after trying a lot of things, I still cant understand how to do this, can someone please give me some direction. thanks Scott

[matto]

Looking a bit closer at the following code, you would need to have an "OR" statement if more than one results was returned from the original query

 

for ($i=0; $i<count($keywords); $i++) {
  $query = "keyword LIKE  '%" . $keywords[$i] . "%';
}

 

For example:

 

select * from search_data
where (keyword like '%this%'
or keyword like '%that%')

 

The above example demonstrates how the sql statement would need to look like if 2 keywords were provided on the search screen

 

 

[nibbo]

Try something like this:

 

        $query = "SELECT * FROM search_data WHERE";

        $sqlOR = " ";

        for ($i=0; $i<count($keywords); $i++) {

                $query .= $sqlOR . "keyword LIKE  '%" . $keywords[$i] . "%';

                $sqlOR = " or  ";

        }

 

note the use of .= (dot/equals) to append each keyword so as not to destroy the original select command and the use of the $sqlOR string. This is space to start with and set to ' or ' for subsequent keywords so you get keyword like '%keyword1%' or keyword like '%keyword2%'.

 

personally I would replace the for loop with a foreach

 

i.e. foreach ($keywords as $keyword)

 

Good luck.

 

 

[scottstown]

I got it working thanks for your help! Scott