Jump to content

Need help with a search function


Rogue3

Recommended Posts

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?
Link to comment
Share on other sites

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."
Link to comment
Share on other sites

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]
Link to comment
Share on other sites

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'
Link to comment
Share on other sites

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]
Link to comment
Share on other sites

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]
Link to comment
Share on other sites

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]
Link to comment
Share on other sites

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.)
Link to comment
Share on other sites

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]
Link to comment
Share on other sites

[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!
Link to comment
Share on other sites

[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?
Link to comment
Share on other sites

[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]
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.