asked on

How to I fix this SQL error?

When saving a new content post using the Drupal CMS, I get the following warning and don't know how to resolve the issue. Please help.

Thanks!

* user warning: 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 '= n.nid WHERE ((n.moderate != 1 OR n.uid = 1779)) AND ( nid = 0)' at line 1 query: SELECT tid FROM term_node LEFT JOIN node n ON .nid = n.nid WHERE ((n.moderate != 1 OR n.uid = 1779)) AND ( nid = 0) in /var/www/vhosts/domain.com/httpdocs/sites/all/modules/taxonomy_menu/taxonomy_menu.database.inc on line 78.
* user warning: 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 'nid = n.nid WHERE ((n.moderate != 1 OR n.uid = 1779)) AND ( nid = 0)' at line 1 query: SELECT tid FROM term_node LEFT JOIN node n ON 20.nid = n.nid WHERE ((n.moderate != 1 OR n.uid = 1779)) AND ( nid = 0) in /var/www/vhosts/domain.com/httpdocs/sites/all/modules/taxonomy_menu/taxonomy_menu.database.inc on line 78.

<?php 
// $Id: taxonomy_menu.database.inc,v 1.1.2.8 2009/03/22 15:13:00 indytechcook Exp $ 
/**
 * @file
 * Database functions
 * 
 * @author Neil Hastings      <http://drupal.org/user/245817>
 * @author Mark Theunissen    <http://drupal.org/user/108606>
 * @author Afief Halumi       <http://drupal.org/user/237472>
 */ 
 /**
 *  helper function to insert a menu item
 *
 * @param $mlid
 * @param $tid
 * @param $vid
 */
function _taxonomy_menu_insert_menu_item($mlid, $tid, $vid) {
  db_query('INSERT INTO {taxonomy_menu} (mlid, tid, vid) VALUES (%d, %d, %d)', $mlid, $tid, $vid);
} 
/**
 * Return the corresponding menu link id.
 *
 * @param $tid
 *   the term's id
 */
function _taxonomy_menu_get_mlid($tid, $vid) {
  return db_result(db_query('SELECT mlid FROM {taxonomy_menu} WHERE tid = %d AND vid = %d', $tid, $vid));
} 
/**
 * Retrieve the term / menu relations for a vocab.
 *
 * @param $vid
 *   vocabulary's id
 * @return
 *   array(tid => mlid)
 */
function _taxonomy_menu_get_menu_items($vid) { 
  $result = db_query('SELECT mlid, tid FROM {taxonomy_menu} WHERE vid = %d', $vid);
  $menu_items = array();
  while ($data = db_fetch_object($result)) {
    $menu_items[$data->tid] = $data->mlid;
  }
  return $menu_items;
} 
 /**
  * Delete all links associated with this vocab from both the taxonomy_menu
  * table and the menu_link table.
  *
  * @param $vid
  *   vocabulary's id
  */
function _taxonomy_menu_delete_all($vid) {
  $menu_terms = _taxonomy_menu_get_menu_items($vid);
  if (!empty($menu_terms)) {
    foreach ($menu_terms as $tid => $mlid) {
      db_query('DELETE FROM {menu_links} WHERE mlid = %d', $mlid);
    }
    db_query('DELETE FROM {taxonomy_menu} WHERE vid = %d', $vid);
  }
} 
/**
 * Get an array of the tid's related to the node
 * 
 * @param $nid
 * @return array of tids
 */
function _taxonomy_menu_get_node_terms($nid) {
  $output = array();
  $result = db_query(db_rewrite_sql('SELECT tid FROM {term_node} WHERE nid = %d', $nid));
  while ($data = db_fetch_object($result)) {
    $output[] = $data->tid;
  }
  return $output;
} 
/**
 * Get an array of the tid's from the parent
 * 
 * @param $tid
 * @return array of tid
 */
function _taxonomy_menu_get_parents($tid) {
  $output = array();
  $result = taxonomy_get_parents($tid);
  foreach ($result as $key => $item) {
    $output[] = $key;
  }
  return $output;  
} 
/**
  * Delete all rows from {taxomony_menu} associated with this tid
  *
  * @param $vid
  * @param $tid
  */
function _taxonomy_menu_delete_item($vid, $tid) {
  db_query('DELETE FROM {taxonomy_menu} WHERE vid = %d AND tid = %d', $vid, $tid);
} 
/**
 * Get all of the tid for a given vid
 *
 * @param $vid
 * @return array of $tid
 */
function _taxonomy_menu_get_terms($vid) {
  $output = array();
  $result = db_query(db_rewrite_sql('SELECT tid FROM {term_data} WHERE vid = %d', $vid));
  while ($data = db_fetch_object($result)) {
    $output[] = $data->tid;
  }
  return $output;
} 
/**
 * used to get the count without children
 * 
 * @param $tid
 */
function _taxonomy_menu_term_count($tid) {
  return db_result(db_query(db_rewrite_sql('SELECT COUNT(n.nid) AS c FROM {term_node} t INNER JOIN {node} n ON t.vid = n.vid WHERE n.status = 1 AND t.tid = %d', $tid)));
}

Open in new window

jb1dev

There is no query on line 78 (it's a comment)
And it doesn't appear to be anywhere else in the since I don't see any LEFT JOIN anywhere either.

p_nuts

there's 2 queries both wrong...
the first ...
left join node n on .nid
should be on term_node.nid ....
and don't forget to classify a table nid =0 should be term_node.nid = 0

the 2nd
same deal except 20.nid should be term_node.nid

LindsayC

ASKER

Lines 76 - 83 are below. I don't know why the code snippet in Experts Exchange deletes blank lines when pasting code.

I also don't see "LEFT JOIN" in the original line 78 or n on .nid.:

$result = db_query(db_rewrite_sql('SELECT tid FROM {term_node} WHERE nid = %d', $nid));

I'm not real bright when it comes to this stuff... I don't really understand your comment. Please try explaining again with more detail for someone who doesn't know SQL at all or and is a PHP beginner.

Thanks!

function _taxonomy_menu_get_node_terms($nid) {
  $output = array();
  $result = db_query(db_rewrite_sql('SELECT tid FROM {term_node} WHERE nid = %d', $nid));
  while ($data = db_fetch_object($result)) {
    $output[] = $data->tid;
  }
  return $output;
}

Open in new window

asafadis

Your problem is actually in line #119.

@p_nuts had the right idea.

/* Correct SQL Statement */
SELECT
  tid
FROM
  term_node t
  LEFT JOIN node n ON (t.nid = n.nid)
WHERE
  ((n.moderate != 1 OR n.uid = 1779))
  AND (t.nid = 0);

Open in new window

ASKER CERTIFIED SOLUTION

LindsayC

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial