Jattie van der Linde wrote:
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]



Reply via email to