Sunday, 26 June 2011

MySQL - avoid beginner traps


Photo by pcambraf

When my client's project (http://connectotravel.com) got online and started to chew real data, we found out that some functions perform too slow.

I started to evaluate application code and performance, using CodeIgniter Profiler class. It revealed that some SQL queries were too slow.

Some of it had unnecessary tables, left hanging there after quick development without enough testing. Removing those tables from FROM statement helped significantly.


Photo by taberandrew

But the most important issue was the way how I used to link tables. There were some SQL queries, SELECT queries linking seven, eight or more tables. I used WHERE all the time. Like this: (pk - primary key, fk - corresponding foreign key)

SELECT tbl1.field_A,tbl2.field_D FROM tbl1,tbl2 WHERE tbl1.id=tbl2.fk
It is OK if you use it in a simple example like this. But for linking more tables, that would be no way to do it, instead, one should use INNER JOIN statement.

Let's say, you need to select something from 3 tables. This is a simple example:

SELECT tbl1.field_a,tbl2.field_c,tbl3.field_g FROM tbl1 INNER JOIN tbl2 ON tbl1.pk=tbl2.fk INNER JOIN tbl3 ON tbl2.pk=tbl3.fk

After implementing this on all big SQL queries, performance speed increased up to ten times or more!

You have an excellent (visual) explanation of SQL joins in this article.




Other thing that helps a lot are SQL table indexes.

Whenever you create table, it has one index and that is primary key.
But you should create indexes on every column that is used to link to other tables or column that would be used in WHERE criteria.

In other words, index should be on fields where server does search.
That is usually foreign keys. Another case are table fields mentioned in WHERE clause.
So making an index on the column speeds up the query.


Photo by kyz

Simple syntax for making an index on tbl2.fk would be:

ALTER TABLE tbl2 ADD INDEX ( `fk` )

This is of course nothing new, just an example from a real situation and experience.

Thursday, 2 June 2011

Database management by single PHP file

Yesterday I found myself in situation when I had to look for something else regarding MySQL database administration. Google search led me to something surprising. It is named "Adminer" and it exists in one single PHP file.

On it's web page, it has a detailed comparison with similar, well known tool, PhpMyAdmin which I had been using for years.

http://www.adminer.org/en/phpmyadmin/

I decided to give it a try and downloaded version "for Mysql".

Since there were no installation instructions, I just put it in my local htdocs directory. Then entered in browser address bar: localhost/adminer-3.2.2-mysql.php

And voila, login prompt.


After entering your credentials, it is straightforward to use if you have previous experience with similar tools.

I am not a power user of PhpMyAdmin, however, I see that Adminer has all features like the former tool.




Look and feel is simplistic. But you can choose some of available skins. Download css file and copy it in the same directory where you put adminer-x.x.x.php.




Do you know of something similar? Share your experience.

Wednesday, 18 May 2011

Recover lost page in Drupal

Did you ever lost a page, in Drupal CMS, without having a backup copy?

It happened to us yesterday.

While working on repeating and rather boring task of pasting a chunk of the same code in many existing pages, my co-worker made a mistake and deleted content of a very important page.

It took us some time to realize what exactly the problem was. Since it was the front page, we thought that the site was not working at all.

The only backup, unfortunately was some 20 days old.

I tried to find some solution googleing about it. First few results clearly explained that Drupal, by itself, does not have some kind of recycle bin. Even the worst fact was that the page was not deleted at all, just completely overrun by the wrong piece of replacement code :-/

Thanks to this post: http://drupal.org/node/392326 we found the solution.


If your page has been online for some time, you can find it by google search, searching by it's url (to get it on top of results). The result has one link labeled "cached" and if you are lucky, cached page will be your exact version that got "lost".

Tuesday, 17 May 2011

Why I love Drupal

Some two years ago, I was starting as a web developer. The same job I work today.
I did not have much experience at real web programming work, only by doing some occasional jobs.

My task, among the rest, was to make a website consisting of some hundred pages. But the real problem is that it supposed to have 115 pages in ONE SINGLE language. Since it was a tourist and traveling services web, it had to have content in 6 languages (two last flags are fake). So I'm talking about almost 700 pages.



I knew I had to use some open source CMS but my short experience on Joomla was not so good. I remember I had been trying to change it's default templates or to turn some custom HTML page into it's template without much success.

Then I stumbled upon Drupal CMS. Despite that my "headache" went worse those days, I started to read it's documentation. After some 5-6 evenings at home, learning for an hour or two a day (after my exhausting daily job) I decided to go for it.


The next step was to find a way to turn regular HTML theme into Drupal theme. I found video podcast on http://mustardseedmedia.com/podcast/episode24 which made it pretty easy to do.
Also a Drupal documentation at http://drupal.org/node/313510 explains it nicely.

This themes making step was the key point.

I did the web site. You can see it at http://accommodation-split.net

Later, to make use of blocks, menus, modules, use JavaScript and PHP code in pages was an easy thing.

And, it saved my job.

Wednesday, 23 March 2011

Simple url rewriting, using Apache mod_rewrite

Some customers want to have nice, fancy, search engine friendly url-s in their browser, on their pages.
That is completely reasonable because it helps to better position the pages in search results.

For instance:


www.somewbsite.com/page1.php?subject=geography&field=cont1


or

www.someanotherwebsite.net/about_us.html

is not so tasty for search engine bots like the following:


www.somewbsite.com/geography/continents/chapter-one


or


www.someanotherwebsite.net/about-us


If your web site runs on Apache web server than you could do it like this:


Create .htaccess file if there is not one already.

Write simple rule.

Write rule using regular expressions.


1. .htaccess file


Just a snapshot of some other code, you start from an empty .htaccess file


Look for it in your server root folder, public_html or htdocs. Open it using your text editor and put on a first line:

RewriteEngine on


Let us use it on above mentioned example:

2. Simple rule

Add this to .htaccess file:

RewriteRule ^about-us$ about_us.html


The part between ^ and $ is your url how you want it to be shown. The rest of the line is the actual file name of a document to be opened in browser.


3. A bit more power...


If you have url queries like in the first ugly url, you must use regular expressions:


RewriteRule ^([a-z]+)/([a-z]+)/chapter-one$ page1.php?subject=$1&field=$2


[a-z] matches all characters between lowercase a and z.
+ sign means any number of ocurrences, and brackets () define a variable to be used later in the rule.
So first ([a-z]+) in our example, matches 'geography' and the second one matches the word 'continents'.
These two variables are referenced in the real url by using $1 and $2. Numbers are assigned by the order of variable appearance.


If it does not work, perhaps you don't have mode_rewrite module enabled on your server. You can check that by executing a php file that contains only one function:

<?php
phpinfo();
?>


Under 'Loaded Modules' section look for mod_rewrite. If it is not listed there, you have to enable it by removing comment from corresponding line in httpd.conf file and restarting Apache server.
If you don't have access to httpd.conf then ask your hosting service to enable the module.

Saturday, 12 March 2011

How to make a simple web page

You need:

- web browser (Firefox, Internet Explorer, Safari or something alike)
- plain text editor (Notepad, Vim, Arachnophilia,...).

The most important part of a web page is HTML document. That is a text document containing page formatting code (HTML tags) and page items (texts, pictures etc...).

Let us assume you use Notepad which is bundled with any version of Windows.

General plan:
Make simple HTML document using some common basic tags. Choose Notepad text editor as main tool now.
Fill the document with some texts and an image.
Add link to another page.
Open it in web browser to see how it works.



Procedure:

1. Create document file

Open Notepad text editor:
(Start->All programs->Accessories->Notepad).
It will show you a blank untitled document. Save it first:
File -> Save as
Now you see a Save As dialog box. It has a field labeled "File name".

Name your document as my_page.html

IMPORTANT:
You have to type name of the document enclosed in quotation marks, like this: "my_page.html".
Otherwise, Notepad will name it like my_page.html.txt and it wont work.

Then you click Save button.

2. Start coding

Make sure you remember where you saved your document, so you can find it later.

Now, We'll start to write code:
You begin with <html> tag, it marks the beginning of a HTML document.
HTML tags are special markings enclosed between angle brackets, like so: <html>, <body>, <h1>, <h2>, etc...

Second tag is <head>. Head section contains code that does not appear on a visible part of a web page, it has some special purpose.

After that, give your page a title: <title>This is my first web page</title>.  Almost all web tags have it's closing tag. It has an extra slash character, like so: </html>,</title>, </body>, </h1> ...
Close the head section: add closing </head> tag.

3. Page content

Now comes the part of putting actual page content, what is supposed to be seen on the page.
Open body tag: <body>
Your code should look like this so far:

<html>
<head>
<title>
This is my first web page
</title>
</head>
<body>


Let's put some text.

Text is often formatted as paragraphs. Put tag to open new paragraph: <p>.

You can write some text of your own, or copy/paste it from somewhere. I'll use dummy text used for making page templates. You can find it on internet by googling "Lorem ipsum".
Paste it after <p> tag.
Then add closing </p> tag to close that paragraph.

4. Preview in browser

Your page has some content so we could give it a try and see how it looks like in a browser.

But before that we have to close body and html tag.
Add closing </body> tag after the closed paragraph.
Then add closing </html> tag.

Save the document: File->Save

Find the document where you saved it (Desktop, My Documents or else), double click on icon and, voila, you see it in a web browser.
On the very top of browser window, you can see the page title you have defined using <title> tags.

Anyway, not much of a beauty to see.

Let's add some more elements to it.

5. Add text title.

Before the opening <p> tag, add opening <h1> tag. It is often used to make a heading title on a page. Write some title for your paragraph. I'll put "What is lorem ipsum" .

Save it again and refresh your page in browser: Ctrl+F5 (reload) to see the added title.

Your code so far should look like this:

<html>

<head>

<title>
This is my first web page
</title>

</head>

<body>

<h1>What is Lorem Ipsum</h1>

<p>

Lorem Ipsum is simply dummy text of the printing and typesetting  industry. Lorem Ipsum has been the industry's standard dummy text ever  since the 1500s, when an unknown printer took a galley of type and  scrambled it to make a type specimen book. It has survived not only  five centuries, but also the leap into electronic typesetting,  remaining essentially unchanged. It was popularised in the 1960s with  the release of Letraset sheets containing Lorem Ipsum passages, and  more recently with desktop publishing software like Aldus PageMaker  including versions of Lorem Ipsum.
</p>

</body>

</html>

6. How to add image

You need to use <img> tag to display images.

Put it after the closing </p> tag.
First, you need the image file. Copy some file of your choice to the same place where your "my_page.html" document is (Desktop, My Documents, or else). Image file should be of type .jpeg, .gif or .png. Today, mostly used image formats on web pages are JPEG and PNG.
You can check it out by looking at file properties: right click on file icon, then click Properties and see what kind of image file you have.
Change <img> file in the following way: <img src="moon.jpg">
My image happens to be an image of the Moon and it's file is named in that manner: moon.jpg

You should find out what is your file named and what is it's extension. Extension is a part of a file name that comes after the dot.
Image files usually have .jpeg, .jpg, .gif, etc. extension. Check it out by looking at file properties.
After you put exact file name of the image including it's extension in image tag's attribute src="image_file_name.file_extension", refresh your document in the browser (Ctrl+F5) and there is your web page with an image.

7. Link it to another page.


Links are something common to see and use on a web page. It is created by putting anchor tags:
<a href="">Link text</a>

The main part is href attribute. It contains web address of a page you want to link to. I'll use Wikipedia.
So put this under the image tag:
<br />
Break tag <br /> makes break on a page, so the next part starts in a new row.
After the break tag add the link code:
<a href="http://wikipedia.org">Wikipedia</a>
http:// part is essential for browser to recognize that it should look for the linked address at internet.

Refresh page and that is it. Click on the link.
 You have web page at your computer, made by you.
Add more texts, images and links to experiment.

Next time I'll show you how to put it on internet.
The complete code:


<html>

<head>

<title>
This is my first web page
</title>

</head>

<body>

<h1>What is Lorem Ipsum</h1>

<p>
Lorem Ipsum is simply dummy text of the printing and typesetting  industry. Lorem Ipsum has been the industry's standard dummy text ever  since the 1500s, when an unknown printer took a galley of type and  scrambled it to make a type specimen book. It has survived not only  five centuries, but also the leap into electronic typesetting,  remaining essentially unchanged. It was popularised in the 1960s with  the release of Letraset sheets containing Lorem Ipsum passages, and  more recently with desktop publishing software like Aldus PageMaker  including versions of Lorem Ipsum.
</p>
<img src="moon.jpg">
<br />
<a href="http://wikipedia.org">Wikipedia</a>
</body>

</html>

Tuesday, 8 March 2011

Invisible trouble

Perhaps you never encountered this. Happened to me once before and today I managed to fix it.
We use CodeIgniter PHP Framework as main tool in our work on this particular project. Me and my co-workers are developing rather huge website.

I am not going to write it's url here, because it is not fully online yet and I would be embarrassed to let you see all it's flaws ;-)  

The main page (home), on it's top, started to display some error messages like "Headers already sent ... blah, blah" and mentioned Session.php library.

I know that it like happens if one would print or echo something in the same method containing the PHP header() function later.

But there was not supposed to be any header() statements on that page/method.

I googled up some tips about it. The most significant advice was that there are some space characters outside of opening or closing PHP tags in a controller.

Notepad++ did not show any spaces there. Nor did the regular Notepad show anything.

This is a kind of thing that makes my self esteem disappear, in seconds :-/ Don't know when or if I am going to fix it or when.

Then I remembered to try again but using GVim. I was not surprised to see some one or two strange characters on the beginning of controller file, because it was the only logical cause for this error.

It also proved to me that I should use GVim as a main editor.

Why this happened? I suppose that is because different text editors open, save and close files in a non consistent manner, or do it in a different way.