mysqli query Fehler: update AND select

[Max]

Hallo

Verstehe nicht, was ich falsch mache. Ich würde gerne die Punkteanzahl eines Users mit gewisser ID in meiner Datenbank um 1 inkrementieren und das Resultat dann auswählen, also UPDATE und SELECT :

SELECT score, user, id FROM table AS temp, (
UPDATE table SET score = score + 1 WHERE user = ? AND id = ?)
WHERE temp.user = ? AND temp.id = ? 

(prepared Statement, daher die Fragezeichen)

...wirft leider einen Fehler.

Habe es dann brutal hintereinander probiert

(UPDATE table SET score = score + 1 WHERE user = ? AND id = ?)
AND
(SELECT score FROM table WHERE user = ? AND id = ?)

...zeigt sich aber auch ziemlich unbeeindruckt.

Was mache ich falsch?

Danke, Max.

[Der Martin]

Hallo Max,

SELECT score, user, id FROM table AS temp, (
UPDATE table SET score = score + 1 WHERE user = ? AND id = ?)
WHERE temp.user = ? AND temp.id = ? 

(prepared Statement, daher die Fragezeichen)

...wirft leider einen Fehler.

dann verrate uns bloß nicht, welchen Fehler. Das könnte ja der Schlüssel zur Lösung sein.

Ich tippe auf einen banalen Syntaxfehler. Nach dem Keyword AS folgt eine kommagetrennte Liste der Aliasnamen für die zuvor im SELECT angesprochenen Spalten. Deine geklammerte Subquery mit dem UPDATE liefert aber keinen alternativen Bezeichner für eine Spalte.

(UPDATE table SET score = score + 1 WHERE user = ? AND id = ?)
AND
(SELECT score FROM table WHERE user = ? AND id = ?)

Was willst du mit dem AND dazwischen? Mit AND machst du eine logische Verknüpfung von zwei boolschen Termen. Setze einfach die beiden Queries separat nacheinander ab.

Einen schönen Tag noch
 Martin

[Max]

dann verrate uns bloß nicht, welchen Fehler. Das könnte ja der Schlüssel zur Lösung sein.

<b>Fatal error</b>:  Uncaught mysqli_sql_exception: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near -- SQL STATEMENT 

So, nun bin ich schon sehr gespannt, wie dir diese Fehleranalyse weiterhilft.

Würden wir aus dem Fehler mehr erfahren, hätte ich ihn wohl gepostet.

Was willst du mit dem AND dazwischen? Mit AND machst du eine logische Verknüpfung von zwei boolschen Termen.

Stimmt, das kann wohl nicht funktionieren. XD

Setze einfach die beiden Queries separat nacheinander ab.

...also mit anderen Worten, du hast auch keine Ahnung?

---

Weiter im Text

UPDATE table SET score = score + 1 WHERE user IN (SELECT user FROM table WHERE user = ? AND id = ?)

funktioniert, das select statement wird aber nicht ausgegeben.

Kann es tatsächlich sein, dass UPDATE und danach SELECT in EINEM Query (so wie ich mir das vorstelle) einfach nicht möglich ist?

Schönen Abend noch, Max.

[Raketenwilli]

Kann es tatsächlich sein, dass UPDATE und danach SELECT in EINEM Query (so wie ich mir das vorstelle) einfach nicht möglich ist?

Ja.

Was sagt Dir übrigen der Begriff SubSELECT?

[Der Martin]

Hallo,

dann verrate uns bloß nicht, welchen Fehler. Das könnte ja der Schlüssel zur Lösung sein.

<b>Fatal error</b>:  Uncaught mysqli_sql_exception: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near -- SQL STATEMENT 

also ist dein tatsächliches SQL-Statement nicht identisch mit dem, das du hier gezeigt hast. Da sehe ich nämlich keinen Kommentar -- SQL STATEMENT.
Aber egal, jedenfalls bestätigt das meine Vermutung: Ein Syntaxfehler. Und warum, hatte ich dir auch schon erklärt.

Würden wir aus dem Fehler mehr erfahren, hätte ich ihn wohl gepostet.

Ja, in diesem Fall war es offensichtlich. Aber der Rat gilt grundsätzlich: Bitte gib bei Fragen zu einem konkreten Problem alle relevanten Informationen an. Und Fehlermeldungen (Wortlaut oder Fehlercode) sind relevant.

Setze einfach die beiden Queries separat nacheinander ab.

...also mit anderen Worten, du hast auch keine Ahnung?

Warum glaubst du das? Ich hatte dir den Fehler erklärt, und auch, wie man es richtig machen kann.

Du hast zwei Vorgänge: Das Aktualisieren von Werten in der DB, und das Abfragen von Werten nach dieser Aktualisierung. Also sind das auch zwei Queries.

UPDATE table SET score = score + 1 WHERE user IN (SELECT user FROM table WHERE user = ? AND id = ?)

funktioniert, das select statement wird aber nicht ausgegeben.

Natürlich nicht, dessen Ergebnismenge fungiert ja hier nur als Auswahlmenge für das UPDATE.

Kann es tatsächlich sein, dass UPDATE und danach SELECT in EINEM Query (so wie ich mir das vorstelle) einfach nicht möglich ist?

Ja. Wie gesagt: Das sind zwei separate Vorgänge.

Einen schönen Tag noch
 Martin

[Rolf B]

Hallo Max,

Was mache ich falsch?

(1) Du erzählst zu wenig, z.B. hast Du nicht gesagt, dass Du PHP mit MYSQLI nutzt. Das hat sich erst aus den Fehlermeldungen ergeben, die später gepostet wurden.

(2) Du verwendest Phantasiesyntax und nicht die, die dein MYSQL Server kennt.

• Ein SELECT Statement kann nur lesen, nichts ändern, und dort, wo Spaltenangaben oder literale Werte zulässig sind, kann auch ein SubSELECT stehen. Kein Substatement. Dieser Subselect muss genau eine Spalte zurückliefern (wenn man von Tupel-Syntax absieht, mit der ich nicht recht vertraut bin).

• Das Schlüsselwort AND dient zum Verknüpfen zweier logischer Bedingungen und sonst nichts. Es dient nicht zum Verketten von zwei Statements.

Zum Verketten mehrerer Statements verwendet man das Semikolon. Das nennt sich "multiple statement query".

UPDATE table SET score = score + 1 WHERE user = ? AND id = ?;
SELECT score FROM table WHERE user = ? AND id = ?

Ich habe das in PHP noch nicht gemacht und kann nur das Handbuch zitieren.

• Im MYSQL Handbuch über das C API steht, man müsse multiple statements explizit aktivieren. Ich finde aber nichts, wie man das in PHP macht.
• Es gibt aber in PHP mysqli_multi_query, dass dieses Feature implizit zu aktivieren scheint
• Jedoch: Prepare kann das nicht. Das erlaubt immer nur genau ein Statement.

Ein Ausweg wäre eine Transaktion, mit der Du sicherstellen kannst, dass zwischen UPDATE und SELECT kein Update durch einen anderen User erfolgen kann. Ich nehme an, dass das der Grund für dein Ansinnen ist. Das setzt aber voraus, dass Du die InnoDB Engine verwendest. Bei MyISAM gibt's keine Transaktionen, da hilft nur LOCK TABLE - was bei hoher Zugriffsfrequenz natürlich schnell zum Flaschenhals werden kann.

Ohne Transaktion ist - meine ich - auch eine Stored Procedure keine Lösung. Da kannst Du die Zeit zwischen UPDATE und SELECT nur verringern, aber nicht verhindern, dass keiner dazwischengrätscht.

Eine etwas aufwändigere Lösung, die ohne Transaktion und ohne Locks auskommt, besteht darin, SELECT und UPDATE zu tauschen. Hä? Ja, kein Scheiß. Ein Prepare lohnt da übrigens nicht wirklich, es sei denn, du machst diese Operation öfter in einem PHP Request. Ich zeige mal exemplarisch, wie man das ohne prepare machen könnte.

do 
{
  $escUser = mysqli_real_escape_string($user);
  $numId = intval($id);
  $result = $db->query("SELECT score FROM table WHERE user='$escUser' AND id=$numId");

  if ($result === FALSE)
  {
    /* Errorhandling */
  }

  $row = $result->fetch_assoc();
  if (!$row)
  {
    /* Row ist noch nicht da, INSERT statt UPDATE oder Fehler */
  }

  $score = intval($row['score']);
  $newScore = $score+1;
  $db->query("UPDATE table SET score=$newScore WHERE $user='$escUser' AND id=$numId AND score=$score");
}
while ($db->affectedRows == 0)

Der Trick ist hier, den UPDATE nur durchzuführen, wenn der zuvor gelesene Score unverändert ist. Das nennt man optimistisches Sperren - d.h. man sperrt eigentlich gar nicht, sondern liest die Daten, bestimmt die neuen Werte und schreibt sie raus - fragt dabei aber ab, ob der Satz unverändert geblieben ist.

Wenn keiner dazwischengegrätscht hat, braucht es nur diese beiden Queries und Du bist fertig.

Wenn aber doch, sorgt die Schleife dafür, dass der Versuch wiederholt wird. Lesen, Rechnen, Schreiben. Solange, bis keine Störung dazwischen ist.

Das ist effizient, wenn die Kollisionswahrscheinlichkeit auf einem User gering ist, aber im Allgemeinen viele Updates auf der Tabelle stattfinden, so dass Transaktionen oder gar ein LOCK TABLE zu einem Engpass führen.

Rolf