MySQL UPDATE JOIN

Ostatnimi czasy potrzebowałem danych z sąsiedniej tabeli przy UPDATE jedngo z pól w bazie danych. Danych do przetworzenia było sporo, więc zwracałem uwagę na wydajność zapytania. Aby zebrać potrzebne informacje, można użyć jednego ze sposobów:

1. Zebrać potrzebne dane za pomocą SELECT‘a, co sprawiłoby, że zajęta zostanie niepotrzebna pamięć w środowisku PHP podczas przypisania rezultatu do zmiennej.
2. Wykonać SET z podzapytaniem, ale potrzebnych mi było kilka kolumn z sąsiedniej tabeli, podzapytanie może zwrócić tylko jedną określoną wartość.
3. Wykonać JOIN przy update, czego niestety wówczas nie potrafiłem zrobić.

Kartkując manual nie natrafiłem się w standardowej dokumentacji na nic konkretnego, aż nie spojrzałem na bardzo przydatne komentarze użytkowników. Okazało się, że przy UPDATE można wykonywać dowolne JOIN‘y, schemat jest następujący:

UPDATE TABLE JOIN another_table SET ...

W tym momencie mamy do dyspozycji wszystkie pola z dołączonej tabeli. Bardzo przydatne.

Przykład z życia.

Miałem za zadanie odznaczyć typy bukmacherskie na trafione, nietrafione, odwołane z przyczyn odwołania całego meczu piłkarskiego oraz te, które jeszcze nie mogą zostać oznaczone jako trafione lub nie, gdyż mecz się jeszcze nie odbył.

UPDATE typer_tickets_items
LEFT JOIN typer_events ON(event_id = item_event)
SET item_status = (
CASE
WHEN event_status IS NULL THEN NULL # mecz nie zostal rozegrany
WHEN event_status = -1 THEN -1 # mecz anulowany
WHEN event_status = item_bet THEN 1 # typ trafiony
ELSE 0 END # typ nietrafiony
) WHERE item_status IS NULL

Mam nadzieję, że komuś się przyda…

Przypadki w MySQL – CASE WHEN THEN ELSE END

Podobnie jak w PHP, baza danych MySQL ma odpowiednik if, czyli przypadków (inaczej serii warunków, instrukcji warunkowych). Różnicą między implementacją CASE‘a w MySQL i ifa PHP jest to, że baza danych zwraca konkretną wartość z case’a, a nie wykonuje dowolnej ilości dowolnych akcji.

CASE Syntax:

Najprostsza struktura CASE’aprzedstawia się nastepująco:

CASE WHEN [conditions] THEN ... ELSE ... END

Składnia powinna rozpocząć się słowem kluczowym CASE, a zakończyć END. Pomiędzy znajdują się warunki WHEN oraz operacja zwrócenia odpowiedniej wartości, która po nich następuje THEN (mamy możliwość uwzględnić nieskończenie wiele warunków). Jeżeli żaden warunek nie zostanie spełniony możemy użyć opcjonalnie ELSE.

Przykłady z życia.

Wyobraźmy sobie, że mamy posortować listę aukcji przedmiotów na Allegro od najtańszych, do najdroższych. Należy założyć, że są 2 typy aukcji: kup teraz i licytacja. Pole licytacji w bazie danych zawiera największą zaproponowaną kwotę przez użytkowników w procesie licytacji, a cena kup teraz ustalana jest przez sprzedającego. Są to dwa różne pola w bazie danych, a jedno kryterium sortowania, dlatego trzeba scalić cenę w jedną, wybierając odpowiednią. Musimy przewidzieć sytuację, w której aukcja jest typu kup teraz oraz licytacji, wówczas jeżeli najwyższa oferta jest większa od ceny kup teraz, wówczas wybieramy pole z największa propozycją:

SELECT (
  CASE
    WHEN (auction_type = 'bidding' OR auction_price_bid > auction_price_buynow)
      THEN auction_price_bid
    ELSE auction_price_buynow
  END) AS auction_price

Stworzyliśmy pole auction_price, po którym można sortować aukcje od najtańszej do najdroższej i na odwrót.

Mam nadzieję, że krótki wpis przyda się początkującym. Nic więcej nie trzeba opisywać, temat wydaje się co najmniej trywialny.

Zintegrowane logowanie cms z forum

Projektowałem wiele serwisów, które miały zintegrowane z forum komponenty takie jak:

• rejestracja,
• przypomnienie hasła,
• zmiana hasła, nicku lub adresu email,
• usunięcie konta.

Wówczas nie było żadnego problemu – wystarczyło wszystkie te akcje z forum przekierować na URL’e obsługiwane przez CMS, który zajmował się zmianami  tabelach forum. Dlaczego przekierować? Jeżeli ktoś rejestruje się w serwisie, jest zarejestrowany na forum, natomiast, gdy rejestruje się na forum, nie jest rejestrowany w serwisie. To CMS integrujemy z forum, a nie forum z CMS’em (chyba, że zamierzamy inaczej, wtedy na odwrót).

Ostatnio klient zażyczył sobie, żeby zintegrowane było również logowanie. Nie najlepiej widzi mi się implementacja systemu autoryzacji z forum w CMS’ie, więc poszedłem “na łatwiznę”, bowiem miałem do czynienia z phpBB. Do osiągnięcia celu postanowiłem wykonać dwa kroki:

1. wysłać żądanie POST do forum na adres logowania z wypełnionymi polami POST z formularza logowania w CMS’ie,
2. przechwycić wysłane przez forum ciasteczka i przekazać je użytkownikowi.

Do połączenia się z forum via http użyłem HttpRequest. Wyszło z tego parę linijek kodu.

Wzorzec projektowy Registry w PHP

Registry to wzorzec projektowy, który ma za zadanie przechowywać i udostępniać dane w obrębie aplikacji. Implementacja wzorca zastępuje globalny zasięg wartości zarejestrowanych w przestrzeni klasy. Różnicą globalnego zasięgu zmiennych oraz wartości ujętych w Registry jest to, że można je ściśle kontrolować (dostęp w aplikacji itd.). W tej publikacji przedstawię jedną z najprostszego wykorzystania wzorca Registry.

Głównym założeniem Registry jest globalny zasięg (pomijając już zabezpieczenia dostępowe, którymi się nie zajmujemy). Język PHP od wersji 5 obsługuje statyczne static wywołania metod klas, czyniąc tym samym ich globalny zasięg wraz z połączeniem ze słowem kluczowym public. Dla wygody – nie trzeba tworzyć instancji klasy, więc wywołanie jest bardzo proste i nie zajmuje dużo miejsca w naszym kodzie.

Podstawowymi funkcjonalnościami Registry będzie:

• dodawanie i usuwanie,
• pobieranie

…zmiennych z rejestru. Pojęcie zmienna jest bardzo względne. Przechowywać w rejestrze możemy praktycznie wszystkie typy zmiennych dostępnych w PHP, włączając w to typ resource (zasoby). Registry to nic innego, jak przechowywanie zmiennych w przestrzeni jednej klasy, więc nie mamy wobec tego żadnych ograniczeń.

• Przykładowy kod najprostszej implementacji wzorca Registry.
• Użycie powyższej klasy RegistrySimple.

Zajmiemy się teraz bardziej rozbudowanym przykładem wzorca. Wykonamy następujące operacje:

1. Nowa klasa RegistryAdvenced będzie dziedziczyła z RegistrySimple na potrzeby metody Registry().
2. Zaimplementujemy interfejsy:
   • Iterator
   • ArrayAccess (na potrzeby poruszania się po instancji jak po tablicy)
   • Countable (aby łatwo otrzymać liczbę przechowywanych danych)
   • Serializable (żeby można było zaserializować i odserializować instancję obiektu)
3. Dodamy prywatny konstruktor, skorzystamy ze wzorca Singleton.

Gotowy kod klasy RegistryAdvenced.

Interpretacja i rozwijanie swojego wzorca Registry jest szeroka, można na przykład zastosowac przestrzenie rejestrów, tj. tworzyć wiele rejestrów na podstawie ich instancji. Ile programistów, tyle pomysłów, ale zasada działania nie zmienia się. Zainteresowanych zapraszam do manuala, jak problem został rozwiązany w Zend_Registry.

Paginacja (stronicowanie) w PHP

Zaczynając swoją przygodę z PHP nie miałem pojęcia jak wykonać paginację newsów, która załamywałaby łańcuchy liczb w momencie, w których chce. Na dzień dzisiejszy postanowiłem napisać swój nowy pager, gdyż ten, który dotychczas używałem przez ostatnie 2 lata nie odpowiadał mi pod trzema względami:

• nagromadzenie metod, tj. setLimit(), getLimit(), zamiast pojedynczego limit(),
• brak możliwości ustawienia załamywania się łańcucha (domyślnie były to 2 liczby):
  1, 2 ... 6, 7, 8 ...  12, 13.
• brak możliwości definiowania limitu, ilości elementów oraz aktualnie przeglądanej strony w jednej metodzie, najchętniej w samym konstruktorze.

Ostatnio potrzebowałem zwiększyć limit liczb “z przodu” i “tyłu” oraz “w środku”:
1, 2, 3 ... 5, 6, 7, 8, 9 ...  11, 12, 13

Dla tych, którzy ciągle szukają komponentu obsługującego paginację prezentuję Vframe_Pagination.

Implementacja od strony kontrolera (metoda krótka):

$oPager = new Vframe_Pagination($iItemsCount, $iLimit, $iCurrentPage);

Metoda długa:

$oPager = new Vframe_Pagination();
$oPager->limit($iLimit);
$oPager->items($iItemsCount);
$oPager->page($iPage);

Metody limit, items oraz page zwracają liczby odpowiadające ich nazwą niezależnie od tego, czy została podana nowa wartość w argumencie, czy nie, co jest absolutnie wygodnym (dla mnie) rozwiązaniem.

Aby wyświetlić oczekiwane rekordy, wykorzystujemy pagera:

$aData = $oModel->GetList($iUser, $oPager->start(), $oPager->limit());

Lub bezpośrednio w zapytaniu do bazy dancyh:

$sSql = "SELECT news_id FROM news LIMIT " . $oPager->start() . ", " . $oPager->limit();

Od strony widoku, prezentacja pagincaji prezentuje się w bardzo prosty sposób:

<?php echo $oPager->Render(true); ?>

Metoda render przyjmuje kolejno:

1. $mMode (mixed: bool, null, string), jeżeli true, paginacja jest zwracana w formacie HTML. Jeżeli null, numerki stron podane w formie array’a zamiast linków HTML. Jeżeli string, zostaje zwracana wartość po renderowaniu pagera, np. element startujący przedział mnożony po limicie: $oPager->Render(‘start’);. Ten sam efekt powoduje metoda $oPager->start();
2. $iDrawCutLimit (int) ilość numerków na początku i końcu łańcucha paginacji.
3. $iDrawCutLimitCenter (int) ilość numerków “w środku” obok aktualnie przeglądanej strony.
4. $bNavigation (bool) linki “poprzednia” i “następna” strona znajdujące się na początku i końcu łańcucha.

Możemy sami ostylować linki generowane przez pager. Wystarczy że w widoku dodamy swój apperance:

$this->oPager->PatternPage('<a href="?[$]">[$]</a>');
$this->oPager->PatternPageCurrent('<strong>[$]</strong>');
$this->oPager->PatternPageNavigation('<a href="?[$]" rel="nofollow">[$$]</a>', array('&laquo; poprzednia', 'następna &raquo;'));
$this->oPager->PatternSeparator('<span>...</span>');

Końcowy efekt, możemy nie wyświetlać pagera, gdy jest tylko jedna strona elementów:

<?php if($this->oPager->Render('pages') > 1) {
// wyswietl pager...
} ?>

Podtrzymanie sesji

Dla niektórych wygasanie sesji jest zabezpieczeniem (banki, etc.). Realizując jeden z projektów oczekiwałem od systemu tego, aby użytkownik nigdy nie gubił sesji, gdy ma otwarte okno w przeglądarce. Dlaczego? Może dodaje posta, być może uzupełnia dość obszerny tekst na stronie. Gdy klika zapisz, przerzuca go do strony logowania, a cały tekst zniknął za sprawą tego, że jego przeglądarka nie zapisuje wartości pól formularza. Skąd to znamy.

Jak użytkownik gubi sesję?

1. Jego ciastko wygasa, więc serwer nie może go zidentyfikować z sesją.
2. Po jakimś czasie, choćby odtworzył ciastko, plik sesji znika z naszego serwera (garbage collection).

Rozwiązania:

1. Wydłużenie czasu wygasania ciastka i sesji.
2. Odświeżenie strony w interwale mniejszym, niż wynosi czas wygasania sesji i ciastka.

Rozmyślając nad podtrzymaniem sesji, próbowałem znaleźć wszystkie metody oraz wybrać najlepszą. Wszystkie sprowadzają się do “odświeżenia” strony lub jej fragmentu tak, aby nasz silnik wykonał tylko potrzebne session_start(); czyli podtrzymanie aktywności sesji. Jest kilka mniej lub bardziej zadowalających sposobów:

1. Odświeżenie całej strony.
   To może spowodować, że dane wprowadzane przez użytkownika w formularzu zostaną utracone. Ponad to, jeżeli użytkownik czyta newsy, denerwującym może być fakt, że lista nagle zostanie przescrollowana do góry (prócz opery).
2. Wysłanie requestu ajax w tle.
   Minusem jest to, że trzeba używać biblioteki ajax lub pisać dodatkowy kod javascriptu. Jeżeli ktoś na stronie używa jakiegoś ajaxa – co za różnica. Poza tym same plusy.
3. Odświeżanie ukrytej ramki iframe lub elementu frameset.
   Minusów usablity prawie brak. Brak potrzeby instalacji javascriptów i ajaxa. Odświeżacz powinien wysłać nagłówek Refresh lub odpowiedni metatag.

Sposób 3 wydaje mi się najlepszy. Można go ulepszyć w ten sposób, aby ramka nie wysyłała żądania zaraz po załadowaniu strony. Powodowałoby to podwójne requesty do serwera.

• Przykład z ukrytym iframe.
• Przykład z Advajax.
• plik ping.php wygląda wówczas następująco: aplikacje nie używające frameworków ingerujących w standardowe działanie sesji
  session_start(); header('Refresh: 60');

Zapewne znajdą się osoby, które powiedzą: a co z użytkownikami, którzy mają wyłączone ramki, lub ich przeglądarki w ogóle ich nie obsługują. Zapytam wówczas: a co z użytkownikami, którzy nie akceptują cisteczek (wówczas sesje nie są dla nich użyteczne, chyba, że użyjemy przesłyki jej identyfikatora w adresie url). Dopytam również: a co z użytkownikami, którzy mają wyłączony Javascript? Patologiczne przepadki się po prostu pomija ;)