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 AND inventory.color=mycolor) If the value is NULL - does it literally look for 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 )

, 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 ( INNER JOIN color ON ( INNER JOIN interior_color ON ( INNER JOIN transmission_type ON (
AND car.year=myyear
AND car.retailprice=myretailprice
AND car.dealercost=mydealercost
AND model.manufacturer_id=mymanufacturer

Options: ReplyQuote

Written By
Please Help Need Search Stored Proc
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.