An example of creating a MySql database with PHP and downloading it as CSV

Download the example package: php_mysql_csv_example.zip

I needed to create a simple and easy to modify intranet database for certain project, and I now offer the setup as a complete working example. PHP and MySql are probably the most commonly used tools to organize information on the internet. The files have been derived from a couple of tutorials about creating a MySql database with PHP and .csv export, and I have compiled and commented the package from the ready job to an easily digestable form. The package is needlessly modular but I hope it provides building blocks for something of your own.

The example is meant to be studied in a localhost environment (Mamp (Mac) / Wamp (Win) / Xampp (Linux)). By using PhpMyAdmin or similar available tool a user named root with password root has to be created (Such user is generally preset in the environment). The user, password, host, database name and table are set in dbinfo.inc.php file. The main variables are then used throughout the example.

Next the browser needs to be directed to file createdatabase.php to create the database and table. The database creation should success with preset values at least. If there are problems check the host address and user handling from the environment help. Index.php is then to be opened with the browser. I will not review the files further, the functionality is pretty straightforward. HTML forms are used to relay data to PHP scripts, which create various MySql queries using the given variables. The method to order database data by clicking table headers is also provided.

I moved the ready job to a closed area by using .htaccess and .htpasswd files, and used .htaccess rules to protect some of the files individually from being downloaded and viewed. Especially dbinfo.inc.php needs to be protected from outsiders. If the aim is to create a public database the sql input strings need to be checked for injections. This example as such is vulnerable and open.

Download the example package: php_mysql_csv_example.zip

Sources:
http://www.freewebmasterhelp.com/tutorials/phpmysql
http://www.ineedtutorials.com/code/php/export-mysql-data-to-csv-php-tutorial
http://www.w3schools.com/php/php_mysql_intro.asp
http://stackoverflow.com/questions/2483379/order-by-column-name-help-via-link-in-html-table-view-php-mysql

Leave a Reply