PHP, MYSQL: Problem mit prepared Statement

Seite 1 von 1 - Forum: Coding Stuff auf overclockers.at

URL: https://www.overclockers.at/coding-stuff/php-mysql-problem-mit-prepared-statement_251401/page_1 - zur Vollversion wechseln!

kleinerChemiker schrieb am 31.05.2018 um 12:57

folgender Code funktioniert:

Code: PHP

try {
    $stmt = $db->prepare("SELECT time, preis FROM `tabelle` WHERE id=$id");
} catch (PDOException $e) {
    echo 'Prepare failed: ' . $e->getMessage();
}

try {
    $stmt->execute();
} catch (PDOException $e) {
    echo 'Execution failed: ' . $e->getMessage();
}

folgender Code funktioniert nicht.

Code: PHP

try {
    $stmt = $db->prepare("SELECT time, preis FROM `tabelle` WHERE id=:id");
} catch (PDOException $e) {
    echo 'Prepare failed: ' . $e->getMessage();
}

try {
    $stmt->bindValue(':id', $id);
} catch (PDOException $e) {
    echo 'BindParam failed: ' . $e->getMessage();
}

try {
    $stmt->execute();
} catch (PDOException $e) {
    echo 'Execution failed: ' . $e->getMessage();
}

Wenn ich also die Variable nicht direkt ins SELECT schreibe, gehts nicht. Warum?
id wäre z.B. 0x374f237256b7e750214ba08bec643a0b7a954a7f
die Spalte in Mysql ist binary(20), es handelt sich dabei um binäre sha Hashes

tia

Crash Override schrieb am 31.05.2018 um 14:13

versuch mal:

Code: PHP

try {
    $stmt = $db->prepare("SELECT time, preis FROM `tabelle` WHERE id = ?");
} catch (PDOException $e) {
    echo 'Prepare failed: ' . $e->getMessage();
}

try {
    $stmt->execute(array($id));
} catch (PDOException $e) {
    echo 'Execution failed: ' . $e->getMessage();
}

Ein paar Kleinigkeiten zum drüber nachdenken:
1. PDO lässt dich nicht alle funktionen von MySQL/MariaDB nutzen, zum Beispiel grillt dir PDO einen Galera Cluster da jeder Prepare auf alle Cluster Nodes repliziert wird, einen simplen SELECT kann jeder Node ohne Sperre absetzen.
2. Du hast duch das Prepare jedesmal die Latenz zwischen Applikation und Datenbank verdoppelt.
3. PDO enthält Codeteile auf die Microsoft und Oracle Patent hält. Die Zukunft ist daher fraglich, wir haben daher auf mysql_nd umgestellt.
4. Sollte id dein PK sein, solltest du aus Performancegründen dein Schema überdenken. Durch befüllen mit zufälligen PKs fragmentierst du deinen PK stark, so dass ständig eine Reorganisation nötig ist, die dir die Performance wegnimmt. Besser ist ein Unsigned Int auto_increment PK wenn du mit 4 Milliarden Einträgen auskommst. Zusätzlich spart dir ein INT PK 16 Byte bei jedem zusätzlichen Index pro Zeile.

kleinerChemiker schrieb am 31.05.2018 um 17:35

Danke für die Infos. Ich bin auch kein Programmierer, sondern mach das nur spaßhalber. Da freut man sich über Tips.

Deine Änderung funktioniert leider auch nicht.

PDO hab ich eigentlich nur gewählt, weil ich nicht wußte was ich nehmen soll und ich so theoretisch auf eine andere DB wechseln könnte. (was so wahrscheinlich ist, wie dass ich einen Galera Cluster nutzen werde) Aber vielleicht wechsel ich auf mysql_nd, der Aufwand ist gering.

Es handelt sich nur ein kleines privates Projekt. Ich lese den Spritpreisrechner für ein paar Positionen aus, um die für mich günstigste Tankstelle auf einen Blick zu haben und um den Preisverlauf zu sehen.

Derzeit gibt es 2 Tabellen:

Code: SQL

--
-- Tabellenstruktur für Tabelle `spritpreis`
--

CREATE TABLE `spritpreis` (
  `id` binary(20) NOT NULL,
  `time` int(10) UNSIGNED NOT NULL,
  `preis` float UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `tankstelle`
--

CREATE TABLE `tankstelle` (
  `id` binary(20) NOT NULL,
  `longitude` float NOT NULL,
  `latitude` float NOT NULL,
  `name` tinytext NOT NULL,
  `adresse` tinytext NOT NULL,
  `plz` smallint(5) UNSIGNED NOT NULL,
  `ort` tinytext NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Indizes der exportierten Tabellen
--

--
-- Indizes für die Tabelle `spritpreis`
--
ALTER TABLE `spritpreis`
  ADD PRIMARY KEY (`id`,`time`);

--
-- Indizes für die Tabelle `tankstelle`
--
ALTER TABLE `tankstelle`
  ADD PRIMARY KEY (`id`);

Die id wird so erstellt:

Code: PHP

$id = sha1($result->longitude . '-' . $result->latitude, TRUE)

Und so kommen die Daten in die DB:

Code: PHP

$stmt1 = $db->prepare("INSERT INTO spritpreis (id, time, preis) VALUES (:id, :time, :preis)");
$stmt2 = $db->prepare("INSERT INTO tankstelle (id, longitude, latitude, name, adresse, plz, ort) VALUES (:id, :longitude, :latitude, :name, :adresse, :plz, :ort) ON DUPLICATE KEY UPDATE id=:id");

Der Spritpreis ist häufig der selbe, durch den PK auf id & time, werden keine doppelten Zeilen eingefügt.
Die id berechne ich so komisch, weil die Koordinaten der Tankstelle das einzige ist, das sich nicht ändert/ändern kann. Wenn ich für den PK ein INT nehme, müßte ich ein UNIQUE auf die Koordinaten legen.

kleinerChemiker schrieb am 31.05.2018 um 17:46

Bezüglich mysql_nd. Wenn ich das PHP Manual richtig verstehe, ist mysql_nd eine Library und und kein Client und kann mit mysqli oder PDO verwendet werden und von mir als Benutzer der DB auch nicht verändert werden. Meinst du dass ich eher mysqli verwenden soll?

edit: ich habe die Problemstelle durch ein normales ->query ersetzt. Prepared Statement ist da wirklich nicht notwendig, weil sowieso nur eine einzige id abgefragt wird. Über Tips zum DB Aufbau würde ich mich dennoch freuen.

Crash Override schrieb am 01.06.2018 um 10:40

Du hast Recht, der mysql_nd ist nur der Treiber und dieser kann auf beide Arten verwendet werden. Mysqli ist nur meistens einfacher...

Ich würde die Tabellen so aufbauen:

Code: SQL

CREATE TABLE `tankstelle` (
  `id` int unsigned NOT NULL auto_increment,
  `longitude` float NOT NULL,
  `latitude` float NOT NULL,
  `name` varchar(30)  NOT NULL,
  `adresse` varchar(30) NOT NULL,
  `plz` smallint(5) UNSIGNED NOT NULL,
  `ort` varchar(30) NOT NULL.
  PRIMARY KEY (`id`),
  UNIQUE KEY `longlat` (`longitude`,`latitude`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `spritpreis` (
  `id` int unsigned NOT NULL,
  `time` int(10) UNSIGNED NOT NULL,
  `preis` DECIMAL(6,3) UNSIGNED NOT NULL,
  PRIMARY KEY (`id`,`time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Dies macht die Tabellen deutlich schneller und kleiner.

int vs binary(20) - 16 Byte gespart.
tynitext vs varchar - andere Speichermethode, varchar kann schneller gelesen und geschrieben werden (Die länge ist wichtig falls mal ein Index drauf gelegt wird, da dann im Index ein CHAR feld draus wird. Diese Längenbegrenzung gibt es bei TINYTEXT nicht.)
decimal vs float - spart nur 1 Byte aber ist besser zu lesen.

Verwende möglichst immer das schmalste Charset, dass dir zur verfügung steht, für in Europa liegende Namen, Adressen und Orte genügt latin1, dies verbraucht nur halb so viel Speicherplatz und Ram wie UTF8. Zusätzlich stimmt MySQL UTF8 nicht mit anderen Standards überein und mann sollte dann lieber UTF8mb4 nutzen, was den benötigten Speicherplatz noch einmal verdoppelt.

Code: SQL

INSERT INTO tankstelle (id, longitude, latitude, name, adresse, plz, ort) VALUES (:id, :longitude, :latitude, :name, :adresse, :plz, :ort) ON DUPLICATE KEY UPDATE id=:id

sollte wohl eher sein:

Code: SQL

INSERT INTO tankstelle (id, longitude, latitude, name, adresse, plz, ort) VALUES (:id, :longitude, :latitude, :name, :adresse, :plz, :ort) ON DUPLICATE KEY UPDATE name=:name, adresse=:adresse, plz=:plz, ort=:ort

kleinerChemiker schrieb am 04.06.2018 um 18:29

Danke für deine Ausführungen.

Zitat aus einem Post von Crash Override
Du hast Recht, der mysql_nd ist nur der Treiber und dieser kann auf beide Arten verwendet werden. Mysqli ist nur meistens einfacher...

Ich würde die Tabellen so aufbauen:
Code: SQL
CREATE TABLE `tankstelle` (
  `id` int unsigned NOT NULL auto_increment,
  `longitude` float NOT NULL,
  `latitude` float NOT NULL,
  `name` varchar(30)  NOT NULL,
  `adresse` varchar(30) NOT NULL,
  `plz` smallint(5) UNSIGNED NOT NULL,
  `ort` varchar(30) NOT NULL.
  PRIMARY KEY (`id`),
  UNIQUE KEY `longlat` (`longitude`,`latitude`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `spritpreis` (
  `id` int unsigned NOT NULL,
  `time` int(10) UNSIGNED NOT NULL,
  `preis` DECIMAL(6,3) UNSIGNED NOT NULL,
  PRIMARY KEY (`id`,`time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Dies macht die Tabellen deutlich schneller und kleiner.

int vs binary(20) - 16 Byte gespart.

Derzeit wird die id aus den Koordinaten berechnet. Ändere ich sie auf INT, kann ich sie nicht mehr berechnen und ich muss jedes Mal, bevor ich die INSERTS in tankstelle und spritpreis mache, mit einem SELECT die id abfragen. Die Tabelle tankstelle hat unter 50 Zeilen und war nach den ersten Tagen aufgebaut. Es ist sehr unwahrscheinlich/selten, dass neue Einträge hinzugefügt werden. D.h. der Index braucht nicht mehr verändert werden. In die Tabelle spritpreis kommen am Tag im Durschnitt weniger als 50 neue Einträge hinzu.

Zitat aus einem Post von Crash Override
tynitext vs varchar - andere Speichermethode, varchar kann schneller gelesen und geschrieben werden (Die länge ist wichtig falls mal ein Index drauf gelegt wird, da dann im Index ein CHAR feld draus wird. Diese Längenbegrenzung gibt es bei TINYTEXT nicht.)

ok, danke.

Zitat aus einem Post von Crash Override
decimal vs float - spart nur 1 Byte aber ist besser zu lesen.

Ok, danke. Ich wollte zuerst decimal nehmen, aber das wollte MySQL nicht, weil ich ich DECIMAL(1,3) wollte.

Zitat aus einem Post von Crash Override
Verwende möglichst immer das schmalste Charset, dass dir zur verfügung steht, für in Europa liegende Namen, Adressen und Orte genügt latin1, dies verbraucht nur halb so viel Speicherplatz und Ram wie UTF8. Zusätzlich stimmt MySQL UTF8 nicht mit anderen Standards überein und mann sollte dann lieber UTF8mb4 nutzen, was den benötigten Speicherplatz noch einmal verdoppelt.

Danke, UTF8 wurde automatisch genommen. Über PHP ausgegeben passt es, aber in phpMyAdmin sind die Umlaute kaputt.

Zitat aus einem Post von Crash Override

Code: SQL

INSERT INTO tankstelle (id, longitude, latitude, name, adresse, plz, ort) VALUES (:id, :longitude, :latitude, :name, :adresse, :plz, :ort) ON DUPLICATE KEY UPDATE id=:id

sollte wohl eher sein:

Code: SQL

INSERT INTO tankstelle (id, longitude, latitude, name, adresse, plz, ort) VALUES (:id, :longitude, :latitude, :name, :adresse, :plz, :ort) ON DUPLICATE KEY UPDATE name=:name, adresse=:adresse, plz=:plz, ort=:ort

Ja, habe ich in der Zwischenzeit auch bemerkt. Ich hab die Doku nicht richtig gelesen und dachte, ich gebe nur die id an, die der PK ist und dann wird, wenn der existiert, aus dem INSERT ein UPDATE und alles was eingefügt werden sollte, wird aktualisiert. Also fast so, wie bei REPLACE.

Rektal schrieb am 08.07.2018 um 22:16

Zitat
Wenn ich also die Variable nicht direkt ins SELECT schreibe, gehts nicht. Warum?

Eigentlich raus gefunden was es war? Bzw. du hast nie angegeben, welchen Fehler du genau bekommen hast (Exception, MySQL Error, ...)?

kleinerChemiker schrieb am 09.07.2018 um 09:42

Nein, hab ich nie herausgefunden. Der Fehler war, dass das Ergebnis leer war.