Import CSV Files into a MySQL Database

Web Developer have to face a large amount of obstacles in their every day tasks in order to complete them. There are some thing that are easier to complete than other but a little bit of help can make a big difference when it comes down to finishing on time. Importing a CSV file into a MySQL database is often needed when creating projects for new clients. This is because they will use a variety of desktop software and they will probably tell you, “Here you go” when it comes to geting the data. To your advantage most of this applications have some way of exporting their data into Comma Separated Value files or CSV. Basically it’s a file that has a layout similar to a spreadsheet but instead of boxes for columns you have a coma separating the fields. In the same way in lieu of rows you will have a new line in the document.

The first step is to get that file from the customer or from it’s source and upload it into your server. After this you will need to access your server’s command line via an application like Putty and start your MySQL server.

$ mysql -h localhost -u root -ppassword

You will need to create your database with the fields that you will be needing. once your database has been set up you can import the data from your CSV file into your MySQL database. Here is the command that you will require in order to import the information into your database table.

load data local infile 'myphpdeveloper.com/httpdocs/_assets/cities.csv'
into table phpdev.gc_cities
fields terminated by ','
enclosed by '"'
lines terminated by '\n'
(zipCode, city, state, county, areaCode);

In the first line of the command is where the location to your Comma Separated Values or CSV file is located at. The second line is the table that you want the information to go into. The third line is telling your MySQL server that the values are separated by comas. In the same way the fourth line is describing how the values are enclosed by single quotes. The fifth line is telling your server that your row ends when the line end. Finally the sixth lane is the fields in your database table. They need to be aligned with the order of your file for example if your files is ID, Price, Description then your locations will be (id, price, description) and so on.

Often there are times that the above command will not work. This is due to the fact that this option is considered to be a big security risk. Some hosting companies like Media Temple are disabling this by default so you will need to make a couple of small changes in order for it to work.

You will have to locate your MySQL configuration file which is usually located at ‘etc/my.cnf’ and comment out the line that prevents the import. You will have to do this while being a root user in your server, if not it will only allow you to read the file but not to reconfigure it. The line that you are looking for is:

local-infile=0

You will have to comment it by adding a “#’ sign at the beginning of the line like:

#local-infile=0

You can learn more about this on the MySQL Documents. After you have completed the change you will need to restart the MySQL server and then do the process once more. The command to restart the server is:

service mysql restart

Hope that this helps any web developers out there. Please feel free to leave any comments, questions or suggestions down below and we will be happy to assist you.

Transfer a Large Database using MySQL Dump and a SHH Client

Have you needed to transfer a large database? One of an eCommerce Cart or of a big blog? phpMyAdmin only allow’s imports of databases of 2 mbs, if you fix the php.ini file you can get maybe 10 or 12 max on most servers. In order to move this you will need to do it through shell access. Here is instructions on how to do this:

Contact your service provider and tell them to allow shell access in your account.
Download a SHH client like puTTY
Open the application, enter your domain and the port. (I have usually encounter them to be 22 or 2222)
Enter your username and password. These are usually the same as your FTP credentials.
To export or dump the MySQL database you will be using a tool calles MySQL Dump, to do this you will type:
1. mysqldump -u [username] -p[password] [dbname] | gzip > [location/name].sql.gzip
2. the items in [ ] need to replaced and the brackets removed. Also please note there is no space between -p and your password
3. This will create what is called a dump file of your database which will be gzipped for easier development.
Now you need to get the file that was created and upload it to your new site via an SFTP or FTP client.
After this enable shell access on your new server or location and access it
First we need to decompress the .gzip file, to do this type:
gzip -d [location/file].sql.gzip
After this is completed the gzip file will dissapear and you will only have the sql file needed.
Now all you need to do is import the file into your database, to do this type:
1. mysql -u[username] -p[password] [dbname] < [location/file].sql
Your import and transfer of your MySQL database is now complete.

————

Do you need any work performed on your database or on your web application? You can contact me at (619) 800-2678 or using the Quote Request form.

Basic Content Management System

If you are a Web Developer like me, then you are always working from different locations, different machines, etc. You do not want to go and look for that client information every time to gain access via FTP to do a small change. There is where a good content management system comes in place. What this is going to allow you to do is to give you the ability to edit any file from any location and from any machine as the editor will be sitting right on the server.

Here is a copy of my Content Management System, which you can just drop in any folder and edit or delete any file in it.

Download My Content Management System

Once you download it, just unzip it and try it in one of your folders. This is a basic management system all that you need to do to install it is to upload, there are a PHP file that is the one doing all the work, and the Tiny MCE folder, which contains all of the required files for the text editor skin, you can delete this if desired. You can also get an updated version by doing a Google search of Tiny MCE.

Please give me your comments and suggestions,

Gilberto Cortez

———————————-

Got termites? Fumigations are the only primaryξrecommendationξgiven by the state. Any other solutions are only secondary and temporary.

MySQL Database Interaction PHP Class

There are many ways out there to connect to a database, but there is only a few of them that are effective. This is the MySQL object that I use to connect to a database via PHP. You will find this class to be simple but very effective as it’s not overfilled, but it only contains what it’s needed in order to compile a successful application. I have used it multiple times in tasks that include from basic user managements to custom cloud application development. From connecting to a single database, to multiple databases.

Here is the code, please make sure to replace the values of the variables so that you can successfully connect to your database.

class db

{

//Object Variables Please Replace, you can also have them set on the constructor so that you can connect to multiple databases.

private $status;

private $host = ‘localhost’;

private $database = ‘db_name’;

private $username = ‘username’;

private $password = ‘password’;

public ξ$result;

public ξ$results;

function __construct(){

//Connect To Database

$this->status = mysql_connect($this->host,$this->username,$this->password) or die (‘Cannot Open Conection to ‘ .

$this->host);

//Select Database

mysql_select_db($this->database,$this->status);

}

function query($sql){

//Get Results

$this->result = ξmysql_query($sql);

}

function fetch($sql,$type){

//Perform Query

$this->query($sql);

switch ($type){

//Fetch as an Array

case ‘array’:

$this->results = mysql_fetch_array($this->result);

break;

//Fetch as an Asspciative Array

case ‘assoc’:

$this->results = mysql_fetch_assoc($this->result);

break;

//Fetch as an Object

case ‘object’:

$this->results = mysql_fetch_object($this->result);

break;

}

//Return Results

return $this->results;

}

function checkit($sql){

//Check Table for Query

return mysql_num_rows(mysql_query($sql));

}

function __destruct(){

//Free Query Result

mysql_free_result($this->result);

//Close DB Connection

mysql_close($this->status);

}

}

Please let any comments, suggestions orξrecommendationsξon the area below.

Import CSV Files into a MySQL Database

Transfer a Large Database using MySQL Dump and a SHH Client

Basic Content Management System

MySQL Database Interaction PHP Class

Enjoy this blog? Please spread the word :)