GS Design Just another developer blog

As some of my posts they usualy start by a question on a forum i write ( you can find them in the about page ). So this time some one asked how do you insert an excel file into a mysql database, and since this is a common problem for many beginers, and had to answer this before i decided to make a post about it so next time just post the link to this ( will decrease my spent time by not writing the same thing twice and bring some trafic ... a win-win situation ) ).

But enough chit-chat, lets get down to the dirty job. I will try to brake it down into small steps and give a few explination at each one.

1. Export the excel file into CSV (comma-separated values)

For those who don't know CSV is a file type that stores tabular data. This is a very basic and quite old format and this is why CSV files are suported on all computer platforms.
Example:

The above table of data may be represented in CSV format as follows:

1997,Ford,E350,ac, abs, moon,3000.00
1999,Chevy,Venture "Extended Edition",,4900.00
1996,Jeep,Grand Cherokee,MUST SELL! air, moon roof, loaded,4799.00

As you can see from the above example each row from the table is stored in a separate in the CSV file, and the values from each row are separated with a comma, from wich comma-separated values name, pretty no-brainer deduction. )

When you will try to export the excel file into this format you will most likely get an error like :

file.csv may contain features that are not compatible with CSV ...DO U WANT TO KEEP THE WORKSBOOK IN THIS FORMAT ?

This is quite normal since the excel file contains things like formulas, cell styles and other things that cannot be saved in the CSV format.

2. Import the CSV into MySQL database

For this step you actualy have 2 options. One the one you read in the title of this post, by using PHP, but there is an easier way to do it by using PhpMyAdmin.

Importing CSV using PhpMyAdmin
This is done quite easy. Most PMA versions have support for CSV, have not seen a server installed PMA that didnt.
You just need to login into PMA, and go to the table you want to import the data into. Once there you select the Import Tab and on the page select the CSV format, select your file and click import.
One thing you must keep in mind is that you need to have the same number of columns in the mySQL database and the CSV file.

Importing CSV using Php
This method although a bit more tricky for a beginner gives you a bit more freedom with the import procedure. For one thing it doesn't matter how many columns does the CSV file have or the order in it. You can make the script insert the data in almost any manner you want.

So here is the actual code :

That is about it ... of course i assume you have a mysql connection opened ... if you dont know what i am talking about you might want to google-it.

There is another alternative, to use a php class from phpclasses.org called Quick CSV import that is going to make things a little easyer for you.

I am not going to be to descriptive about the script, because i think it is pretty basic, it basically makes a mysql insert query for each row. For those who don't understand parts of it i am happily to answer any of you questions.

Hope you have gained even a little knowledge from this article, and i am awaiting your comments.

On a recent project i had to take a HTML source code and break it into several pieces, all with ought breaking the HTML tags. After struggling for a while i started googling for a solution from the most inspired developers . After some time of trying different codes i managed to come upon the code below. It is a method from cakephp and it did the job perfectly.

I hope your lucky to find this article and save your self some time.
Cheers

This article is meant to help you to speed up your application by by tweaking the php configuration. The time gained might not be to visible to the user, but i should relief a little of the load on the server. Also this tweaks are going to help you a little in security.

If you are on a shared server and dont know how to change your php configuration you might want to check this article also.

But enough talk and lets get right into it. I will write the configuration name, the recommended setting and how this helps your application.

short_open_tag = Off

This will turn off short tags ( <? .... ?> ) and have the php engine one problem less to worry about.

asp_tags = Off

Do not use ASP like tags: <% echo “hello world”; %>

memory_limit = 32M

This is one configuration that you might need to tweak once in a while. But for start it is better to set it to a lower value and adjusting it when you need it. This will help you keep track of your aplication memory usage.
An article about memory usage you can find here.

register_long_array = Off

This will tells PHP to not register the deprecated long $HTTP_*_VARS type predefined variables. The default value is On. Since almost nobody ever uses them any more ( unless they have some compatibilty problems with old scripts, and they need them on ) it's recommended to turn them off, for performance reasons. Instead, use the superglobal arrays, like $_GET.

This directive became available in PHP 5.0.0 and was dropped in PHP 6.0.0.

register_argc_argv = Off

This is more or less the same with register_long_arrays referring mostly to GET informations so it should be set to Off as well.

auto_globals_jit = On

This makes php stop generating the SERVER and ENV variables at script start ( default behaver ) and generating them when needed (Just In Time). These should help performance by cutting down the memory used by your scripts.
For this to work you need to have register_globals, register_long_arrays, and register_argc_argv disabled.
As an inside info : i tried this on a server .. .and it didn't seem to create any SERVER or ENV variables, but i guess it was the server configuration because on a different server it worked .

magic_quotes_gpc

From my experience having this on can be a real pain in the [****]. When this is on, all ' (single-quote), " (double quote), \ (backslash) and NUL's are escaped with a backslash automatically. Why this is bad ? because you dont have a control over it. For example you use a mysql wrapper for youre database interactions that already has an escape function, this will make your values double escaped, and you endup after a few submits with a bunch of backslashes. So better have it off and do your own escaping, and maybe use a cleaning function to prevent SQL Injection ( more info here )

always_populate_raw_post_data = Off

Raw Post data is one of the more advanced knowledge, so if you dont use it, relief php of it concern and set always_populate_raw_post_data = Off.

Sometimes your application might require one or more specific php configurations changed from its current value in order to work properly. The most common of this is the requires global_registers flag off ( i have no idea why some servers keep it on ).

If you are talking about a local sever running on your computer or a server to witch you have root rights, the solution is quite simple : you just modify the PHP configuration file (php.ini) to your needs, restart the server ... and voila job done.

But what if you are on a shared server. You cant just go and ask the servers support team to modify the configuration for you ( they couldnt even if they wanted to, because that will affect all the websites on there server and then there would be some angry folks on the chat really fast ). Luckily the nice folks that designed the software running the server ( apache most of the time ) have thought of you and this situation and added a 2 possibilities for you to change the php configuration only localy without affecting the other websites hosted on the server.

1. Creating a local PHP.ini file

You just create a php.ini file in your domain root and add your configuration as if it was the php.ini from the actual server settings. This will overwrite the settings just for your domain. Getting back to our example where you want to disable register_globals flag off you would add a line to the file like :

“register_globals = off”

2. Using a .htaccess file

The second way would be to use a .htaccess file ( some of you used to do URL rewrite with it ). Now for this you have to know a certain syntax :

# to modify a flag attribute
php_flag [name] [value]

# to modify a value attribute
php_value [name] [value]

Again returning to our example : php_flag register_globals off

To check if your configuration has been changed you can use the phpinfo() function. Your new setting will appear on the local column.
You should know that that not all servers support this methods of modifying the php configuration, some will only support one of them, so you might want to check this would your server support if it doesnt seem to work.

On another note not all php configuration can be changed locally. A full list of php configuration variables can be found on php official website at :

http://www.php.net/manual/en/ini.php#ini.list

The options that can be overwriten localy have the PHP_INI_PERDIR or PHP_INI_ALL Changeable column. The ones with PHP_INI_SYSTEM can only be changed by the php.ini from the php instalation directory.

PHP Fatal error: Allowed memory size of 33554432 bytes exhausted

This is an error that seem to puzzle most beginners, so i will try to explain what this error is all about.First lets understand why this is happing, there are a few reasons why your getting this error:

• the output of your is very large
• your script grabs over the 8MB default memory limit of PHP ( maybe from POST FILES variables )
• there's a bug in your script that is causing the script to eat that much memory

Now dont get scared its not a big deal, and usualy its a easy fix . To solve this problem you should try to:

• optimize your script so that it will not eat up the maximum memory limit
• find bugs (memory leaks) in your script
• minimize the output of your script
• increase the memory_limit setting in php.ini or directly in your php script by using the ini_set() function

This 8MB default limit is coded in php to stop any script from taking to much memory out of the server and maybe crashing it. This is way the best method of dealing with this error is to figure out what is causing the need for this much memory and reducing it, rather then going for the easy way out and increasing the limit.

Hope this helped you and i await your comments

This operation is a very simple one and any one can do it using an image procesing software. But when it comes to doing in PHP, then it becomes a bit tricky since php does not have any default functions for this operation.

Grayscale Images

First off let me explain what a grayscale image i. I know you probably think you know this, but i am going to explain it a bit different, in a way that will help you how to convert images to gray scale in php.

A grayscale image contains only various levels of gray in the image, which can be expressed mathematically by the red, green and blue values (expressed as RGB) all equal.

Convert an image to grayscale

Now for the important part how this convertion is actually done. Of course, true color images can have any value for r, g and b, so in order to convert this to a grayscale value a transformation is needed : from RGB to HSV ( Hue, Saturation and Value).
And to make the image grayscale all we need to do is to use the Value, with no Hue or Saturation.
The convertion from RGB to HSV is don trough the equations below :

H = pow(cos( ( (1/2)*( (r-g)+(r-b) ) / ( sqrt( (r-g)*(r-g) + (r-b)*(g-b) ) ) ), -1);
S = 1 - (3 / (r+g+b))*min(r,g,b) );
V = (1/3) * (r + g + b);

Don't get scared by looking at those equations thinking they are pretty complicated, the first two are, but the beauty is we don't need them . As i told you above we only need the value which is simply the average of RGB values.

But enough talk, lets get down and dirty and write the actual code to do an image converion :

Hope this article is going to save you some time on youre googling for a solution, and await youre comments

Sending array by POST is easyer then you thought

All you need to do is name the variables a certain way :

<form METHOD="post" ACTION="index.php" METHOD="post" METHOD="post"><input TYPE="text" NAME="array[0]" />

<input TYPE="text" NAME="array[1]" />

<input TYPE="submit" NAME="submit" VALUE="Submit" /></form>

in the action file you acces the variable like this

$array=$_POST['array']

also if you don't care about the array key's you can even do like this

<form METHOD="post" METHOD="post" ACTION="index.php" METHOD="post" METHOD="post" METHOD="post">
<input TYPE="text" NAME="array[]" />

<input TYPE="text" NAME="array[]" />

<input TYPE="submit" NAME="submit" VALUE="Submit" />

</form>

this way they the array keys will take numeric values starting with 0

Hope this helped someone of a bit of trial and erorr and looking forword to you're coments

After a few tinkering i wrote this bit of code that i use on almost all my websites to determine the page generation time. It's relatively simple but it does the job. you just call the function once at the begining of the script and once at the end .... end voila .