I'm trying to build a dynamic piece of code that generates a HTML view like a year
planner for all employees in the company all on one page. My appraoch is to use 2
Quesries, the first getting all the active employee names and the second then
determines the dates of absence and plot that on a 365 dayscale to show the absence
and type of absence on a simple sideways stacked barrgraph generated using shaded
table fields. This works very well and my second query looks like this:
SELECT username, dayofyear(startdate), dayofyear(enddate),dayofyear(enddate) -
dayofyear(startdate)+1 , absencetype
FROM `absence`
where username = '$Name $Surnames[$idx]'
order by absencetype
The problem that I encounter is with the Irish surnames like O'Kelly and O'Flynn. In
the where clause the variables will translate to:
where username = 'Niall O'Kelly' and the Kelly part will cause an error message:
DBD::mysqlPP::st execute failed: You have an error in your SQL syntax near 'Kelly'
order by absencetype
' at line 3 at
D:\Data\script\emp_year.cgi line 29.
I was hoping to replace the ' with some escape code for ' using a search and replace
feature but I am not that familiar with search and replace yet. Can anyone offer me
some valuable assistance or pointers to my problem.
