Hi martin, thanks for the email. I'm sorting it out slowly but surely. I was just a little over worked the night I wrote that novel of an email. I don't know if you saw my latest email titled "Mysql Result". That's the last step I am at. I used a select statement across multiple tables to get all the values that I wanted. I forgot because I was so tired that, once I had those values in there that I could sort by anything I wanted to. If you have a solution for my Mysql Result problem, that would help me out alot. Most people are saying that I have to do a sub query, so I am not really sure. Thanks though. -Wendell Frohwein -----Original Message----- From: Martin Norland [mailto:martin.norland@xxxxxxxxxx] Sent: Monday, March 07, 2005 7:08 AM To: php-db@xxxxxxxxxxxxx Subject: Re: Shopping Cart Wendell Frohwein wrote: > Hello everyone. Just want to thank you for all the help you have > provided me in the past. I have a new task that I am trying to tackle, > but have come into a snag. I am building a customized shopping cart for > auto accessories. I have gotten about 90% of the cart worked out except > for the search feature. It is not a textual search. It is a vehicle > specific search. > > When products are added to the cart, all the standard information is > there along with the vehicle make, model, and year. Example: Honda Civic > Si 1999. So this sounds like a peace of cake, as I would think. But in > the customized car industry, some products fit on multiple cars. > Example: DC Sport 4-1 Header AHS6607s fits all 92-93 Acura Integra's, > but also fits 94-99 Acura Integra's GSR Only. > > When adding a product. I have this option that says, "Select Vehicle > Make, Model, and Year". Option for newly created product says, "Not > Selected". So they click to add and a window opens that allows you to > pick these choices from drop down menus. So for the above examples, you > would choose: > > Make Drop Down: Acura > Model Drop Down: Integra > Year Text Field: 92-93 > > Then you click add vehicle to this product. That window stays open and > allows you to put another entry for the current product. > > Make Drop Down: Acura > Model Drop Down: Integra > Year Text Field: 94-99 > > Once again, click add vehicle to this product. > > So all this goes into a table that I called cart_search. Here is the > table layout for the cart_search table: > > CREATE TABLE `cart_search` ( > `id` INT( 20 ) NOT NULL AUTO_INCREMENT , > `pid` INT( 20 ) NOT NULL , > `make` INT( 20 ) NOT NULL , > `model` INT( 20 ) NOT NULL , > `year` INT( 4 ) NOT NULL , > PRIMARY KEY ( `id` ) > ); > > For the above examples, the following rows would be added: (forgive my > crappy hand coded mysql output) > > | id | pid | make | model | year | > 1 1234 1 1 1992 > 2 1234 1 1 1993 > 3 1234 1 1 1994 > 4 1234 1 1 1995 > 5 1234 1 1 1996 > 6 1234 1 1 1997 > 7 1234 1 1 1998 > 8 1234 1 1 1999 > > > in the cart_makes table the id that corresponds with Acura would be 1 > in the cart_models table the id that corresponds with Integra would be 1 > This is the reason for the 1's in the above simulated mysql output. > > When that is all done, the window is closed and you are back at the add > product page. Click Add Product button at the bottom. A new row is > inserted to cart_products and the id row is retrieved with > mysql_insert_id(). Then all the cart_search rows that have 1243 as the > pid would be set to the respective product id that was retrieved by > mysql_insert_id() > > So now you are a potential customer on the website wishing to search for > some products for your 93 acura integra. You go to the search page and > select Acura from drop down 1, page refresh's and the model dropdown is > populated with all Acura models, you select Integra as the model, Page > refreshes again populating the Year drop down with a list of years, You > choose 1993. Hit the search button and mysql does a query to the db as > so: > > $query=mysql_query("SELECT * FROM cart_search WHERE > make='{$_POST["make"]}' AND model='{$_POST["model"]}' AND > year='{$_POST["year"]}'"); > > If the database was only populated with the entry's from above, It would > then return 1 result with a product id for that dc sports header we > added. > > > So after all this, I supposed it works. But not like it should, Cause I > have no idea how to sort it, and to top it off, this just seems like a > really long process just to search a database for some products that > match specified make, model and years. > > Incase you are wondering the search page would return about 3 columns. > Product Description, Catalog Number, and Price. It would be pulling all > this information out of the cart_products table by its product id number > that was returned by the mysql_query above. > > > > So I am sorry if this seems like a novel to anyone. I just really > believe that there is an easier method to doing all of this. Any help > that someone can provide me would be greatly appreciated. I was looking > at a lot of websites on the net that sale car accessories and no one > really has a search that is this detailed. Most of them are static html > pages created by hand. This business has way to many products to make > hundreds of static pages like that. > > > So thank you in advanced, and I hope someone could lend a hand. [snip] You're right, you're 90% done. The only remaining 10% is basically confidence. Sounds to me like you have the right code, the right interface, and a fine implementation working already. If you're wondering how to sort the products that are returned from the search, sort them however you want - default to product id, have an option to sort by price, etc. It's just a listing of products, like before, they just happen to be filtered to a specific vehicle/make/model/etc. Join that cart search to the products table on the pid, pull the information of interest, and have at it. That cart search is just a table holding metadata on a product, there's no need to list it until you've already chosen a specific product. If you're talking about not knowing how to sort it in SQL: $my_order_field = 'products.price'; $order_direction = 'ASC'; // DESC SELECT cart_search.*, products.price, products.desc, products.cat_num from cart_search, WHERE make='{$_POST["make"]}' AND model='{$_POST["model"]}' AND year='{$_POST["year"]}' AND products.pid = cart_search.pid ORDER BY $my_order_field $order_direction; but maybe I'm misunderstanding the problem, you seem to have everything under control... P.S. - If you have it (the data) in digital form, I highly recommend setting up an import. Your interface won't get the testing it would otherwise, but it will sure save a huge heap of hours, for someone at least. If you get updates in digital form, you can always diff them and just add the new bits. The error rate approaches zero much faster without the human factor. Cheers, -- - Martin Norland, Sys Admin / Database / Web Developer, International Outreach x3257 The opinion(s) contained within this email do not necessarily represent those of St. Jude Children's Research Hospital. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
