By durpleman on

Hello,

Below is a bit of code which displays at most 100 "employees" per page. It displays them in order of their "title" (default field for any content type). "Employee" has a firstname, lastname, and group field. Suppose I want to print them out ordered by a different field. How would I go about this?

I would assume I need a statement similar to this: WHERE n.type = 'employee' AND n.group = 'manager.' However, this does not work. I assume this doesn't work because there is no column called "group" in my table. How can I access single fields of a content type through SQL? How can I find the name of columns in my table? I am aware of the type, title, nid, and created columns, but I do not know of any others.

I have been told that the views module would be the optimal way for me to solve this problem without writing my own SQL, but unfortunately I do not have permissions to install modules.

Please let me know if I'm approaching this in the wrong way. I am very new to Drupal and SQL. Thanks in advance. 

  $content_type = 'employee';

  $results = pager_query(db_rewrite_sql(
      "SELECT *
       FROM {node} n 
       WHERE n.type = '$content_type' AND n.status = 1 
       ORDER BY n.title ASC"),100);                    
                                                                  
  while ($node = db_fetch_object($results))
  {   $output = node_view(node_load(array('nid' => $node->nid)), 1);
      print $output;
  }

EDIT: I've been doing some reading on this topic. Is it correct that Drupal creates an entire table for each content type field rather than a single column?

⋅ Categories: Drupal 6.x

Comments

gooney0’s picture

gooney0 commented

You are correct. Drupal 7 will create a table for each field. If you added a field "group" it would create a table:

field_data_field_group

This table has several columns, most useful would be:

entity_id and field_group_value

To use these you'd likely join them on node.nid = field_data_field_group.entity_id. You could then add field_data_field_group.field_group_value to your SELECT or use it in your WHERE clause.

durpleman’s picture

durpleman commented

This is very helpful information, I will work through this tomorrow morning. Thanks for the response! You make a point of saying this applies to Drupal 7... but I am currently running Drupal 6. Does it work the same way with 6? If not, does anyone know where I might find some information regarding this subject? Thanks in advance.

EDIT: I'm getting an error that the field_data_field_group table does not exist. This is the code I've implemented for testing purposes:


//This should order by title and display only those employees with the group field value of 'manager'.

$results = pager_query(db_rewrite_sql(
    "SELECT *
    FROM node JOIN field_data_field_group
    ON node.nid = field_data_field_group.entity_id
    WHERE node.type = 'employee'
    AND node.status = 1
    AND field_data_field_group.field_group_value = 'manager'
    ORDER BY node.title ASC"),30);
  
while ($node = db_fetch_object($results)) {   
      $output = node_view(node_load(array('nid' => $node->nid)), 1);
      print $output;
  }

Looks to me like Drupal 6 is different in this regard. Any ideas on where I can find a Drupal 6 solution?

spovlot’s picture

spovlot commented

I would inquire further why you cannot use the Views module. Views is key to creating a successful Drupal site. It seems really unnecessary to write this kind of SQL code when a module does exactly that.

durpleman’s picture

durpleman commented

I was finally able to solve my problem, all without the help of the Views module.

For Drupal 6, there is a table called "content_type_[your type goes here]". So in my case, I dealt with "content_type_employee." Once I identified the name of the table, I used this snippet to display all the values of each row for my type... mostly to identify the names of my rows, since I did not know what they were.

 $result = db_query("SELECT * FROM {<table name goes here>}");
 while ($row = db_fetch_array($result)) {
      drupal_set_message(var_dump($row));
 }

From there, I used a join with my main node and this table on nid as recommended above. Now I've got my script sorting dynamically. Thank you for everyone's input.