Currently I have a database fulled with vehicles. I have separate tables for all makes and models, with the year being included with the makes. My question is, how can I retrieve the correct information from the database when someone performs a search for "2010 Ford Mustang". The 2010 Ford is in the makes table, and Mustang is in the models table. Both tables use varchar. Any help would be greatly appreciated.
Why start being efficient now? Your database is horribly, horribly, horribly laid out. First, you need to normalize (google that term) your database. Makes and models shouldn't be in their own tables, they should be in the same table with make and model fields. Also, do not store unique pieces of data with other unique pieces of data--that means '2010' should be in its own field, 'Ford' should be in its own field and 'Mustang' should be in its own field. After that, it will be easier to write that efficient query you dream of.
Need help retrieving info from a database efficiently. I want to ask how to use the correct datebase? thanks
This database isn't completely developed yet. I'm still in the building process of the site, and I'm trying to work out all the kinks now before it's too late. That said, I have read up on "normalization" quite a bit the last few days and don't have a problem redoing the database. Assuming I do, how could I efficiently get the results if someone searches for "2010 ford mustang", especially considering now those words would all be in separate tables.
Uh oh, now it sounds like you might be over doing the normalization. I think that "2010", "Ford", and "Mustang" should be in the same table but in seperate fields. However, your structure may be fine too, can you list your planned table structure? In general, there will be 2 ways to do this: 1. Involves setting up a form with an input for each field that users can supply values for. That means there would be a Year input where they can put "2010", a Make input where they would put "Ford" and a Model input where they would put "Mustang", possibly one for the engine, the color, etc. One input for every field in your database they can limit their search to. Then your script would take their input and construct the WHERE clause of your query lining up their input data with the appropriate field (i.e. ...WHERE caryear=2010 AND carmake='Ford' AND carmodel='Mustang'...). 2. Involves a form with one input that users can input their entire criteria like "2010 Ford Mustang". Then your script would take that input and try to find that text in a concatenation (http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat-ws) of all the data for a record. Your WHERE clause for that would look like this: ...WHERE CONCAT_WS(" ",caryear, carmake, carmodel, carcolor, carengine) LIKE "*2010 Ford Mustang*". #1 is preferable because it's easier on you to code and lets the user be extremely specific. #2 is harder to code and is less accurate for the user (i.e. "Red 2010 Mustang" can pull different results than "2010 Red Mustang"). Again, post your table structure, because I think your structure still may be sub-optimal. http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat-ws
Actually you structure will depend on how the database is supposed to be. Will you require sorting by year, maker, model etc? Also depends on which you will be needing more often [listing all models of a maker or all models throughout]. Normailisation to some amount is good but the actual structure will depend on where you use it and how