Here is a way in order to create SQL from Excel data. Suppose a table data is stored in an Excel sheet and you want to create insert query for data in a particular row. Let us imagine that there are 4 columns in Excel sheet -

In order to cerate the insert SQL for a table with 4 columns sufficient to accommodate these 4 values, following is the format for SQL which you need to specify in Column E -

="Insert into QUOTES values(’"&A1&"’,'"&B1&"’,'"&C1&"’,'"&D1&"’);"

So that after pressing enter/return key, table looks like -

This is quite helpful when you are having hundreds of rows in excel and you can just create SQL for all of them on fly by just dragging down the above column for rest.

If you use CVS as source control for your projects, you might be familiar with general problem while transferring files from one user’s machine to other user’s machine.

When a file is checked out from CVS, a hidden folder with name CVS also gets created for the user who checked out the file. And there is a specific file in CVS which is created for storing user’s login information. And when there is very deep level hierarchy for such files and directories, it becomes very difficult to go to every file and change the user login information. To get over with this problem, here is a java file which can be used in order to replace login information of one user with another user for all the files and folder lying within root folder. You can download it here (CVSModify.java) and use it after going through the instructions given in the file.

There are two major issue with PHP, MySql and Apache combination when dealing with database dump or executing a SQL through phpMyAdmin - one that you are not allowed to upload a SQL file more than a specific limit and secondly, you keep getting following error again and again -

   1: MySQL Server has gone Away - Error 2006

I am working on Windows Vista and using phpMyAdmin as interface to MySQL.

To fix upload problem, you need to update upload_max_filesize variable inside php.ini file. By default, it is set to 2M (2 MB). I have changed it to 170M in order to accompany the files I am dealing with.

In order to keep MySQL functioning properly and not throwing errors like 2006 which comes up because of extremely large queries, set max_allowed_packet = 20M which is 1M by default. You can check value of variable by executing following command on MySQL prompt -

   1: show variables like ‘max_allowed_packet’;

In addition, to save your session timing out quickly, you can also consider changing max_execution_time variable in php.ini and LoginCookieValidity inside config.inc.php of phpMyAdmin.

There are more settings which can be referenced in order to keep all these things working properly. I am linking all the files with this post -

• httpd.conf
• my.ini
• php.ini
• config.inc.php

If you have installed or re-installed Apache server and you are facing error corresponding to port 80 specially when you think of yourself completely insane during installation, it is very possible that some other service is accessing Port 80. Following is the error message which appears while starting Apache as service or running httpd command from command prompt within Apache bin directory -

The Apache service named  reported the following error:
>>> (OS 10048)Only one usage of each socket address (protocol/network address/port) is normally permitted.  : make_sock: could not bind to address 0.0.0.0:80

In my case, I was running Skype which blocked port 80 and therefore, Apache wasn’t starting. Both Skype as well as Apache are justified in using same port as per its definition -

Typically, an HTTP client initiates a request by establishing a Transmission Control Protocol (TCP) connection to a particular port on a host (port 80 by default;

Apache Lounge (The Apache Webmasters & Programmers Community) is quite useful community to look out for resolution of most of your Apache related doubts, specially on Windows platform. The solution to above problem can be found at this thread. There is another useful thread which details about problems faced while re-installing Apache guiding about un-installation procedure and then installation of apache and PHP along-with their consolidation. I faced this problem after the consolidation, so came across this thread too.

In continuation to my initial PHP hiccups (personal notes)…There is an excellent tutorial available to install Apache, MySQL, PHP, PHPmyadmin and Zend which provides clear guidelines for installing and integrating these platforms with each other. It is available at Learn to install Apache, MySQL, PHP, phpMyAdmin & Zend Optimizer. Although it is a bit older but still quite helpful.

One confusion while installing PHP appears as which extensions need to be selected while installing PHP. Following are the ones which I select as a beginner user -

• IMAP
• MBString
• MySql

After installing PHP, you need to configure httpd.conf file of Apache in order to incorporate PHP. Here are the few useful lines to be added -

Allow from 127.0.0.1 inside Directory tag

DirectoryIndex index.html index.php inside IfModule dir_module tag

#ScriptAlias /php/ "c:/server/PHP/"
AddType application/x-httpd-php .php
#Action application/x-httpd-php "/php/php.exe"

Configure php.ini file properly and enable the selected extensions.