» Multiple INSERT mit PDO

mgutt · 05.03.2015, 22:08

Ich musste eine Tabelle mit allen verfügbaren Postleitzahlen füllen, während die Städtenamen später nachgeschoben werden sollten. Dazu wollte ich nicht jeden INSERT einzeln absetzen, sondern einen großen. Durch die Übergabe mehrerer Klammern nach "VALUES" ist das problemlos möglich.

Allerdings sollte der Code möglichst flexibel werden bzw. ich bin zu faul ständig in die Datenbank zu schauen wie viele Spalten nun vorhanden sind, denn die Anzahl der Platzhalter "?" muss ja genau übereinstimmen. Daher habe ich zuerst mit SHOW COLUMNS die Spalten abgefragt und das ganze dann im Code als Basis genutzt.

Hat die Tabelle also 3 Spalten entsteht daraus der Query:

INSERT INTO towns (id,postcode,town) VALUES(?,?,?),(?,?,?),...

Und sind es 4, dann wird daraus das:

INSERT INTO towns (id,postcode,town,district) VALUES(?,?,?),(?,?,?),...

In meinem Beispiel wollte ich aber nur "postcode" setzen. Jetzt müsste ich erst in der Datenbank nachschauen an wie vielter Stelle "postcode" ist. Das wäre jetzt die 2. Spalte gewesen. Aber evtl. ändert sich das später mal. Dann müsste ich den Code aktualisieren, wenn sich an dieser Reihenfolge etwas ändert. Das fand ich unflexibel, weshalb ich auch das gleich automatisiert habe. Füllt man also nur einen Teil der Daten mit einem Wert, dann setzt das Script automatisch die richtige Position.

Es muss der entsprechende Wert im folgenden Format gesetzt werden:

$row['postcode'] = sprintf('%05d', $postcode);

Also bei zwei Werten dann z.B. so:

$row['col1'] = 1;
$row['col2'] = 'foo';

Dabei darf man auf keinen Fall das machen, weil sonst die Anzahl der Gesamtspalten nicht mehr stimmt:

$row = array(
'col1' => 1,
'col2' => 'foo',
);

Schlussendlich eine Anmerkung zu den zwei while()-Schleifen. Hier kommt es einfach darauf an wie groß man "$values" werden lassen will bzw. den auszuführenden Query, denn irgendwann läuft der PHP-memory voll. Daher habe ich zwei Schleifen genommen und habe bei 10000 Einträgen einen "break" gemacht.

		// obtain column template
		$stmt = $db->prepare('SHOW COLUMNS FROM towns');
		$stmt->execute();
		$columns = array_fill_keys(array_values($stmt->fetchAll(PDO::FETCH_COLUMN)), null);
		// multiple INSERT
		$postcode = '01000';// smallest german postcode
		while ($postcode <= 99999) {// highest german postcode
			$values = array();
			while ($postcode <= 99999) {
				// reset row
				$row = $columns;
				// now fill our row with data
				$row['postcode'] = sprintf('%05d', $postcode);
				// build INSERT array
				foreach ($row as $value) {
					$values[] = $value;
				}
				$postcode++;
				// avoid memory kill
				if (!($postcode % 10000)) {
					break;
				}
			}
			// build query
			$count_columns = count($columns);
			$placeholder = ',(' . substr(str_repeat(',?', $count_columns), 1) . ')';//,(?,?,?)
			$placeholder_group = substr(str_repeat($placeholder, count($values) / $count_columns), 1);//(?,?,?),(?,?,?)...
			$into_columns = implode(',', array_keys($columns));//col1,col2,col3
			// this part is optional:
			$on_duplicate = array();
			foreach ($columns as $column => $row) {
				$on_duplicate[] = $column;
				$on_duplicate[] = $column;
			}
			$on_duplicate = ' ON DUPLICATE KEY UPDATE' . vsprintf(substr(str_repeat(', %s = VALUES(%s)', $count_columns), 1), $on_duplicate);
			// execute query
			$stmt = $db->prepare('INSERT INTO towns (' . $into_columns . ') VALUES' . $placeholder_group . $on_duplicate);//INSERT INTO towns (col1,col2,col3) VALUES(?,?,?),(?,?,?)... {ON DUPLICATE...}
			$stmt->execute($values);

Der "ON DUPLICATE KEY"-Teil ist natürlich optional. Das kann interessant sein, wenn man PRIMARY KEY auf einer ID hat und dann keinen INSERT ausführen will, sondern automatisch ein UPDATE.

programmierer-forum.de · Gast

Mach mit!

Wenn Dir die Beiträge zum Thread "Multiple INSERT mit PDO" gefallen haben oder Du noch Fragen hast oder Ergänzungen machen möchtest, solltest Du Dich gleich bei uns anmelden:

Registrierte Mitglieder genießen die folgenden Vorteile:
✔ kostenlose Mitgliedschaft
✔ keine Werbung
✔ direkter Austausch mit Gleichgesinnten
✔ neue Fragen stellen oder Diskussionen starten
✔ schnelle Hilfe bei Problemen
✔ Bilder und Videos hochladen
✔ und vieles mehr...

Autor	Nachricht
mgutt Administrator Name: Marc Geschlecht: Anmeldedatum: 28.08.2004 Beiträge: 52434 Wohnort: Lohmar Meine eBay-Auktionen:	05.03.2015, 22:08 zitieren Ich musste eine Tabelle mit allen verfügbaren Postleitzahlen füllen, während die Städtenamen später nachgeschoben werden sollten. Dazu wollte ich nicht jeden INSERT einzeln absetzen, sondern einen großen. Durch die Übergabe mehrerer Klammern nach "VALUES" ist das problemlos möglich. Allerdings sollte der Code möglichst flexibel werden bzw. ich bin zu faul ständig in die Datenbank zu schauen wie viele Spalten nun vorhanden sind, denn die Anzahl der Platzhalter "?" muss ja genau übereinstimmen. Daher habe ich zuerst mit SHOW COLUMNS die Spalten abgefragt und das ganze dann im Code als Basis genutzt. Hat die Tabelle also 3 Spalten entsteht daraus der Query: `INSERT INTO towns (id,postcode,town) VALUES(?,?,?),(?,?,?),...` Und sind es 4, dann wird daraus das: `INSERT INTO towns (id,postcode,town,district) VALUES(?,?,?),(?,?,?),...` In meinem Beispiel wollte ich aber nur "postcode" setzen. Jetzt müsste ich erst in der Datenbank nachschauen an wie vielter Stelle "postcode" ist. Das wäre jetzt die 2. Spalte gewesen. Aber evtl. ändert sich das später mal. Dann müsste ich den Code aktualisieren, wenn sich an dieser Reihenfolge etwas ändert. Das fand ich unflexibel, weshalb ich auch das gleich automatisiert habe. Füllt man also nur einen Teil der Daten mit einem Wert, dann setzt das Script automatisch die richtige Position. Es muss der entsprechende Wert im folgenden Format gesetzt werden: `$row['postcode'] = sprintf('%05d', $postcode);` Also bei zwei Werten dann z.B. so: `$row['col1'] = 1; $row['col2'] = 'foo';` Dabei darf man auf keinen Fall das machen, weil sonst die Anzahl der Gesamtspalten nicht mehr stimmt: `$row = array( 'col1' => 1, 'col2' => 'foo', );` Schlussendlich eine Anmerkung zu den zwei while()-Schleifen. Hier kommt es einfach darauf an wie groß man "$values" werden lassen will bzw. den auszuführenden Query, denn irgendwann läuft der PHP-memory voll. Daher habe ich zwei Schleifen genommen und habe bei 10000 Einträgen einen "break" gemacht. // obtain column template $stmt = $db->prepare('SHOW COLUMNS FROM towns'); $stmt->execute(); $columns = array_fill_keys(array_values($stmt->fetchAll(PDO::FETCH_COLUMN)), null); // multiple INSERT $postcode = '01000';// smallest german postcode while ($postcode <= 99999) {// highest german postcode $values = array(); while ($postcode <= 99999) { // reset row $row = $columns; // now fill our row with data $row['postcode'] = sprintf('%05d', $postcode); // build INSERT array foreach ($row as $value) { $values[] = $value; } $postcode++; // avoid memory kill if (!($postcode % 10000)) { break; } } // build query $count_columns = count($columns); $placeholder = ',(' . substr(str_repeat(',?', $count_columns), 1) . ')';//,(?,?,?) $placeholder_group = substr(str_repeat($placeholder, count($values) / $count_columns), 1);//(?,?,?),(?,?,?)... $into_columns = implode(',', array_keys($columns));//col1,col2,col3 // this part is optional: $on_duplicate = array(); foreach ($columns as $column => $row) { $on_duplicate[] = $column; $on_duplicate[] = $column; } $on_duplicate = ' ON DUPLICATE KEY UPDATE' . vsprintf(substr(str_repeat(', %s = VALUES(%s)', $count_columns), 1), $on_duplicate); // execute query $stmt = $db->prepare('INSERT INTO towns (' . $into_columns . ') VALUES' . $placeholder_group . $on_duplicate);//INSERT INTO towns (col1,col2,col3) VALUES(?,?,?),(?,?,?)... {ON DUPLICATE...} $stmt->execute($values); Der "ON DUPLICATE KEY"-Teil ist natürlich optional. Das kann interessant sein, wenn man PRIMARY KEY auf einer ID hat und dann keinen INSERT ausführen will, sondern automatisch ein UPDATE. Gefällt mir Teilen twittern
▲	pn email

programmierer-forum.de Gast	05.03.2015, 22:08 zitieren Mach mit! Wenn Dir die Beiträge zum Thread "Multiple INSERT mit PDO" gefallen haben oder Du noch Fragen hast oder Ergänzungen machen möchtest, solltest Du Dich gleich bei uns anmelden: Registrierte Mitglieder genießen die folgenden Vorteile: ✔ kostenlose Mitgliedschaft ✔ keine Werbung ✔ direkter Austausch mit Gleichgesinnten ✔ neue Fragen stellen oder Diskussionen starten ✔ schnelle Hilfe bei Problemen ✔ Bilder und Videos hochladen ✔ und vieles mehr...
▲