[SOLVED] Problem with mySQL in User Defined Tag - Need help

[nhaack]

Hi together,

I want to join (inner join) two table with a mysql query.  I want it to return all content blocks of the name "page summary" (additional content block in template) of pages underneath a specific content_id. 

The idea is to use this tag to create an overview (index template) of underlying pages, based on what is stated on the single pages second content block "summary".

When I am in the db management and I run the following query, I get just the result i want: 

Code: Select all

SELECT parent_id, content_alias, show_in_menu, active, cms_content_props.content_id, prop_name, content
FROM cms_content, cms_content_props
WHERE cms_content.content_id = cms_content_props.content_id AND prop_name="summary" AND parent_id= 34 

Full of enthusiasm, I went to create a UDT. I looked at the recently_updated plugin since it seemed as a good starting point (as I am not quite a coder). So, to execute the query, I came up with the follwoing in my UDT:

Code: Select all

1: global $gCms;
2: 
3: $db = &$gCms->db;
4: 
5: $q= "SELECT parent_id, content_alias, show_in_menu, active, ".cms_db_prefix()."content_props.content_id, prop_name, content
6: FROM ".cms_db_prefix()."content, ".cms_db_prefix()."content_props 
7: WHERE ".cms_db_prefix()."content.content_id = ".cms_db_prefix()."content_props.content_id AND prop_name="summary" AND parent_id= 36";
8: 
9: $dbresult = $db->Execute( $q);
...

But when I try to save, I get the following notification: 

    * Invalid code entered.
    * Parse error: parse error, unexpected T_STRING in /homepages/3/d116996210/htdocs/opticalvalve/content/admin/edituserplugin.php(105) : eval()'d code on line 7

I already spent quite a few hours finding out what causes this , but I still haven't figured out the problem. Does anyone of you have a tipp on how to solve this problem? Or what I have to look for to find the answer?

CMS Made Simple 1.1
Apache            1.3.33 (Unix)
MySQL              4.0.25
PHP Version        4.4.7

Greetings from Hamburg
Nils

[nhaack]

mhh... now as I look at my post, I think the reason will most like be, that the servers mySQL 4.0.25 is not capable of subqueries (or how they are called). I was testing the query locally ... this would explain why the UDT failed online.

[calguy1000]

Code: Select all

WHERE ".cms_db_prefix()."content.content_id = ".cms_db_prefix()."content_props.content_id AND prop_name="summary" AND parent_id= 36";

OR.... you've just got a syntax error (or 3) in your UDT.

Finding them is left as an excercise to the user.  I can see them.

[nhaack]

Hi calguy,

hehe... the joy after having found the problem. I should have used ' instead of " (at least I think that's what went wrong). I was close to giving up on my idea. But now I did it 

I came up with a little User Defined Tag I'd like to share. Even though it is not completely done yet (no styling... just output), it could probably be of any help for other beginner.

Greetings
Nils

// What this UDT does:
// It finds out the pages which lie underneath a specific content_id (parameter: childrenof).
// It then loads the specified content block (parameter: blockname) and prints out the content
// blocks modified_date, the owning content page title, the specified content block and a link
// to the single page.
//
// For example: {load_teaser childrenof=19 blockname="Summary"} will output the content of
// {content block="Summary"} of all pages which parent page is pageid=19
//
// This is just a poorly tested scribble, but it works for me! Use at own risk!
// enjoy Nils Haack
//
// Inspired by recently_updated (Elijah Lofgren) and the user handbook

global $gCms;

$hm =& $gCms->GetHierarchyManager();
$db = &$gCms->db;


$q = "SELECT parent_id, content_name, content_alias, show_in_menu, active, ".cms_db_prefix()."content_props.content_id, prop_name, ".cms_db_prefix()."content_props.modified_date, content
FROM ".cms_db_prefix()."content, ".cms_db_prefix()."content_props
WHERE ".cms_db_prefix()."content.content_id = ".cms_db_prefix()."content_props.content_id
AND prop_name = '$params[blockname]'
AND parent_id = $params[childrenof]
AND active = 1
AND show_in_menu = 1";

$dbresult = $db->Execute( $q );

if( !$dbresult )
{
    echo 'DB error: '. $db->ErrorMsg()."";
}

while ($dbresult && $pagetotease = $dbresult->FetchRow())
{
    $curnode =& $hm->getNodeById($pagetotease['content_id']);
    $curcontent =& $curnode->GetContent();

    $output .= $pagetotease['modified_date'];
    $output .= '
';

    $output .= $pagetotease['content_name'];
    $output .= '
';

    $output .= $pagetotease['content'];
    $output .= '
';

    $output .= 'GetURL().'">'.$pagetotease['content_name'].' detail page';
    $output .= '';
}

echo $output;

[calguy1000]

it sounds like a useful udt.... but I would have
a) put all of the output fields into a StdClass object
b) assigned the stdclass object to a smarty variable, or returned it
    (then people can display what they want, use smarty logic, and style it any way they want)

I took the liberty of tweaking it (though I didn't test it):

Code: Select all

// What this UDT does:
// It finds out the pages which lie underneath a specific content_id (parameter: childrenof).
// It then loads the specified content block (parameter: blockname) and prints out the content
// blocks modified_date, the owning content page title, the specified content block and a link
// to the single page.
//
// For example: {load_teaser childrenof=19 blockname="Summary"} will output the content of
// {content block="Summary"} of all pages which parent page is pageid=19
//
// This is just a poorly tested scribble, but it works for me! Use at own risk!
// enjoy Nils Haack
//
// Inspired by recently_updated (Elijah Lofgren) and the user handbook

global $gCms;

$hm =& $gCms->GetHierarchyManager();
$db = &$gCms->db;


$q = "SELECT parent_id, content_name, content_alias, show_in_menu, active, ".cms_db_prefix()."content_props.content_id, prop_name, ".cms_db_prefix()."content_props.modified_date, content
FROM ".cms_db_prefix()."content, ".cms_db_prefix()."content_props
WHERE ".cms_db_prefix()."content.content_id = ".cms_db_prefix()."content_props.content_id
AND prop_name = '$params[blockname]'
AND parent_id = $params[childrenof]
AND active = 1
AND show_in_menu = 1";

$dbresult = $db->Execute( $q );

if( !$dbresult )
{
    echo 'DB error: '. $db->ErrorMsg()."<br/>";
}

$output = array();
while ($dbresult && $pagetotease = $dbresult->FetchRow())
{
    $curnode =& $hm->getNodeById($pagetotease['content_id']);
    $curcontent =& $curnode->GetContent();

    $obj = new StdClass;
    $obj->modified_date = $pagetotease['modified_date'];
    $obj->name = $pagetotease['content_name'];
    $obj->content = $pagetotease['content'];
    $obj->link = '<a href="' . $curcontent->GetURL().'">'.$pagetotease['content_name'].' detail page</a>';
    $output[] = $obj;
}

if( isset($params['assign']) )
  {
     /* assign the output object to a smarty variable */
     $smarty =& $gCms->GetSmarty();
     $smarty->assign($params['assign'], $output);
     return;
  }

return $output;