By Phillip Mc on

Hi Guys,

Need some sql help with a snippet.

I am trying to create a PHP snippet that lists the teasers of a specific cck content type, sorted by a specific cck field.

<?php $nlimit = 10; ?>
<?php $contentname = 'content_gig'; ?>
<?php $result1 = pager_query(db_rewrite_sql("SELECT n.nid, n.created FROM {node} n WHERE n.type = '$contentname' AND n.status = 1 ORDER BY n.created ASC"), variable_get('default_nodes_main', $nlimit)); ?>
<?php while ($node = db_fetch_object($result1)) {$output2 .= node_view(node_load(array('nid' => $node->nid)), 1);}; ?>
<h1>Upcoming Gigs</h1>
<?php print $output2; ?>

the above snippet is sorted by n.created or the "date it was created". I would like to change that to be a specific cck field. i.e. order by field_date_time_on_stage ASC

Where field_date_time_on_stage is a date field set with each cck node from the content type 'content_gig'.

I've tried fiddling about with INNER JOIN, but, I'm unable to get it working.

Any SQL wizards out there able to help? Even a link to another forum post/help page would help.

thanks.

phil

⋅ Categories: Drupal 4.7.x

Comments

Phillip Mc’s picture

Phillip Mc commented

fortgot to mention - I have already tried views.module but that's like using jcb to eat your dinner for what I want to do.

phil

drawk’s picture

drawk commented

I have already tried views.module but that's like using jcb to eat your dinner for what I want to do.

I'm not positive what jcb is, but I understand the sense of what you are saying.

But are you sure? This seems to me to be exactly what Views is designed to do, and exactly the type of situation it is supposed to handle.

---
www.whatwoulddrupaldo.org

Phillip Mc’s picture

Phillip Mc commented

the views module is great but, to give you an idea to what a JCB looks like: http://www.publiquip.com/photo/JCB-3CX-Backhoe-K.jpg

Phil

Anonymous’s picture

Anonymous (not verified) commented

cck doesn't lend itself to being manually queried. View module is really the way to go here. If you had to do it manually here's a completely untested guess to start with. You'd have to look up your field, I have a textfield set up from playing with and it uses the table node_data_field_myfield with the data stored in the column field_myfield_value so the field is 'field_myfield'. Not sure if cck uses different structures for different data types...

$field = 'field_myfield';
$query = 'select n.nid, n.vid, f.' . $field . '_value from node n left join node_data_' . $field  . 'f on on n.vid = f.vid order by f.' . $field . '_value';
Phillip Mc’s picture

Phillip Mc commented

thanks for posting that debtman...but I wasn't able to work it out from there. I looked up the field in phpmyadmin and the ql query that gives me is:

SELECT * 
FROM `node_content_gig` 
ORDER BY `field_date__time_on_stage_value` ASC

Using your snippet, here's my revised attempt:

//define limit, cck content type and the cck content field I want to sort the query by
$nlimit = 10; 
$contentname = 'content_gig'; 
$field = 'field_date__time_on_stage_value';

// construct the query
$query = 'select n.nid, n.vid, f.' . $field . '_value FROM {node} WHERE n.type = '$contentname' node 
n left join node_data_' . $field  . 'f on on n.vid = f.vid order by f.' . $field . '_value';

// run the query
$result1 = pager_query(db_rewrite_sql ($query), variable_get('default_nodes_main', $nlimit)); 

//extract the output
while ($node = db_fetch_object($result1)) {
  $output2 .= node_view(node_load(array('nid' => $node->nid)), 1);
 };

// print the results.
print '<h1>Upcoming Gigs</h1>';
print $output2;

Can you spot anything obviously wrong with the above? (it's giving me no output)

Phil

dublin drupaller’s picture

dublin drupaller commented

this should work for you Phil.

/** 
* this snippet will list 10 CCK content type titles & teasers and sort them based on a 
* certain field from that same CCK content type.
*
* change the $imit to increase the length of the list
* Change the $contentname to the cck content type
* change the $sortbyfield to be the field name you want the list sorted by.
*
* IMPORTANT NOTE: this bypasses the Drupal access check to see if a user has permission 
* to view the content type
*
**/

//define limit, cck content type and the cck content field I want to sort the query by
$nlimit = 10; 
$contentname = 'node_content_gig'; 
$sortbyfield = 'field_date__time_on_stage_value';

// construct the query
$query = "SELECT * FROM $contentname ORDER BY $sortbyfield ASC";

// run the query
$result1 = pager_query(db_rewrite_sql ($query), variable_get('default_nodes_main', $nlimit)); 

//extract the output
while ($node = db_fetch_object($result1)) {
  $output2 .= node_view(node_load(array('nid' => $node->nid)), 1);
 };

// print the results.
print '<h1>Upcoming Gigs</h1>';
print $output2;

Dub

Currently in Switzerland working as an Application Developer with UBS Investment Bank...using Drupal 7 and lots of swiss chocolate

Phillip Mc’s picture

Phillip Mc commented

That works but, it only displays the teasers, which is fine as I can add an extra field to my cck content type to get around that.

It would be useful, though if the snippet could display the titles as well as I have other cck content types that I would like to use the same snippet for.

Phil

dublin drupaller’s picture

dublin drupaller commented

Hi Phil,

This is the corrected snippet with node Titles included.

I just tried this with 4.7.3 and it works. Please note that I'm not an expert in sql queries, so there is probably a much simpler and shorter way of doing this. 

/** 
* this snippet will list 10 CCK content type titles & teasers and sort them based on a 
* certain field from that same CCK content type.
*
* change the $imit to increase the length of the list
* Change the $contentname to the cck content type
* change the $sortbyfield to be the field name you want the list sorted by.
*
* IMPORTANT NOTE: this bypasses the Drupal access check to see if a user has permission 
* to view the content type
*
**/

//define limit, cck content type and the cck content field I want to sort the query by
$nlimit = 10; 
$contentname = 'node_content_gig'; 
$sortbyfield = 'field_date__time_on_stage_value';

// construct the query
$query = "SELECT * FROM $contentname ORDER BY $sortbyfield ASC";

// run the query
$result1 = pager_query(db_rewrite_sql ($query), variable_get('default_nodes_main', $nlimit)); 

//extract the output
while ($node = db_fetch_object($result1)) {
  $ccknode = node_load($node->nid);
  $output2 .= "<h1>". $ccknode->title ."</h1>";  
  $output2 .= node_view(node_load(array('nid' => $node->nid)), 1);
 };

// print the results.
print '<h1>Upcoming Gigs</h1>';
print $output2;

dub

Currently in Switzerland working as an Application Developer with UBS Investment Bank...using Drupal 7 and lots of swiss chocolate