Mhanaz Syed Mhanaz Syed - 24 days ago 6
MySQL Question

how to con-cat dynamic ID with select query in mysql

CREATE DEFINER=`root`@`localhost` PROCEDURE `test2`(in employeeId text)
BEGIN
set @SQLQuery =CONCAT("select groupId,eventId,scheduleId,description,events,eventType,
scheduledDate,name,designation,image,skills,duration,status,attachmentPath,
case when scheduledDate < now() and (select count(*) from event_request where
event_id=eventId and employee_code='",employeeId,"')>0 then 1
when scheduledDate < now() and (select count(*) from event_request where
event_id=eventId and employee_code='",employeeId,"')=0 then 0
else '' end as hasRequested ,(SELECT actual_attendance_status_id FROM TJU.event_attendees_mapping where
scheduleId=event_schedule_id and employee_code='",employeeId,"')
as attendingStatus,
case
when
(select count(*) from event_attendees_mapping where
event_schedule_id=scheduleId and employee_code='",employeeId,"')>0 then 1
else 0 end as isMyEvent,meetingRoom from EventList_View ", "and 1=1");

select @SQLQuery;

END


this is my procedure for dynamic query i am passing dynamic employee ID and when i call this procedure call ("TJU_741");

then my query becomes

select groupId,eventId,scheduleId,description,events,eventType,
scheduledDate,name,designation,image,skills,duration,status,attachmentPath,
case when scheduledDate < now() and (select count(*) from event_request where
event_id=eventId and employee_code=''TJU_741'')>0 then 1
when scheduledDate < now() and (select count(*) from event_request where
event_id=eventId and employee_code=''TJU_741'')=0 then 0
else '''' end as hasRequested ,(SELECT actual_attendance_status_id FROM TJU.event_attendees_mapping where
scheduleId=event_schedule_id and employee_code=''TJU_741'')
as attendingStatus,
case
when
(select count(*) from event_attendees_mapping where
event_schedule_id=scheduleId and employee_code=''TJU_741'')>0 then 1
else 0 end as isMyEvent,meetingRoom from EventList_View and 1=1


here you can see that each employeeID becomes like this ''TJU_741'' while it should 'TJU_741' please suggest me how to concat so that my query becomes for employee ID 'TJU_741' .

Answer

Your query must be like:

$param1 = 10;
$param2 = 'Max';

$query = "select column from table where column = ".$param1." and "'.$param2.'" and column = '1'";

Note: String, date, datetime, enum etc must be in single quotes. So don't forget to bind in single quotes.