Need help with a search function

[Rogue3]

I'm trying to create a search engine for my mysql database. I think i'm pretty close. I made a search form, but no matter what I type in I get a list of ALL items in my database. I'm wanting it to only return results that include ALL search terms. Here is the code I think is hanging me up:

$data = mysql_query("SELECT * FROM `guides`");

while($result = mysql_fetch_array( $data )) 

Here is my search page:
http://jeditemplearchives.com/search/

Can anyone offer any help here?

[Wildbug]

If that's the code you're using to return search results, then you're not constraining the results at all.  You'll need a WHERE clause to limit results based on what comes in through the search form.  The query as it stands returns all rows in the table "guides."

[Rogue3]

That would explain why i'm getting all my results!

Can you show me where I would put the "where" clause
in the above strings?

[Wildbug]

Reading [url=http://dev.mysql.com/doc/refman/5.0/en/retrieving-data.html]retrieving information from a table[/url] and [url=http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html]string comparison functions[/url] from the MySQL manual should get you on top of things.

To get you started....
[code]SELECT * FROM guides WHERE whatever_field LIKE '%term1%' OR whatever_field LIKE '%term2%'[/code]

[Rogue3]

OK, that is getting me closer. However, I'm a bit confused on which items I need to substitute my own variables in.

So, from here:

whatever_field LIKE '%term1%' OR whatever_field LIKE '%term2%'

do I replace the 'whatever_field' with the name of my fields from my table? I have 3 different fields I want to make part of the search, so do I have another 'OR' in there? Also, the '%term1%', is this a variable that I need to change too?

Here is my form code:

<form name="search" method="post" action="/search/search.php">
Seach for: <input type="text" name="find" />
          <input type="hidden" name="searching" value="yes" />
<input type="submit" name="search" value="Search" />
</form>

There's no choice for different fields to search, it's just a single search box.

My fields in my table that I want to search are 'series' , 'type' , and 'name'

[Wildbug]

Read the string comparison functions section, specifically the LIKE syntax.  The percentage sign (%) represents a wildcard not unlike the asterisk (*) wildcard in filename globbing.

The easiest way to get decent results would be to perform a substring search on all of your columns.  And, yes, in the example, 'term1' and 'term2' represent search terms entered in your search form.  Of course, you may have more or less than two....

(Oh, sorry, I misread your first post where you want only matches with ALL search terms.  In that case you'll want to use "AND" instead of "OR.")

[code]
// Your query string will look something like this:
// SELECT * FROM guides WHERE CONCAT(series,type,name) LIKE '%term1%' AND CONCAT(series,type,name) LIKE '%term2%'

// ... and you can assemble it with something like this:
$query = 'SELECT * FROM guides WHERE CONCAT(series,type,name) LIKE "%' . implode('%" AND CONCAT(series,type,name) LIKE "%', preg_split('/\s+/',$_POST['find'])) . '%"';
[/code]

[Rogue3]

Where is 'term1' and 'term2' defined?

It seems that no matter what I put in my search I come back with no results.

[Wildbug]

I don't know how much more I can explain it; that "$query = ..." line will nearly do everything for you.  Search terms are the words entered in the search form.  Term1, term2 are just my generic representation of some imaginary terms entered into the search form in order to provide a concrete example of an SQL query.

Maybe something less abstract:

[list]
[*]A user comes to your site, wants to find something, and types "jedi action figure".
[*]$_POST['find'] contains "jedi action figure"
[*][b]preg_split('/\s+/',$_POST['find'])[/b] splits that string into "jedi","action", and "figure"
[*]You want to form the SQL query "[b]SELECT * FROM guides WHERE CONCAT(series,type,name) LIKE '%jedi%' AND CONCAT(series,type,name) LIKE '%action%' AND CONCAT(series,type,name) LIKE '%figure%'[/b]"
Note:  [i]CONCAT(series,type,name) concatenates the three column values into one string, so you'll only need to search one string for each search term[/i]
[*]Assemble the query by "gluing" the search terms ("jedi","action","figure") together with implode(): [b]implode('%" AND CONCAT(series,type,name) LIKE "%', preg_split('/\s+/',$_POST['find']))[/b]
[*]Put the whole thing together:  [b]$query = 'SELECT * FROM guides WHERE CONCAT(series,type,name) LIKE "%' . implode('%" AND CONCAT(series,type,name) LIKE "%', preg_split('/\s+/',$_POST['find'])) . '%"';[/b]
[*]Execute the query:  [b]$data = mysql_query($query);[/b]
[/list]

[Rogue3]

Thanks for all your help. I will try this again. Sorry, i'm a bit new to some of this. But, i'm getting it, slowly but surely!

[Rogue3]

Ok, getting closer.... I didn't get the 'no results' this time, I just got a blank result. Here is the code as I have it now... this is all directly below my database information.

Also, right now I only have 7 entries in my database until I hash this search out. So, some search terms (for testing) that should bring back results:

luke
leia
skywalker
luke skywalker
vintage (this one should bring back all results so far)


[code]$find = strtoupper($find);
$find = strip_tags($find);
$find = trim ($find);


//search criteria

$query = 'SELECT * FROM guides WHERE CONCAT(series,type,name) LIKE "%' . implode('%" AND CONCAT(series,type,name) LIKE "%', preg_split('/\s+/',$_POST['find'])) . '%"'; 

$data = mysql_query($query);




{
echo $result['series'];
echo " ";
echo $result['type'];
echo "<br>";
echo $result['link'];
echo "<br>";
echo "<br>";
}

//This counts the number or results - and if there wasn't any it gives them a little message explaining that
$anymatches=mysql_num_rows($data);
if ($anymatches == 0)
{
echo "Sorry, but we can not find an entry to match your query<br><br>";
}

//And we remind them what they searched for
echo "<b>Searched For:</b> " .$find;
}
?> [/code]

[Rogue3]

BINGO!

I was forgetting the result line:

while($result = mysql_fetch_array( $data ))

It is working now... finally!

Thank you for all your help!

[Rogue3]

If I wanted to sort my results by my 'date' field, where would I add that information?

[Wildbug]

See the [url=http://dev.mysql.com/doc/refman/5.0/en/select.html]SELECT[/url] section of the MySQL manual for the proper syntax.  You put ORDER BY clauses near the end of the query, but it depends on what other options you are using as to exactly where.  (But in your case, probably right at the end.)

[Rogue3]

I did it like this, but I get an error upon search...

[code]$query = 'SELECT * FROM guides WHERE CONCAT(series,type,mfg,keywords,year,name) LIKE "%' . implode('%" AND CONCAT(series,type,mfg,keywords,year,name) LIKE "%', preg_split('/\s+/',$_POST['find'])) . '%"' ORDER BY year;[/code]

[Wildbug]

[quote author=Rogue3 link=topic=99549.msg392580#msg392580 date=1152200242]
I did it like this, but I get an error upon search...

[code]$query = 'SELECT * FROM guides WHERE CONCAT(series,type,mfg,keywords,year,name) LIKE "%' . implode('%" AND CONCAT(series,type,mfg,keywords,year,name) LIKE "%', preg_split('/\s+/',$_POST['find'])) . '%"' ORDER BY year;[/code]
[/quote]

That's your PHP code?  Of course there's an error!  "ORDER BY year;" is a bare string.  You need to enclose it in quotes and concatenate it to the rest of the query string.  Check your code, man!

[Rogue3]

[quote]"ORDER BY year;" is a bare string.  You need to enclose it in quotes and concatenate it to the rest of the query string.[/quote]

Thanks for the advice. I'm still a beginner at the coding part (hence the failing code :-) ), so can you help me out here?

[fenway]

[code]$query = 'SELECT * FROM guides WHERE CONCAT(series,type,mfg,keywords,year,name) LIKE "%' . implode('%" AND CONCAT(series,type,mfg,keywords,year,name) LIKE "%', preg_split('/\s+/',$_POST['find'])) . '%" ORDER BY year';[/code]

[Rogue3]

Thank you! It seems I was missing just one '  :-\