sql | Eric's Drupal Blog

Content tagged with: sql

Eric's picture

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

Submitted by Eric on Sun, 08/30/2009 - 12:56

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.

gradient spacer
Eric's picture

How to dump and compress all mysql databases on a server into separate files using PHP and PEAR

Submitted by Eric on Mon, 06/22/2009 - 21:20

Here's a quick code snippet I just wrote to dump and compress (gzip) all mysql databases on a server into separate files using PHP and PEAR.

<?php
// include PEAR DB library
require_once('DB.php');

// define the DSN in an array
// NOTE: user must have access to all databases
$dsn = array(
  'phptype' => 'mysql',
  'username' => 'YOURUSER',
  'password' => 'YOURPASSWORD',
  'hostspec' => 'HOSTNAME', // localhost?
);

// instantiate a PEAR DB object
$DB =& DB::connect($dsn);

// check for an error
if (DB::isError($DB)) die($DB->getMessage());

// set the DB fetch mode to associative
$DB->setFetchMode(DB_FETCHMODE_ASSOC);

// define sql statement
$sql = "show databases";

// fetch sql result
$databases = $DB->getAll($sql);

// loop through results
foreach ($databases as $index => $result) {

  // define the mysqldump command
  $command = "mysqldump -u " . $dsn['username'] . " "
    . "-p" . $dsn['password'] . " "
    . "-h " . $dsn['hostspec'] . " "
    . $result['Database'] . " | gzip > "
    . $result['Database'] . ".sql.gz";

  // execute command
  `$command`;
   
}
?>

gradient spacer
Eric's picture

Taking a SQL statement and generating a table from the results

Submitted by Eric on Tue, 06/02/2009 - 10:33

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;
}
?>

gradient spacer
Eric's picture

Passing date ranges into a view as arguments

Submitted by Eric on Thu, 12/11/2008 - 18:58

At some point, you might want to pass date ranges as arguments to a view. Unfortunately, the built-in functionality of views does not allow you to use a date argument as a range. If you try to specify the date arguments by editing your view, the views module will automatically insert SQL to match the dates exactly, which will not work in this scenario. Assuming that you already have a view and a content type that has two fields for start and date ranges, you can add a hook_db_rewrite_sql() function to add your own SQL to the view's query.

<?php
// define the db_rewrite_sql hook:
function MYMODULE_db_rewrite_sql($query, $primary_table = 'n', $primary_field = 'nid', $args = array()) {
  // search for the view
  if (is_object($args['view']) && $args['view']->name=='MYVIEWNAME') {
    // if there are no view arguments, don't bother continuing     
    if (!is_array($args['view']->args)) return;

    // get the start and end date ranges from the view arguments
    // NOTE: if you don't know which arguments are which,
    // you can use: print_r($args['view']->args)
    $startDate = $args['view']->args[0];
    $endDate = $args['view']->args[1];

    // validate dates. exit function if issue
    if (strlen($startDate) && !checkdate(date('m', strtotime($startDate)), date('d', strtotime($startDate)), date('Y', strtotime($startDate)))) return;
    if (strlen($endDate) && !checkdate(date('m', strtotime($endDate)), date('d', strtotime($endDate)), date('Y', strtotime($endDate)))) return;

    // create var for where clause
    $where = "";

    // define table alias
    $tableAlias = 'MYALIASNAME';

    if ($startDate) {
      $where = " {$tableAlias}.field_start_date_value >= '$startDate' ";
    }

    if ($endDate) {
      if (strlen($where)) $where .= " and ";
        $where .= " {$tableAlias}.field_end_date_value <= '$endDate' ";
    }

    if (strlen($where)) {
      return array('join' => "inner join {content_type_MYCONTENTYPE} $tableAlias on node.vid = $tableAlias.vid and $where");
    }
   
  }

}
?>

Now, you can pass the date ranges in as arguments using the views_embed_view function:

<?php
$viewName = 'MYVIEWNAME';
$arg = array('2008-01-01','2008-12-31');
$viewsHtml = views_embed_view($viewName, 'default', $arg);
?>

gradient spacer
Eric's picture

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

Submitted by Eric on Fri, 06/06/2008 - 08:27

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>";
?>

gradient spacer
Syndicate content