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.
For future inquiries like the other responder stated there is a [EMAIL PROTECTED] mailing list. In this case you have two options, either escape the single quotes in the strings, like so:
Depending on your database:
$Surnames[$idx] =~ s/'/\\'/g;
- or -
$Surnames[$idx] =~ s/'/''/g;
now the problem is the "depending on your database" which is why DBI provides a 'quote' method, check the documentation for DBI for usage.
http://danconia.org
-- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
