This is a receipe how I save stuff to S3 from my Mac:

1.) Signup with S3: http://aws.amazon.com/s3/ (check pricing!). This will give you access to the AWS Management Console.

2.) Create a Bucket: This can be done via the AWS Management console. If you are not familiar with the concept of ‘buckets’ check-out the S3 documentation. Simply put, it is a virtual storage device that has a fixed geographical location.

3.) Go to ‘Security Credentials’ in your account settings in the AWS Management console and create an accesskey.

4.) Download JetS3t. You then have the following directory on your Mac:
/Applications/jets3t-0.7.3

Open the Terminal and change into the bin directory:
$ cd bin;

Create a file named synchronize.properties there:
$ nano synchronize.properties;
and save the following content using your keys from step 3:

accesskey=XXXXXXXXXXXXXXXXXXXX
secretkey=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

5.) To sync the contents in the path /Users/marco/MySyncStuff with the bucket myBucketName use this command:
$ ./synchronize.sh UP myBucketName /Users/marco/MySyncStuff/ –properties synchronize.properties;

Of course you can create as many buckets as you like and script and schedule your data syncs now from here as you wish. Use the command
$ ./synchronize.sh –help
to see what the synchronize.sh else has on offer.

6.) Browsing buckets: JetS3t has its own S3 browser. To start and use it do the following:

$ cp cockpitlite.sh cockpitlite.command;
$ ./cockpitlite.command &;

You should see the Java coffee cup on your task bar. Use your keys to log in and browse your buckets.

You can also use the free S3 Browser for Mac.

Just in case somebody has a similar problem… Coming mainly from MySql I had to deal with some MsSql/SQL Server specialties. Consider the following working SQL for MsSql:

SELECT AK.AK_TEXT, BU.BUDGET_BEREICH ,TY.AKTIONSTYP_TEXT ,AK.AK_STARTDATUM ,AK.AK_ENDEDATUM
FROM AKTIONEN AS AK WITH (nolock)
LEFT JOIN BUDGET_TRAEGER AS BU
ON AK.AK_BUDGET_NUMMER=BU.BUDGET_NUMMER
LEFT JOIN AKTIONSTYP AS TY
ON AK.AK_TYP=TY.AKTIONSTYP_TYP
LEFT JOIN AKTIONEN_FILIALE AS AF
ON AK.AK_LFDNR=AF.AF_LFDNR
WHERE (
  ( AK.AK_ENDEDATUM<=CAST('2010-06-30' AS smalldatetime)
   AND AK.AK_ENDEDATUM>=CAST('2010-01-01' AS smalldatetime)
  )
  OR ( AK.AK_STARTDATUM<=CAST('2010-06-30' AS smalldatetime)
    AND AK.AK_STARTDATUM>=CAST('2010-01-01' AS smalldatetime)
  )
)
GROUP BY AK.AK_TEXT, BU.BUDGET_BEREICH ,TY.AKTIONSTYP_TEXT ,AK.AK_STARTDATUM ,AK.AK_ENDEDATUM;

Interesting parts explained:

(a) The addition ‘WITH (nolock)’ prevents my application from locking the table during select queries.

(b) The fields from which I liked to select a bunch of records touching a daterange between AK_STARTDATUM and AK_ENDEDATUM were of the fieldtype ’smalldatetime’. This is some strage SQL Server format. It needs to be converted to with the CAST() function in order to return the expected resultsets. It understands several input formats. You can test the correct conversion with a simplified SQL statement like this: SELECT CAST(’2010-03-29′ AS smalldatetime);

(c) The statement will not work and return an error if you do not include ALL your selected fields also in the GROUP BY clause.

We have just had the case of having ftp access to a site on a shared hosting LAMP webspace but needed also the database and mainly the database scheme to setup our own development-system for the app. This is actually very straight forward using the following snippet uploaded and executed on the webserver:

<?php
exec( 'mysqldump -h dbXXX.1und1.de -u dboYYY -pZZZ dbYYY > dump.sql', $ret );
var_dump($ret);

This is what you do:

• Find the configuration with the db-credentials in the source of the application and replace
  • dbXXX.1und1.de with the hostname,
  • dboYYY with the username,
  • ZZZ with the password (notice there is NO blank between the option -p and the password!),
  • dbYYY with the database you would like to backup.
• Save the snippet as file with extension .php and copy it via ftp to the webspace.
• Find the URL to execute the script.
• If it worked, there should be a new file dump.sql in the same directory, download it, it contains the db-schema and all data.
• Delete both files to wipe your traces.

If it does not work try something like “exec(’ls -la’);”. If you see the contents of the directory you can use the exec-function and the problem is something else.

Happy hacking!

This is just a link to a very good tutorial on how to make your own SVN post-commit hook using a PHP script. It sends the following information via email:

• Committer name
• Commit message
• List of files edited
• Diff of changes made

http://techchorus.net/writing-php-script-send-svn-commit-changeset-email-notification

I just hacked together the following quick and dirty PHP-script to use the tidy-extension from the command line. Maybe somebody else needs something like this somewhere. Check the comment for details:

<?php

//
// (a) Save this as tidy.php
// (b) Call it from commandline like this: $ php tidy.php trashed.html > tidy.html
//     to tidy the file trashed.html to a new file tidy.html.
//

// Installation and configuration see http://php.net/tidy
$config = array('indent'=> true, 'output-xhtml' => true, 'wrap'=> 200);

$tidy = new tidy;
$tidy->parseString(file_get_contents($_SERVER['argv'][1]), $config, 'utf8');
$tidy->cleanRepair();

echo $tidy;

Here is how you install PEAR on Mac OSX:

sudo mkdir /usr/local/temp;
sudo chmod -R 777 /usr/local/temp;
sudo mkdir /usr/local/share/pear;
sudo chmod -R 777 /usr/local/share/pear;
curl http://pear.php.net/go-pear | sudo php;

Set /usr/local as path prefix and install.

You should then be able to use pear, e.g.: pear help.

In order to install modules you also need a temp dir:

sudo mkdir /usr/local/temp;
sudo chmod 0777 /usr/local/temp/;

You can now install PHPUnit:

sudo pear channel-discover pear.phpunit.de;
sudo pear install phpunit/PHPUnit;

Have Fun!

Every old PHP4 application faces this question: Does it also run under a newer PHP5 version? Hopefully you have migrated or deprecated all your legacy stuff already… or at least have customers that understand the value of a proper relaunch and are also willing to pay for it.

To make some customer fossils run on a PHP5 host, we did the following dirty hacks:

• Set “AllowOverride All” in your Apache config in order to enable .htaccess files for runtime configuration.
• Copy your legacy app on your new php5 host.
• Place a .htaccess file in the directory containing something like this:

  php_flag register_globals on
  php_flag register_long_arrays on
  php_flag zend.ze1_compatibility_mode on
  php_flag short_open_tag on
  php_value auto_prepend_file "/home/web/my_legacy/_prepend.php"

  More info on configuration changes in php and list of ini-directives.

• Place a file called _prepend.php in the same directory and set path info accordingly:

  <?php
  // See also .htaccess file in this dir with more directives set for this site
  
  // Php4ify error reporting
  error_reporting(E_ERROR | E_WARNING | E_PARSE);
  
  // Add required include paths to some distant libs
  $path_arr = array();
  $path_arr[] = '/home/web/blah/libs/legacy';
  $path_arr[] = '/home/web/blah/libs/whatever';
  
  $path_str = implode(PATH_SEPARATOR, $path_arr);
  set_include_path( get_include_path() . PATH_SEPARATOR . $path_str);

This is indeed very very dirty and should only be considered as last-resort to make some legacy app runnable under php5 without having to do crazy search-and-replace orgies on old stuff.

This is a simple example-snippet of a form with a dynamic add-row feature, done with jQuery. You can also submit the form and see the results. The form re-displays all entered values. You can extend it to max. 10 rows. Take a look at the source, there you will find all you need to know:

• Demo: dyn_form_rows.php
• Source: dyn_form_rows.phps

I love jQuery!

Once in a while every bigger website is relaunched.

In order to deploy bigger changes without bothering your visitors with strange behaviour during a data migration, updates and the like, you should use Apache2’s mod_rewrite. Just put the following lines in a .htaccess file in your webroot directory and all traffic (also deep links to subdirectories) gets diverted to the maintenance-page. The scond line sets an exception for your IP address, so you are the only visitor who is NOT redirected to the mainteance page:

RewriteEngine on
RewriteCond %{REMOTE_ADDR} !192\.168\.123\.101
RewriteRule !maintenance/index.html /maintenance/index.html [L,NC,R=301]

Do not forget to remove the .htaccess file after you have finished your work!

I am currently working on a project in which we have lots of images from an old CMS waiting to be migrated into a new layout. Of course there are restrictions so it should not happen that certain image types exceed a certain max. width.

OK, we have many many images… So I took a closer look at ImageMagick (also take a look at the usage examples). And I have to say: Awsome!

You can install ImageMagick on Ubuntu or Debian with a simple
# apt-get install imagemagick

In combination with a bit conditional scripting I came up with the following solution:

Console doing bulk resize.

I wanted to have a shell script that, given a directory containing all our images, checks the width of each image and resizes it if it exceeded a certain width. Simple, but powerful.

Usage:

$ ./resize_image_dir.sh ../../brand_logos

And you are done with thousands of images in a minute. Do not forget to make a backup if designers change the desired width later…

You can download the shell scripts with example images ready to test:
!resize_conditional_images_bulk2

I just hacked together a very simple class and CLI script to help me generate Google sitemap XML on a daily basis using PHP. If you have not yet heard of it: Google sitemaps help you promote your content if you have certain deep links that only show up if a visitor for example performs a search and clicks a result.

Check the documentation of the Google Sitemap Format

Here is the code: Google Sitemap Generator

It works like this:

$ php generate_gsitemaps.php; #will generate your standard sitemap
$ php generate_gsitemaps.php -e > my_sitemap.xml; #echo sitemap xml to another file


Example: generated_example_sitemap.xml

Have fun being found!

Case: I was building a CLI script to aggregate data from one MySql database and write results to another MySql database using 2 open db-connections. This way the script’s execution time was in the some-minutes-range… not good! I tried a more effective attempt: I let the CLI script output all necessary statements to drop and create all needed tables and fill them with insert-statements. I called it like this from the command line and diverted output to a file:

$ php aggregatData.php > all_sql_i_need.sql

I could then import the whole thing in one go into my target database like this:

$ mysql the_dbname -h localhost -uMarco -pPass < all_sql_i_need.sql

Insight: The execution-time was way better than my first attempt and took only seconds. I could also have combined the two lines in a shell-script.

The problem with the generated SQL statements was, that the data also contained relations between primary and foreign keys of records and that these IDs were generated at import-time by auto_increment fields. I solved this issue by using MySql variables which can be set and use d again like this:

INSERT INTO gruppen (name) VALUES ('Gruppe 1');
SET @temp_gruppe_id = LAST_INSERT_ID();
INSERT INTO gruppe_eigenschaften (id_gruppe, name, wert) VALUES
     (@temp_gruppe_id, 'email1', 'email1@emailxxx.com');

The variable @temp_gruppe_id is set and can be used in the subsequent SQL statements. It also worked in MySql4.

Here is an example of SQL statements illustrating this mechanism in the above mentioned script. You can cut-and-paste it into your PhpMyAdmin.

I needed to see some debug output on a production system and used the following hack:

error_log("\n@@@\n" . var_export($this->config, true) . "\n");

To see the output, I set a tail on the error-log-file:

tail -f ~/sites/logs/php_error.log

I had a bunch of .dbf (source was Lotus Approach) files, at which I wanted to shoot SQL-like queries to generate some meaningful reports. So there were 2 tasks to be accomplished:

1. Read the data into a tool or database and
2. formulate and perform the queries to generate results to base your reports on.

As of opening and saving, I first tried MS Access. But some of the tables could not be imported (reasons unknown). Then I tried to open those files with MS Excel. It worked, but I had more than 65536 records per table, so I could not actually use Excel. After some investigation I came across dbftools which promised to help me converting the data into a text-based format.

I simply unpacked the C source and typed ‘make’ from the console and I had a working dbf2txt binary. I created a directory with all the dbf-files in it and hacked together the following simple shell script: convert.sh

#!/bin/bash
for i in data/*.dbf
do
dbf/src/dbf2txt -v "$i" > "$i.txt";
done


It supplied me with all txt-files each containing a table with fieldnames in line 1 and the data in tab-separated format. Nice!

To get this data into MySql, I hacked this little tab2sql script which converts the txt-files into SQL statements. It is called like this:

$ php tab2sql.php data/my_table.dbf.txt > sql/my_table.dbf.txt.sql;

You can then dump all sql-files in the sql-dir into MySql via:

$ for dir in data/*.sql; do mysql dbf_import -h localhost -u root < “${dir}”; done;

Thanks Frank Koormann for the dbftools!

Sidenote: If you have big tables and you plan to work with joins… Do not forget to set field-types and indices properly after the import! This could otherwise stress your machine.

On one of my current projects, I had the problem that I had done several commits to the repository and suddenly changed my mind and wanted to go back to a certain point in the revison history where I started with my changes. How would you do that?

Let’s say you started your changes at revision 901 with your first commit and the current revision (HEAD) is 910.

To your working-copy you could just do:

svn up -r 900 .

But that would bring back the unwanted changes to you as soon as you update again and would not affect other people working on the code when they update their workig copies to the current revision. What you really have to do in this case is to ‘merge back’ your working-copy and then commit the changes with a comment:

svn up;
svn merge -r HEAD:900 .;
svn commit -m "Reverted back to rev 900. Sorry, changed my mind.";


Be careful! This only works if ALL revisions (901-910) have been committed by yourself.

I had the case to build a WebService which had to be called transporting an array with some user data. This was the first time I was using an array as parameter in a SOAP-Service. Here are some examples that show what I figured out about how to setup the WSDL:

• Example WSDL describing use of an array as parameter
• Example PHP5 server containing method expecting array as param
• Example PHP5 client using the service

Notice the complexType ArrayOfString at the top in the types-element, which did the trick.

By the way: I could not get my favorite tool SOAPUI to build the appropriate SOAP-request for me in order to test my webservice. ZendStudio did not generate working WSDL in this case.

One of my customers wanted to have spoken text on his webpage. Having no idea about Flash & Co. I found this neat solution: Use a Wordpress-Plugin to do the job.

• Tutorial: http://www.macloo.com/examples/audio_player/
• Download necessary stuff: http://www.1pixelout.net/download/audio-player.zip
• MP3 for testing: Tusch!

A nice shortcut I came across to update content in a mySQL-table without writing a script to do it - which was the first thing that came to my mind…

Case: I had many HTML-snippets in a CMS table called ‘freepages’ containing hard coded domain-names in URLs to images etc. Now guess what happened as we switched the overall site domain from ‘www.mydomain.de’ to ‘info.mydomain.de’? Much of the content seemed to have gone.

This snippet was the elegant way of solving this directly in mySQL without headaches:

UPDATE freepages SET body=REPLACE(body, 'www.mydomain.de', 'info.mydomain.de');

Sweet!

A neat little trick to prevent pesky spam-bots from flooding your form-based applications with garbage: Use a simple token in the session to block bots from submitting data to your forms. It works like this:

1.) Generate a random token when the form is displayed, remember it in your session and put it into a hidden field of your form:

<form ...>
<?php
	/*security*/
	$my_token = create_random_string(5);   //generate the token
	$_SESSION['my_sess_token'] = $my_token;  //set it in your session
?>
<input type="hidden" name="token" value="<?=$my_token?>">
</form>

2.) Check match of those 2 tokens on every form submit and only accept submission if both are equal:

/*process form*/
if ($_POST['submit'] && $_SESSION['my_sess_token']==$_POST['token']) {
	//process your form
}

This is a handy function to create such a token:

/**
* Create a random word
*
* @param    numeric $length character lenth of the word
* @return   string random password of length
*///---------------------------------------------------------------------
function create_random_string($length=6) {
	$arr = array("1","2","3","4","5","6","7","8","9","q","w","e","r","t",
	"y","u","i","o","p","a","s","d","f","g","h","j","k",
	"z","x","c","v","b","n","m","Q","W","E","R","T","Y","U",
	"P","A","S","D","F","G","H","J","K","L","Z","X",
	"C","V","B","N","M");
	srand((float) microtime() * 1000000);
	for($i = $length; $i > 0; $i--) {
		$str .= $arr[rand(0, sizeof($arr))];
	}
	return $str;
} //function

I have been struggeling many times to do initial filling of databases for projects - especially if the data is a deliverable of a customer and the database structure is already fixed. So I built a VBA macro that generates SQL statements for creation of tables and data inserts from a given XLS-file. The idea is to build a ‘template’ of tables (=worksheets with fieldnames in the column headings) which have to be filled with data by the customer. As soon as this file is returned you execute the macro and voilà you have generated SQL statements which you can easily be feed into phpMyAdmin.

You can understand it as follows:

• The XLS-file represents one database with structural information and content.
• Worksheets represent the tables (their name being the tablename). This results in a CREATE TABLE statement.
• Headings (the first line, their name being the fieldname) of each worksheet for as many consecutive cells as you need represent the fields of that table. This results in the fields created in the CREATE TABLE statements.
• Lines from line 2 on downwards until the first empty line is met will be converted to INSERT statements, INSERTS INTO the fields of each cell’s column.

Puzzled? Check out the examples, they should be self explanatory:

VBA macro source

Excel file containing example ‘tables’/worksheets to be exported to SQL and the macro

Resulting SQL saved to C:\exported.sql

The VBA snippet has to be added as a ‘module’ in the Excel VBA editor in the XLS-file holding your structure and data and can be saved with it as a macro. Triggering the macro will then export your SQL statements. You can see this from the example XLS-file above.