query | Eric London's Blog

Prevent the user from creating more than one node (of a certain type)

Submitted by Eric.London on 8/30/2009

Tags:

1 comment

Last year I wrote a quick code snippet to Prevent the user from creating more than one node of a certain type for Drupal 5. I received a comment request to update this code for Drupal 6.

<?php
function MYMODULE_nodeapi(&$node, $op, $a3 = NULL, $a4 = NULL) {

  // define node type that a user will only be allowed to create one instance of 
  $singleNodeType = 'YOUR-NODE-TYPE';
  
  // test for node/add/NODETYPE page
  if ($node->type==$singleNodeType && $op=='prepare' && arg(0)=='node' && arg(1)=='add') {

    // define sql to create node table
    $sql = "select nid from {node} where type='%s' and uid='%d'";
    
    // execute sql
    $resource = db_query($sql, $singleNodeType, db_escape_string($GLOBALS['user']->uid));
    $result = db_result($resource);

    // test for result
    if (!empty($result)) {
      
      // set a message
      drupal_set_message("Sorry, you are only allow to create one $singleNodeType.");
      
      // redirect the user
      drupal_goto('node/add');
      
    }
        
  }
  
}
?>

The above code tests if the user is on the node/add/NODETYPE page and if the user has already created an instance of the node type, sets a message and redirects them away from the node/add page.

Taking a SQL statement and generating a table from the results

Submitted by Eric.London on 6/2/2009

Tags:

Add new comment

Functionality that I regularly create in applications is the ability to take a SQL statement and return a table of results for reports, debugging, etc. Here's a helper function I created to implement this functionality. I purposely left off pagination, sortable columns, and other features to focus on how the Drupal theme_table() function operates. This helper function will use the column names from the SQL as the table column headers, so make sure they are properly formatted.

<?php
// first, I defined this function in my module:
function _MYMODULE_sql_to_table($sql) {
    
  $html = "";
    
  // execute sql
  $resource = db_query($sql);
    
  // fetch database results in an array
  $results = array();
  while ($row = db_fetch_array($resource)) $results[] = $row;
    
  // ensure results exist
  if (!count($results)) {
    $html .= "Sorry, no results could be found.";
    return $html;    
  }

  // create an array to contain all table rows
  $rows = array();
    
  // get a list of column headers
  $columnNames = array_keys($results[0]);
    
  // loop through results and create table rows
  foreach ($results as $key => $data) {

    // create row data
    $row = array();
    
    // loop through column names
    foreach ($columnNames as $c) {
      $row[] = array(
        'data' => $data[$c],
        'class' => strtolower(str_replace(' ', '-', $c)),
      );
    }

    // add row to rows array
    $rows[] = $row;
        
  }
    
  // loop through column names and create headers
  $header = array();
  foreach ($columnNames as $c) {
    $header[] = array(
      'data' => $c,
      'class' => strtolower(str_replace(' ', '-', $c)),
    );
  }
    
  // generate table html
  $html .= theme('table', $header, $rows);
    
  return $html;
    
}

// then you can call it in your code...
function _MYMODULE_some_page_callback() {
  
  $html = "";

  $sql = "select * from {node}";

  $html .= _MYMODULE_sql_to_table($sql);

  return $html;
}
?>

Querying the Drupal database and fetching all the results in an associative array

Submitted by Eric.London on 6/6/2008

Tags:

4 comments

Here's a code snippet to show how to execute a query and fetch all the results into an associative array:

<?php
// create the SQL in a string
$sql = "
  select *
  from {node} n
  where n.status = '1'";

// execute the query
$resource = db_query($sql);

// loop through the resource and fetch the rows
$results = array();
while ($row = db_fetch_array($resource)) $results[] = $row;

// show the results
echo "<pre>" . print_r($results, TRUE) . "</pre>";
?>

Querying the node table for a distinct list of updated nodes per user

Submitted by Eric.London on 5/6/2008

Tags:

Add new comment

This morning I was writing a query to get a distinct list of updated/added times for each node type per user. The result was going to be using in a table to show when the user last added or updated each type of node in the system. Here is an example of how to query the node table with subqueries:

<?php
$sql = "
  select n.nid, n.vid, n.created, n.changed, n.type, n.title
  from {node} n
  join (
    select max(changed) as maxChanged, type
    from {node}
    group by type
  ) as n2 on n2.type = n.type and n.changed = n2.maxChanged
  join (
    select max(nid) as maxNid, type
    from {node}
    group by type
  ) as n3 on n3.type = n.type and n.nid = n3.maxNid
  where 1=1
  and n.uid=%d
  and n.status=1 
  order by n.changed desc
";

$resource = db_query($sql, db_escape_string($GLOBALS['user']->uid));
$results = array();
while($row = db_fetch_array($resource)) $results[] = $row;
?>