PHP/MySQL Search engine

Internal Website Search Engine

[shdwmage]

Ok, I have another question for you guys.

Now that I can make my list results from my database, I want to change it so my visitors can select more than 1 item from the list is created. I know how to create the form in html,but I am at a loss as to how to use the data.

So the search list will look like this coded by the site:

 <select name="year" size="10" multiple="multiple" id="select">
<option value="1999">1999</option>
<option value="2000">2000</option>
<option value="2001">2001</option>
<option value="2002">2002</option>
<option value="2003">2003</option>
<option value="2004">2004</option>
<option value="2005">2005</option>
<option value="2006">2006</option>
<option value="2007">2007</option>
<option value="2008">2008</option>
 </select> 

I have made it so the users can select all of the options if they want to. I guess I just need to find out what the output comes across like, but it only shows the last value selected when I do a:

 
<?
echo $_POST[year];
?>

Any ideas?

[cameraman]

Try
print_r($_POST);

to see what you're getting.

[penders]

<select name="year[]" size="10" multiple="multiple" id="select">

In order to return multiple values it needs to return an array, so add [] to the end of your control name. Then you should get something like the following returned...

$_POST['year'][0] = 2000 
$_POST['year'][1] = 2004 
$_POST['year'][2] = 2005 
: 
:

count($_POST['year'])

will return the number items.

[shdwmage]

yea I was missing the brackets to signify it was an array, then i could see the output without any problems.

When I'm dealing with an array I just do an implode(",",$variable); and then echo that back out, to check and make sure everything is working correctly.

I am sure I will come up with more questions as I code the search engine to take advantage of the array input.

[shdwmage]

Wow, so I got to writting my script, and it over used the my available server resources for a minute. It was a pain in the behind!

So this is what my code looks like

<?php 
$year = $_POST[year]; 
$firstn = $_POST[firstn]; 
$lastn = $_POST[lastn]; 
//start building the query
$query = "SELECT * FROM db WHERE (active='yes') AND (";
// set the counter to 0
$count = 0;
//check and see if a firstn is selected
if (isset($firstn)) {
  foreach($firstn as $v1) {
   if ($count <> 0) {
 $count = $count +1;
$query .= " or firstn='" . $v1 . "'";
 } else {
  $count = $count +1;
$query .= "firstn='" . $v1 . "'";
 }
  }
}
//check and see if a lastn is selected
if (isset($lastn)) {
  foreach($lastn as $v2) {
   if ($count <> 0) {
 $count = $count +1;
$query .= " or lastn='" . $v2 . "'";
 } else {
  $count = $count +1;
$query .= "lastn='" . $v2 . "'";
 }
  }
} 
//check and see if a year is selected
if (isset($year)) {
  $query .= ") AND (";
  $count = 0;
  foreach($year as $v3) {
   if ($count <> 0) {
 $count = $count +1;
$query .= " or year1='" . $v3 . "'";
 } else {
  $count = $count +1;
$query .= "year1='" . $v3 . "'";
 }
  }
}
$query .= ") ORDER BY firstn asc,lastn asc,trim asc,year1 asc";
echo $query;
?>

Just building the query, slams the heck out of my server, not to mention its not really working the way that I want it to.

Any idea's on how to simplify the building of the query to take less system resources then what it currently does?

Thanks in advance.

[jatar_k]

so all you want to do is select from multiple years that were given by the user where active=yes?

or is there more to it?

explain in the query in words and we should be able to hand you some better code

[shdwmage]

Basically this is a stripped down example code that I am messing with before going full scale on it.

Lets say I have 3 search fields, First Name, Last Name, and year.

The search values are gathered by doing a distinct search, limiting to active people. This way inactive people do not show up in the search list.

I want it to be able to be searched by any of the 3 values individually, or a limiting search if more than one field has inquiries input.

So I could do a search for all the Bobs, or just all The Johns, or I could do a search for all Smiths named John, or all John and Bob with the last name of Smith. Or even go as far as limiting the search results to the persons birth year.

This is something I am trying to figure out on a small test database before moving it over to a larger project I am working on.

I hope, it helps. If not I will check back later today and respond to any questions.

[jatar_k]

the other issue then is are all 3 of these fields going to allow multiple inputs?

if so I think you should rethink, multiple years and single last and first names would be best, then just have them do another search for another person/name

you can implode the year and use IN with a list of the years selected

then a single first and last

select * from table where firstn='firstn' and lastn='lastn' and year in ('2005','2006','2007','2008') and active='yes'

you then break up the construction to cover whether teach field was entered and remember to account for a single year where you would need = instead of in, though in may work for a single value list as well

you coule then do partials or LIKE searches but I think if you program for all eventualities you will be in trouble

if you really feel it is necessary to add multiples then limit it, make it 2 or 3 for each. Your server will cry when that helpful person adds a list of 100 names to each, bye bye site

you could also look at [sphinxsearch.com...]

[shdwmage]

yea, thats the problem I was having.. My server cried and said I had used all my procesing privldeges for 60 seconds.

Alright, I know this is probably a java script thing, but I know there is a way to have it when you select the value in the first input field, to have the second search field limited to just the first input.

So if I select Bob, its not going to show every last name in the database, just the ones who's first name is Bob.

[shdwmage]

Also eventually this code, when fixed to work the way I want it to, will be used for an e-commerce type search engine. Where the visitor can select the product line, and then the product name, blah blah blah. You know what I mean.

I want to do it this way instead of a text search to keep the visitor only to what we actually have in stock.

[jatar_k]

you could do an ajax type thing that does the queries as you go

some sites have support sections like that though I can't find the one I was thinking of

another option, since it is ecomm, would be to just do it graphically, similar to [support.creative.com...]

if they are really searching, they will probably do it on a search engine and go to a specific product page

if they are browsing then they may need a little visual stimulation

[shdwmage]

The picture reference would only work for certain types of searches. I could do a style search from that, but not a brand search.

[jatar_k]

the ajax stepped type then should work fine for you

[lexipixel]

Can they years selected be random and out of order, (e.g.- someone could pick 1998, 1996, 2000 and 2001), or are they ranges, e.g. "1996-2001" like in an auto parts database where one particular part usually fits several model years in a sequential range ?

[shdwmage]

no they can select the years individually.