Puzzling PHP / MySQL problem.

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
peterhf
Forum Newbie
Posts: 4
Joined: Tue Aug 31, 2004 12:30 am
Location: Pasadena, CA

Puzzling PHP / MySQL problem.

Post by peterhf »

The following code is giving me problems:

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 />';
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 -
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

Looks good to me, though I've never personally used parentheses around a single WHERE condition like that.

I'd suggest putting some \n (newline) characters in your query, so that when you get the error message back, when it tells you the error line number, you'll know more specifically where to look.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Post by califdon »

The clue is in the error message:
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."
I don't see anything in your code that includes the phrase " at line 1." That invariably means that you have omitted a quote mark someplace and that PHP is trying to parse something that you didn't intend it to. Go over your entire script, searching for unbalanced quote marks. A good editor that highlights code in color is an enormous time saver.
Post Reply