By crick on

Hello,

I'm trying to create a block that only displays upcoming events for a specific taxonomy term of my events category.

the code below makes a block that displays the upcoming events for ALL terms (i need it to only display taxonomy id 13):

unset ($output);
$list_no = 5;
$time = time() - (2 * 60 * 60);

$sql = "SELECT node.nid, node.title, event.event_start FROM node INNER JOIN event ON node.nid = event.nid WHERE event.event_start >= %d ORDER BY event_start LIMIT $list_no";

$output .= "<table cellpadding=3>";
$result = db_query(db_rewrite_sql($sql), $time);

while ($anode = db_fetch_object($result)) {
  $output .= "<tr><td valign=top>".date(' m/d/y ', $anode->event_start)."</td>";
  $output .= "<td>".l($anode->title, "node/$anode->nid")."</td></tr>";
}
$output .= "</table>";
print $output;

I'm using this code to for a block that displays a list of "announcements" which are taxonomy id 3:

unset ($output);
$taxo_id = 3;
$list_no = 5;
$sql = "SELECT node.title, node.nid, node.created FROM node INNER JOIN term_node ON node.nid = term_node.nid WHERE (term_node.tid = $taxo_id) ORDER BY node.created DESC LIMIT $list_no";
$output .= "<table cellpadding=3>";
$result = db_query($sql);
while ($anode = db_fetch_object($result)) {
  $output .= "<tr><td valign=top>".date(' m/d/y ', $anode->created)."</td>";
  $output .= "<td>".l($anode->title, "node/$anode->nid")."</td></tr>";
}
$output .= "</table>";
print $output;

sorry if i'm not explaining this very well. i tried to combine the two sql queries to achieve the desired results but I don't know enough about mysql syntax to make it work. when i try to combine the queries it breaks.

⋅ Categories: Drupal 4.7.x

Comments

karl sf’s picture

karl sf commented

Hey, I'd like to see where this lands, I am workng on the same thing. I am using 5.1 with Date, Views and Calendar Views. What are you using to create an event? you are calling events above a 'category.' is Event a taxonomy term for you, or a content type?

I created a CCK content Type called event and gave it a "Date" field. Now I want to show upcoming events tagged by taxonomy vocabs, or one block per vocabulary.

Will the code above work for this approach?

Karl Erb
User Experience Architect
Information Architect, User Interface Designer
http://www.linkedin.com/in/karlerb

crick’s picture

crick commented

I'm using 4.7 with the Event module. It has an Event content type. I added an Event Vocabulary with 4 taxonomy terms (categories) when a user creates an Event they are required to choose one of the Event Categories

I created a content type Announcements using flexinode module. then i made an announcements vocabulary and announcements taxonomy term. The announcements block just displays the 5 newest announcements. I can use the same code to display the newest events for one of the event taxonomy terms simply by changing the value of the $taxo_id variable, however, that only shows the newly added events in that term. I need it to show the upcoming events in that term.

I don't know if either one of the blocks i posted will work in 5.1, i've never worked with 5.1.

crick’s picture

crick commented

basically I need to know if it is possible to combine the following sql queries:

$sql = "SELECT node.nid, node.title, event.event_start FROM node INNER JOIN event ON node.nid = event.nid WHERE event.event_start >= %d ORDER BY event_start LIMIT $list_no";

$sql = "SELECT node.title, node.nid, node.created FROM node INNER JOIN term_node ON node.nid = term_node.nid WHERE (term_node.tid = $taxo_id) ORDER BY node.created DESC LIMIT $list_no";
crick’s picture

crick commented

ok, i thought I was where I needed to be on this but i'm not.

unset ($output);
$list_no = 5;
$taxo_id = 13;
$time = time() - (2 * 60 * 60);


$sql1 = "SELECT node.nid FROM node INNER JOIN term_node ON node.nid = term_node.nid WHERE (term_node.tid = $taxo_id)";

$result_1 = db_query($sql1);

while ($race = db_fetch_object($result_1)) {

$sql = "SELECT node.nid, node.title, event.event_start FROM node INNER JOIN event ON node.nid = event.nid WHERE node.nid = $race->nid AND event.event_start >= %d ORDER BY event_start LIMIT $list_no";

$output .= "<table cellpadding=3>";
$result = db_query(db_rewrite_sql($sql), $time);

while ($anode = db_fetch_object($result)) {
  $output .= "<tr><td valign=top>".date(' m/d/y ', $anode->event_start)."</td>";
  $output .= "<td>".l($anode->title, "node/$anode->nid")."</td></tr>";
 }
}
$output .= "</table>";
print $output;

see it here: www.utktriathlon.com

crick’s picture

crick commented

my problem with the previously posted block code is that I can't seem to control the number of events that are displayed in the block.

any ideas?