Drupal 6 – “The Requested Page Can Not Be Found”

This started popping up on a website I manage today.  There were no new modules, views, etc installed.  A user was just doing thier daily activities of adding new content.

To fix I uploaded a file remove-cache.php to the server and went to it from a browser.

This fixed the issue.

Attaching Taxonomy Terms to Existing Drupal Nodes with SQL

A website I work with has a whole bunch of Taxonomy “keywords” that needed to be assigned to existing content. There’s no way I could use third party modules to assign these terms to the existing content because I’m using large data sets (~500,000). Plus, why have the overhead when I just use SQL to directly access manipulate the data.

Below is a SQL statement that will add a taxonomy association to any content that contains the taxonomy keyword:


INSERT INTO term_node
(nid, vid, tid)
(
SELECT q.nid, q.nid, t.tid
FROM content_type_yourtype q
JOIN term_data t
-- Make sure you only find full words, not partial matches
ON q.field_type_value LIKE CONCAT('% ', t.name, ' %')
-- Don't try to add existing entries
WHERE NOT EXISTS (SELECT n.nid FROM term_node n WHERE n.nid = q.nid AND n.vid = q.nid AND n.tid = t.tid)
-- Clear out blanks
AND t.name != ''
-- This is optional, I need it because my hosting provider times out on 3+ min queries...
AND q.nid >= 200000 AND q.nid < 300000 )

SCRATCH THIS. You need to setup a job to run this, it's too much work on the database:

PHP Script to use:


= $min AND q.nid < $max ");

Bash Script to automate:


min=202500;
limit=2000;
for i in {1..150};
do echo "Running Job $i with $j";
php -e tax_update.php $min `expr $min + $limit`;
min=`expr $min + $limit`;
done

Manually Fixing / Adding Permissions to node_access Table (Drupal 6)

For some reason one of the Drupal sites that I work on for one of my clients wasn’t adding new information to the node_access table for new content being created. We noticed this because some of the results weren’t showing up in searches. (They won’t if the node doesn’t have grant_view checked.) Anyhow, I created the following query to fix this:


/*
* This statement is just used to check how many need to be
* sync'd and can be ignored. It does let you know how many
* nodes your missing though 😉
*/
SELECT count(*) FROM mydatabase.node n where n.nid not in (
select a.nid from mydatabase.node_access a)
and (n.type = 'content_type_a' or n.type = 'content_type_b');

/*
* Create a temp table to insert all missing permissions.
* Note, this isn't required if you only have one access type,
* but since I three, it is necessary. This tables structure
* is just a replica of node_access table.
*/
create temporary table s (
nid int not null,
gid int,
realm varchar(255),
grant_view tinyint(3),
grant_update tinyint(3),
grant_delete tinyint(3)
);

/*
* I have three node permission groups that I have to change,
* so I will run three insert statements to populate my temp
* table. There is probably a way to combine this into one
* SQL query, but that's another days lesson.
*/
insert into s
/* This group gets view access */
select n.nid, GID_VAL, 'term_access', 1, 0, 0
from mydatabase.node n
where n.nid not in (
select a.nid from mydatabase.node_access a
)
and (n.type = 'content_type_a' or n.type = 'content_type_b');

insert into s
/* This group gets view access */
select n.nid, GID_VAL, 'term_access', 1, 0, 0
...

insert into s
/* This group gets view, update and delete access */
select n.nid, GID_VAL, 'term_access', 1, 1, 1
...

/*
* Finally add all of the missing node permissions into
* the real node_access table!
*/
insert into mydatabase.node_access
(nid, gid, realm, grant_view, grant_update, grant_delete)
select s.nid, s.gid, s.realm, s.grant_view, s.grant_update, s.grant_delete
from s;

/*
* You might wonder why you need the temp table, it's cause the
* where clause would return no rows after that first INSERT.
* If you only have one GID to worry about, you can forget
* the temp table step.
*/

/*
* Confirm you get 0 results. (No missing nodes)
*/
SELECT count(*) FROM mydatabase.node n where n.nid not in (
select a.nid from mydatabase.node_access a)
and (n.type = 'content_type_a' or n.type = 'content_type_b');

/*
* Remove temp.
*/
drop table s;

Yes, you could use just rebuild all of the permissions through Drupal, but when the site has an extremely large node population, this takes hours.

Drupal 6 Custom Module Causes WSOD

Issue:
You created a custom module and tried to setup a theme function with it. After changing an Administration setting, you get the WSOD (white screen of death) on all pages. There are no errors in the Apache or PHP error logs. This is not a memory or time out issue. You screwed up in your module 😉

Fix:

# Disable your module
update yourdrupaldatabase.system set status = 0 where filename like 'sites/all/module%' and name like 'your_custom_module_groups_%';

# Remove main cache
delete from yourdrupaldatabase.cache;

# Remove all other cache tables.
delete from yourdrupaldatabase.cache_*

Now you should be able to access the site. After you remove any template references in your module code and delete any .tpl files, you can safely re enable your custom module:


update yourdrupaldatabase.system set status = 1 where filename like 'sites/all/module%' and name like 'your_custom_module_groups_%';

Cheers, strick.

How to Use Drupal’s db_query() With AJAX Calls

I recently had a scenario on a Drupal 6 site that I am developing where I wanted to refresh some content every 10 seconds with new data from the Drupal database. Usually to do something like this I do the following:

  • Use an AJAX call to a PHP script on my site.
  • PHP script queries database and sends back the response.

You can’t do this though if you want to use Drupal’s API since Drupal needs to go through it’s entire boot process before any of the API is avaible. Luckily there is an easy way to get around this; create a module with a menu hook.

To create the module first create the .info file:


;custom_ajax.info
name = Custom Ajax Calls
description = Allows AJAX to use Drupal API
core = 6.x
project = "custom_ajax"

Next create the module file with the hook_menu() and call back.


// custom_ajax.module

// menu hook call
function custom_ajax_menu()
{
// List of navagation links to your ajax functions.
$items = array();

// Creates a link yoursite/?q=my/ajax/function that will call the page callback function (custom_ajax_my_ajax_function())
$items["my/ajax/function"] = array(
"title" => t("title"),
"description" => t("provides access to a php function that can be requested by an ajax call"),
"page callback" => "custom_ajax_my_ajax_function",
"access arguments" => array("access content"),
"type" => MENU_CALLBACK
);
}

// The function that actually does what you need for the ajax request
function custom_ajax_my_ajax_function()
{
$q = "";
db_query($q); //drupal api
echo json_encode($data);
exit; // Don't render the page.
}

Now in your JavaScript code, you just make the AJAX request to the callback path


$.ajax({
url:'/?q=my/ajax/function',
success: function(data, success, jqXHR){
}
});

Using SQL to Update Content In One Table With Content From Another

This isn’t something new, but I found myself doing this again the other day so I figured I’d “archive” it. (Though most of this stuff will be out of date in 10 years anyway :-P)

So I have two tables; node and content_type_author (yeah we’re talking Drupal). I wanted to update the ‘title’ column of all the rows in node that were type ‘author’ with the authors first name and last name (found in the content_type_author table):


UPDATE node N, content_type_author A
SET N.title = CONCAT(IFNULL(A.field_fname_value, ''), ' ', IFNULL(A.field_lname.value, ''))
WHERE N.nid = A.nid AND N.type = 'author'

That’s it. The only really interesting part is:


CONCAT(IFNULL(A.field_fname_value, ''), ' ', IFNULL(A.field_lname.value, ''))

which says to combine the first and last name columns to create a name string, but if either the first name or last name is not there, just replace it with an empty string. (In MySQL if you try to concatenate a string with NULL you get NULL)


CONCAT('Brian', NULL) => NULL
CONCAT(NULL, 'Strickland') => NULL
CONCAT('Brian', IFULL(NULL, '')) => 'Brian'

Add Custom Lists to “Load a pre-built option list” in Drupal’s Webform Module

Drupal’s webform module is awesome. The only problem that I see (maybe I’ll right a patch for them) is that you can not easily use existing taxonomy lists for multiple options. e.g. radio, check boxes and select inputs. They did create a hook you could use though. The following shows how to do this:

Problem: Need to use my existing Department taxonomy list for a multi option input in Webform

Solution:

1) Create a new module and put the following code inside your .module file:

define('US_DEPARTMENT_ID', 3);

// use the provided webform_select_options_info hook.
function us_webform_webform_select_options_info() {
$items = array();

// Basically says to add Departments to the "Load a pre-built option list" list.
$items['departments'] = array(
'title' => t('Departments'),
'options callback' => 'us_webform_options_departments'
);

return $items;
}


// Creates the list I want.
function us_webform_options_departments() {

$departments = array();

// Simple query to the tax table.
$q = "SELECT tid, name FROM {taxonomy_term_data} WHERE vid = :vid ORDER BY name";
$rs = db_query($q, array(":vid" => US_DEPARTMENT_ID));

// Create key|value pairs.
foreach($rs as $r) {
$departments[$r->tid] = t($r->name);
}

return $departments;
}

Wala:

Drupal – Import data into nodes and content types

I needed to import a whole bunch of stuff from a text file into Drupal and it involve using custom CCK types.  I found some great PHP code to do this here: http://www.rtraction.com/blog/devit/drupal-import-data-into-nodes-and-content-types.html but I wanted to do it using Python, so here is my translation of that code:


import MySQLdb
db = MySQLdb.connect(host='', user='', passwd='', db='')
cursor = db.cursor()

def insert_node(title, type,body, date = ""):

# Drupal required me to have a vid on insert.
cursor.execute("select max(vid)+1 from node");
vid = int(cursor.fetchone()[0])

# If no date value
if date == "":
sql = "INSERT INTO node (title, type, uid, created, changed, vid, promote, comment) VALUES('"+title+"','"+str(type)+"',1,UNIX_TIMESTAMP(), UNIX_TIMESTAMP(), "+str(vid)+", 1, 2)"
else:
sql = "INSERT INTO node (title, type, uid, created, changed, vid, promote, comment) VALUES('"+title+"','"+str(type)+"',1,"+date+","+date+", "+str(vid)+", 1, 2)"

# Insert the node
cursor.execute(sql)
insert_id = int(cursor.lastrowid)
db.commit()

# Insert revision node.
sql = "INSERT INTO node_revisions (nid, title, uid, body) VALUES ('"+str(insert_id)+"', '"+title+"',1,'"+body+"')"
cursor.execute(sql)
revision_insert_id = int(cursor.lastrowid)
db.commit()

# Not sure if this code has any meaning
if insert_id > 0:
sql = "UPDATE node SET vid = "+str(revision_insert_id)+" WHERE nid = "+str(insert_id)
cursor.execute(sql)
db.commit()

return {'insert_id':insert_id, 'revision_id':revision_insert_id}

def insert_special_node(content_type, data, title = "", body = "", date = ""):

# Insert node data
data_ids = insert_node(title,content_type, body ,date)

# Get any custom columns and values
cols = []
values = []

for c in data.keys():
cols.append(c)
v = "'" + data[c].replace("'", "\'") + "'"
values.append(v)

# Create a string list out of them
col_str = ",".join(cols)
val_str = ",".join(values)

# Insert custom content data.
sql = "INSERT INTO content_type_"+content_type+" (vid, nid,"+col_str+") VALUES("+str(data_ids["revision_id"])+","+str(data_ids["insert_id"])+" ,"+val_str+")"
cursor.execute(sql)
db.commit()