Solved

PHP/MySQL database search engine

Posted on 2011-09-22
20
391 Views
Last Modified: 2012-05-12
Good Afternoon Experts!
I am looking to put in a search engine to our website to filter by specific vehicles.

A bit of details about the site:

The website currently filters by vehicle type – cars, trucks, sport utilities, vans, and toys
Toys are things like motorcycles, boats, etc.
We have anywhere from 400-700 vehicles active on the website at any given time
The site uses PHP/MySQL, with the update to the site it will be the most current version of both.

The problem: There are a couple of different ways I am looking at approaching this and I’m looking for advice on which I should go and how  I would go about doing it.

I need to be able to search the following fields:   1) year 2) make 3) model
In addition I plan on putting in a filter by stock number, but this one will be easy as it will only display the stock number they are looking for.  The following code will easily take care of that one.
$query = “select * from vehicle_table where stocknumber = $stocknumber”

Open in new window

1. Option 1

I have thought about using AJAX to be able to select a make, and then display model, and then display only the available years for that make and model combination.  I’m not quite certain how I would do that.

2. Option 2

The other option I have thought of is to do a select box and list all of the available makes in one box and models in another box.  Followed by a box that shows start year and a box that shows end year.

Variables / Tables / Data storage

The information I am looking to search is currently in a single table.
(similar table name) – vehicle_info
Variable 1 – make
Variable 2 – model
Variable 3 – year

I am certain I am forgetting information, but if you need more let me know and I will do my best to add any information I have forgot to list above.
0
Comment
Question by:shdwmage
  • 9
  • 4
  • 4
  • +2
20 Comments
 
LVL 9

Expert Comment

by:rfportilla
ID: 36583278
I think this sounds more like a user experience question than a coding question.  You can do this many different ways and it is probably best to allow your end user to select in more than one way.

I like option one.  Yes, AJAX is a good way to deal with this and you would have to find a good AJAX library to do it.  It's not too bad, though.

Option 2, I'm not completely clear on. Would the select boxes be populated dynamically as the user selects?  Would the user select the year?  What is the flow?  

0
 
LVL 2

Author Comment

by:shdwmage
ID: 36583324
I had a working set of code before, but it spiked processor usage and our webhost wasn't happy with it.  It's been a long time and I doubt I still have the code.

The form would be like the following I suppose:
After selecting all of the vehicle makes (ford, chevy, chrsyler, etc) it would transition them to a second page where just the models for the selected makes shows up, and then transition again unto a page to select a range of years and then show a result.  

You are correct that it is an experience question, I have writen search engines in different languages, but never in php/mysql. I'm a real stickler for trying to do things as efficiently as possible. I understand the concepts behind it, but since I've never done it I'm a bit tepid about it.

We have a lot of traffic that comes into our website, and I cannot afford to make a mistake that would cause issues with it.
0
 
LVL 11

Accepted Solution

by:
maeltar earned 300 total points
ID: 36583379
You could use something like what is on this page as a starting block, looks to meet the requrements for Option 2.

http://www.webmonkey.com/2010/02/build_an_ajax_dropdown_menu/
0
 
LVL 11

Expert Comment

by:maeltar
ID: 36583395
In fact this link may be better for you ..

http://www.plus2net.com/php_tutorial/php_drop_down_list.php
0
 
LVL 9

Expert Comment

by:rfportilla
ID: 36583438
AJAX is what I would do.  It is much nicer.  If you were having a processor spike then there had to be bad code.  Ajax shouldn't do that.

Back to option 2, the only thing I don't like about it is that when I do a car search, I like to be able to search in many different ways, by year, by model, by make, etc.  The challenge is finding a good way to present all of these options the user.  I like how newegg works.  You have several different options on a single page and each option limits search further.  

Good luck with this.  It sounds like a fun project.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 36583446
Why not use Google Site Search?  They kind of have search all figured out over at Google!
http://www.google.com/sitesearch/#utm_campaign=en&utm_source=en-ha-na-us_ca-bk&utm_medium=ha&utm_term={keyword}
0
 
LVL 2

Author Comment

by:shdwmage
ID: 36583483
Ray, in my case google site search won't work for what I need.  I need to be able to custom format the output.  Additionally it doesn't handle my site structure well because there are multiple stores running off the same website.  So when I perform a search it returns the search for all the stores showing multiple options for the exact same vehicle.
0
 
LVL 2

Author Comment

by:shdwmage
ID: 36583543
When I assembled my search page before I had a couple of queries on it to build the list.  They were like what follows

$query = "select DISTINCT make from vehicle_info where active ='yes' order by make";
and then doing another query with
$query = "select DISTINCT model from vehicle_info where active = 'yes' order by model";

Open in new window


With the AJAX code, I am assuming when I first build the page I will need to do a query like the first one and build the list with a while loop.

And then have a second file that is called that performs a query like the following:
$query = "select DISTINCT from vehicle_info where active = 'yes' and make = $make order by model";

Open in new window


The other thought that just occured to me is that I probably want to be able to filter by body style as well.  So would I want to select body style first then make then model and drill down that way showing only results for that?  And allowing for "all" to be selected in the body style to remove that filter.
0
 
LVL 9

Expert Comment

by:rfportilla
ID: 36583701
I hope you don't mean to have any part of the query accessible to the user.  

I would create a list of options and maybe even store them in cookies for ease and accessibility.  With each ajax request, you should send the entire list of selected options and values to the server.  This should then be checked on the server side to make sure the data is valid and safe.  Then a query is constructed and the data returned to the page.  This should happen each time there is an update.  

Updates can be automatic or user inittiated (with like a button or something).
0
 
LVL 2

Author Comment

by:shdwmage
ID: 36583867
RF,
The queries won't be accessable to the end user. In addition any input that is brought in from the website is thoroughly scrubbed and clean before being allowed to even be processed.

I intend to make a drop down list so they can only select one type of vehicle at a time.  So the following will show up to begin with (in psuedo code):

<?php
#display available body styles, ran from database to prevent any non-represented body styles from being shown, sorted by body style to alphabatize.
$query = "select DISTINCT body_style from VEHICLES where ACTIVE = 'yes' order by body_style";
$result = mysql($query);
#start building the list
echo"          <select name="body_style" id="bstyle">
            <option selected="selected" value="all">ALL</option>"
while($row = mysql_fetch_array($result, MYSQL_ASSOC)){
#loop through the options to make the list.
echo <option value = '$row[body_style]'>$row[body_style]</option>;
}
# close the select
echo </select>

#Repeat the above for vehicle makes
?>

Open in new window


Now that is all psuedo code, but you get the jist of what I am thinking. With the turn over rate of vehicles at our company it's difficult to keep up with whats being added and removed at any given time. It's not that I'm opposed to generating a file, xml, txt or whatever, but the data is in a constant state of flux.  I would have to add the update script to the add and remove vehicle selections and even the modification screens.  As I said not opposed, just explain to me how you would handle it please.
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 108

Expert Comment

by:Ray Paseur
ID: 36583940
I would have a DBA look at this problem, with the data base tables laid out on the wall so you can see all the data at once.  Most automotive sites have some kind of filtering mechanism, so you can look at a few and get some design ideas for the client side of things.
0
 
LVL 2

Author Comment

by:shdwmage
ID: 36583966
Ray, while I would love to do something like that, the company I work for is way to "froogle" to spend the money on that kind of thing. They are even to froogle to pay for antivirus software so I get told a lot "that's what we pay you for."  
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 36584023
It's not "frugal" to save money by not buying anti-virus software.  It is something different from frugal.  Maybe more like foolish.  That's like saying, I want you to take the seat belts out because I want to save money, so I am not going to pay for seat belts.

Here is what the company needs to know:  The cost of design decisions is about 1/1000 the cost of error remediation after the code has been written.  So the time to invest is now, before the code is written.  This isn't something I am just making up - it's taught in the engineering programs of every major college and can be demonstrated in real-life examples, where one company made good design choices and another made poor design choices.  Fast forward to the day of application deployment.  One company is celebrating a successful launch and the other company is making an emergency call to IBM.
0
 
LVL 2

Author Comment

by:shdwmage
ID: 36584043
Ray, we trip over dollars to save pennies all the time.   Anyhow, I am working towards something like what is on this KBB webpage:

http://www.kbb.com/car-values/

The section like by make (which is easy) or the "search cars for sale"
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 36584071
Yeah, KBB have a good interface.  It is a JavaScript thing using AJAX to talk to the back end and populate the pull-down select boxes.  But there is no real "search" going on.  They just have junction tables to associate the model with the make, etc.
0
 
LVL 9

Assisted Solution

by:rfportilla
rfportilla earned 200 total points
ID: 36586990
I agree with Ray_Paseur about the AJAX comment.  When you select one dropdown, a request is being made in the background by an AJAX library to populate the next dropdown.  This is certainly a good way to do this.

I'm a little confused, though, as to what is holding you back at this point.  You seem to have the parts that you need.  I think this is the procedure I would follow. Please let me know if there is anything I am missing or doesn't make sense.

1. create a database view with the relevant information
2. Structure code to build the sql query based on filter options.  
    For ex,
       $sql = "select color,price,mileage from .... "
       if(isset ($carmake)) { array_push($filter, "make = $carmake" )}
       if(isset ($carmodel)) {array_push($filter, "model = $carmodel") }
       if(isset ($caryear)) { array_push($filter, "model = $caryear") }
       if(count($filter) > 0) {$sql .= "where " . join (" and " , $filter)}
...
3. Use AJAX to submit requests and update page after each time user selects from dropdowns


 
0
 
LVL 2

Author Comment

by:shdwmage
ID: 36587626
Yes, I believe I have all the peices of the puzzle together now. I am in the process of writing the code right now to test with.  I'll update if I run across any snags and/or when I finish with a successful product.
0
 
LVL 8

Expert Comment

by:LocoTechCJ
ID: 36588117
I'm a little late in the game, but have you looked at Sphinx?  http://sphinxsearch.com/  We use it a lot for our Rails apps and it does a lot for indexing the data you want to search through and speeds up the search a great deal. It's runs quite well on MySQL and can be used with PHP.

As far as the user experience, the way most of the auto sites require you to pick a year, then pick a make, then pick a model, then pick an engine type, then pick a.... drives me nuts.  It should be WAY easier than that.  I should have one search box that I can start typing in Corvette and it auto completes a selection that I can choose from.  Anyway, that's my professional and personal 2 cents.
0
 
LVL 2

Author Comment

by:shdwmage
ID: 36588331
Thanks Loco, but I'm getting close to being done with the code now.
0
 
LVL 2

Author Closing Comment

by:shdwmage
ID: 36599093
Thanks guys, between the two selected answers I was able to peice something together that runs quick and smooth!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

"In order to have an organized way for empathy mapping, we rely on a psychological model and trying to model it in a simple way, so we will split the board to three section for each persona and a scenario and try to see what those personas would Do,…
This article discusses four methods for overlaying images in a container on a web page
This video teaches users how to migrate an existing Wordpress website to a new domain.
The viewer will get a basic understanding of what section 508 compliance can entail, learn about skip navigation links, alt text, transcripts, and font size controls.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now