Using PHP and SQL (interrogating the Drupal database)
This is the code used on the opening page of Inkpotamus
It uses SQL to read the MySQL database that stores all the Drupal data for the website, reading in the bookpages defined on the site and pulling in the icon image attached to each page.
It uses PHP to manipulate the data and write out the HTML and Javascript that makes the page work.
<?php
// This script reads the the Drupal database
// It gets details of all nodes that are published and promoted to the front page.
// It displays one row of the most recently created nodes
// and a grid showing all the published nodes
// The width of the rows is controlled by $col_max = (number of columns)-1
// The number of rows in the main grid is determined by how many nodes there are
// Nodes are in alphabetical order grouped by book,
// with the book name shown prior to its nodes
// CONTROL VALUES
// width of the grid, i.e. maximum column number (starting from 0)
$col_max=6;
// MAIN CODE
// query database and build an array of all items
$sql_str = create_sql();
$item_array = perform_sql_query($sql_str);
// build a list of all the most recent items
$recent_item_array = build_recent_list($item_array,$col_max);
// build a grid array of html for most recent items, then build table html for it
$recent_result = build_grid($recent_item_array,$col_max);
$recent_icon_grid = $recent_result['grid'];
$recent_row_end = $recent_result['row end'];
$recent_table_html = create_table_html($recent_icon_grid,$col_max,$recent_row_end);
// build a grid array of html for all items, then build table html for it
$all_result = build_grid($item_array,$col_max);
$all_icon_grid = $all_result['grid'];
$all_row_end = $all_result['row end'];
$all_table_html = create_table_html($all_icon_grid,$col_max,$all_row_end);
// get javascript which is used for magnifying icons when mouse hovers over them
$javascript = create_javascript();
// write out the html for the page
print "$javascript";
print "$recent_table_html";
print "<br />";
print "$all_table_html";
// FUNCTIONS
// ***********
// prepare SQL
// ***********
function create_sql()
{
$sql_str=
"SELECT
node.title AS NODE_title,
node.created AS NODE_created,
concat('node/',node.nid) AS NODE_nodesrc,
booknode.title AS BOOKNODE_title,
concat('node/',book.bid) AS BOOKNODE_nodesrc,
files_image.filepath AS FILESIMAGE_filepath,
url_alias.dst AS URLALIAS_url,
bookurl_alias.dst AS BOOKURLALIAS_url
FROM node
LEFT JOIN book book ON node.nid = book.nid
LEFT JOIN node booknode ON book.bid = booknode.nid
LEFT JOIN url_alias url_alias ON url_alias.src = concat('node/',node.nid)
LEFT JOIN url_alias bookurl_alias ON bookurl_alias.src = concat('node/',book.bid)
LEFT JOIN image_attach image_attach ON node.nid = image_attach.nid
LEFT JOIN node node_image_attach ON image_attach.iid = node_image_attach.nid
LEFT JOIN image image ON node_image_attach.nid = image.nid
LEFT JOIN files files_image ON image.fid = files_image.fid
WHERE (node.status <> 0) AND (node.promote<>0) AND (files_image.filename = '_original')
ORDER BY booknode.title ASC, node.title ASC";
return ($sql_str);
}
// *************
// perform query
// *************
function perform_sql_query($sql_str)
{
$location="../../content/"; // the high level folder name to use in the URL for the nodes
$item_array = array(); // each item in this array is either a node or the start of a new book
$item_index = 0;
$current_book="";
$bgcolor_index=0; // background color changes for each new book, actual colors are set in determineStyle()
// query the database an loop through the rows that are returned
$page_query = db_query(db_rewrite_sql($sql_str));
while ($sql_row = db_fetch_object($page_query))
{
// use the book's URL alias if it has one, else use node number for link
if (strlen($sql_row->BOOKURLALIAS_url)>0)
{
$book_url=$location . $sql_row->BOOKURLALIAS_url;
}
else
{
$book_url=$location . $sql_row->BOOKNODE_nodesrc;
}
// if it is the start of a new book, populate the array with a link for the book
if ($current_book!=$sql_row->BOOKNODE_title)
{
$current_book=$sql_row->BOOKNODE_title;
$bgcolor_index=$bgcolor_index+1;
$item_array[$item_index++] = array(
'item'=>'book',
'book'=>$sql_row->BOOKNODE_title,
'bgcolor'=>$bgcolor_index,
'burl'=>$book_url );
}
// use the node's URL alias if it has one, else use node number for link
if (strlen($sql_row->URLALIAS_url)>0)
{
$href_url=$location . $sql_row->URLALIAS_url;
}
else
{
$href_url=$location . $sql_row->NODE_nodesrc;
}
// trim the title if it's too long (this appears under the icon)
if (strlen($sql_row->NODE_title)>15)
{
$link_text=substr($sql_row->NODE_title, 0, 12) . '...';
}
else
{
$link_text=$sql_row->NODE_title;
}
// full node title will appear as the <title> of the icon, shown when mouse hovers over it
$item_name=$sql_row->NODE_title;
// node creation date used to decide which are the most recent items
$item_date=$sql_row->NODE_created;
// location of the icon image file
$file_url=$location . '/' . $sql_row->FILESIMAGE_filepath;
// store details in the array and increment the index
$item_array[$item_index++] = array(
'item'=>'node',
'date'=>$item_date,
'book'=>$sql_row->BOOKNODE_title,
'bgcolor'=>$bgcolor_index,
'burl'=>$book_url,
'name'=>$item_name,
'href'=>$href_url,
'file'=>$file_url,
'text'=>$link_text);
}
return ($item_array);
}
// ************************
// create recent items list
// ************************
function build_recent_list($item_array,$col_max)
{
// add each item to the array of most recent items keyed by date
// reverse sort the array to put it into descending date order
// if you have more than a row-ful of items, remove the last one
foreach ($item_array as $key => $value)
{
$item_date=$item_array[$key]['date'];
$recent_array[$item_date]=$key;
krsort($recent_array);
if (count($recent_array)>$col_max)
{
array_pop($recent_array);
}
}
$recent_item_index=0;
// create a starter item to show what this row contains
$recent_item_array[$recent_item_index++] = array(
'item'=>'book',
'book'=>'Newly added',
'bgcolor'=>0,
'burl'=>'' );
// copy the item details from the main array
foreach ($recent_array as $key => $value)
{
$recent_item_array[$recent_item_index] = $item_array[$value];
$recent_item_array[$recent_item_index]['bgcolor']=0;
$recent_item_index = $recent_item_index + 1;
}
return ($recent_item_array);
}
// **********
// build grid
// **********
function build_grid($all_item_array,$col_max)
{
// builds a multi-dimensional array containing html for each item and corresponding to the grid of icons to be shown
$icon_grid=array();
$col=0;
$row=0;
$current_book="";
$book_style="font-family:\"Tempus Sans ITC\"; text-align:left; color:black; font-size:small; padding:0; margin:0; ";
$anchor_style="font-size:xx-small; line-height:100%; padding:0; margin:0; ";
$icon_style="width:69px; height:69px; margin:3px; border:0 none transparent; padding:0; ";
foreach ($all_item_array as $key => $value)
{
// create link to the book if it is the first icon for that book
$item_value=$value['item'];
$book_value=$value['book'] . ":";
$burl_value=$value['burl'];
$bgcolor_value=$value['bgcolor'];
if ($item_value!='node')
{
$item_html="<a href='$burl_value' title='$book_value' style='$book_style' ><strong>$book_value</strong></a>";
}
else
{
// create the icon link
$name_value=$value['name'];
$href_value=$value['href'];
$file_value=$value['file'];
$text_value=$value['text'];
$date_value=$value['date'];
// each icon will have a unique id
// a javascript function is called to resize the icon when the mouse hovers over it
$icon_id="icon" . $book_value . $key;
$item_html=
"<a style='$anchor_style' href='$href_value' title='$name_value' >
<img style='$icon_style' id='$icon_id' title='$name_value' alt='$name_value'
onmouseover='resizeImage(\"$icon_id\",\"big\")' onmouseout='resizeImage(\"$icon_id\",\"icon\")'
src='$file_value' />
<br />
$text_value
</a>";
}
$icon_grid[$row][$col]['item']=$item_value;
$icon_grid[$row][$col]['html']=$item_html;
$icon_grid[$row][$col]['bgcolor']=$bgcolor_value;
$col_end=$col;
$row_end=$row;
if ($col<$col_max)
{
$col=$col+1;
}
else
{
$col=0;
$row=$row+1;
}
}
// fill the rest of the row with blanks
// but use the same background colour value that the last book had
$col_end=$col_end+1;
if ($col_end<=$col_max)
{
for ($i=$col_end; $i<=$col_max; $i++)
{
$icon_grid[$row][$col]['item']='node';
$icon_grid[$row_end][$i]['html']="";
$icon_grid[$row_end][$i]['bgcolor']=$bgcolor_value;
}
}
return array('grid'=>$icon_grid,'row end'=>$row_end);
}
// **********
// Javascript
// **********
function create_javascript()
{
// when the mouse hovers over the icon images, it calls this javascript to magnify/de-magnify the image
$javascript =
"<script type='text/javascript' >
function resizeImage(item,size)
{
var item1=document.getElementById(item);
if (size=='icon')
{
item1.style.width='69px';
item1.style.height='69px';
item1.style.margin='3px';
item1.style.border='0 none transparent';
}
else
{
item1.style.width='73px';
item1.style.height='73px';
item1.style.margin='0';
item1.style.border='1px solid silver';
}
}
</script> ";
return ($javascript);
}
// **********
// HTML TABLE
// **********
function create_table_html($icon_grid,$col_max,$row_end)
{
// takes the html from the grid array and wraps appropriate table html around it
// producing the final chunk of html to be written out
$table_style=
"border-width:0; border-collapse:collapse; border-spacing=0; margin:0; padding:0; ";
$table_html="";
$table_html=$table_html . "<table style='$table_style' ><tbody>";
for ($j=0; $j<=$row_end; $j++)
{
$table_html=$table_html . "<tr>";
for ($k=0; $k<=$col_max; $k++)
{
$style_value=determineStyle($icon_grid,$j,$k,$col_max,$row_end);
$table_html=$table_html . "<td style=' $style_value ' > ";
$table_html=$table_html . $icon_grid[$j][$k]['html'];
$table_html=$table_html . "</td>";
}
$table_html=$table_html . "</tr>";
}
$table_html=$table_html . "</tbody></table>";
return ($table_html);
}
// *********
// functions
// *********
function determineStyle($grid,$r,$c,$col_max,$row_end)
{
// creates the style value for each square in the grid
$td_node_style="vertical-align:bottom; text-align:center; padding:0; margin:0; ";
$td_book_style="vertical-align:middle; text-align:center; padding:0; margin:0; ";
$top_style="border-top:1px solid silver; ";
$bot_style="border-bottom:1px solid silver; ";
$rhs_style="border-right:1px solid silver; ";
$lhs_style="border-left:1px solid silver; ";
$bgcolor_style[0]="background-color:#FFFDED; ";
$bgcolor_style[1]="background-color:#EBF5FC; ";
$bgcolor_style[2]="background-color:#D5E9D7; ";
$bgcolor_style[3]="background-color:#CCCCFF; ";
$bgcolor_style[4]="background-color:#EBF4F3; ";
$bgcolor_style[5]="background-color:#CCFFFF; ";
// book links are middle-aligned, node links are bottom-aligned
if ($icon_grid[$r][$c]['item']!='node')
{
$style_string=$td_book_style;
}
else
{
$style_string=$td_node_style;
}
// any square in the last column will show a right-hand border
if ($c==$col_max)
{
$add_rhs=true;
}
// any square on the last row will show a bottom border
if ($r==$row_end)
{
$add_bot=true;
}
// any square on the first row will show a top border
if ($r==0)
{
$add_top=true;
}
// any square that isn't on the top row, will only show a top border if the square above it is in a different book
if ($r!=0 && $grid[$r][$c]['bgcolor']!=$grid[$r-1][$c]['bgcolor'])
{
$add_top=true;
}
// any square in the first column will show a left-hand border
// any square in the other columns will only show a left-hand border if the square to its left is in a different book
if ($c==0)
{
$add_lhs=true;
}
elseif ($grid[$r][$c]['bgcolor']!=$grid[$r][$c-1]['bgcolor'])
{
$add_lhs=true;
}
// add the appropriate styles according to the logical variables set above
if ($add_rhs)
{
$style_string=$style_string . $rhs_style;
}
if ($add_lhs)
{
$style_string=$style_string . $lhs_style;
}
if ($add_top)
{
$style_string=$style_string . $top_style;
}
if ($add_bot)
{
$style_string=$style_string . $bot_style;
}
// add the bgcolor style according to the index value
$bgcolor_index=$grid[$r][$c]['bgcolor'];
$style_string=$style_string . $bgcolor_style[$bgcolor_index];
return $style_string;
}
?>