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.

Google +1 Button is Going International

The biggest search engine in the market Google has announced that they will be launching the +1 Button internationally. The Google +1 button is the company attempt to gain ground on Facebook Like button. However, because of the Google brand behind social meter it can assist brands to generate more trust in their sites when they have many +1’s. Until now this has been showed only on English results but it has now been released to sites across the world wide web. This is great news for Hispanic and international brands that are running an online marketing campaign.

Here are some of the countries where the +1 button is firstly going to be rolled out at:

• Google.Co.Uk
• Google.De
• Google.Jp
• Google.Fr

Quickly after this initial expansion has been completed they will start to expand further into all other Google search result portals. Webmasters can now go into Google Webmasters Central and use the +1 button tool to generate code snippets to +1 buttons which is available in 44 different languages. The Official Announcement was made today on Google’s Webmaster Central Blog.

A Successful Inbound Marketing Campaign

Google Analytics eCommerce Tracking

Here is the script that needs to be added to the receipt or confirmation page, after the user has completed the sale. You will just need to add php inside of the script so that the content can be created dynamically per customer. One tip is to use a PHP loop. Here is the code:

<script type="text/javascript">



ξ var _gaq = _gaq || [];

ξ _gaq.push(['_setAccount', 'UA-XXXXX-X']);

ξ _gaq.push(['_trackPageview']);

ξ _gaq.push(['_addTrans',

ξ ξ '1234', ξ ξ ξ ξ ξ // order ID - required

ξ ξ 'Acme Clothing', ξ// affiliation or store name

ξ ξ '11.99', ξ ξ ξ ξ ξ// total - required

ξ ξ '1.29', ξ ξ ξ ξ ξ // tax

ξ ξ '5', ξ ξ ξ ξ ξ ξ ξ// shipping

ξ ξ 'San Jose', ξ ξ ξ // city

ξ ξ 'California', ξ ξ // state or province

ξ ξ 'USA' ξ ξ ξ ξ ξ ξ // country

ξ ]);



ξ ξ// add item might be called for every item in the shopping cart

ξ ξ// where your ecommerce engine loops through each item in the cart and

ξ ξ// prints out _addItem for each

ξ _gaq.push(['_addItem',

ξ ξ '1234', ξ ξ ξ ξ ξ // order ID - required

ξ ξ 'DD44', ξ ξ ξ ξ ξ // SKU/code - required

ξ ξ 'T-Shirt', ξ ξ ξ ξ// product name

ξ ξ 'Green Medium', ξ // category or variation

ξ ξ '11.99', ξ ξ ξ ξ ξ// unit price - required

ξ ξ '1' ξ ξ ξ ξ ξ ξ ξ // quantity - required

ξ ]);

ξ _gaq.push(['_trackTrans']); //submits transaction to the Analytics servers



ξ (function() {

ξ ξ var ga = document.createElement('script'); ga.type = 'text/javascript'; ga.async = true;

ξ ξ ga.src = ('https:' == document.location.protocol ? 'https://ssl' : 'http://www') + '.google-analytics.com/ga.js';

ξ ξ var s = document.getElementsByTagName('script')[0]; s.parentNode.insertBefore(ga, s);

ξ })();



</script>

Creating custom 404 page

Everyone at some point has been browsing trough google or their favorite search engine and clicked on a link just to go into a white page with the words ???404 Error??? up on top. After this the only thing you have is to hit the ???Back??? button on your browser. As a user, this means that you go backand try another link. As a developer do, it means that you lost a potential clients, and that you have a glitch in ξyour application that you are not aware of.

You can set upt a custom 404 page using your apache .htaccess file. Here is how to do this:

1. Access your server via FTP
2. Make sure that you can see hidden files
3. Locate for .htaccess
4. If .htaccess does not exist go ahead and create it
5. Open the file
6. Type: ErrorDocument 404 /404.php
7. Save the file
8. Now you can go and edit this file so that you can have a custom error page.

That is all that you have to do in order to set up your server to have a custom 404 page. Use your imagination on this one, for examle you can have a search function or related pages to what the user was looking for. You can also have this page email you so that you become aware of the situation.

Hope this helps, any questions or concerns can be submitted below.

Basic AJAX connection using the POST method to retrieve content

Hello guys,

I have been working on a project for school which used AJAX and I just wanted you guys to have this code for reference. This code basically makes a call to a PHP script which gets Zip Code information (city and state) and then adds them into your web page. I used a span tag with id of ???zipinfo??? to act as a holder for the content that was going to be retrieved. Hopefully this helps some of you in your projects.

//Get Zip Code Information

function zipInfo(zipcode) {

fieldValue = ???connection=zipInfo&zip=???+zipcode;

var url=???includes/management.php???;

httpRequest(???POST???, url, true, fieldValue, function(result){

document.getElementById(???zipInfo???).innerHTML = result;

});

}

//AJAX Request

function httpRequest(reqType, url, asynch, fieldValue, callback) {

request = new XMLHttpRequest();

request.open(reqType,url,asynch);

request.setRequestHeader(???Content-type???, ???application/x-www-form-urlencoded???);

request.setRequestHeader(???Content-length???, fieldValue.length);

request.setRequestHeader(???Connection???, ???close???);

request.send(fieldValue);

request.onreadystatechange = function() {//Call a function when the state changes.

if(request.readyState == 4 && request.status == 200) {

callback(request.responseText);

}

}

}

Creating a Sample HTML5 Structure

 

TML5 is the thing that developers are starting to take a look more and more. More and more browsers are getting compatible with it and with the iPhone choosing this as it???s main multimedia language I have no doubt that this will be demanded in great quantities. In this post I will explain how to create a sample HTML5 structure for your web site.

<!DOCTYPE HTML>

<html lang="en">

<head>

<meta charset=utf-8>

<title>Sample HTML5 Structure</title>

<style>

header, nav, article, footer, address, section {

ξdisplay: block;

}

#container {

ξwidth:900px;

ξmargin:auto;

ξbackground-color:white;

}

header {

ξbackground-color:#666;

}

nav li {

ξdisplay:inline;

ξpadding-right:1em;

}

nav a {

ξcolor:white;

ξtext-decoration:none;ξ

}

nav a:hover {

ξtext-decoration:overline;ξ

}

h1, h2 {

ξmargin:0px;

ξfont-size:1.5em;

}

h2 {

ξfont-size:1em;

}

footer {

ξbackground-color:#999;

ξtext-align:center;

}

</style>

<script>

document.createElement("article");ξ

document.createElement("footer");ξ

document.createElement("header");ξ

document.createElement("hgroup");ξ

document.createElement("nav");ξ

</script>

</head>

<body>

<div id="container">

ξξξ <header>

ξξξ ξ<h1>Sample HTML5 Structure</h1>

ξξξξξξξ <nav>

ξξξξξξξξξξξ <ul>

ξξξξξξξξξξξξξξξ <li><a href="#">Home</a></li>

ξξξξξξξξξξξξξξξ <li><a href="#">About</a></li>

ξξξξξξξξξξξ </ul>

ξξξξξξξ </nav>

ξξξ </header>

ξξξ <section>

ξξξ ξ<hgroup>

ξξξξξξξ ξ<h1>Main Section</h1>

ξξξξξξξξξξξ <h2>This is a sample HTML5 Page</h2>

ξξξξξξξ </hgroup>

ξξξ ξ<article>

ξξξξξξξ ξ<p>This is the content for the first article</p>

ξξξξξξξ </article>

ξξξξξξξ <article>

ξξξξξξξ ξ<p>This is the content for the second article</p>

ξξξξξξξ </article>

ξξξ </section>

ξξξ <footer>

ξξξ ξ<p>This is the Footer</p>

ξξξ </footer>

</div>

</body>

</html>

As you can see the syntax is very similar, the start tags changed somewhat, but it makes it more convinient for the coder. Alsoas you can see now instead of making separate divs to stylize the header and footer, now HTML5 gives you a specified tag for each of them. The <nav> tag is the where your main site navigation will be at. You can also see the <section> tag, this will define the differents parts of your page, for example articles, blogroll, etc.ξ The <hgroup> tag is used to group various content heading, for example when you have an Article title and a Subtitle.

The JavaScript part of the code will create the tags so that older browsers and IE will accept them. This step is very important, if you do go ahead and skip it then older browsers will have trouble understanding your ode and the results will vary a lot.

Please leave any comments and/or suggestions below,

Gilberto Cortez

Image Resizing with PHP

Hello guys,

Today I will post a image resizing script, this will assist you in all of your sites specially ecommerce ones in which you need to display your products with different sizes. The script is self explanatory, but please send me a message or a comment if you need further assistance with it.

//Image Target

$pic = ???images/???.$_GET[‘pic’];

//Set Max Sizes

$max_height = 48;

$max_width = 48;

//Get Image Size

$size= getimagesize($pic);

//Set Ratios

$width_ratioξ = ($size[0] / $max_width);

$height_ratio = ($size[1] / $max_height);

if($width_ratio >=$height_ratio)

{

$ratio = $width_ratio;

}

else

{

$ratio = $height_ratio;

}

//Set new size

$new_widthξξξ = ($size[0] / $ratio);

$new_heightξξ = ($size[1] / $ratio);

//Set header

header(???Content-Type: image/jpeg???);

//Create Image

$src_img = imagecreatefromjpeg($pic);

$thumb = imagecreatetruecolor($new_width,$new_height);

imagecopyresampled($thumb, $src_img, 0,0,0,0,$new_width,$new_height,$size[0],$size[1]);

imagejpeg($thumb);

imagedestroy($src_img);

imagedestroy($thumb);

All you need to do now is to point the src element of your image tag to this script, and set the file you desire using the get method. Ex: img.php?pic=image.jpg

Please contact me with any doubts,

GilbertoξCortez

Interactive Web Development for ???The Cloud???

Lesson 2: Pre-Production Process and Market and Keyword Analysis

This is the second part of a series of search engine optimization lessons. On this lesson I will start to explain you what I call the “Pre-Production” process of your campaign. At the start of each campaign one must do a market and keyword analysis research. Market Research is when one researches and evaluates the current online competition for that specific client. By doing this you will be finding out who your actual competition is and what they are doing to stay on top of things. You can find your competition by using different methods, one of them is by doing a search on the keywords that you think your client will rank for, and you will notice that some websites will start to looking familiar after a while. Write them down in a document.

 

Lesson 4: Internal and External Linking