By JimK on

Just installed and configured Taxonomy block. When I enable it in Administer/Blocks and go to a page I get:
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 ') AND n.status = 1 ORDER BY sticky DESC, created DESC LIMIT 5' query: SELECT DISTINCT(n.nid), n.title, n.body FROM term_node t INNER JOIN node n ON t.nid = n.nid WHERE t.tid IN () AND n.status = 1 ORDER BY sticky DESC, created DESC LIMIT 5 in /home/cpnhelp/www/www/includes/database.mysql.inc on line 66.

Anyone know what I should do? I'm a database novice (obviously!).

Comments

rjl’s picture

rjl commented

Well the problem with the SQL is with

WHERE t.tid IN ()

Their needs to be one or more comma-separted tid values between the parenthesis (each one of your categories or vocabulary terms has a tid).

So it appears that the module didn't pick up any categories. Why, is of course the question. I installed the module and get the same results as you, so this problem is not unique to you.

I looked at the code and it appears the suspect SQL is generated on line 231

$nodes = db_query(db_rewrite_sql('SELECT DISTINCT(n.nid), n.title, n.body FROM {term_node} t INNER JOIN {node} n ON t.nid = n.nid WHERE t.tid IN (%s) AND n.status = 1 ORDER BY sticky DESC, created DESC LIMIT %d'), implode($tids, ','), $result->length);

implode($tids, ',') should produce the appropriate tids, but as we know, it does not.

Not really sure what to do at this point. The preceding code that is supposed to geneated the tids seems ok...

$tids = array();
  $result = db_fetch_object(db_query('SELECT * FROM {taxonomy_block} WHERE bid = %d', $bid));
  if ($result) {
    if ($result->type=='term') {
      $tids[] = $result->tid;
    }
    else {
      $tree = taxonomy_get_tree($result->tid);
      foreach ($tree as $term) {
        $tids[] = $term->tid;
      }
    }

Sorry I am not much help...

rjl’s picture

rjl commented

Not being satisfied with my previous response I decided to see what else could be going on....

I checked the taxonomy_block table and the problem screamed at me...

The module allows for either an entire vocabulary to be chosen or a single term. The choise we make is stored in a field named TYPE. Note the case, it is very important.

When the data is gatherd it requests a value from type (lowercase) not TYPE (uppercase) in the following code...

    if ($result->type=='term') {

The field type is not found, thus it cannot be equal to 'term' thus the next line of code (which would have given us our tid) is skipped, leaving us with some bad SQL (There should have been a check for this in the code, but that is another thing.) I hope I am making some sense...

So, you could change the code to ... (change case of the word type to TYPE)

    if ($result->TYPE=='term') {

That works, but is probably not the best solution...

Instead change the field name in the database....
I changed the field name in the database itself to type (lowercase) and that worked fine. I checked the code and I didn't find any occurances of TYPE so changing the field name shouldn't create an issue elsewhere.

So, I hope this helps...

BTW - I don't get a list of nodes as I was expecting would appear in the block, but at least the SQL error is gone. I guess some other fix will be needed for that.

JimK’s picture

JimK commented

I'm deeply appreciative of how fast I got a response to my post, and in how much depth you searched into the code. Thanks so much.

I seem to have it fixed now. It appears to be simply a matter of needing subcatagories in the selected taxonomy for the box. I had made a "News" taxonomy, but had nothing under it. After I deactivated the Taxonomy block, added catagories like Editorial, New Content, Commentary to the News catagory, and reactivated, then posted a test page, no more errors.

Your finding of the case change issue may be more an issue when no type is found, ie when no appropriate categories existed to list in the block. If it recurrs, I'll try the code change you suggested.
Thanks,
Jim

www.cpnhelp.org

kunilkud’s picture

kunilkud commented

I dont think the cause is in the taxonomy_block module.

It only happens when you define mutiple/single hierarchy taxonomy, but you don't define the term/sub-taxonomy of it yet.

Any taxonomy that has defined as multiple/single hierarchy MUST HAVE its child..Otherwise the taxonomy_block will generate SQL error fetching undefined hierarchy

-kunil
--the world can do without me, but not without you