SQL Dynamic Select Statement Problem with possible Search and Replace solution

Posted on 2003-03-25
Medium Priority
Last Modified: 2010-03-05
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.
Question by:Jattie
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Accepted Solution

yokel earned 135 total points
ID: 8204156

$quotedsurname = $dbh->quote( $surname );

Then use $quotedsurname in your select statement.

$dbh->quote not only quotes the string correctly depending on what DB you are using, but also should correctly handle strings containing the quote character.

Author Comment

ID: 8204175

It worked!

Featured Post

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

On Microsoft Windows, if  when you click or type the name of a .pl file, you get an error "is not recognized as an internal or external command, operable program or batch file", then this means you do not have the .pl file extension associated with …
In the distant past (last year) I hacked together a little toy that would allow a couple of Manager types to query, preview, and extract data from a number of MongoDB instances, to their tool of choice: Excel (http://dilbert.com/strips/comic/2007-08…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
Six Sigma Control Plans

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question