MySQL Forums
Forum List  »  Stored Procedures

Please Help Need Search Stored Proc
Posted by: John Gilmour
Date: March 11, 2009 11:13AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Please Help Need Search Stored Proc
3356
March 11, 2009 11:13AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.