Puzzling PHP / MySQL problem.
Posted: Thu Feb 01, 2007 4:18 pm
The following code is giving me problems:
The following scenarios have been tried:
selected a value for event category only (pulldown returning an integer) -> query worked ( the event sub-category is automatically chosen)
entered a string value "a" for the URL (wibsite) -> query worked
selected/enter values for all three fields -> query worked
enter a value for title only ->
"ERROR 1: 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 '' at line 1."
Below is the result of the echo shown above:
"SELECT eventID, venueID, title, summary, eventType, eventSubtype, eventStartDate, eventStartTime, admissionPrice FROM events WHERE (title LIKE '%a%') ORDER BY title ASC"
Thanks in advance for any suggestions.
Peter -
Code: Select all
$whereString = '';
// category and sub-category
if ($_REQUEST['eventCategory'] > 0)
{
$whereString = 'eventType = ' . $_REQUEST['eventCategory'] . ' AND eventSubtype = ' . $_REQUEST['eventSubCategory'];
}
else
{
// nada
}
// title
if (!empty($whereString) && !empty($_REQUEST['title']))
{
$whereString .= ' AND title LIKE \'%' . $_REQUEST['title'] . '%\'';
}
elseif (!empty($_REQUEST['title']))
{
$whereString = 'title LIKE \'%' . trim($_REQUEST['title']) . '%\'';
}
else
{
// nada
}
// website
if (!empty($whereString) && !empty($_REQUEST['website']))
{
$whereString .= ' AND website LIKE \'%' . $_REQUEST['website'] . '%\'';
}
elseif (!empty($_REQUEST['website']))
{
$whereString = 'website LIKE \'%' . $_REQUEST['website'] . '%\'';
}
else
{
// nada
}
$cityStateZipQueryString = '';
if (!empty($cityStateZipQueryString) || !empty($whereString))
{
$quickSearchQuery = 'SELECT eventID, venueID, title, summary, eventType, eventSubtype, eventStartDate, eventStartTime, admissionPrice FROM events WHERE ';
if (!empty($whereString) && !empty($cityStateZipQueryString))
{
$quickSearchQuery .= ' (' . $whereString . ') AND venueID IN (SELECT venueID FROM venues WHERE ' . $cityStateZipQueryString . ')';
}
elseif (empty($whereString))
{
$quickSearchQuery .= ' venueID IN (SELECT venueID FROM venues WHERE ' . $cityStateZipQueryString . ')';
}
elseif (empty($cityStateZipQueryString))
{
$quickSearchQuery .= ' (' .$whereString . ') ';
}
$quickSearchQuery .= ' ORDER BY title ASC';
echo $quickSearchQuery . '<br /><br />';selected a value for event category only (pulldown returning an integer) -> query worked ( the event sub-category is automatically chosen)
entered a string value "a" for the URL (wibsite) -> query worked
selected/enter values for all three fields -> query worked
enter a value for title only ->
"ERROR 1: 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 '' at line 1."
Below is the result of the echo shown above:
"SELECT eventID, venueID, title, summary, eventType, eventSubtype, eventStartDate, eventStartTime, admissionPrice FROM events WHERE (title LIKE '%a%') ORDER BY title ASC"
Thanks in advance for any suggestions.
Peter -