MySql Syntax Error

I have a php (5.4.) with MySql (5.1.73)

I get the following error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CRUISER' or unidad='QUAD CAB' or unidad='RAM 1500' or unidad='RAM 1500 Y RAM CHA' at line 1

I am at a loss.

I ran the application on a server with php (5.2.6) and MySql (5.0.45) and I ran without a glitch.

Any ideas?
robrodpAsked:
Who is Participating?
 
Chris StanyonCommented:
The problem is not that your records have the apostrophe in - your problem is that your SQL Query is trying to select a record based on a value that has an apostrophe in, so no - you don't need to change your records.  You just need to change your SELECT statement

Marco's pointed out one solution and that's to escape the apostrophe in the query. Another alternative is to wrap the data in double quotes:

Select * from park where unit="LION'S";

You say that you're doing this in PHP, so the best-practice way of doing it is to use prepared statements. That way, you won't need to escape the data. It'll just work:

$stmt = $dbh->prepare("SELECT col1, col2, col3 FROM yourTable WHERE unit = :unit");
$stmt->execute(array("unit" => "LION'S"));
$stmt->fetch();

Open in new window

0
 
Marco GasiFreelancerCommented:
At least, as minimum requirement, we should be able to see the whole query: can you post it here?
In addition, are you running this query directly  in phpMyAdmin or other mysql frontend or you're running it through some script? And in the latter case, can you post the code?
It should also be useful to know the table structure to evaluate the query.
Thank you.
0
 
Walter RitzelSenior Software EngineerCommented:
CAn you share your code that is giving the error?
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
robrodpAuthor Commented:
Got the problem.

Some of my values in a certain fields have a ' (single quote) and the queryget confused.

for example

Select * from park where unit='LION'S'

MySql gets confused with the 'S.

Is there a work around? or do I have to change the single quote in all the values?
0
 
Marco GasiFreelancerCommented:
You should be able to escape the 'extra' quote adding a backslash:

Select * from park where unit='LION\'S'
0
 
robrodpAuthor Commented:
I have to change all the single quotes to \' ?
0
 
Marco GasiFreelancerCommented:
Only the ones which break the syntax, as in my example: just the quotes used as apostrophes, for instance.
0
 
robrodpAuthor Commented:
I have about 10,000 records with that problem.  what update query will

update park set unit=replace(unit,''','\'')

to replace all ' with \'

I can seem to find a way
0
 
robrodpAuthor Commented:
Thx guys. This dis the trick
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.