Exporting mysql data to csv

Today I'm going to discuss the opposite of the previous post. Here it is :

/*some checking here like if the user is logged in, this is important in cases where we don't want other users to get hold of the data*/

//connect to the mysq;
define('host', 'localhost'); //Mysql Server name
define('database', 'dbname'); //Mysql Database name
define('user', 'dbuser'); //Mysql Username
define('password', 'dbpassword'); //Mysql Password
$dbtable = "dbtable";
$connection = mysql_connect(host,user,password);
$db = mysql_select_db(database,$connection);

$sql = mysql_query("select * from ".$dbtable.");

$fields = mysql_list_fields("yourdatabasename",$dbtable);
$columns = mysql_num_fields($fields);
$output = '';

// Put the name of all fields
for ($i = 0; $i < $columns; $i++)
{
$l=mysql_field_name($fields, $i);
$output .= '"'.$l.'",';
}

$output .="\n";

//we now put all values from table
while ($l = mysql_fetch_array($sql))
{
for ($i = 0; $i < $columns; $i++)
{
$output .='"'.$l["$i"].'",';
}

$output .="\n";
}

// Output to browser with appropriate mime type, you choose
header("Content-type: text/x-csv");
header("Content-Disposition: attachment; filename=yourcsvfile.csv");
echo $output;
exit;

?>

This script will prompt for a download for the csvfile named yourcsvfile.csv which contain all the columns and entries from the table dbtable.

Importing a csv file to mysql

This is one of the things I did with my current project. It is about adding data on MySQL using a csv file and I thought I should share what I did.

First we need to have an html form that will ask for the file input. It will be something like this one:

[form action="import_csv.php" method="post" enctype="multipart/form-data"]
[input name="csv" type="file"]
[input name="filesubmit" value="Submit" type="submit"]
[/form]

Note: Replace "[" and "]" with "<" and ">" respectively. I did it like this because this blog won't allow attaching of html tags.

The enctype attribute is very important because it specifies to use "multipart/form-data" as content type for the form. This also means that the form requires a binary data like the file. The file input type specifies the input is looking for a file. This will appear as a text field with a browse button next to it.

Then we need our php script that will process the csv file and save the entries on the database.

//this is where the file is stored temporarily
$csvfile = $_FILES['csvfile']['tmp_name'];

$file = fopen($csvfile, "r"); //we now open the file
$filesize = filesize($csvfile); //get the filesize

$contents = fread($file, $filesize); //read contents of the file
fclose($file); //close file

$linecount = 0;
$linedata = array();

//you can modify "\n" if your csv file is ended differently
foreach(split("\n",$contents) as $line)
{
$line = trim($line," \t");
$line = str_replace("\r","",$line);
$line = str_replace("'","\'",$line);
$linedata = explode(",",$line);
$mysqlline = implode("','",$linedata);
$linecount++;

//insert the data into the database
//replace $yourtablename with your mysql table name
$sql = mysql_query("insert into '.$yourtablename.' values('$mysqlline')");
}

That's it. We have a simple php script that accepts a csv file and then reads the contents and save the data into your mysql database. Of course this still needs to be optimize. We need to check if the file input is really a csv file. Or is the file empty or too large? I will leave it up to you.

Oh, and be sure that you connect to mysql first. :)

Another web programming day with phpGoogleStoreLocator

It's been a very tiring day for me. It's raining hard the whole day. Today, I continued with what I have been programming the past 2 days. I've been working on a store locator for a dietary supplement drink website. I've been using this very cool php script phpGoogleStoreLocator which you can download here.

phpGoogleStoreLocator is an application that uses the Google Maps API service which can serve as a store locator in your website. It is very easy to set up and these are all you need to get it running:

• Google API Key (http://maps.google.com/apis/maps/)
• Webserver with php 4.0+
• MySQL 3.0+

Very good right? It is compatible with different browsers too. These are the browsers that are compatible with it right now:

• Internet Explorer 5.5+
• Mozilla/Firefox 1.0+
  
• Safari 1.2+
• Konqueror 3.0+
• Galeon 1.2+
• Epiphany 1.0+
• Flock 1.0+
• Opera 9.01

To install, just extract the files to a directory and run install.php. You will know what to do when you get there. Remember to delete your install.php file after installing.

Some of it's features are :

• A Google Map
  
• Customizable Map Icons
• Balloon images for store locations
• Stores are searchable by country, street address, city, state and zip code.
  
• Support for address level geocoding for the US, UK, CA, AU, All of Europe, Taiwan, India, and New Zealand
• City level geocoding for the entire planet

It also has an admin panel and a customizable layout which is very easy. You can see the full features here.

What I have been doing the past 2 days were modifying some parts of the script and also the template to suit the site owner's requirements. So far so good and it's working like the way he wanted it to be. I'm just waiting for some feedbacks after he finished testing the site. If there are any problems then I will be working on it again tomorrow. For now I'll be on stadnby on that part and I'm going to work on another project for another client. I'll talk about this one tomorrow.