php - MySQL Stored Procedure Creation with Input and Output Parameters -

---

answer: https://stackoverflow.com/a/24034862/1173155

---

i testing stored procedure follows:

delimiter $$ create procedure `test1`(in `tab_name` varchar(40), in `value_of` varchar(40), out `the_id` int(1)) begin  set @t1=concat('     begin          if exists (select ',tab_name,'.id ',tab_name,' ',tab_name,'.',tab_name,' = ',value_of,')                         select id ',the_id,' ',tab_name,' ',tab_name,'.',tab_name,' = ',value_of,';         else              insert ',tab_name,' values (null,',value_of,'); end if;');  prepare stmt3 @t1;  execute stmt3;  deallocate prepare stmt3; end $$ delimiter ; 

trying select id, or insert. have single row in table, when try , use procedure is; doesn't return anything.

the identifier value want table name simplicities sake. have spent many hours , i'm @ loss.

have tried everything, thought adding null, work table has 2 values , had forgotten put in realise trying within first if clause... ie, entering data ensure first statement invoked

thanks help

---

update

still having trouble this. trying make simple possible now.

i have city table, 1 record in. structure city.city = 'dublin'

value , table name same. follow select statement works when executed via phpmyadmin

begin      set @t1= concat("           select id               ",tab_name,"                       ",tab_name," = '",value_of,"'                        ");      prepare stmt3 @t1;      execute stmt3;      deallocate prepare stmt3; end 

now need ensure if select statement doesn't return anything, insert value , return select statement.

this seems such simple problem, yet can't find solution anywhere

--

insert ignore not because increments id regardless of inserting. there work around because use then.

insert ignore... // without increment?

select ^ ....

---

further update

my procedure looks (takes 2 varchar parameters) , working. doesn't increment id if value exists either fantastic.

begin  set @t1= concat("insert ",tab_name,"(",tab_name,") select '",city_name,"' dual  not exists (select 1      ",tab_name,"       ",tab_name," = '",city_name,"');         ");    prepare stmt3 @t1;      execute stmt3;      deallocate prepare stmt3;     end 

all want simple select statement return id of row city name. eg select id tab_name tab_name = 'city_name';

but adding in causes error :( if has solution ridiculous problem

the statement wrote should not return results. both select , insert statements not return results.

it's stored procedure.

do select first, follow insert(s). select first output result set of procedure.