search help

[luke noob]

can some one please help me adapt my script so that i can search for multiple words within my database,


my keywords feild looks something like this....
[code=php]
Big Teddie Bear Collection Tiny Small Soft
[/code]

At the moment if i search for the word "teddie" i will get "teddie" and if i search for "teddie bear" i will get "teddie bear", because in the keywords feild in the database "teddie" and "bear" are next to each other. but if i search for soft teddie i would get no results please help.

this is my script....

[code=php]

<?php

function search() {

// Get the search variable from URL
$var = ucwords(strtolo wer(@$_GET['q']));
$trimmed = trim($var); //trim space from the stored variable

// rows to return
$limit=15;
$s = trim(@$_GET['s']);

// check for an empty string and display a message.
if ($trimmed == "")
{

}

mysql_connect(" localhost","use rname","pass"); //(host, username, password)

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

// Build SQL Query
$query = "SELECT id, description, tn_src, src, lens, titles FROM dogs WHERE CONCAT(keywords , ' ', id, description, lens, camera, copyright, titles, Price) LIKE \"%$trimmed% \"
ORDER BY id DESC";

$numresults=mys ql_query($query );
$numrows=mysql_ num_rows($numre sults);

if ($numrows == 0)
{

$q = "SELECT titles, description, tn_src, src, id FROM dogs ORDER BY RAND() LIMIT 15";

$r = mysql_query($q) or die("Couldn't execute query");

[/code]

[Markus]

This articles should be of some help to you: http://devzone.zend.com/article/1304

[luke noob]

I have read the articles and iv tryed many combinations but still cant work it out. I have never done a search script before and im finding it a bit difficult, can any 1 else help me out a bit more please.

[dlite922]

Forget the code and first work out your query from a command line or an SQL client interface.

Why doesn't your query get any results? Does it have an error? Do all the fields exist?

Why are you searching words in id, price, etc instead of just 'keyword' and 'description' fields?



Dan

[luke noob]

I dont have any errors i have checked already, I am searching in all fields because all the photos require all that infomation to be called in, the search does work but only for 1 word or 2 that are together in the database.

[Markus]

Well, the article I provided covers the problem clearly, so I won't reiterate it. Also, see this page of the MySQL documentation.

[luke noob]

I think im close but not sure wat im missing

[code=php]

$query = "SELECT * MATCH(keywords, ' ', id, description, lens, camera,
copyright, titles, Price)AGAINST LIKE \"%$trimmed% \" FROM dogs WHERE
MATCH (keywords, ' ', id, description, lens, camera, copyright, titles, Price)
AGAINST LIKE \"%$trimmed% \"
ORDER BY id DESC";

[/code]

[luke noob]

i get an error, says $numrows has an invalid argument,

[Dormilich]

$numrows is a variable and variables typically don’t have input parameters.

[luke noob]

sorry i ment this.........

[code=php]

Warning: mysql_num_rows( ): supplied argument is not a valid MySQL result resource in /home/gary/public_html/functions.php on line 35

[/code]

[Dormilich]

that happens when you don't check for the mysql_query() execution success.

[luke noob]

how do i go about doing that? it worked ok before i changed my $query

[Dormilich]

basicly it goes like

mysql_query(…) or die(mysql_error());

[luke noob]

Thanx i will defo be using that from now on. I get the error

MySQL server version for the right syntax to use near '' ', id, description, lens, camera, copyright, titles, Price) AGAINST LIKE ("' at line 1

this is my query im using......

[code=php]

$query = "SELECT *, MATCH(keywords, ' ', id, description, lens, camera,
copyright, titles, Price) AGAINST LIKE (\"%$trimmed%\" ) FROM dogs WHERE
MATCH (keywords, ' ', id, description, lens, camera, copyright, titles, Price)
AGAINST LIKE (\"%$trimmed%\" )
ORDER BY id DESC";

[/code]

[Dormilich]

' ' (in MATCH()) is not a valid field.