Rechnen direkt in MySQL?

[Bernd]

Hallo,

kann MySQL direkt rechnen oder dass ich erst alles über eine foreach Schleife auslesen muss und dann die Werte z.B. so zusammenzählen muss?

$Berechnung_3 +=$Berechnung_2;

Folgendes habe ich

$stmt_Berechnung = $mysqli->prepare("SELECT a_code, a_programm, a_masseinklverpackungb,
                                            a_masseinklverpackungl, a_tiefeinklverpackungb,
                                          	a_produzierterBestand, a_defekt, a_verlust 
                                    FROM artikel WHERE a_programm=?");
$stmt_Berechnung->bind_param("s",$Programm);
$stmt_Berechnung->execute();
$stmt_Berechnung->bind_result($a_code, $a_programm, $a_masseinklverpackungb,
                              $a_masseinklverpackungl, $a_tiefeinklverpackungb,                 
              							  $a_produzierterBestand, $a_defekt, $a_verlust);
$stmt_Berechnung->fetch();
$stmt_Berechnung->close();

Zu Rechnen wäre folgendes

• a_masseinklverpackungb * a_masseinklverpackungl * a_tiefeinklverpackungb
• a_produzierterBestand - a_defekt - a_verlust
• Ergebnis aus Zeile 1 * Ergebnis aus Zeile 2

Zudem kommt noch hinzu, dass ich die Werte aus Zeile mit einem , habe und diese müssten dann in ein . umgewandelt werden.

Oder sagt ihr dieses ist viel zu kompliziert, mach dieses lieber mit PHP? Die PHP Version würde so aussehen

$b = str_replace(',', '.', $array['a_masseinklverpackungb']);
$l = str_replace(',', '.', $array['a_masseinklverpackungl']);
$t = str_replace(',', '.', $array['a_tiefeinklverpackungb']);

$Lagerbestand = $array['a_produzierterBestand'] -$array['a_defekt'] - $array['a_verlust'];

$Berechnung_1 = $b*$l*$t;

EDIT: MySQL hat auch eine replace Function:
https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_replace

Die Frage ist nur, wie wende ich dieses an, ich werde aus dem Beispiel nicht wirklich schlau.

[encoder]

Ja MySql kann rechnen. Für mehr Details schaust du dir am besten Beispiel im Internet an, es dürfte unzählige geben.

Eine Zahl im Zahlenformat hat kein Komma. PHP konvertiert zwischen Zahl und String wie es gerade will. Das funktioniert ab und zu, oder eben auch nicht.
Eine Diskussion über Sinn und Unsinn einer so schlamperhaften Programmiersprache sollten wir hier besser nicht entfachen :-) Mit dem austauschen von Komma und Punkt machst du dieses Ratespiel jedenfalls nur noch schlimmer.
Speichere keine Zahl in der Datenbank als Text! Behandle die Zahl so lange als Zahl wie es geht. Bei der Umwandlung in Text gibst du dann an welches Trennzeichen du haben willst.

[Bernd]

Ich weiß, ich hätte das , als . eintragen sollen. Jetzt habe ich aber über die Jahre alle Scripte daraufhin angepasst. Wenn ich jetzt die Einträge in der Datenbank bereinige was mir am liebsten wäre, dann müsste ich sehr viele Scripte anpassen und dieses lohnt sich für mich leide so wie es ausschaut nicht mehr.

Daher mache ich aus dem was ich habe das beste.

[Rolf B]

Hallo Bernd,

nein, du hättest die Spalten als DECIMAL bzw. NUMERIC anlegen sollen. Aber das Kind ist jetzt im Brunnen und es rauszuholen macht massiv Arbeit.

Also lieber einen Speiseaufzug bauen und das Kind unten versorgen. Siehe meinen anderen Beitrag.

Rolf

[Camping_RIDER]

Aloha ;)

nein, du hättest die Spalten als DECIMAL bzw. NUMERIC anlegen sollen. Aber das Kind ist jetzt im Brunnen und es rauszuholen macht massiv Arbeit.

Also lieber einen Speiseaufzug bauen und das Kind unten versorgen. Siehe meinen anderen Beitrag.

Aus physikalischer Sicht muss ich da jetzt allerdings widersprechen. Die Hubarbeit, die ein Speiseaufzug über die Lebensdauer des Kindes verrichtet, dürfte - egal wie leicht der gebaut ist - die Hubarbeit zum Heraufholen des Kindes übersteigen.

Ich schlage vor, das Kind stattdessen einfach mit den Lebensmitteln zu bewerfen. Wo nichts mehr wieder hochkommt wird auch keine unnötige Hubarbeit verrichtet.

Grüße,

RIDER

[Rolf B]

Hallo Camping_RIDER,

das ist GENIAL. Nach gewisser Zeit steigt das Kind in der eigenen Sch... auf und kann rausklettern. Baden, fertig.

uärx

Rolf

[Camping_RIDER]

Aloha ;)

das ist GENIAL. Nach gewisser Zeit steigt das Kind in der eigenen Sch... auf und kann rausklettern. Baden, fertig.

Ich empfehle als Kompromiss zwischen olfaktorischer Belastung und günstiger Energiebilanz stattdessen einen kontrollierten Erdrutsch.

Grüße,

RIDER

[Rolf B]

Hallo Bernd,

ich hätte ja fast gesagt: „kein Problem“, aber dann

Zudem kommt noch hinzu, dass ich die Werte aus Zeile mit einem , habe und diese müssten dann in ein . umgewandelt werden.

Hatten wir darüber nicht schon mehrfach diskutiert? Die typengerechte Speicherung von Daten in der DB macht das Leben signifikant leichter.

Wären deine Felder numerisch, könnte man so anfangen:

SELECT a_masseinklverpackungb * a_masseinklverpackungl * a_tiefeinklverpackungb as volumen,
       a_produzierterBestand - a_defekt - a_verlust as menge
  FROM artikel WHERE a_programm=?

Das ist eine Query die dir pro Zeile die gewünschten Werte liefert. Um die kann man eine aggregierende Query herum legen:

SELECT SUM(volumen * menge)
FROM (SELECT a_masseinklverpackungb * a_masseinklverpackungl * a_tiefeinklverpackungb as volumen,
             a_produzierterBestand - a_defekt - a_verlust as menge
      FROM artikel WHERE a_programm=?)

Das Ergebnis ist genau eine Zahl. Allerdings hast Du dann auch nur genau das, und sonst nichts.

Wenn Du diese Rechnung nicht für ein bestimmtes Programm, sondern über alle Programme weg haben willst, kannst Du das so machen:

SELECT a_programm, SUM(volumen * menge)
FROM (SELECT a_programm, 
             a_masseinklverpackungb * a_masseinklverpackungl * a_tiefeinklverpackungb as volumen,
             a_produzierterBestand - a_defekt - a_verlust as menge
      FROM artikel)
GROUP BY a_programm

Die code-Spalte ist jetzt verloren gegangen. Um die korrekt einzubauen, müsste man wissen, wie Code und Progamm zueinander stehen. Haben alle Artikel eines Programms den gleichen Code? Wenn ja, kann man sie mit gruppieren. Wenn nicht, dann geht es nicht, dann ist es auch sinnlos, Code und Summe in einer Query zu ermitteln (weil zu einer Summe ja mehrere Codes gehören).

Bleibt noch ein Problem: Wie rechnest Du in SQL mit deinen Komma-Strings. Wie gesagt: typgerechte Speicherung macht das Leben leichter, und im SQL herumzukonvertieren macht (a) die Query lahm und (b) das SQL kaum noch lesbar. Immerhin hast Du mit MYSQL Glück: Es ist bereit, Fehler zu verzeihen. Laut Doku konvertiert es Strings bei Bedarf in Zahlen. Du musst also nur das Komma durch einen Punkt austauschen. Das geht mit REPLACE(wert, ',', '.'), d.h. wenn Du das jeweils auf die String-Felder deiner Abfrage anwendest, sollte der Versuch, mit dem Ergebnis zu rechnen, es automatisch in eine Zahl umwandeln. Viel Glück 😀

Rolf

[Bernd]

Hallo,

vielen Dank für deine sehr Ausführliche Erklärung. Hab dein Beispiel so geändert

SELECT SUM(volumen * menge)
FROM (SELECT REPLACE(a_masseinklverpackungb, ',', '.') * REPLACE(a_masseinklverpackungl, ',', '.') * REPLACE(a_tiefeinklverpackungb, ',', '.') as volumen,
             a_produzierterBestand - a_defekt - a_verlust as menge
      FROM artikel WHERE a_programm='53');

Jetzt erhalte ich folgende Meldung, die auch erhalte wenn ich REPLACE nicht einsetzte

#1248 - Every derived table must have its own alias

Ich glaube ich bleibe bei PHP.

[Rolf B]

Hallo Bernd,

das kommt davon wenn man Tipps nicht vorher ausprobiert, sorry.

Aber es ist ganz einfach: du musst nur tun, was er dir sagt. Der innere Select ist eine abgeleitete Tabelle (derived table) und braucht einen Aliasnamen.

Was ist ein Aliasname? Kennst Du, z.B. hier:

select x.foo, x.bar FROM sometable x

x ist der Aliasname für die Tabelle sometable.

In deinem Fall also so (ich verkürze mal etwas):

select sum(...) 
from (select *wilde rechnerei* from artikel where a_programm=?) werte

Der Aliasname wäre hier werte.

Ich hätte aber noch eine andere Idee für Dich. MYSQL kennt ein Ding namens VIEW. Du könntest einen View auf deine Artikeltabelle anlegen, in dem die falsch gespeicherten Felder konvertiert werden. Beispiel aus meiner lokalen DB:

CREATE TABLE `artikel` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `foo` varchar(20) DEFAULT NULL,
  `bar` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
        
INSERT INTO `test`.`artikel` (`foo`, `bar`)
VALUES ('12,34', '47,11');

CREATE 
VIEW `num_artikel` AS
    SELECT 
        id, 
        CAST(REPLACE(`artikel`.`foo`, ',', '.') AS DECIMAL (10 , 2 )) AS `foo`,
        CAST(REPLACE(`artikel`.`bar`, ',', '.') AS DECIMAL (10 , 2 )) AS `bar`
    FROM
        `artikel`

Wenn Du sowas tun willst, musst Du es natürlich für Dich und Deinen Bedarf anpassen. Felder, die keine Kommazahlen enthalten (bei mir: Id), übernimmst Du 1:1. Jedes Feld, das deine Kommazahlen enthält, braucht einen entsprechenden CAST und REPLACE. Im View erscheint es dann als ein DECIMAL(10,2) Feld, das bedeutet: 10 Ziffern, 2 davon Nachkommastellen. Wenn deine Zahlen anders strukturiert sind, musst Du die Größe der Decimals anpassen.

Aber damit kannst Du dann Queries machen, die nicht mehr konvertieren müssen. Die normalen CRUD-Operationen (das Tagesgeschäft: Create, Read, Update, Delete) machst Du auf der Table. Und wenn Du in der DB rechnen willst, nimmst Du den View. Der spiegelt automatisch immer den Inhalt der Table wieder.

Rolf

[Bernd]

Hallo,

danke wieder für deine weitere Erklärung. Ich habe es so in phpMyAdmin eingegeben

SELECT SUM(volumen * menge)
FROM (SELECT REPLACE(a_masseinklverpackungb, ',', '.') * REPLACE(a_masseinklverpackungl, ',', '.') * REPLACE(a_tiefeinklverpackungb, ',', '.') as volumen,
             a_produzierterBestand - a_defekt - a_verlust as menge
      FROM artikel WHERE a_programm='53' AND a_status = 1) werte

Als Ergebnis erhalte ich folgendes

Ich weiß nicht ob es an meiner Internetverbindung liegt, aber phpMyAdmin braucht für die Abfrage sehr lange?

Als Ergebnis bekomme ich das gleiche wie wenn ich folgendes mit PHP Ausführen würde

SELECT a_code, a_programm, a_masseinklverpackungb, a_masseinklverpackungl, a_tiefeinklverpackungb, a_produzierterBestand, a_defekt, a_verlust FROM artikel WHERE a_programm = '53' AND a_status = 1

Welche Version würdest du bevorzugen? PHP oder direkt MySQL?

[Bernd]

OK, es lag wohl an meinem Rechner. Hab es jetzt so umgesetzt

function VolumenproPlatz($mysqli, $lagerflaeche){

        $sql = "SELECT SUM(volumen * menge)
                    
                    FROM (SELECT REPLACE(a_masseinklverpackungb, ',', '.') * REPLACE(a_masseinklverpackungl, ',', '.') * REPLACE(a_tiefeinklverpackungb, ',', '.') as volumen, a_produzierterBestand - a_defekt - a_verlust as menge

                    FROM artikel WHERE a_programm=? AND a_status = 1) werte ";
        $res = $mysqli->prepare($sql);
        $res->bind_param("s", $lagerflaeche);
        
        $res->execute();
        $res->bind_result($SUML);
        $res->fetch();
        $res->close();  

        return $SUML; 
    }

Und die Ausgabe dann so

<?php echo VolumenproPlatz($mysqli, $array_kategorieuebersicht['ma_id']); ?> qm

[Rolf B]

Hallo Bernd,

ich würde sagen, wenn die SUM Version länger braucht als die PHP-Berechnung, dann ist was faul. Er zeigt dir aber an, dass er flotte 1,1ms für die Abfrage brauchte, d.h. das Problem liegt nicht an SQL, sondern der Server war überlastet (entweder der mit phpMyAdmin oder der SQL Server selbst). Wenn die Laufzeit im SQL riesig wäre, müsste man EXPLAIN anwerfen.

Wenn Du außer der Summe nichts brauchst würde ich immer die SQL Version vorziehen.

Waren die 1,1ms Laufzeit eigentlich auf der Test- oder Produktiv-Datenbank?

Rolf

[Bernd]

Ich habe es jetzt mal so getestet und es scheint zu funktionieren

<?php $kategorieuebersicht = frontend_kategorieuebersicht($mysqli); ?>

	<?php 
	$Berechnung_3 = 0;
	foreach($kategorieuebersicht as $array_kategorieuebersicht){

		$test = $array_kategorieuebersicht['ma_id']; 

		$query  = "SELECT a_code, a_programm, a_masseinklverpackungb, a_masseinklverpackungl, 
                      a_tiefeinklverpackungb, a_produzierterBestand, a_defekt, a_verlust  

               FROM artikel WHERE a_programm = '$test' AND a_status = 1";
		
		$result = $mysqli->query($query);

		//var_dump($query);

		while ($row = $result->fetch_assoc()) {
		  	
      $b = str_replace(',', '.', $row['a_masseinklverpackungb']);
			$l = str_replace(',', '.', $row['a_masseinklverpackungl']);
			$t = str_replace(',', '.', $row['a_tiefeinklverpackungb']);

			$Lagerbestand = $row['a_produzierterBestand'] -$row['a_defekt'] - $row['a_verlust'];

			$Berechnung_1 = $b*$l*$t;

			$Berechnung_2 = $Berechnung_1 * $Lagerbestand;
			
			$Berechnung_3 +=$Berechnung_2;
		}

	?>
		<div style="display: flex; margin-bottom: 1em;">
			 <div style="width: 30%;"><?php echo $array_kategorieuebersicht['ma_titel']; ?></div>
			 <div style="width: 30%; text-align: right;"> <?php echo $Berechnung_3; ?> qm</div>	
		</div>
	<?php } ?>