Montag, 7. März 2016

PHP - SELECT GREATEST & LEAST value in only one row

Größter und kleinster Wert einer einzigen Zeile in der Datenbank-Tabelle auslesen.
Max and min value in only one row for a database-table.

Die PHP MySQL Befehle sehen das Auslesen des größten und kleinsten Wertes einer einzigen Datenbank-Tabellen-Zeile nicht vor. Das ist sehr schade.

In den Rohdaten einer Datenbank-Tabelle (SQL) sind die Daten einer Datenbank-Tabellen-Zeile zwar in eine Text-Zeile geschrieben, können aber nicht als solche so einfach ausgelesen werden, sondern die Werte sind den Spaltennamen zugeordnet. Das ganze hat Ähnlichkeit mit einem ARRAY, mit dem Werte gegen andere Werte ausgetauscht und Werte zugeordnet werden können.

So sehen die Rohdaten einer SQL-Datei aus (table-one):
SET NAMES utf8;
SET time_zone = '+00:00';
SET foreign_key_checks = 0;
SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
DROP TABLE IF EXISTS `database-table-name`;
CREATE TABLE `database-table-name` (
  `id` tinyint(8) unsigned NOT NULL,
  `name` tinytext COLLATE utf8_unicode_ci NOT NULL,
  `1980` bigint(20) unsigned NOT NULL,
  `1981` bigint(20) unsigned NOT NULL,
  `1982` bigint(20) unsigned NOT NULL,
  `1983` bigint(20) unsigned NOT NULL,
  `1984` bigint(20) unsigned NOT NULL,
  `1985` bigint(20) unsigned NOT NULL,
  `1986` bigint(20) unsigned NOT NULL,
  `1987` bigint(20) unsigned NOT NULL,
  `1988` bigint(20) unsigned NOT NULL,
  `1989` bigint(20) unsigned NOT NULL,
  `1990` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
INSERT INTO `database-table-name` (`id`, `name`, `1980`, `1981`, `1982`, `1983`, `1984`, `1985`, `1986`, `1987`, `1988`, `1989`, `1990`) VALUES
(1, 'laus', 258, 325, 554, 147, 115, 648, 541, 221, 554, 998, 905),
(2, 'simi', 254, 144, 141, 582, 774, 325, 369, 366, 144, 547, 507),
(3, 'jenni', 145, 143, 369, 465, 368, 485, 999, 244, 358, 111, 503),
(4, 'trixi', 325, 698, 789, 217, 994, 256, 121, 895, 985, 254, 705),
(5, 'meli', 895, 427, 846, 348, 355, 668, 125, 475, 654, 105, 606),
(6, 'cat', 951, 943, 671, 147, 442, 554, 358, 226, 456, 501, 202);

Es wird empfohlen, Datenbank-Tabellen nach dem Auslesen von Spalten zu gestalten.

Typische Datenbank-Tabellen-Struktur (table-two):








ID name ort date wert

1 laus laushausen 11.03.2015 12345

2 simi simidorf 12.03.2015 56789

3 jenni jennikaff 13.03.2015 54321

4 trixi trixistadt 14.03.2015 69874

5 meli melicity 15.03.2015 25846

6 cat catland 16.03.2015 30102







Mit dieser Datenbank-Tabelle kann mit einer PHP-Schleife sehr einfach eine HTML-Tabelle erstellt werden, in dem jede Datenbank-Tabellen-Spalte (column) in eine verschachtelte while-for-Schleife gepackt wird und bei jedem Durchlauf die nächste Datenbank-Tabellen-Zeile (row) als neue HTML Tabellen-Zeile geschrieben wird.

Mit dieser Datenbank-Tabellen-Struktur ist es einfach, den größten und kleinsten Wert einer Spalte zu ermitteln. Für das Ermitteln des größten und kleinsten Wertes einer Datenbank-Tabellen-Spalte sind die PHP MySQL Befehle MAX und MIN geeignet.
SELECT MAX(column-name) AS max FROM database-table-name

SELECT MIN(column-name) AS min FROM database-table-name

Datenbank-Tabellen sind in MySql auf 1.000 Spalten begrenzt, aber die Zeilenanzahl ist unendlich. Deswegen wird geraten, eine Datenbank-Tabellen-Struktur nach Zeilen auszurichten, so dass die Spaltennamen keine Werte, wie Jahreszahlen, enthalten.
Die verwendete Datenbank-Tabelle in diesem Tutorial sollte eigentlich besser so aussehen (table-one-better):







ID name year wert

1 laus 1980 258

2 laus 1981 325

3 laus 1982 554

4 laus 1983 147

5 laus 1984 115

6 laus 1985 648

7 laus 1986 541

8 laus 1987 221

9 laus 1988 554

10 laus 1989 998

11 laus 1990 905

12 simi 1980 254

13 simi 1981 144

14 simi 1982 141

15 simi 1983 582

16 simi 1984 774

17 simi 1985 325

18 simi 1986 369

19 simi 1987 366

20 simi 1988 144








Mit solch einer Datenbank-Tabellen-Struktur wird jedem Namen (name) nur ein Wert (wert) mit einem Zuordnungs-Wert (year) zugewiesen. Zur besseren Übersichtlichkeit, können die Daten für die einzelnen Namen in extra Tabellen geschrieben werden. Dabei kann dann die Spalte "name" entfallen.

Nun gibt es aber Situationen, in denen die Daten in der Datenbank-Tabellen-Struktur wie in einer herkömmlichen CSV-Datei gespeichert sind. Ein Beispiel dafür in der folgenden Datenbank-Tabelle (table-one).
















ID name 1980 1981 1982 1983 1984 1985 1986 1987 1988 1989 1990

1 laus 258 325 554 147 115 648 541 221 554 998 905

2 simi 254 144 141 582 774 325 369 366 144 547 507

3 jenni 145 143 369 465 368 485 999 244 358 111 503

4 trixi 325 698 789 217 994 256 121 895 985 254 705

5 meli 895 427 846 348 355 668 125 475 654 105 606

6 cat 951 943 671 147 442 554 358 226 456 501 202















Mit dieser Datenbank-Tabelle ist es nicht möglich mittels SELECT MIN MAX den größten und kleinsten Wert für einen Namen zu ermitteln, bzw. auszulesen.

Auch mit SELECT GREATEST und SELECT LEAST ist dies nicht ohne weiteres möglich, aber doch möglich.

Mit folgendem PHP-Script kann der kleinste Wert (SELECT LEAST) einer einzigen Datenbank-Tabellen-Zeile (table-row) ermittelt werden. Dabei ist aber einiges zu beachten, dass nach dem PHP-Script erläutert wird.
<?php

$dbconnect = new mysqli("localhost", "username", "password", "database-table-name");

/* check database-connection */
if (mysqli_connect_errno())
{
printf ("Connect failed: %s\n", mysqli_connect_error());
exit();
}

$dbquery = "SELECT LEAST(`1980`, `1981`, `1982`, `1983`, `1984`, `1985`, `1986`, `1987`, `1988`, `1989`, `1990` ) AS least FROM database-table-name WHERE name = 'meli'";

$dbresult = mysqli_query($dbconnect, $dbquery);

while($row = mysqli_fetch_assoc($dbresult))
{
$min = $row[least];
}

echo $min;

?>

Ergebnis: 105
Der gleiche PHP-Script funktioniert auch mit SELECT GREATEST.
<?php

$dbconnect = new mysqli("localhost", "username", "password", "database-table-name");

/* check database-connection */
if (mysqli_connect_errno())
{
printf ("Connect failed: %s\n", mysqli_connect_error());
exit();
}

$dbquery = "SELECT GREATEST(`1980`, `1981`, `1982`, `1983`, `1984`, `1985`, `1986`, `1987`, `1988`, `1989`, `1990` ) AS greatest FROM database-table-name WHERE name = 'meli'";

$dbresult = mysqli_query($dbconnect, $dbquery);

while($row = mysqli_fetch_assoc($dbresult))
{
$max = $row[greatest];
}

echo $max;

?>

Ergebnis: 895

Was unbedingt zu beachten ist (absolutely attention):

Die folgenden Bedingungen müssen unbedingt beachtet werden. Sie sind Bedingungen, also unbedingte Voraussetzungen für die Funktion der beiden PHP-Scripte.


1.) Backticks verwenden

Es müssen in den PHP-Scripten die Spaltennamen nicht in einfache ( ' ) oder doppelte ( " ) Anführungszeichen gesetzt werden, sondern in sogenannte Backticks ( ` ).

Die Backticks, oder ein Backtick, wird in der Typografie "accent grave" oder "Gravis" genannt.

Die Gravis sind von französischen Buchstaben bekannt: à è ì ò ù.

Backticks sind mit der Tastatur durch drücken der Shift-Taste  ⇧  und der Taste rechts neben dem Fragezeichen ( ? ) mit dem Scharfes-S/Eszett ( ß ) und dem Backslash ( \ ) und dem anschließenden drücken der Leertaste/Space zu erreichen.
Shift ⇧ + ` + Space = `
Es kann aber auch einfach copy & paste verwendet werden.

In PHP werden diese Zeichen auch "Operatoren" genannt.
PHP Manual zur Operatoren: http://php.net/manual/de/language.operators.execution.php

Wikipedia-Artikel zu "Gravis": https://de.wikipedia.org/wiki/Gravis_%28Typografie%29


2.) Keine nicht vorhandenen Spaltennamen verwenden

Es dürfen im SELECT GREATEST und SELECT LEAST Befehl keine Spaltennamen angegeben sein, die nicht in der Datenbank-Tabelle vorhanden sind. Wenn dies der Fall ist, funktioniert der Befehl nicht.

3.) Nach letztem Spaltennamen kein Komma

Nach dem letzten Spaltennamen darf kein Komma gesetzt werden.
`1989`, `1990` )

Wird all das bedacht, müsste das PHP-Script funktionieren.

AUTOMATISIERUNG
Leider wird der SELECT GREATEST und SELECT LEAST Befehl bei Datenbank-Tabellen mit sehr vielen Spaltennamen sehr lang. Dies lässt sich vermeiden, indem die Werte (Jahreszahlen) für den SELECT Befehl generiert werden. Dabei wird eine Funktion benutzt, die einzelne Spaltennamen (columnnames) auslesen kann und eine Funktion, die die Anzahl der Spalten auslesen kann.
<?php
$dbconnect = new mysqli("localhost", "username", "password", "database");

/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}

$dbquery = "select * from database-table";
$dbresult = mysqli_query($dbconnect, $dbquery);

// Den ersten Jahres-Spaltennamen auslesen
$firstcolname = mysqli_fetch_field_direct($dbresult, 2); // Zahl 2 ist 3. columnname von links.
$firstyear = $firstcolname->name;

// Anzahl der Spalten in der Datenbank-Tabelle auslesen
$colcount = mysqli_field_count($dbconnect);

// Den Spaltennamen der letzten Spalte berechnen
$lastyear = (($firstyear + $colcount) - 3);

// Anzahl der Spalten mit Jahreszahlen berechnen minus letzte Spalte
$numcolumns = (($lastyear - $firstyear) +2 );

// Generieren der Werte (Jahreszahlen) für die SELECT Funktion
$colnames = ''; // Variable mit den gesammelten Jahreszahlen, außer der letzten
for ( $i = 2; $i < $numcolumns; $i++ )
{
$columnnames = mysqli_fetch_field_direct($dbresult, $i);
$colnames .= '`' . $columnnames->name . '`, '; // Zeichen anfügen, mit Komma
}

// Zeichen an letzte Jahreszahl anfügen (ohne Komma!)
$lastcolname = "`$lastyear`";

// SELECT GREATEST value in only one row
$dbquery = "SELECT GREATEST($colnames $lastcolname ) AS greatest FROM database-table  WHERE name = 'meli'";
$dbresult = mysqli_query($dbconnect, $dbquery);

while($row = mysqli_fetch_assoc($dbresult))
{
$max = $row[greatest];
}

echo $max; // GREATEST Ergebnis

// SELECT LEAST value in only one row
$dbquery = "SELECT LEAST($colnames $lastcolname ) AS least FROM dabase-table  WHERE name = 'meli'";
$dbresult = mysqli_query($dbconnect, $dbquery);

while($row = mysqli_fetch_assoc($dbresult))
{
$min = $row[least];
}

echo $min; // LEAST Ergebnis

mysqli_close($dbconnect);
?>
Dieser PHP-Code funktioniert nur bei aufwärts fortlaufenden Zahlen als Spaltennamen (1, 2, 3, 4, 5, 6, 7, 8, 9, ... ). Sind die Spaltennamen-Zahlen abwärts sortiert, so muss das PHP-Script umgeschrieben werden. Fehlen fortlaufende Zahlen als Spaltennamen, so funktioniert das PHP-Script nicht.
Zu beachten ist, dass die Spaltennamen-Nummerierung bei 0 beginnt. Die 1. Spalte wird also mit 0 angesprochen, die 2. Spalte mit 1, die 3. Spalte mit 2, u.s.w. , und von links aus gezählt.

Infos zum Auslesen der Spaltenanzahl (mysqli_field_count): http://php.net/manual/de/mysqli.field-count.php

Infos zum Auslesen eines bestimmten Spaltennamens (mysqli_fetch_field_direct): http://php.net/manual/de/mysqli-result.fetch-field-direct.php

Nicht getestet wurde das PHP-Script mit einer Datenbank-Tabelle mit Zellen die leer sind, oder mit "NULL" gefüllt sind.


Ausschluss-Klausel: Keine Garantie. Keine Gewähr. Kein Support. Anfragen zwecklos.