Warunek IF i pętla w zapytaniu do bazy MSQLI

Question

Witam.

Czy w zapytaniach INSERT lub UPDATE istnieje operator IF albo czy w bazie danych można wykonać pętlę?

Piszę funkcję która dodaje tagi podane przez użytkownika z formularza do bazy danych. W wysłane tagi znajdują się w tablicy. W php mógłbym użyć pętli i przy pomocy polecenia SELECT dokonać sprawdzenia dla każdego tagu, czy taki tag istnieje w bazie danych.

Jeśli istnieje to nie dokonuje insertu w bazie danych nowego tagu a jeśli nie to wykonuje polecenie insert.

I tak zacząłem się zastanawiać czy to najlepszy sposób na dokonanie tego typu operacji, czy aby nie można sprawy załatwić jednym zapytaniem do bazy. I to baza by wykonała pętlę na tablicy. Dzięki temu zredukowało by się ilość zapytań do bazy danych.

Czy można to wykonać? A jak tak to jak?

Dziękuję

Answer 1

INSERT INTO `table` (`userid`, `tickets`) VALUES (1, 1) ON DUPLICATE KEY UPDATE `tickets`=`tickets`+1

 

Comments

Można prosić o wyjaśnienie kodu?

---

Wyjaśniając od podstaw:
Musisz na kolumnę `userid` założyć PRIMARY lub UNIQUE index, dzięki temu baza danych będzie zwracać błąd w przypadku dodawania do bazy rekordu z `userid` które się powtórzy. Powyższe zapytanie przy dodawaniu rekordu wykryje ten błąd i zamiast dodać rekord doda 1 do `tickets`. Podane nazwy kolumn oczywiście przykładowe.

Działanie w praktyce (wykonywane z góry na dół):

INSERT INTO `table` (`userid`, `tickets`) VALUES (1, 1) ON DUPLICATE KEY UPDATE `tickets`=`tickets`+1
#To zapytanie doda rekord do bazy
INSERT INTO `table` (`userid`, `tickets`) VALUES (1, 1) ON DUPLICATE KEY UPDATE `tickets`=`tickets`+1
#Jako, że rekord z userid=1 utworzyliśmy pierwszym zapytaniem i `userid` nie może się powtórzyć, efektem tego zapytania będzie dodanie 1 do kolumny `tickets` dla użytkownika o id 1

 

Answer 2

1. nie napisałeś jaka to baza danych. Szczegóły dialektu SQL różnią się między bazami
2. podaj struktury danych do których piszesz. Jakie masz tabele? Co chcesz zapisać?

Comments

Powyżej dokładny kod.

Answer 3

Tutaj dokładnie przedstawię kod jaki udało mi się stworzyć a który chciałbym zoptymalizować.

Baza danych to MySQL.

Struktura:

Tabela articles: (id, title, itp.)

Tabela tags: (id,name).

Tabela tags_ref (id, artId, tagId).

I działający kod.

public function updateTags($artID,$tags){

        foreach ($tags as $tag) {
            try {
                $checkTag = $this->db->query("SELECT id FROM tags WHERE name = '$tag' ");
                if($checkTag ->rowCount()!=1){
                    $inserTag = $this->db->prepare("INSERT INTO tags (id,name) VALUES (NULL,:tag)");
                    $inserTag->bindValue(':tag', $tag, PDO::PARAM_STR);
                    $inserTag->execute();
                    $this->addTagsRef($artID,$this->db->lastInsertId());
                }else{
                   $tagID = $checkTag->fetch();
                   $this->addTagsRef($artID,$tagID[0]);
                }
            } catch (PDOException $e) {
                echo $e->getMessage();
            }
        }
    }

    private function addTagsRef($artID,$tagId){
        try {
            $inserTagRef = $this->db->prepare("INSERT INTO tags_ref (id,artId,tagId) VALUES (NULL,:artID,:tagRef)");
            $inserTagRef->bindValue(':artID', $artID, PDO::PARAM_INT);
            $inserTagRef->bindValue(':tagId', $tagId, PDO::PARAM_INT);
            $inserTagRef->execute();
        }catch(PDOException $e){
            echo $e->getMessage();
        }
    }

 

I teraz to fundamentalne pytanie, jak to można poprawić?

Dziękuję

Comments

można oprzeć się na indeksach unique. Załóż indeks unique na name w tags i parę artId, tagId w tags_ref. Wtedy możesz po prostu wpisywać tam wartości. Jeżeli istnieją, to baza odrzuci update jako naruszający constraint unique.

Jeżeli nie chcesz widzieć tam błędów, to możesz wykonać INSERT ... ON DUPLICATE KEY UPDATE id=id

manual: http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html

To spowoduje, że zapytanie nie zwróci błędu, jeżeli naruszy indeksy unikalności.

---

A po co mi niby uniqe w tabeli tags_ref? Przecież tam wartości mogą się powtarzać.

Ale w niczym to nie rozwiązuje problemu. Muszę jakoś pobrać id tagu który już istnieje aby później dodać go do tabeli "tags_ref".

Jeśli tag już istnieje to $this->db->lastInsertId() zwraca mi 0.

W sumie tak czy tak muszę wykonać następne zapytanie typu INSERT aby dodać wpis do tabeli "tags_ref".

I pętla foreach i tak musi być wykonana w skrypcie php.

---

A po co mi niby uniqe w tabeli tags_ref? Przecież tam wartości mogą się powtarzać.

UNIQUE na OBU polach (tag_id, art_id). To zapewnia, że jeden tag może być zapisany dla artykułu tylko raz. Twój kod teraz przy ponawianiu dopisze do artykułu ten sam tag kilka razy.

Zamiast robić pętlę możesz zrobić:
1. select * from tags where name in (lista tagów jakie chcesz dodać)
2. sprawdzenie czego Ci brakuje, zrobienie insertów.

Ja też zastanowiłbym się, czy kluczem w tabeli tags nie zrobić po prostu name. Wtedy w tag_ref też nie potrzebuje swojego klucza, para tag,art_id może być kluczem głównym. Wtedy nie musisz się martwić o identyfikatory wierszy dla poszczególnych tagów, tylko robisz:

4. insert ... on duplicate key update id=id na raz dla wszystkich tagów jakie masz w kolekcji. Dodadzą się tylko brakujące. Czyli całość można zrobić na trzech zapytaniach niezależnie od ilości tagów.

Możesz to jeszcze uprościć - bo tutaj nie uwzględniasz sytuacji w której update ma USUNĄĆ jakiś tag, jaki był wcześniej przypisany. Wtedy można po prostu usunąć wszystkie refy hurtem i utworzyć nową kolekcję. Przy małej ilości obiektów i braku liczbowego klucza głównego niewiele Cię to boli :)

 

---

Teraz to ma sens co napisałeś :). Jeszcze za bardzo nie wiem jak by to miało wszystko wyglądać w kodzie ale będę coś kombinował. To pierwsze moje takie bardziej skomplikowane zapytanie sql. Do tej pory proste zapytania wystarczały.

UNIQUE na OBU polach (tag_id, art_id). To zapewnia, że jeden tag może być zapisany dla artykułu tylko raz. Twój kod teraz przy ponawianiu dopisze do artykułu ten sam tag kilka razy.

To nie wystąpi bo tagi mam sprawdzane pod kątem duplikatów przed zapytaniem do bazy.

A jak wygląda takie zapytanie INSERT gdzie jednym zapytaniem wstawiamy np 3 nowe rekordy a dane te trzymamy w tablicy? Sorry że pytam ale nie wiem jak to ma wyglądać.

Bardzo dziękuję za wyjaśnienie mi poprzednich kwestii.

Pozdrawiam