PHPLayersMenu 3.2.0 follow up

Just spent a couple of hours playing with this script that creates a nice DHTML tree menu system and is pretty well cross browser with a nice fall back feature should it need it.

Anyhow I wanted to change a menu on my site and decided I would adapt this script to use an XML file for it’s menu data rather than a flat text file.

I didn’t want to get to involved in someone elses unsupported code. So, I decided to write a quick php function that takes an xml file and generates the required format that the original script requires. It’s literally just a couple of lines so I thought I’d put it on here:

function displayChildrenRecursive($xmlObj, &$resultString, $depth=0) {
  foreach($xmlObj->children() as $child) {
 // [dots]|[text]|[link]|[title]|[icon]|[target]|[expanded]
 $resultString = $resultString.str_repeat(‘.’,$depth+1).’|’.$child[description].’|’.$child[url].’|’.$child[tooltip].’|’.$child[icon].’|’.$child[target].’|’.$child[expanded].”\n”;
    displayChildrenRecursive($child, $resultString, $depth+1);
  }
  return $resultString;
}

Now all I need to do in the phplayersmenu script is this:

$xml = simplexml_load_file(‘menu.xml’);
$phpLayersMenuString = displayChildrenRecursive($xml, $phpLayersMenuString);

$mid->setMenuStructureString($phpLayersMenuString); //instead of $mid->setMenuStructureFile(‘menu.txt’);

And voila! There you have it – this script now works with a well formed XML document which is a much nicer way to represent a tree!



Making Union Queries work between Excel and Access

I have just succeeded in getting Microsoft Excel 2007 to show the results of a query written in Access 2oo7.

No big deal you might think – except that the query is defined in Access as a Union Query.

Not only that but it requires parameters.

Background: Lots of people seem to be having an issue with Excel complaining that ‘Too Few Parameters. Expected <n>.

<n> here can be any number really. Depending upon what the ODBC driver determines is appropriate for the query.

And herein lies the problem. The ODBC driver for Access is not capable of doing this…

I’ve just spent about 4 hours figuring out exactly what setup does work though…so keep reading.

If you copy and paste the connection string from the wizard into some boilerplate VBA code that uses ADO to connect then when you do a cmd.Parameters.Refresh you will find these parameters have strange names such as Pa_RaM000.

Again the problem is the ODBC Driver – you need to force the Excel Data Wizard to use the OLEDB Driver.

You do this by clicking ‘Connections’ on the Data Tab. Then clicking ‘Add’, then clicking ‘Browse For More…’

Next choose: +Connect to New Data Source (or click the New Source button)

Next choose: Other/Advanced and click Next

Choose the appropriate OLEDB provider. For Access 2007 this is: Microsoft Office 12.0 Access Database Engine OLE DB Provider

Configure the provider and choose any table you like for now (we will change it in a minute).

Once the connection has been successfully created you can edit it within Excel.

Select Connections from the Data tab again. Choose your connection. Click Properties. Select the Definition Tab.

From the CommandType: dropdown you can now select ‘SQL’.

Unfortunately the parameters button will not be enabled but you can call a parameterised Union query like so: EXEC MyUnionQuery “param1″, “param2″, “param3″;

And bingo – that is it.

You should now hopefully have achieved in a few minutes what took me several hours – I spent a long time searching through newsgroups and found little real help. Figured it out through trial and error in the end. I do wish Microsoft would pick this up and fix it in Office 2010 if it isn’t already. I understand that they might not be able to fix the ODBC driver for Access for various reasons but at least the Data wizard should make it a lot easier to do what I have layed out here. The original error when you try this in Excel with a predefined ODBC connection to Access is just so unhelpful to the average user.

If enough people ask me to I might write this up a little clearer with some images – it’s now very late and I’m going to go to sleep.



Zen Cart, MySQL, phpMyAdmin, Apache

I’m currently working on some E-commerce stuff. I’ve been learning quite a bit about Zen Cart, MySQL, phpMyAdmin, Apache.

Spent a couple of hours today upgrading from MySQL 5.0 to MySQL 5.1

It went ok.

While trying to find some quick and easy ways to import data into Zen Cart I’ve ended up looking into phpMyAdmin.

Also MySQL 5.1 seems to support CSV as a storage engine. See this post: http://dev.mysql.com/tech-resources/articles/csv-storage-engine.html

All good stuff – I’m probably going to need to refer back to that sometime hence the post – time to sleep now.



Access criteria expressions

http://office.microsoft.com/en-us/access-help/examples-of-query-criteria-HA010066611.aspx

Very useful list of query criteria expressions for retrieving records by date:

e.g. last week, last month, last 28 days



Open BSD

I don’t often seem to find the time to play around with some of the various UNIX variants out there. Anyway when a customers VPN network appliance died on me today I went to investigate.

It was supplied by a company who are no longer in business so I was pretty much on my own.

After attaching a monitor and keyboard and watching it boot I discovered it was booting OpenBSD 3.8

I didn’t know the root password and this appliance had lost all tcp/ip connectivity. So I googled and found http://www.cyberciti.biz/tips/reset-forgotten-openbsd-root-password.html

This post worked like a charm for me and I had reset the root password within a few minutes.

No time to find out why this appliance cannot communicate over tcp/ip currently.



WDS

Been configuring Netgear wireless routers with WDS settings to extend the range of the wireless signal in a home network environment. All worked great though I was using two DG834G routers and they need to be v3 or higher.