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?
robrodpProgrammerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
robrodpProgrammerAuthor 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
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

Marco GasiFreelancerCommented:
You should be able to escape the 'extra' quote adding a backslash:

Select * from park where unit='LION\'S'
0
robrodpProgrammerAuthor 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
robrodpProgrammerAuthor 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
Chris StanyonWebDevCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
robrodpProgrammerAuthor Commented:
Thx guys. This dis the trick
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.