By Canadrian-1 on

I'm using the front_page.module to create different pages for anonymous and logged-in users. The anonymous one is easy - just a static message in HTML. The autheticated users one uses snippets, and I've run into a glitch because I only pretend to know anything at all about php and mysql. :)

The snippets I'm using greet the user and wish them a happy birthday if appropriate, count down the days until Christmas, and then presents a list of nodes with summaries (so basically it lets them see all the latest site content). But I only want to show nodes which have been promoted to front - essentially the same behaviour as just visiting /node. Here's the (modified) snippet I'm using for the node display - can anyone point me in the right direction for limiting the content to only promoted nodes?

<?php // WHATSUP
/**
* This php snippet displays content of a specified type, with teasers
*
* To change the type of content listed, change the $content_type.
*
* Works with drupal 4.6
*
* (Modified to show ALL  content by removing references to $content_type)
*/
  $result1 = pager_query(db_rewrite_sql("SELECT n.nid, n.created FROM {node} n WHERE n.status = 1 ORDER BY n.created DESC"));
  while ($node = db_fetch_object($result1)) {
    $output .= node_view(node_load(array('nid' => $node->nid)), 1);
  }
print $output;
?>
⋅ Categories: Drupal 4.6.x

Comments

gwerker’s picture

gwerker commented

It looks like you should just be able to add AND n.promoted = 1 to your WHERE clause. Or if you're using sticky, and only want those, then put AND n.sticky = 1.

Canadrian-1’s picture

Canadrian-1 commented

Hmm... nope. I get the following two errors:

user error: Unknown column 'n.promoted' in 'where clause'
query: SELECT COUNT(*) FROM node n WHERE n.status = 1 AND n.promoted = 1  in /xxx/xxx/xxx/xxx/includes/database.mysql.inc on line 66.
user error: Unknown column 'n.promoted' in 'where clause'
query: SELECT DISTINCT(n.nid), n.created FROM node n WHERE n.status = 1 AND n.promoted = 1 ORDER BY n.created DESC LIMIT 0, 10 in /xxx/xxx/xxx/xxx/includes/database.mysql.inc on line 66.

Just to be totally transparent, I changed this line:

$result1 = pager_query(db_rewrite_sql("SELECT n.nid, n.created FROM {node} n WHERE n.status = 1 ORDER BY n.created DESC"));

to this:

$result1 = pager_query(db_rewrite_sql("SELECT n.nid, n.created FROM {node} n WHERE n.status = 1 AND n.promoted = 1 ORDER BY n.created DESC"));

Ideas?

----------
Dissecting literature is a lot like dissecting a cat; it's a grisly, meticulous, and above all, tedious operation, at the conclusion of which one is left with a repulsive mess where once there was an unadulterated and altogether much more desirable whole.

gwerker’s picture

gwerker commented

I checked the node table again, and it looks like the column is called promote, not promoted. Try that.

Canadrian-1’s picture

Canadrian-1 commented

Gwerker, you are a saint. Thanks so much!

----------
Dissecting literature is a lot like dissecting a cat; it's a grisly, meticulous, and above all, tedious operation, at the conclusion of which one is left with a repulsive mess where once there was an unadulterated and altogether much more desirable whole.

matt@antinomia’s picture

matt@antinomia commented

If you want to list all promoted nodes, plus put the sticky ones on top of the list, use this code:

<?php
/**
* This php snippet displays content of a specified type, with teasers
*
* To change the type of content listed, change the $content_type.
*
* Works with drupal 4.6
*/
  $list_length = 5;
  $result1 = pager_query(db_rewrite_sql("SELECT n.nid, n.created FROM {node} n WHERE n.status = 1 AND n.promote = 1 ORDER BY n.sticky DESC, n.created DESC"));
  while ($node = db_fetch_object($result1)) {
    $output .= node_view(node_load(array('nid' => $node->nid)), 1);
  }
$output .= theme('pager', NULL, $list_length);
print $output;
?>