get 'error in your SQL syntax'

nilubon03 24 Mar, 2015

I have $form->data['SchoolName'] which use 'DB Multi Record Loader' to select from database and display in a drop down list.

Then I need to get a school type from database based on school name selected. It works fine except if the school name has apostrophe; for example Children's miracle.

// get school type from school_lookup
$query = "
SELECT SchoolType
FROM `xxxxx_chronoforms_data_school_lookup`
WHERE `SchoolName` = '{$form->data['SchoolName']}';

When I select Children's miracle school, I received this message.

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 's miracle'' at line 3 SQL=SELECT SchoolType FROM `xxxxx_chronoforms_data_school_lookup` WHERE `SchoolName` = 'Children's miracle' "

Please advise.

Thank you,
GreyHead 25 Mar, 2015
Hi Nilubon,

Please try this:
$db = JFactory::getDBO();
$query = "
SELECT` SchoolType`
  FROM `#__chronoforms_data_school_lookup`
    WHERE `SchoolName` = {$db->quote($form->data['SchoolName'])};
The $db->quote() should quote the string correctly for the search.

nilubon03 25 Mar, 2015
Thank you Bob,

You are my life saver. It worked. I appreciate your quick respond.
Thank you,
This topic is locked and no more replies can be posted.