Please Help Need Search Stored Proc
Hi All,
I am creating an inventory management system and have 7 tables total. I am having a trouble creating my sproc for Searching the tables. I've been banging my head against the wall with this for 2 weeks now. Please help!
My front-end is written in PHP and I have drop down menus passing the correct value ID into the sproc. If a field is left blank I have it passing value "NULL" to the IN parameter of my PROC. In the WHERE clause, I have a bunch of "AND" statements (ie inventory.id=myid AND inventory.color=mycolor) If the value is NULL - does it literally look for inventory.id to equal NULL? I need it to ignore that variable as an IN parameter...Does this make sense? I'll include my sproc below. Please let me know if you have any questions or suggestions...Thank for looking!
The my____ are variables being passed from my PHP. If the variable matches the value in the database I want it to return the results selected. I know I'm missing something, but I'm new, and not sure what it is...
CREATE PROCEDURE `sp_test2`(IN mydoors INT, IN mymodel INT , IN mycolor INT , IN myyear YEAR, IN myretailprice DECIMAL(10,2) , IN mydealercost DECIMAL(10,2) , IN myinteriorcolor INT , IN mymanufacturer INT )
BEGIN
SELECT car.id
, model.manufacturer
, model.modelname
, car.year
, color.color_name
, interior_color.interior_color
, car.doors
, car.retailprice
, car.dealercost
, transmission_type.transmission_type
FROM car
INNER JOIN model ON (car.model_id=model.id) INNER JOIN color ON (car.color_id=color.id) INNER JOIN interior_color ON (interior_color.id=car.interior_color_id) INNER JOIN transmission_type ON (car.transmission_type_id=transmission_type.id)
WHERE
car.doors=mydoors
AND model.id=mymodel
AND color.id=mycolor
AND car.year=myyear
AND car.retailprice=myretailprice
AND car.dealercost=mydealercost
AND interior_color.id=myinteriorcolor
AND model.manufacturer_id=mymanufacturer
ORDER BY car.id;
END