MySQL – NULL polja i sortiranje

Vrlo često imamo situaciju da želimo sortiranje po nekom određenom polju u tebeli ali tako da se NULL polja nikada ne pojavljuju na početku. Tipičan primer je recimo frontend koji prikazuje podatke iz neke tabele, gde se klikom na header kolone obavlja sortiranje po rastućem (asc) ili opadajućem (desc) poretku.

Obzirom da se ja u poslednje vreme dosta bavim domenima, kreirao sam jednu tabelu sa par svojih domena, čisto kao demonstraciju koncepta.

mysql> select * from domains;
+----+-------------------+-------------+
| id | domain            | expire_date |
+----+-------------------+-------------+
|  1 | dinke.net         | 2010-01-17  | 
|  2 | lampix.net        | 2009-12-26  | 
|  3 | blogodak.com      | 2010-09-08  | 
|  4 | maestrodesert.com | 2009-09-11  | 
|  5 | nepostojeci.com   | NULL        | 
+----+-------------------+-------------+
5 rows in set (0.00 sec)

Dakle problem, želim sortiranje po expire_date polju ali tako da se NULL polje (recimo domen koji još nije regovan ili je istekao) uvek pojavljuje na kraju. Po defaultu NULL se javlja na početku ako sortiramo u rastućem (ASC) orderu odnosno na kraju ako sortiramo po opadajućem (desc) orderu.

mysql> select * from domains 
order by expire_date asc;
+----+-------------------+-------------+
| id | domain            | expire_date |
+----+-------------------+-------------+
|  5 | nepostojeci.com   | NULL        | 
|  4 | maestrodesert.com | 2009-09-11  | 
|  2 | lampix.net        | 2009-12-26  | 
|  1 | dinke.net         | 2010-01-17  | 
|  3 | blogodak.com      | 2010-09-08  | 
+----+-------------------+-------------+
5 rows in set (0.00 sec)

mysql> select * from domains 
order by expire_date desc;
+----+-------------------+-------------+
| id | domain            | expire_date |
+----+-------------------+-------------+
|  3 | blogodak.com      | 2010-09-08  | 
|  1 | dinke.net         | 2010-01-17  | 
|  2 | lampix.net        | 2009-12-26  | 
|  4 | maestrodesert.com | 2009-09-11  | 
|  5 | nepostojeci.com   | NULL        | 
+----+-------------------+-------------+
5 rows in set (0.00 sec)

Problem sortiranja ćemo rešiti korišćenjem MySQL-ove IF f-je, a rešenje je:

mysql> select * from domains 
order by if(expire_date is null, 1, 0), expire_date asc;
+----+-------------------+-------------+
| id | domain            | expire_date |
+----+-------------------+-------------+
|  4 | maestrodesert.com | 2009-09-11  | 
|  2 | lampix.net        | 2009-12-26  | 
|  1 | dinke.net         | 2010-01-17  | 
|  3 | blogodak.com      | 2010-09-08  | 
|  5 | nepostojeci.com   | NULL        | 
+----+-------------------+-------------+
5 rows in set (0.00 sec)

MySQL-ova IF f-ja slična je ternarnom operatoru, tj. vraća prvi argument ako je iskaz tačan odnosno drugi u slučaju da nije, dakle u ovom slučaju vraća 1 za null vrednosti odnosno 0 za ostale, čime dobijamo upravo prikaz koji želimo tj. NULL polje na kraju liste.

Znam da ovo može delovati pomalo konfuzno pa ću otići još jedan korak dalje i dodati još jedno polje u našoj tabeli čisto radi razjašnjenja šta se ovde tačno događa:

mysql> alter table domains 
add column nullorder tinyint not null;
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

a zatim i update-ovati vrednosti nullorder polja tako da sadrže vrednost IF iskaza odozgo:

mysql> update domains 
set nullorder = if(expire_date is null, 1, 0);
Query OK, 1 row affected (0.00 sec)
Rows matched: 5  Changed: 1  Warnings: 0

mysql> select * from domains;
+----+-------------------+-------------+-----------+
| id | domain            | expire_date | nullorder |
+----+-------------------+-------------+-----------+
|  1 | dinke.net         | 2010-01-17  |         0 | 
|  2 | lampix.net        | 2009-12-26  |         0 | 
|  3 | blogodak.com      | 2010-09-08  |         0 | 
|  4 | maestrodesert.com | 2009-09-11  |         0 | 
|  5 | nepostojeci.com   | NULL        |         1 | 
+----+-------------------+-------------+-----------+
5 rows in set (0.00 sec)

Sve u svemu naš gornji query iz rešenja problema:

select * from domains 
order by if(expire_date is null, 1, 0), expire_date asc;

Potpuno je isto što i ovaj query:

mysql> select * from domains 
order by nullorder, expire_date;
+----+-------------------+-------------+-----------+
| id | domain            | expire_date | nullorder |
+----+-------------------+-------------+-----------+
|  4 | maestrodesert.com | 2009-09-11  |         0 | 
|  2 | lampix.net        | 2009-12-26  |         0 | 
|  1 | dinke.net         | 2010-01-17  |         0 | 
|  3 | blogodak.com      | 2010-09-08  |         0 | 
|  5 | nepostojeci.com   | NULL        |         1 | 
+----+-------------------+-------------+-----------+
5 rows in set (0.00 sec)

osim što naravno nullorder polje nismo morali da kreiramo.

Naravno na sličan način možemo dobiti NULL polja na početku u desc prikazu (za slučaj da je to ikome potrebno).

Client size resizovanje slika

Radeći na servisu Blogodak, nedavno sam se susreo sa problemom kod slika velikih dimenzija koje neki korisnici uključuju u svojim feedovima. Naime, kako Blogodak “vuče” feed-ove sa raznih domaćih blogova, a sa njima i slike koje se u njima nalaze, dešavalo se da one dimenzija većih od 560px uredno “skrljaju” layout blogotka, obzirom da to e == “Microsoft Internet Explorer”)
{prevazilazi predviđenu veličinu containera, tako da u najboljem slučaju dolazi do pojave horizonatalnog skrol bara. Kako nemamo nikakav uticaj na slike koje se vuku sa servera gde su hostovane, server side varijante(npr. resize korišćenjem GD liba ili Image Magicka) nisu primenjive, jedino rešenje je da se dimenzije slike smanje direktno u browseru – Client Side.

CSS Rešenje
Za browsere koji imaju potpunu podršku za css2, dovoljno je staviti nešto tipa:

.container img{
  max-width:560px;
}

u stil strane, tako da će sve slike koje se nalaze unutar nekog <div idclass=”container”>…</div> elementa biti ograničene na max 560 piksela, tj. biće automatski resizovane na odgovarajuću veličinu. Naravno, ovo ne radi u IE-u (verzije < 7) tako da je potreban hack :)

IE Hack (CSS verzija)

.container img{
  max-width:560px;
  /*hack for IE*/
  width: expression(this.width > 560 ? 560: true);
}

Ovaj css hack je validan samo u IE-u, koristi se neka vrsta “ternarnog operatora”, a sam hack skinut je sa ovog bloga. Rado bih vam rekao da više informacija potražite tamo, ali i sam autor priznaje da na razume mnogo oko toga “kako to radi”, ali jednostavno radi. Za ljubitelje standardnijih rešenja, sledi JS verzija.

IE Hack (JS verzija)

Obzirom da mi se nije svidela ideja da koristim nevalidan css kod koji uz to i ne razumem u potpunosti(na stranu što je pravio i neke nekoegzistentne probleme sa IE-om za koji je i namenjen), odlučio sam se za JavaScript rešenje koje sledi.

function fixImages()
{
  //fix images for ie only
  if(navigator.appName == "Microsoft Internet Explorer")
  {
    for(i=0; i<document.images.length; i++)
    {
      //if image is bigger than 560
      if(document.images[i].width > 560)
      {
        imgRatio = document.images[i].width/document.images[i].height;
        document.images[i].width = 560;
        document.images[i].height = 560 / imgRatio;

        //hack neophodan da bi se uklonio skroler zbog prvobitne velicine slike
        //mainContent je ime div kontejnera koji drzi sadrzaj strane				
        divid = document.getElementById('mainContent');
        content = divid.innerHTML;
        divid.innerHTML = '';
        divid.innerHTML = content;
      }
    }
  }
}

Ova funkcija se poziva nakon učitavanja stane(onLoad). U slučaju da je u pitanju IE, proveravaju se dimenzije svih slika na strani(parsuje se niz document.images), i u slučaju da dimenzije prevazilaze 560 piksela, setuju se na manje, uz očuvanje proporcija slike. Naravno, ovaj metod ima manu, jer je neophodno da se sve slike prvo učitaju, pa tek onda dolazi do resizovanja. Kod IE-a i pored resizovanja slike na “prihvatljive” dimenzije, bilo je potrebno nekako mu i staviti do znanja da je došlo do promene dimenzija(samo resizovanje nije dovoljno da nestane horizontalni skroler), tako da je bilo neophodno ručno ili skriptabilno rezisovati i prozor browsera. Srećom posle manjeg “prčkanja” sa alternativama prošlo je i jednostavnije rešenje sa setovanjem kontejnera cele strane na prazan string i vraćanjem na prvobitno stanje korišćenjem innerHTML-a.

Nadam se da će ovo nekome biti od koristi :)

Adsense – izmena javnih reklama

Kao što verovatno već znate, adsense za sadržaj odnedavno podržava i hrvatski jezik, što je mnoge ovdašnje Webmastere(rekao bih ponovo) zainteresovalo za adsense. Naravno, i pored činjenice da su srpski i hrvatski veoma slični jezici, nakon što na vaš sajt postavite adsense, veoma često bićete u prilici da gledate takozvane “javne reklame” (public ads), koje ne donose nikav prihod, a uz to i njihov izgled često značajno odstupa od boja koje ste odabrali za adsense reklame, što dodatno kvari vizuelni identitet vašeg sajta.

Srećom, gornji problem se može jednostavno rešiti postavljanjem alternativnog url-a za public ads, a što je najlepše od svega, u pitanju je sasvim legalan “hack”, dakle google vas neće banovati zbog toga. Sve što treba da uradite je da kreirate posebnu html stranu koja će prikazivati vaše banere, i da google-u stavite do znanja kako da do nje dođe. Pored html strane, možete staviti i link do slike, ali imajte u vidu da ona neće biti linkovana(što imho i nema mnogo smisla), a alternativno možete staviti i boju koja će jednostavno popuniti prostor predviđen za reklamu.

Što se same html strane tiče, tu nema nikakvih trikova, jednostavno kreirajte najobičniju html stranu koja sadrži baner (slika ili flash) koji se opciono može dinamički generisati iz php skripta. Jedina bitna stvar je da se u stilu za stranu setuju margine i padding na 0, i da naravno baner bude identičan dimezijama reklama koju menja. Na primer za reklamu dimenzija 468×60 trebalo da i dimenzije banera budu identične. Takođe, poželjno je da urlovi na objekte koje linkujete budu absolutni.

Evo kako to recimo izgleda na servisu blogodak za prikaz flash banera humanost.org:

<html>
<head>
	<title>Banner</title>
	
	<style type="text/css">
		body
		{
			margin:0px; 
			padding:0px; 
			background:#ffffff; 
		}
	</style>
	
</head>
<body>
 <object classid="clsid:D27CDB6E-AE6D-11cf-96B8-444553540000" codebase="http://download.macromedia.com/pub/shockwave/cabs/flash/swflash.cab#version=7,0,19,0" width="468" height="60">
    <param name="movie" value="http://www.blogodak.com/images/banners/humanost468.swf" />
    <param name="quality" value="high" />

    <embed src="http://www.blogodak.com/images/banners/humanost468.swf" quality="high" pluginspage="http://www.macromedia.com/go/getflashplayer" type="application/x-shockwave-flash" width="468" height="60"></embed>
  </object>
</body>
</html>

Naravno, stranu možete generisati potpuno dinamički, rotirati banere, pratiti statistiku itd. Nakon što postavite vašu stranu na server, neophodno je da google-u stavite do znanja kako da do nje dođe. To možete učiniti tako što ćete proći kroz adsense setup, a zatim u delu sa dodatnim opcijama unesete url do vaše strane.

adsense.gif
Adsense Setup – Setovanje alternativnog url-a za public ads

Alternativno, možete u već generisani adsense kod, odmah ispod google_ad_client linije dodati google_alternate_ad_url liniju, kao na primer:

google_ad_client = "pub-0981372496796058";
google_alternate_ad_url = "http://www.blogodak.com/misc/display_banner.php";
google_ad_width = 468;
...

Dodatne informacije o celoj proceduri možete naći na google adsense help stranama, ovde.

Uvod u GeoIP

Verovatno ste već bili u prilici da koristite Google Analytics alat, gde između ostalog možete na mapi sveta videti odakle tačno dolaze posetioci vašeg sajta, ili ste tu i tamo posetili sajt koji bi Vam između ostalog izbacio podatke o Vašoj trenutnoj lokaciji. Naravno, nije u pitanju nikakva magija, tačna lokacija posetioca definisana je na osnovu njegove IP adrese, a tehnologija koja se koristi prilikom “lociranja” korisnika opšte je poznata pod nazivom GeoIP.

Danas ćemo pričati o tome kako “locirati” posetioca pomoću PHP-a i Max Mind-ove GeoIP baze. U primerima koji slede koristicemo besplatne(lite) verzije GeoIP baza, obzirom da se za pune verzije plaća $50USD + $12USD za update (GeoIP Country baza) i $370USD + $90USD za update (GeoIP City baza). Mana lite verzija je što nisu uvek 100% ažurne, ali će odlično poslužiti za naš tutorijal, a iz ličnog iskustva tvrdim da su upotrebljive i u većini live projekata.

MaxMind obezbeđuje API za nekoliko popularnih programskih jezika, (kompletna lista dostupna je ovde), a detalji o PHP API-u dostupni su ovde. Pored takozvanog “Pure PHP API-a” koji ćemo ovde koristiti, postoje i PECL ektstenzija kao i apache modul(mod_geoip), koji pružaju bolje perfomanse ali i komplikovaniji setup.

Za početak neophodno je da skinete sve fajlove koji se nalaze na http://www.maxmind.com/download/geoip/api/php/ i snimite ih negde unutar vašeg Web stabla(recimo /htdocs/geoip). Za korišćenje GeoIP Country treba skinuti lite bazu odavde, a za city GeoLiteCity bazu odavde. Radi jednostavnosti korišćenja, obe baze ćemo takođe raspakovati u isti direktorijum gde smo i snimili fajlove iz PHP API-a (/htdocs/geoip).

GeoIP Country
——————————–

Idemo sa primerom detekcije zemlje posetioca:

<?php
/**
 * Primer Koriscenja GeoIP Country Baze
 * 
 * @version $Id$
 * @package geoip
 * @copyright © 2006 Lampix.net
 * @author Dragan Dinic <dinke@lampix.net>
 */

require_once("geoip.inc");

$gi = geoip_open("GeoIP.dat", GEOIP_STANDARD);

$ip = $_SERVER['REMOTE_ADDR'];
//ako testirate u lokalu koristite ovaj ip radi testa
//posto ce $_SERVER['SERVER_ADDR'] biti 127.0.0.1
//$ip = "89.216.226.174";

$country_name = geoip_country_name_by_addr($gi, $ip);
$country_code = geoip_country_code_by_addr($gi, $ip);
if($country_name)
{
	echo "Zemlja iz koje nas posecujete je: $country_name <br />";
	echo "Skracena Oznaka: $country_code <br />";
}
else 
{
	echo "Nazalost, nismo bili u mogucnosti da vas lociramo.";
}

geoip_close($gi);
?>

Dakle, na početku uključujemo geoip.inc koji sadrži sve f-je potrebne za korišćenje GeoIP County baze, zatim kreiramo novu instancu GeoIP klase pomoću geoip_open f-je, i na kraju pozivamo odgovarajuće f-je (geoip_country_name_by_addr i geoip_country_code_by_addr) da bi smo dobili ime/kod zemlje u kojoj se nalazi ip adresa posetioca(u slučaju da testirate u lokalu nemojte koristiti $_SERVER[‘REMOTE_ADDR’]).

Kao izlaz skripta, trebalo bi da dobijemo nešto poput:

Zemlja iz koje nas posecujete je: Serbia and Montenegro 
Skracena Oznaka: CS

F-je koje smo koristili da bi dobili podatke o zemlji posetioca, samo su neke od f-ja koje su dostupne u API-u. Ostatak možete i sami pronaći jednostavnom analizom PHP sourca geoip.inc fajla.

GeoIP City
—————————-

A sada da proširimo podatke o zemlji sa tačnom lokacijom (grad, poštanski kod itd).

<?php
/**
 * Primer Koriscenja GeoIP City Baze
 * 
 * @version $Id$
 * @package geoip
 * @copyright © 2006 Lampix.net
 * @author Dragan Dinic <dinke@lampix.net>
 */

require_once("geoipcity.inc");

$gi = geoip_open("GeoLiteCity.dat", GEOIP_STANDARD);

$ip = $_SERVER['REMOTE_ADDR'];
//ako testirate u lokalu koristite ovaj ip radi testa
//posto ce $_SERVER['SERVER_ADDR'] biti 127.0.0.1
//$ip = "89.216.226.174";

$record = geoip_record_by_addr($gi, $ip);

if(!$record)
{
	echo "Nazalost, nismo bili u mogucnosti da vas lociramo.";
}
else
{
	echo "Zemlja: " .$record->country_name . "<br />";
	echo "Skracena Oznaka: " . $record->country_code . "<br />";
	echo "Skracena Oznaka2: " . $record->country_code3 . "<br />";
	echo "Region: " .$record->region . "<br />";
	echo "Grad: " .$record->city . "<br />";
	echo "Postanski Kod: " .$record->postal_code . "<br />";
	echo "Geog. Sirina: " .$record->latitude . "<br />";
	echo "Geog. Duzina: " .$record->longitude . "<br />";
}

geoip_close($gi);
?>

Kao što vidite, PHP kod je sličan kodu za detekciju zemlje, s tim što smo koristili geoipcity.inc kao i GeoLiteCity.dat bazu. F-ja geoip_record_by_addr($gi, $ip) vraća instancu klase ‘geoiprecord’ koja sadrži kao promenljive(osobine) podatke o lokaciji koje koristimo u gornjem kodu. Nakon pokretanja skripta trebalo bi da dobijemo nešto poput:

Zemlja: Serbia and Montenegro
Skracena Oznaka: CS
Skracena Oznaka2: SCG
Region: 02
Grad: Beograd
Postanski Kod: 
Geog. Sirina: 44.8186
Geog. Duzina: 20.4681

Napominjem da je GeoIP baza najažurnija kada su u pitanju gradovi sa severnoameričkog dela planete, dok je njena preciznost znatno manja kada se dođe do “egzotike” u koju nažalost spada i Srbija.

CaseStudy – Redirekcija na osnovu IP adrese
————————————————————–
Za kraj znanje stečeno ovde iskoristićemo u jednom pravom projektu. Naime cilj je da se na dvojezičnom sajtu(blogu) korisnici koji dolaze iz Srbije usmere na srpsku verziju sajta, dok će se svi ostali usmeriti na englesku verziju. Evo kako to izgleda:

<?php
/**
 * Case Study - Redirekcija na osnovu lokacije
 * 
 * @version $Id$
 * @package geoip
 * @copyright © 2006 Lampix.net
 * @author Dragan Dinic <dinke@lampix.net>
 */

require_once("geoip/geoip.inc");

$gi = geoip_open("geoip/GeoIP.dat",GEOIP_STANDARD);

$country_code = geoip_country_code_by_addr($gi, $_SERVER['REMOTE_ADDR']);

geoip_close($gi);

if($country_code == 'CS')
{
        header("HTTP/1.1 301 Moved Permanently");
        header('Location: http://www.dinke.net/blog/sr/');
}
else
{
        header("HTTP/1.1 301 Moved Permanently");
        header('Location: http://www.dinke.net/blog/en/');
}
?>

Primer koji vidite gore koristi se upravo na ovom blogu, kako bi sve korisnike koji ne dolaze iz Srbije automatski preusmerio na englesku verziju bloga. Slanje custom 301 redirection headera je važno kako bi botovi (Google i sl.) indeksirali strane na odgovarajući način.

Hacking Webalizer

Few weeks ago I found a way to install Webalizer on DreamHost. Webalizer is a little bit of an obsolete utility (comparing to Google Analytics Webalizer stats look rude), but still has some advantages. One of the biggest is that it is still used by many hosting solutions, so you don’t have to change all of your site(blog) pages just to make sure Google will track your stats.
(more…)

Next Page »