MySQL syntax error

mike123

Hello,

If I'm using:

$last3 = $db->CacheGetAll("SELECT * FROM PLD_LINK WHERE `STATUS` = 2 AND `CATEGORY_ID` = 12 ORDER BY HITS DESC LIMIT 0,3");
$tpl->assign('last3', $last3);

Everything is ok and displays the results fine.
What I want to do is to extract the last 3 rows from the field CATEGORY_ID in PLD_LINK table. But CATEGORY_ID is a variable that change on each page where I want to list the data from the rows.

So if I'm using

$last3 = $db->CacheGetAll("SELECT * FROM PLD_LINK WHERE `STATUS` = 2 AND `CATEGORY_ID` = `$CATEGORY_ID` ORDER BY HITS DESC LIMIT 0,3");
$tpl->assign('last3', $last3);

it's not working.

I tried many different options for $CATEGORY_ID but nothing works...
I don't know how to write the right expression to read the variable from the table. Can somebody help me?

I'm using phpld script with smarty.
Thank you.

dagon

is $CATEGORY_ID defined before the query is run?

mike123

No.
Maybe this is what is missing. And I don't know exactly how to write it.
🙁

dagon

mike123;10937219 wrote:
No.
Maybe this is what is missing. And I don't know exactly how to write it.
🙁

Based on the information provided, nor do I.

mike123

What other information do you need dagon?

dagon

how does the page know, what CATEGORY_ID is ?

mike123

Everything is done via smarty.

I will print here index.php and init.php, maybe in this way will be more easy for you to understand how is working...

init.php -> hostxtra[.]info/init.php.txt (the files are too long, both more than 37000 characters, after the problem is solved, you/admin can delete the links...)

With CATEGORY_ID = 12, the code is working but the problem is that each page will show always the same 3 records from category with id=12. Each page (details of the links in directory) belongs to different category.

index.php -> hostxtra[.]info/index.php.txt

Thank you for your time.

dagon

i have never used smarty, i asume CATEGORY_ID is stored in a variable, which you can then use in the mysql

mike123

I don't thing that is related to smarty, is more how to read CATEGORY_ID from the database being on that particular details page and allocate to the 'CATEGORY_ID' in the query. Like you said before, something is missing before query, maybe I need to read 1st the CATEGORY_ID from the row, but I don't know how.

I saw expressions as:
".$db->qstr($id)."
".$db->qstr($this->get_template_vars('CATEGORY_ID'))."
".$db->qstr($row['ID'])."
".$db->qstr($id)."
".$db->qstr($_SESSION['phpld']['category']['id'])."

but I don't know how to implement in this particular situation.

mike123

I will post also the code that I'm using on the details page to show data from the rows. Maybe somebody will give me a solution to try..

{foreach from=$last3 item=link name=links}
<tr>
      <td>
{if !empty($link.URL)}» <a id="id_{$link.ID}" href="{if !$smarty.const.ENABLE_REWRITE}
                                                {$smarty.const.DOC_ROOT}/detail.php?id={$link.ID}
                                             {else}
                                                {$smarty.const.DOC_ROOT}/{$link.CATEGORY_URL|escape|trim}{$link.TITLE|escape|trim|strtolower|replace:' ':'-'}-{$link.ID}.html{/if}" title="{$link.TITLE|escape|trim}"
   {* nofollow *}
   {if $link.NOFOLLOW or ($link.RECPR_VALID eq 0 and ($smarty.const.RECPR_NOFOLLOW eq 2 or ($smarty.const.RECPR_NOFOLLOW eq 1 and $link.RECPR_REQUIRED eq 1)))} rel="nofollow"{/if}
   {if $smarty.const.ENABLE_BLANK} target="_blank"{/if}>
   {$link.TITLE|escape|trim}</a>
   {else}
<span class="link">{$link.TITLE|escape|trim}</span>{/if} 
     </td>
      </tr>
{/foreach}

johanafm

mike123;10937226 wrote:
Everything is done via smarty.

Irrelevant. You'd have to first

$smarty->assign('varname', $value);
//or
$smarty->assign_by_ref('varname', $value);

So if you don't know where a value comes from or where/how it is defined, you still can't assign it to smarty. Also, once you do know $value, there's no point

$smarty->get_template_vars('varname');
// since you could simply
$value;

mike123;10937226 wrote:
With CATEGORY_ID = 12, the code is working but the problem is that each page will show always the same 3 records from category with id=12.

So it would appear you do know where and how to set category_id? Why not substitute 12 with user input, in whatever fashion you prefer: get or post.

And you shouldn't use upper case names for anything but constants. Sure, you're free to use any coding standard you like, but in this case I still recommend changing it.

mike123

The table PLD_LINK has the following fields:
ID
TITLE
DESCRIPTION
URL
CATEGORY_ID
and more...

When a details page is open for a link (like name-site-ID.html (ID is the id from the PLD_LINK table), all the info from the fields related to the row with ID are coming on the page. To print all this info I use {}, for ex. {DESCRIPTION}, and will show what is on that field. Even if I use {CATEGORY_ID}, I can print the number of the category. Inside of the page I can not use php code, the files have extension .tpl (for this reason I said "Everything is done via smarty.")

On my example, if the link was submitted in category 12, if I'm using to code {CATEGORY_ID}, on the description page I will see 12.

All the queries are done on the root in the index.php and init.php files.

If I'm using

$smarty->assign('varname', $value);

on init.php, where the query is, the site is not working at all.

I'm thinking that in order to use {CATEGORY_ID} as a value on the query, I need to declare or to extract this value using another function on init.php file.

Because I understand that the query doesn't know how to read the value {CATEGORY_ID} even I can print it and is there...

And you shouldn't use upper case names for anything but constants. Sure, you're free to use any coding standard you like, but in this case I still recommend changing it.

This is the way the script was created. All capital letters.

mike123

johanafm;10937318 wrote:
So if you don't know where a value comes from or where/how it is defined, you still can't assign it to smarty. Also, once you do know $value, there's no point.

Maybe this is something that will help you to understand how is working and to help me... this is a .php file who is generating the data for the details page where I want to run my query. I didn't realize that is important but now I think it is.

<?php

require_once 'init.php';

//Determine link ID
if (ENABLE_REWRITE == 1)
{
   preg_match ('#(.*)(_|-)(\d+)\.htm[l]?$#i', request_uri(), $matches);

   $id = (!empty ($matches[3]) ? intval ($matches[3]) : 0);
}
else
{
   $id = (!empty ($_REQUEST['id']) ? $_REQUEST['id'] : 0);
}

if (!empty ($id))
{
   if (!preg_match ('`^[\d]+$`', $id))
   {
      $tpl->assign('error', "Invalid Link ID");
   }
   else
   {
      $rdata = $db->CacheGetRow("SELECT *, DATE_FORMAT(DATE_ADDED, '%M %d, %Y %r') AS `DAT` FROM `{$tables['link']['name']}` WHERE `ID` = ".$db->qstr($id));
      $data = array ();
      if (!$rdata)
         $tpl->assign('error', "Invalid ID [{$id}] passed - not found in database");
      else
      {
         $data['ID']           = $id;
         $data['TITLE']        = $rdata['TITLE'];
         $data['DESCRIPTION']  = $rdata['DESCRIPTION'];
         $data['URL']          = $rdata['URL'];
         $data['CATEGORY_ID']  = $rdata['CATEGORY_ID'];
         $data['OWNER_NAME']   = $rdata['OWNER_NAME'];
         $data['DATE_ADDED']   = $rdata['DAT'];
         $data['HITS']         = $rdata['HITS'];
         $data['PAGERANK']     = $rdata['PAGERANK'];
         $data['RATE_COUNT']   = $rdata['RATE_COUNT'];
         $data['RATE']         = $rdata['RATE'];
         $data['RATE_ENABLED'] = $rdata['RATE_ENABLED'];

     $site_url = (substr (SITE_URL, -1) != '/' ? SITE_URL.'/' : SITE_URL);

     do {
        $cdata = $db->GetRow("SELECT `PARENT_ID`, `TITLE` AS `CATEGORY_TITLE`, `CACHE_URL` FROM `{$tables['category']['name']}` WHERE `ID` = '".(empty($parent_id) ? $rdata['CATEGORY_ID'] : $parent_id)."' LIMIT 1");

        $CATEGORY_TITLE[] = $cdata['CATEGORY_TITLE'];
        $CATEGORY_URL[] = $site_url. (ENABLE_REWRITE == 1 ? trim ($cdata['CACHE_URL']) : 'index.php?c='.$cdata['CATEGORY_ID']);

        $parent_id = $cdata['PARENT_ID'];
        } while ($parent_id!=0);

     $tpl->assign('CATEGORY_TITLE', $CATEGORY_TITLE);
     $tpl->assign('CATEGORY_URL', $CATEGORY_URL);

     $data['META_KEYWORDS']    = $rdata['META_KEYWORDS'];
     $data['META_DESCRIPTION'] = $rdata['META_DESCRIPTION'];
     $data['GMAPKEY'] = GMAP_KEY;
     unset ($cdata, $site_url);
     $odata = $db->GetAll("SELECT * FROM `{$tables['link']['name']}` WHERE `OWNER_EMAIL` LIKE ".$db->qstr($rdata['OWNER_EMAIL'])." LIMIT 0 , 30");

     usort ($odata, "cmp");
     $data['RELATED'] = "";
     foreach ($odata as $value)
        if ($value['ID'] !== $id)
        {
           if (preg_match ("/\/$/", SITE_URL))
              $link = SITE_URL."details.php?id=".$value['ID'];
           else
              $link = SITE_URL."/details.php?id=" . $value['ID'];

           $data['RELATED'] .= "<li><a class=\"special\" href=\"{$link}\">{$value['TITLE']}</a></li>\n";
        }

     if($data['RELATED'])
        $data['RELATED'] = "<ul>\n{$data['RELATED']}</ul>\n";

     unset ($odata);
  }
   }
   unset ($rdata);
}
else
   $tpl->assign('error', "No ID parameter");

$path   = array ();
$path[] = array ('ID' => '0', 'TITLE' => _L(SITE_NAME)        , 'TITLE_URL' => DOC_ROOT, 'DESCRIPTION' => SITE_DESC);
$path[] = array ('ID' => '0', 'TITLE' => _L('Details'), 'TITLE_URL' => ''      , 'DESCRIPTION' => _L('Listing Details'));
$tpl->assign('path', $path);
$tpl->assign($data);

$tpl->assign('MetaKeywords'   , (!empty ($data['META_KEYWORDS'])    ? trim ($data['META_KEYWORDS'])    : trim ($MetaKeywords)));
$tpl->assign('MetaDescription', (!empty ($data['META_DESCRIPTION']) ? trim ($data['META_DESCRIPTION']) : trim ($MetaDescription)));

//Clean whitespace
$tpl->load_filter('output', 'trimwhitespace');

//Compress output for faster loading
if (COMPRESS_OUTPUT == 1)
   $tpl->load_filter('output', 'CompressOutput');

//Make output
echo $tpl->fetch('detail.tpl', $id);

unset ($data);

function cmp($a, $b) {
   return strcmp ($a["TITLE"], $b["TITLE"]);
}
?>

I did the modification from

do {
            $cdata = $db->GetRow("SELECT `PARENT_ID`, `TITLE` AS `CATEGORY_TITLE`, `CACHE_URL`, `CATEGORY_ID` FROM `{$tables['category']['name']}` WHERE `ID` = '".(empty($parent_id) ? $rdata['CATEGORY_ID'] : $parent_id)."' LIMIT 1");

        $CATEGORY_TITLE[] = $cdata['CATEGORY_TITLE'];
        $CATEGORY_URL[] = $site_url. (ENABLE_REWRITE == 1 ? trim ($cdata['CACHE_URL']) : 'index.php?c='.$cdata['CATEGORY_ID']);

        $parent_id = $cdata['PARENT_ID'];
        } while ($parent_id!=0);

     $tpl->assign('CATEGORY_TITLE', $CATEGORY_TITLE);
     $tpl->assign('CATEGORY_URL', $CATEGORY_URL);

do {
            $cdata = $db->GetRow("SELECT `PARENT_ID`, `TITLE` AS `CATEGORY_TITLE`, `CACHE_URL`, `CATEGORY_ID` FROM `{$tables['category']['name']}` WHERE `ID` = '".(empty($parent_id) ? $rdata['CATEGORY_ID'] : $parent_id)."' LIMIT 1");

        $CATEGORY_TITLE[] = $cdata['CATEGORY_TITLE'];
        $CATEGORY_URL[] = $site_url. (ENABLE_REWRITE == 1 ? trim ($cdata['CACHE_URL']) : 'index.php?c='.$cdata['CATEGORY_ID']);

	$category_id = $cdata['CATEGORY_ID'];

        $parent_id = $cdata['PARENT_ID'];
        } while ($parent_id!=0);

     $tpl->assign('CATEGORY_TITLE', $CATEGORY_TITLE);
     $tpl->assign('CATEGORY_URL', $CATEGORY_URL);

and use in the query

$last3 = $db->CacheGetAll("SELECT * FROM `{$tables['link']['name']}` WHERE `STATUS` = 2 AND `CATEGORY_ID` = `$category_id` ORDER BY HITS DESC LIMIT 0,3");
$tpl->assign('last3', $last3);

it's not working.

Also I did another modification

do {
            $cdata = $db->GetRow("SELECT `PARENT_ID`, `TITLE` AS `CATEGORY_TITLE`, `CACHE_URL`, `CATEGORY_ID` FROM `{$tables['category']['name']}` WHERE `ID` = '".(empty($parent_id) ? $rdata['CATEGORY_ID'] : $parent_id)."' LIMIT 1");

        $CATEGORY_TITLE[] = $cdata['CATEGORY_TITLE'];
        $CATEGORY_URL[] = $site_url. (ENABLE_REWRITE == 1 ? trim ($cdata['CACHE_URL']) : 'index.php?c='.$cdata['CATEGORY_ID']);

     $CATEGORY_ID[] = $cdata['CATEGORY_ID'];

        $parent_id = $cdata['PARENT_ID'];
        } while ($parent_id!=0);

     $tpl->assign('CATEGORY_TITLE', $CATEGORY_TITLE);
     $tpl->assign('CATEGORY_URL', $CATEGORY_URL);

 $tpl->assign('CATEGORY_ID', $CATEGORY_ID);

and use:

$last3 = $db->CacheGetAll("SELECT * FROM `{$tables['link']['name']}` WHERE `STATUS` = 2 AND `CATEGORY_ID` = `$CATEGORY_ID` ORDER BY HITS DESC LIMIT 0,3");
$tpl->assign('last3', $last3);

It's still not working. Any other idea?

Thank you.