I thought I’d be spending a few hours creating a nice reusable way to turn a flat query result into a hierarchial array so I could use it on things like trees or HTML or whatever. When I wrote it out on paper though I felt stupid because the solution was extremely simple.
Assume you have a query result or data like this:
container1 parentA child11
container1 parentA child12
container1 parentB child13
container2 parentC child14
container2 parentC child15
and so on.
every column represents a parent of the column to its right. Duplicates in the columsn let you know which things get grouped together. The solution to turn this into a hierarchy is:
$arrTree[container][parent][child]
you can follow the same pattern indefinately, change the values and the keys as needed. The simplicity in the solution is that by using the columns as the keys it automatically creates them as unique and puts the children under them correctly. I hope this save someone else the paper epiphany 
I have been using the JQuery for quite a while. JQuery does not have an official plugin for grids, there are several of them out there. I tried out a few and settled on jqGrid I am very pleased with it in every way. I’ve used it in several projects and it’s always been very flexible and extendable for my needs. I’ve been able to easily add after effects like color sorting, drag and drop reorder etc. with ease. Writing the PHP for the typical CRUD (Create Read Update Delete) functions is very easy but is so boring that I had to do SOMETHING that would both give me a bit of fun and give me smoething to make this part of the process faster for me in the future. So I have a .php template here with it, I can change a few variables around and apply it to any MySQL operation that interacts with jqGrid.
define
$crudColumns =
array(
‘id’=>
‘id’
,
‘title’=>
‘title’
,
‘icon’=>
‘icon’
,
‘description’=>
‘description’
,
‘parent’=>
‘parent_list_id’
);
$crudTableName =
‘list’;
$postConfig[‘id’] =
‘id’;
and it’ll create the select, search, paging, insert, update, delete interactions for jqGrid for you.
it also has some convenient spots to edit the sql for additional column / query manipulation. At a minimum it should serve as a good starting point for working with jqGrid beyond the examples they provide (Which are very good and helpful already). If you have any updates or suggestions for this please let me know.
-enjoy
There is a site that has an open source database of IP ranges to geographic info called hostip.info. They alow you to download the database in several different formats. I chose MySQL. However, I don’t care much for the way that they structured the database because it’s divided into 255 tables that each represent the first octet of an IP. So I moved them all into a single table.
Here is some PHP code to generate the SQL script for combining all of the tables and dropping the old ones. I will also included the resulting file.
<?php
for ($i =
0;
$i <=
255;
$i++
)
{
$query =
"Insert into ip4_all(a,b,c,country,city,cron) Select ".
$i.
", b, c, country, city, cron From ip4_".
$i.
";";
$HTMLOutput =
$HTMLOutput .
$query .
"<br><br>";
}
for ($i =
0;
$i <=
255;
$i++
)
{
$query =
"Drop Table ip4_".
$i.
";";
$HTMLOutput =
$HTMLOutput .
$query .
"<br><br>";
}
echo $HTMLOutput;
?>
There are also daily updates to the database which would need to be obtained using scripts and then converted into this new data structure.
Powered by ScribeFire.
Microsoft has a tool that should be in every DBA’s toolbox. Even if you aren’t running MSSQL on a Windows server it’s very handy to run on your windows desktop to get info out of flat files very quickly.
This can be used as a great alternative to DTS because you can insert the records into the DB that you want instead of inserting all records and then filtering.
http://www.logparser.com/
This is a utility that allows you to run a SQL query against a directory of flat files in various formats as if they were in a database. Most of the functions you can think of which are in SQL and are not in many other command line tools for parseing flat files are availabel such as Distinct, group by, order by, count, max, min, avg, etc. There are 3 main parts to it: the input format, the query and the output format. There are many different input formats, I most commonly use IISLog directories as an input, and it automically knows how to recognize it. One awesome thing is that it keeps track of which files it’s been through so it can be setup to only process deltas.
The Queries use a limitied set of SQL syntax.
The outputs can display on the screen, another file, or direct into a database.
here’s an example of a logparser query that will give you a count of how many hits occured on a file in a specified timeframe from all of the IISLogs:
Logparser -i:IISW3C "Select count(*) from f:\IISlogs\*.log where cs-uri-stem like '%%PAGEYOUARELOOKINGFOR'and TO_TIME(time) BETWEEN TIMESTAMP('15:45:48', 'hh:mm:ss') AND TIMESTAMP('21:03:48', 'hh:mm:ss')"
