vijay kumar vijay kumar - 5 months ago 17
MySQL Question

how to create store procedure for this mysql query which i want to call from php

FROM employee
INNER JOIN experience ON employee.PersonID=experience.PersonID WHERE employee.Firstname LIKE '%".$fname."%' AND experience.Year_Of_Experience >= $year;

As I am using inner join of two table and can any one help with how to call from php to fetch the data from database.

Help me to solve this.

Answer Source

I'm guessing based on your params that this would be suitable:


DROP PROCEDURE IF EXISTS great_procedure_name $$

CREATE PROCEDURE `great_procedure_name`(fname varchar(21844) CHARACTER SET utf8, year int)


FROM employee
INNER JOIN experience 
ON employee.PersonID = experience.PersonID 
WHERE employee.Firstname LIKE CONCAT('%', fname , '%') 
AND experience.Year_Of_Experience >= year;

END $$


Then to call it:

CALL great_procedure_name('abc', 23);

I tested this out on my system before posting the answer, so it should work for you. I don't know how you are doing MySQL queries in your PHP, but using CALL is usually quite straight forward. The problem many people will have is that procedures can return multiple results, so you need to be prepared to step through them, even when all you have is one.