[SOLVED] error in syntax

[kahodges]

I'm working on building an employee database with php 5 and mysql 5.0. I'm getting this 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 ') LEFT OUTER JOIN `employees` AS lp1 ON (t1.`gevity_number` = lp1.) LEFT OUTER J' at line 1

 

Here is the code surrounding the offending error:

 

$filterstr = sqlstr($filter);
  if (!$wholeonly && isset($wholeonly) && $filterstr!='') $filterstr = "%" .$filterstr ."%";
  $sql = "SELECT * FROM (SELECT t1.`employee_name`, lp0.`employee_name` AS `lp_employee_name`, t1.`gevity_number`, lp1.`GevityNo` AS `lp_gevity_number`, t1.`reference_number`, t1.`claim_number`, t1.`date_entered`, t1.`accident_date`, t1.`accident_time`, t1.`accident_location`, lp7.`location_name` AS `lp_accident_location`, t1.`accident_description`, t1.`start_time_lost`, t1.`end_time_lost`, t1.`first_treat_physician`, t1.`first_treat_phy_phone`, t1.`first_treat_phy_address`, t1.`first_fax`, t1.`primary_treat_center`, t1.`primary_address`, t1.`primary_phone`, t1.`primary_fax`, t1.`claim_adj_name`, t1.`claim_adj_address`, t1.`claim_adj_phone`, t1.`claim_adj_email`, t1.`claim_adj_fax` FROM `accidents` AS t1 LEFT OUTER JOIN `employees` AS lp0 ON (t1.`employee_name` = lp0.) LEFT OUTER JOIN `employees` AS lp1 ON (t1.`gevity_number` = lp1.) LEFT OUTER JOIN `location` AS lp7 ON (t1.`accident_location` = lp7.)) subq";
  if (isset($filterstr) && $filterstr!='' && isset($filterfield) && $filterfield!='') {
    $sql .= " where " .sqlstr($filterfield) ." like '" .$filterstr ."'";
  } elseif (isset($filterstr) && $filterstr!='') {
    $sql .= " where (`lp_employee_name` like '" .$filterstr ."') or (`reference_number` like '" .$filterstr ."') or (`claim_number` like '" .$filterstr ."') or (`accident_date` like '" .$filterstr ."') or (`accident_time` like '" .$filterstr ."') or (`lp_accident_location` like '" .$filterstr ."') or (`accident_description` like '" .$filterstr ."') or (`start_time_lost` like '" .$filterstr ."') or (`end_time_lost` like '" .$filterstr ."') or (`first_treat_physician` like '" .$filterstr ."') or (`first_treat_phy_phone` like '" .$filterstr ."') or (`first_treat_phy_address` like '" .$filterstr ."') or (`first_fax` like '" .$filterstr ."') or (`primary_treat_center` like '" .$filterstr ."') or (`primary_address` like '" .$filterstr ."') or (`primary_phone` like '" .$filterstr ."') or (`primary_fax` like '" .$filterstr ."') or (`claim_adj_name` like '" .$filterstr ."') or (`claim_adj_address` like '" .$filterstr ."') or (`claim_adj_phone` like '" .$filterstr ."') or (`claim_adj_email` like '" .$filterstr ."') or (`claim_adj_fax` like '" .$filterstr ."')";
  }
  if (isset($order) && $order!='') $sql .= " order by `" .sqlstr($order) ."`";
  if (isset($ordtype) && $ordtype!='') $sql .= " " .sqlstr($ordtype);
  $res = mysql_query($sql, $conn) or die(mysql_error());
  return $res;
}

 

Any help would be most appreciated.

Thanks in advance.

[celsoendo]

Can you print the $sql variable just after all the ifs and elses?

[kahodges]

I'm assuming you mean the line with the offending code? It's below:

 

$sql = "SELECT * FROM (SELECT t1.`employee_name`, lp0.`employee_name` AS `lp_employee_name`, t1.`gevity_number`, lp1.`GevityNo` AS `lp_gevity_number`, t1.`reference_number`, t1.`claim_number`, t1.`date_entered`, t1.`accident_date`, t1.`accident_time`, t1.`accident_location`, lp7.`location_name` AS `lp_accident_location`, t1.`accident_description`, t1.`start_time_lost`, t1.`end_time_lost`, t1.`first_treat_physician`, t1.`first_treat_phy_phone`, t1.`first_treat_phy_address`, t1.`first_fax`, t1.`primary_treat_center`, t1.`primary_address`, t1.`primary_phone`, t1.`primary_fax`, t1.`claim_adj_name`, t1.`claim_adj_address`, t1.`claim_adj_phone`, t1.`claim_adj_email`, t1.`claim_adj_fax` FROM `accidents` AS t1 LEFT OUTER JOIN `employees` AS lp0 ON (t1.`employee_name` = lp0.) LEFT OUTER JOIN `employees` AS lp1 ON (t1.`gevity_number` = lp1.) LEFT OUTER JOIN `location` AS lp7 ON (t1.`accident_location` = lp7.)) subq";

 

Here is just the area of the above line showing up as the error:

 

AS t1 LEFT OUTER JOIN `employees` AS lp0 ON (t1.`employee_name` = lp0.) LEFT OUTER JOIN `employees` AS lp1 ON (t1.`gevity_number` = lp1.) LEFT OUTER JOIN `location` AS lp7 ON (t1.`accident_location` = lp7.)) subq";

 

Here is the entire section of code:

function sql_select()
{
  global $conn;
  global $order;
  global $ordtype;
  global $filter;
  global $filterfield;
  global $wholeonly;

  $filterstr = sqlstr($filter);
  if (!$wholeonly && isset($wholeonly) && $filterstr!='') $filterstr = "%" .$filterstr ."%";
  $sql = "SELECT * FROM (SELECT t1.`employee_name`, lp0.`employee_name` AS `lp_employee_name`, t1.`gevity_number`, lp1.`GevityNo` AS `lp_gevity_number`, t1.`reference_number`, t1.`claim_number`, t1.`date_entered`, t1.`accident_date`, t1.`accident_time`, t1.`accident_location`, lp7.`location_name` AS `lp_accident_location`, t1.`accident_description`, t1.`start_time_lost`, t1.`end_time_lost`, t1.`first_treat_physician`, t1.`first_treat_phy_phone`, t1.`first_treat_phy_address`, t1.`first_fax`, t1.`primary_treat_center`, t1.`primary_address`, t1.`primary_phone`, t1.`primary_fax`, t1.`claim_adj_name`, t1.`claim_adj_address`, t1.`claim_adj_phone`, t1.`claim_adj_email`, t1.`claim_adj_fax` FROM `accidents` AS t1 LEFT OUTER JOIN `employees` AS lp0 ON (t1.`employee_name` = lp0.) LEFT OUTER JOIN `employees` AS lp1 ON (t1.`gevity_number` = lp1.) LEFT OUTER JOIN `location` AS lp7 ON (t1.`accident_location` = lp7.)) subq";
  if (isset($filterstr) && $filterstr!='' && isset($filterfield) && $filterfield!='') {
    $sql .= " where " .sqlstr($filterfield) ." like '" .$filterstr ."'";
  } elseif (isset($filterstr) && $filterstr!='') {
    $sql .= " where (`lp_employee_name` like '" .$filterstr ."') or (`reference_number` like '" .$filterstr ."') or (`claim_number` like '" .$filterstr ."') or (`accident_date` like '" .$filterstr ."') or (`accident_time` like '" .$filterstr ."') or (`lp_accident_location` like '" .$filterstr ."') or (`accident_description` like '" .$filterstr ."') or (`start_time_lost` like '" .$filterstr ."') or (`end_time_lost` like '" .$filterstr ."') or (`first_treat_physician` like '" .$filterstr ."') or (`first_treat_phy_phone` like '" .$filterstr ."') or (`first_treat_phy_address` like '" .$filterstr ."') or (`first_fax` like '" .$filterstr ."') or (`primary_treat_center` like '" .$filterstr ."') or (`primary_address` like '" .$filterstr ."') or (`primary_phone` like '" .$filterstr ."') or (`primary_fax` like '" .$filterstr ."') or (`claim_adj_name` like '" .$filterstr ."') or (`claim_adj_address` like '" .$filterstr ."') or (`claim_adj_phone` like '" .$filterstr ."') or (`claim_adj_email` like '" .$filterstr ."') or (`claim_adj_fax` like '" .$filterstr ."')";
  }
  if (isset($order) && $order!='') $sql .= " order by `" .sqlstr($order) ."`";
  if (isset($ordtype) && $ordtype!='') $sql .= " " .sqlstr($ordtype);
  $res = mysql_query($sql, $conn) or die(mysql_error());
  return $res;
}

 

Here is the sql for the two tables the code is trying to left outer join:

-- Table structure for table `accidents`
--

CREATE TABLE IF NOT EXISTS `accidents` (
  `employee_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `gevity_number` varchar(12) COLLATE utf8_unicode_ci DEFAULT NULL,
  `reference_number` varchar(25) COLLATE utf8_unicode_ci DEFAULT NULL,
  `claim_number` varchar(25) COLLATE utf8_unicode_ci DEFAULT NULL,
  `date_entered` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `accident_date` date DEFAULT NULL,
  `accident_time` time DEFAULT NULL,
  `accident_location` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `accident_description` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `start_time_lost` date DEFAULT NULL,
  `end_time_lost` date DEFAULT NULL,
  `first_treat_physician` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `first_treat_phy_phone` varchar(12) COLLATE utf8_unicode_ci DEFAULT NULL,
  `first_treat_phy_address` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `first_fax` varchar(12) COLLATE utf8_unicode_ci DEFAULT NULL,
  `primary_treat_center` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `primary_address` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `primary_phone` varchar(12) COLLATE utf8_unicode_ci DEFAULT NULL,
  `primary_fax` varchar(12) COLLATE utf8_unicode_ci DEFAULT NULL,
  `claim_adj_name` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `claim_adj_address` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `claim_adj_phone` varchar(12) COLLATE utf8_unicode_ci DEFAULT NULL,
  `claim_adj_email` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `claim_adj_fax` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  UNIQUE KEY `employee_name` (`employee_name`),
  UNIQUE KEY `reference_number` (`reference_number`,`claim_number`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `accidents`
--

CREATE TABLE IF NOT EXISTS `employees` (
  `GevityNo` varchar(10) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `employee_name` varchar(75) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Sex` varchar(7) COLLATE utf8_unicode_ci NOT NULL,
  `MaritalStatus` varchar(11) CHARACTER SET latin1 NOT NULL DEFAULT '',
  `pr_deductions` varchar(13) CHARACTER SET latin1 DEFAULT NULL,
  `nextel` varchar(13) CHARACTER SET latin1 DEFAULT NULL,
  `ssNumber` varchar(13) CHARACTER SET latin1 DEFAULT NULL,
  `InsTypes` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `BirthDate` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
  `Hire_Date` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
  `QuitDate` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
  `PayRate` varchar( COLLATE utf8_unicode_ci DEFAULT NULL,
  `eeoc` varchar(75) COLLATE utf8_unicode_ci DEFAULT NULL,
  `street_address` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `City` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `state` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `zip_code` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `home_phone` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `cellphonenum` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL,
  `dlnum` varchar(30) CHARACTER SET latin1 DEFAULT '',
  `Lic_expires` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
  `DOT_expiration` varchar(12) COLLATE utf8_unicode_ci DEFAULT NULL,
  `DLclass` varchar(12) CHARACTER SET latin1 DEFAULT NULL,
  `updatedon` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  `updatedby` varchar(255) CHARACTER SET latin1 DEFAULT '',
  UNIQUE KEY `GevityNo` (`GevityNo`),
  UNIQUE KEY `employee_name` (`employee_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

 

Again, thanks.

[akitchin]

i see a few issues. first, you never close the parenthesis on that first subquery. second, you have a several JOIN conditions that simply end with a table and no field:

 

ON (t1.`employee_name` = lp0.) LEFT OUTER JOIN `employees` AS lp1 ON (t1.`gevity_number` = lp1.)

 

lp0.what? lp1.what?

[kahodges]

Got it. It's working now. Thanks.