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 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.