Data stored in a MySQL database is easily sortable by computers, but the table file itself can be difficult for humans to read and is not necessarily compatible with other computers. To easily share your data, you can export the MySQL information to a spreadsheet, converting it to what is known as a “comma separated” format, a system of dividing the tabular data so that it can be analyzed by a program like Microsoft Excel.
Instructions
- Create a new blank text file and name it “export.php.” Open the file in a text editor – or a PHP development environment, if you have one – ” ” (Don’t include the quotes) on the last line to demarcate the PHP code – everything else will be written between lines
- Define a PHP link variable via the mysql_connect () function and MySQL database location, username and password. . If the database were located at “mysql.example.com” and the user “johndoe” had the password “12345”, type (include the quotation marks):
- $ link = mysql_connect ( ” mysql . example.com ” , ” johndoe ” , ” 12345 ” ) .
- This should be the second line in the PHP file, right after “<php “=”” .=”” <br=””>
- select the database with the mysql_select_db () command, putting the database name in brackets. If your database were named “my_database,” you need to type the following as the third line of PHP code (the quotes around the database name):
- mysql_select_db ( ” my_database ” ) ;
- create the variable “$ getdata,” and use PHP’s mysql_query () function to create a valid MySQL query. To select everything from the table “my_table,” you need to type (include the quotes around the MySQL query):
- $ getdata = mysql_query ( “SELECT * FROM my_table “);
- Change the query in the brackets of mysql_query () if you want to export anything less than your full table
- Copy and paste the following code directly into the PHP file just after line 5 and before the closing “?>” Tag, without making any changes to the code:
- rowpr $ = ” ” ;
- while ( $ row = mysql_fetch_array ( $ getdata , MYSQL_NUM ) ) {
- foreach ( $ row da $ el ) {
- $ rowpr = $ rowpr . $ el . “,” ;
- }
- $ rowpr = substr ( $ rowpr , 0 , -1 ) . ” \\ n”; }
- mysql_close ( $ link) ;
- echo $ rowpr
- Save the file and close the text editor. If the PHP server is not on the same computer, transfer the file to the PHP
- server
- open the file in a web browser,. You will see a long string of text made up of data. Choose “Save As” (Control-S on most PCs, Command-S on Apple OSX) and save the file with the “.csv” extension. This file can be opened in Excel, edited and shared with others who also have Excel on their computer.
