distinct Drupal user list with views

having trouble displaying a DISTINCT user list with views? This problem often arises because you need to choose the 'user' type view when you are first creating the new view.

Drupal Views Templates and CCK $fields Array

Having difficulty accessing the $fields array under your Drupal Views template? Are you sure you have named your template correctly (for instance, if you have named it with 'field' instead of 'fields' the template will apply to individual fields, for which the $fields array is not exposed)

How to migrate Drupal to xampp for Development

Occasionally it is important to work on a local development machine. For this task I often use the xampp package, which mirrors a standard lamp stack.

To migrate an existing remote drupal site do the following:

1. install xampp at c:\xampp
2. modify c:\xampp\apache\conf\httpd.conf
1. the line 'Listen 80' to 'Listen 88'
2. If you had Clean URLs on your remote site (i.e. localhost/drupal/admin instead of localhost/drupal/?q=admin)
   
1. #LoadModule rewrite_module modules/mod_rewrite.so' to ' LoadModule rewrite_module modules/mod_rewrite.so' (take out the #)
1. AllowOverride None to AllowOverride All
   
3. start apache and mysql from the xampp interface (you may need to change mysql configuration)
   
3. copy over the following folders to a new 'drupal_remote_site' folder on your local machine
1. Themes
2. Modules
3. Files
4. if you don't already have the Backup and Migrate module on your remote machine, install it
5. backup your database on the remote machine, copy this into the local folder 'drupal_remote_site'
6. install the same version of drupal on your local machine which you had on your remote -- note: this downloads as a tarball which must be expanded and copied into within c:\xampp\htdocs\drupal (rename the copied folder as drupal from drupal-6.5 for instance)
7. copy in your themes, modules, and files folder into this new drupal folder
8. go to http://localhost:88/drupal/install.php, this will install necessary tables,etc. into the db
9. now enable the Backup and Migrate module on the local machine
10. use the 'Restore/Import DB' tab in drupal to import your db
11. voila, all should be back to normal
    

The $fields array in Drupal (CCK)

the $fields array is very important for accessing field content when doing custom theming. Unfortunately field names are not well documented, nor are they intuitive. For example, the field name for a node:link field is view_node. In order to track down names of my fields I usually use print_r ... the trick here is that you must look for 'stdClass Object', almost always the index giving these objects as values are the names of the fields used within the $fields array.

For example:
[field_image_fid] => stdClass Object ( [content] =>

can be accessed in your tpl.php by:
$fields['field_image_fid']->content

having trouble with no "active" class being generated for menu's or primary/secondary links (

) in Drupal 6.x? It has been documented that 6.x has issues with generating an active class ... at 6.5 the active

is classed as "active-trail", so if you are bringing in a theme that worked at 5.x this might be the issue.

For me it was a naming issue within my primary links configuration (the same would be true for a menu). You must use a Drupal recognizable path such as "" for your homepage or "data" for a page at "http://www.yoursite.com/data". To avoid this, make sure to select this menu when creating new content.

with the php domdocument class was getting the following error:

Warning: main() [function.main]: unterminated entity reference 64-bit

this error was occurring when trying to set the name of an anchor element ... the same error popped up when setting both within the constructor and through the nodeValue method. I haven't tested the solution via the constructor, but by wrapping the value with the built-in htmlspecialchars(), I solved this error

i.e.:
$anchornode = $doc->createElement('a');
$anchornode->nodeValue = htmlspecialchars("$somevalue");

create dom html with php, getelementbyid doesn't work

I leverage the domdocument php class to create elegant, dynamic html documents via the dom tree. However, I was finding that the "getelementbyid" method was not returning elements as expected. The reason for this is that I had not defined a DTD, which is necessary in this case because we only specify that we are saving as HTML at the end of the process, so the domdocument object is only understood as generic XML, which has no predefined "id." To create a DTD use this code (here using 4.01 transitional html):

$doc = new DOMDocument();
$doc->loadHTML('<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">;');

there are so many options with pear's db library for php, I sometimes forget the optimal code for getting a sql result as an array that I can easily use within php. The code below fully demonstrates connecting to and getting an associative array with an 'order by' field as the array key. This code is for a postgres rdms, but could easily be modified for mysql, sqlserver, etc. Parameters are in caps surrounded by '%' in the form %PARAMETER%.

require_once 'DB.php';

$dsn = "pgsql://%LOGIN%:%PASSWORD%@%HOST%/%DBNAME%";
$dbh =& DB::connect ($dsn);
if (PEAR::isError($dbh)) {
die($dbh->getMessage());
}

$dbh->setFetchMode(DB_FETCHMODE_ASSOC);
$res =& $dbh->getAssoc('%SQL STATEMENT, USE 'ORDER BY' TO DEFINE ARRAY KEY%');

if (PEAR::isError($res)) {
die($res->getMessage());
}

When defining projection from ArcCatalog (shapefile properties) I get an error message "Failed to Alter the Spatial Reference". If I try to do this from the "Define Projection" geoprocessing tool (ArcToolbox) I get this error insted: "Cannot create a projection file for filepath Failed to execute (DefineProjection).

I solved this problem by accesing the shapefile through a mapped drive (a.k.a. "Z:\") within ArcCatalog, rather than its \\server\drive$ pathname.

There are multiple caching levels to be mindful of with a typical ArcGIS Server environment. This factor often makes debugging difficult. Below I have made an expanding list of issues related to cahcing as well as the various cache levels to clear when debugging.

Issues related to caching: query does not return correct field as defined in 'outFields' query property

Cache Levels

1. AGS web service cache, this can be cleared by restarting the AGS web service from ArcCatalog or by web admin interface
2. Tile cache, cleared by 'deleting cache', and then reconfiguring tile cache within service properties of the web service
3. Web browser cache, cleared by clearing cache of web browser
4. REST API cache, cleared by going to http://yourserveraddress/ArcGIS/rest/admin/cache and clearing cache manually
5. KML cache, if using a Google hosted KML with your application, to have this load dynamically you need to add a random number to the end of your kml address ... google it!
   

To do a non-boolean conditional insert in SQL Server (the "where exists" clause is best for boolean), use the "where in" clause along with an inner join, in the following form:

INSERT INTO [Destination Table]([Destination Field1],[Destination Field2],[...])
SELECT [Origin Field1],[Origin Field2],[...]
FROM [Origin Table]
WHERE ([Test Field A, could be from origin] Not In (select [Test Field B] from
[Test Table, could be destination]));

Gmaps: GEvent and "a is not defined"

I was getting the (generic and fairly common) "a is not defined" (on line 287 of main.js I believe) javascript error with gmaps when trying to add a GEvent click event listener. I intially thought it was an issue with my some map layers with ArcGIS Javascript Extension for Google Maps, but I resolved this issue by sticking the even handler in the initialize function. For some reason gmaps must not have been available at the scope of the even handler outside this function ... the gmap object was being instantiated inside this function, yet the variable was first declared outside the function in the first lines of the application ... strange.

Flash Map Workflow, described

Thematic groups created in ArcMap and enhanced with Adobe Illustrator (AI), were saved as separate AI files (by region) and further organized as AI “layers.” Note that a layer should be created for every group of shapes which would be always dealt with in the same way in an animation. For example, if a group of lines would appear and disappear together and never separately, they should be organized into a layer. These layers later correspond to symbols in the Flash application. All layers were then selected and copied/pasted into Flash at once. Flash has the ability to implicitly import AI layers as Flash layers copied and pasted into it. Note that the “paste in place” command should be used in order to guarantee uniform positioning across layers copied/pasted from different AI files -- the dimensions of the Flash canvas must be the same as the AI document for this to work.

The Flash Actionscript program then deals with all user interaction and data display. Symbols are displayed/not displayed, fade in/out, or are magnified based on program functions referencing the symbols indicated by other user interface symbols (buttons, etc.) which are invoked by user interaction.

New Tool: Map Extent Calculator

I just created a small tool for calculating decimal degree map extents :

you can access it here

add arcgis service remotely from arccatalog

when adding a service to a remote arcgis server you cannot browse to the actual location of the mxd or geodatabase on the remote server. windows file sharing addresses do not work. you must use the address on the local machine exactly as if you were accessing the mxd from the local machine.

this is a nice pear db query which returns an associative array

$data = $sqlconnectionobject ->getAssoc('sql statement here',true, array(), DB_FETCHMODE_ASSOC, true);

if (PEAR::isError($data)) {
die($data->getMessage());
}

Setting up a Tiling/Caching Google Maps/ArcGIS Server Solution

Setting up a Setting up a Tiling/Caching Google Maps/ArcGIS Server Solution has gotten much easier with the advent of ArcGIS Server 9.3 and its REST-ful Javascript API. Specifically, the ArcGIS JavaScript Extension for Google Maps makes creating this kind of mashup almost a breeze ;)

added 8/20/08: this link at an ESRI site really helped a lot

1. reproject shapefiles into world mercator web projection (only available in 9.3)



















2. add new service from mxd containing that/those shapefiles

3. enable tiling in the "service properties" for your web service. You should use the google/virtual earth tiling scheme



















4. paste the service url into the example code for the extension

ArcGIS Server, not able to "input layers" for cache, or preview map in ArcCatalog

In trying to make an ArcGIS server (9.3) caching service I was initially not seeing any option for the required "input layers" parameter in the cache manager (through service properties).

Afterwards I noticed that I was unable to preview the map service in ArcCatalog (though coordinates were visible at the bottom of that frame).

This issue was probably fixed by changing properties to include all arcgis server related accounts (used "everyone", just to make sure it was a permissions issue) as having read permissions for the individual shapefiles participating in the mxd I was trying to publish.

It may have also been fixed by not initially specifying a cache directory when creating the service through ArcCatalog, but probably not.

Arcmap geocoding error

Was getting the following error when trying to geocode, though I suspect it might come up whenever a feature class cannot be created which would have an invalid data table (dbf) associated with it:

Create Feature Class

There was an error trying to process this table.

Operation failed

Solution: Remove any unnecessary fields from the table

Workflow: Access to Arcmap and Back

1. export as .xls (with no formatting option)
   
2. open w/ excel 2003 or earlier. press ctrl + A twice, do column autofit
   
3. convert all double number fields to number w/ adequate number of sig digits (6 is good for decimal degrees)
4. export as dbf
5. import dbf to arcmap
6. when creating shp via geoprocessing/etc name 6 chars/less and no special chars
7. close arcmap
8. import into access
   

postgres to mysql workflow

postgres to mysql workflow

1. Export database from postgres to plain text sql. You should do this with the pgAdmin III tool by right clicking on the db you want to export and choosing "Backup ..." this will take you to the following screen, where you should select options as displayed below (notice the "Plain" and "Insert" options are checked):


2. Once you have exported your plaintext sql you must create tables in mysql into which you can insert your data. Do this by copying the CREATE statement appearing in the "SQL Pane" (after you have clicked/selected a table) in pgAdmin III into the MySQL query line and execute this. Do this for every table you want to import

3. Now run insert statements ... if you get errors, make sure the table structure of the new table is the same as your old table (and insert statement)

Import/Copy CSV/data to Postgres

I recently was having trouble importing CSV to postgres. The trouble was with empty trailing columns (the first row had an empty trailing column). I found that a tab delimited file (export from excel, using export as ms-dos text) allowed me to avoid warnings about empty columns.

was getting this error in postgres while trying to run the "COPY" command from sql:

WARNING: nonstandard use of escape in a string literal

This error was followed by a description of where the error was occuring, which was showing the error in the wrong place. To fix this issue, make sure that all slashes (such as in file paths) are "/" rather than "\"

Here is an example of a proper COPY command:

COPY count_crime TO 'c:/temp/temp.csv' USING DELIMITERS ','

was getting the following error after installing php 5.2.6 on IIS with IIS CGI configuration:

PHP Warning: PHP Startup: Unable to load dynamic library 'C:\Program Files\PHP\ext\php_pgsql.dll' - The specified module could not be found. in Unknown on line 0

I resolved this by replacing the .dll in this location with my .dll from a previous install (php 5.2.5) ... you can probably use the .dll from any version

are you getting field value with with the leading zeros excluded, but are joining to a field with the zeros included? this is a common issue with census tract numbers, for instance.

you can use the vbscript below in arcmap or other vba data programs to generate a six character value with leading zeros, where the value is less than six characters

*remember new field MUST BE TEXT format, else will always loose leading zeros!

parameters:
short_text_field: shortened field which longer field will be based on
need to change the loop condition to the longer number of characters involved (here is 6)

dim txtShort
txtShort = CStr([short_text_field])
Do Until len(txtShort) = 6
txtShort = "0" & txtShort
Loop

I was getting the following error on my web browser:

"The directory name is invalid."

I assumed this was a PHP error, and indeed is probably related in some way to PHP, but realized that this was coming up instead of a 404 error ... I'm not sure why.

I just typed the correct URL and the page worked.

having problems with your pear install on a drive other than c (d?) on iis? you must run the pear install .bat from the cmd prompt referencing the pear install .bat on that drive. otherwise, pear is configured to run on the c drive, and therefore doesn't work. this tip from the cml dba, karl dailey.

a common task I come across is the need to create a table of "distinct" records based upon "uniqueness" of values in a given field.

SELECT * INTO new_distinct_table FROM [old_non_distinct_table] WHERE [some_unique_field] In (SELECT Max([some_unique_field
]) FROM [old_non_distinct_table
] GROUP BY [non_unique_field_of_interest]);

I had earlier shown how to do an update query based on a field from another table in MS Access. Here is the syntax for postgres:

UPDATE to_table SET to_column = from_column from from_table where to_table.primary_key= from_table.foreign_key;

handy notes for this query:

• append in postgres is || (double pipe)
• cast in postgres is like column_to_cast::type_to_cast
  

using xxamp for a lamp (linux,mysql, php) environment to do development on php? perhaps you are already running an iis server (or even the others through iis) ... in that case the default ports for webhost and mysql db will already be in use. to get apache to listen on other ports you will have to change the "listen" and "server" parameters in respective httpd.conf. For mysql .cnf the parameters are "port" at 20 and 27

you should do this at least for:

1. http: in C:\xampp\apache\conf\httpd.conf
   
2. ssl: in C:\xampp\apache\conf\extra\httpd-ssl.conf
   
3. mysql: C:\xampp\mysql\bin\my.cnf

you may also want to change port values for mysql in:

• C:\xampp\mysql\bin\mysqld-nt.exe
• C:\xampp\apache\bin\php.ini (line 795)

add the following lines to administer your server with phpmyadmin (at line 20 in C:\xampp\phpMyAdmin\config.inc.php), with whatever your new port number is in the section noted below

• $cfg['Servers'][$i]['host'] = 'localhost';
• $cfg['Servers'][$i]['port'] = 'whatever your new port number is';
  

alright, there is actually a way to do this built into excel! good, it is as it should be! format > column > autofit selection

obsolete idea:
ever wondered how to adjust all columns to the smallest width necessary to fit all data, all at once? i just figured this one out, but wish i knew before:

1. hit select all twice (i.e. ctrl + a, ctrl + a)
2. right click on the header area of your table, select 'column width'
3. set your column width to 1

voila ... your columns are all now the proper width.

I often get tripped up doing an update of one table from fields in other table in access sql. i'm not sure if its because its different in other sql's, but in access it is required to do an inner join to the table which is being updated within the foreign update query.

Here is the general syntax in ms access sql to do one of these babies:

UPDATE [toTable] INNER JOIN [fromTable] ON [toTable].[primaryKey] = [fromTable].[foreignKey] SET [toTable].[toFieldUpdate]] = [fromTable].[fromFieldUpdate];

Gone are my days of crawling ftp uploads. Now I just upload zip archives and expand with this nifty little php file, which works on linux machines, and even provides a gui listing all .zip files in the folder it is located within.

Read more here.

openlayers wms manager error on iis

The WMS Manager library for openlayers is a great tool for allowing users to dynamically select layers from one or many wms servers. While demonstrating setup of this library on our localhost for the advanced gis class I teach, I came across a confounding error "Unhandled Request return Object Not Found."

The example page I am attempting to view is at "lib/openlayers/examples/wms_manager.html"

First, an aside of sorts: This error was actually one of many due to some issues with paths (in the WMS manager example versus our local version). To avoid this error get an openlayers install with WMS manager and required libraries already integrated. You can find this on SVN at: http://svn.openlayers.org/sandbox/ominiverdi/openlayers (you can use a SVN program like tortoise for windows.

This error occurs because you do not have a proxy host defined. A proxyhost is necessary when making sending a request to a remote host from javascript, since javascript is not normally allowed to do that.

Openlayers comes with a proxyhost cgi called proxy.cgi which is located in the examples directory. If your server is not setup to run python cgi's you must do so now. If you are running windows 2000 or have experience with iis (and/or have already installed python on your machine) I recommend this tutorial, if you are installing on windows server 2003 check out this one. If those don't get you going here is the one from microsoft with each and every step. The funny thing about the microsoft tutorial is that they actually give a non-working example to test your iis-python-cgi install, which causes your browser to attempt to save the test file every time you try to access it. Use the example here ("Problem 3") to correct that issue

Once you have IIS set up to deal with python cgi's, drag the proxy.cgi file into your new cgi-bin folder. Finally change OpenLayers.ProxyHost = "http://localhost/cgi-bin/proxy.cgi?url=" (if your cgi is at the root of your localhost like mine). For WMS manager you must change this variable in "/lib/OpenLayers/Ajax.js " and in "lib/openlayers/examples/wms_manager.html"

But, lo, you may find yourself stuck with an "Unhandled request return Bad Gateway" error when you try to pull up a wms host -- NO NEED TO WORRY -- this error occurs when you have not yet added the host to your proxy.cgi file. Just open up that baby and stick your desired host name in there and you should be good to go (just try another wms host to see it work if you don't believe me).

URI limit in sajax

another embarrassingly simple fix which took me a little while to figure out:

While passing a hierarchical list to sajax to write to a file, I failed to get a return response. I thought this might have something to do with the heavily nested hiearchy which I was having problems with earlier in the day. As is often the case the reason was much simpler: by default sajax can only take a limited (and relatively small) number of characters in variables, since these are all being passed through the URI.

The solution here is to use post instead of get to send ajax requests. Here is a snippet to change that setting in your sajax head (probably at the top of your page):

$sajax_request_type = "POST";   // or "GET"
sajax_init();

MS Access Error on running "join/select into" query

Was getting the following error when running a "join/select into" query in MS Access

"Cannot open database ''. It may not be a database that your application recognizes, or the file may be corrupt."

I followed a bunch of suggestions I had found on the web ... especially this post which had suggested that I should reinstall Jet (which led me to this post which suggested I ultimately needed to reinstall Windows XP SP2 ... which in a slipstream install means that the entire OS needs to be reinstalled!)

Ultimately, I was able to solve this problem by simply saving the database in MS Access 2003 format (.mdb) from the 2007 format I had stored it in (.accdb). I was able to run the query from the mdb and then resaved back into Access 2007 format, where I am again able to re-rerun the query.

complete workflow for transfering wordpress site to iis

this workflow assumes you are using the WP-DB-Backup plugin (I highly recommend) to backups of your mysql/wordpress db. Also am assuming that you use the mysql gui tools to interact with your desktop db (also highly recommended)

1. do a database backup with WP-DB-Backup
   
1. optional: save the archived .gz in your backups folder, nested inside a dated directory, and expand here ... you will need to expand this either way
2. expand downloaded .gz if you didn't do it in 1.1
   
2. download wp folder (or whatever the name of your root install is) via ftp
   
1. optional: download this into your backups folder, nested inside a dated directory. Copy the wp directory to your inetpub folder
   
2. if haven't already done so in 2.1, move wp directory into inetpub folder
3. change permissions on this folder so that user has read/script execute permissions on all child objects
   
3. open up mysql query browser
1. make your wp schema default (or create one if you don't already have a schema for wp)
2. next, file > open script > [browse to path of (expanded) .sql file saved with WP-DB-Backup]
3. execute this query
4. inside the wp_options change the following parameters to equal your new localhost install rather than the previous web install
1. siteurl
2. home
3. any other options from plugins which have an external path defined
   

Adding a 3rd sidebar

I've begun adding a 3rd sidebar to my theme as a first step towards figuring out how to add widgets outside of sidebars. While it took me an hour or two to figure this out, the solution is quite simple. This post helped a lot.

1. Duplicate sidebar.php within your theme folder. This will become your new sidebar.
   
2. Remove all code between the php if function_exists tags. Also remove all except one li block (having class=widget)
3. So far your code might look like this:

1. <div id="new_sidebar">
      <ul class="sidebar_list">
          <?php if (!function_exists('dynamic_sidebar') || !dynamic_sidebar(1)) : ?>
          <li class="widget">
              <h2>Blah</h2>
              <ul>
                  Blah Blah
              </ul>
          </li>
          <?php endif; ?>
      </ul>
   </div>
   



4. Next you will need to change two parameters so that wordpress knows it is looking for 3 (or more) sidebars each with an id 1-3
1. First you will need to change the id parameter of the dynamic_sidebar function on each sidebar.php file for example you can see that the dynamic_sidebar function below has a parameter of 1 ... thus this sidebar.php file should relate to sidebar id 1 ... each sidebar.php should have a different integer here from 1-3 (for three sidebars)
   

1. 
          <?php if (!function_exists('dynamic_sidebar') || !dynamic_sidebar(1)) : ?>
   

2. Next we will change the parameter of the register_sidebars function (which is normally in functions.php but may be elsewhere in your wp install) to 3 (since we have three sidebars to register)

1. register_sidebars(3);
   

SWF Flash Maps

Are SWF maps worthwhile, or just a gimmick?

Many sites have been putting up some pretty slick SWF maps lately, and the big map api's (google, yahoo, mapquest -- ve?) have followed suite. There's obviously something cool about these SWF maps. However, when I tried to figure out if it would be worth creating one for the most recent CML application, I was hard pressed to figure out how to make a flash map do what I want it to to do, and would it be worth it. Therefore, I figured it would be useful to describe why these flash maps are cool: its because they're vectorized and all geometries are fully loaded after the flash is loaded. This allows all sorts of interaction with individual elements -- elements which don't need to be pulled from remote store. Yet, a prime concern also seems to be more control over styling, and quick integration with other adobe cs tools.

but for a site which needs to generate vectors on the fly from a spatial database, which would then be loaded into the map, is flash still worthwhile? the speed of static geometries would be sacrificed, much of the non-map ui enhancements done in flash can be replicated by using scriptaculus. Other features must be considered.

Especially consider the NYC Subway map -- very nice -- if I can get something like this working, it would be well worth doing in flash -- still though, none of these geographies are generated on the fly.

OOOOH ... I THINK WE JUST NEED TO INCLUDE ALL VECTORS AND JUST TURN THEM ON AND OFF BASED ON ATTRIBUTES IN THE DB OR XML!!!!


Check out these links:
http://www.onnyturf.com/subway/
http://coenraets.org/blog/2006/06/google-maps-collaboration-using-flex-flash-media-server-and-ajax-updated-for-flex-2-ga/
http://justin.everett-church.com/index.php/2005/11/08/maps-that-are-consistent-with-your-design/
http://developer.yahoo.com/flash/maps/
http://map.uoregon.edu/
http://www.theweathernetwork.com/

Steps for making Transparent FeatureType from PostGIS DataStore

Though OpenLayers does define an opacity property for the layer object, I haven't been able to get it working. Therefore, I have had to direct define opacity within the SLD associated with the layer I am interested in making transparent. If you have defined the Polygon SLD for your feature type: within the administration web interface, navigate to Config>Data>Style> choose the Polygon SLD and edit this. Finally find the tag. Within this you should find a tag wtih a corresponding value. You need to create a new tag value pair that should look something like this 0.7. This parameter tells the map viewer to display this layer with an opacity of 70%.

proper pear db query for an awesome ass. array

$data=& $dbh->getAssoc('select * from table', false, array(), DB_FETCHMODE_ASSOC, false);