This article describes how to create scripts from manufacturer data using PHP and mySQL databases, which convert this manufacturer data into GDL scripts.
The advantage of this method is that you always get the manufacturer data transferred 1:1 and that errors cannot occur due to incorrect copying.
The principle consists of the following steps: Export the Excel file, import into the mySQL database, PHP script to read out the database contents in combination with GDL script snippets.
The following steps are individually necessary to automatically transfer manufacturer data from an Excel file into a GDL script:
- Open manufacturer Excel in Open Office, as Excel outputs special characters incorrectly during CSV export
- The ® (Registered) symbol (and other special characters) must be removed if present.
- CAUTION: sometimes the export doesn't work. However, it always works from Open Office.
- Open PHPadmin on your server, go to the "Operations" tab and import the contents of the CSV table removed earlier into the database, copying a TAB as a column separator (from Coda, Sublime Text or another text editor).
Sometimes you have to try the import in SQL multiple times. - Write a PHP script that accesses the database and outputs the required values in the correct order. Insert the necessary GDL code sections between the queried database sections.
- Call the PHP web page in the browser and copy the displayed text from the browser into the corresponding GDL script.
An example script (old PHP code: no longer valid for PHP 7 and above) for calling the database "connect_database.inc.php"
PHP
<?php
// Datenbankverbindung mit PDO (PHP 7+ / PHP 8+)
// Verbindungsparameter anpassen:
define('DB_HOST', 'localhost');
define('DB_NAME', 'Hersteller_Update_2019');
define('DB_USER', 'root');
define('DB_PASS', '******');
define('DB_CHARSET', 'utf8mb4');
try {
$dsn = "mysql:host=" . DB_HOST . ";dbname=" . DB_NAME . ";charset=" . DB_CHARSET;
$options = [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false,
];
$pdo = new PDO($dsn, DB_USER, DB_PASS, $options);
} catch (PDOException $e) {
// Im Produktivbetrieb: Fehlermeldung loggen, nicht anzeigen
die('Datenbankverbindung fehlgeschlagen: ' . $e->getMessage());
}
/**
* Liest einen einzelnen Feldwert aus einer Tabelle anhand der ID.
* Verwendet Prepared Statements gegen SQL-Injection.
*
* @param PDO $pdo PDO-Verbindungsobjekt
* @param string $was Spaltenname (wird als Identifier eingesetzt)
* @param string $von Tabellenname (wird als Identifier eingesetzt)
* @param int $id Datensatz-ID
* @return string Gefundener Wert oder leerer String
*/
function da_ba_fra(PDO $pdo, string $was, string $von, int $id): string
{
// Tabellen- und Spaltennamen können in PDO nicht als Parameter gebunden werden,
// daher Whitelist-Validierung (nur alphanumerisch + Unterstrich erlaubt):
if (!preg_match('/^[a-zA-Z0-9_]+$/', $was) || !preg_match('/^[a-zA-Z0-9_]+$/', $von)) {
throw new InvalidArgumentException("Ungültiger Tabellen- oder Spaltenname.");
}
$sql = "SELECT `$was` FROM `$von` WHERE id = :id LIMIT 1";
$stmt = $pdo->prepare($sql);
$stmt->execute([':id' => $id]);
$row = $stmt->fetch();
return $row ? (string) $row[$was] : '';
}
?>
And the corresponding script for reading the database and generating the GDL code "Master_GDL.php":
PHP
<!doctype html>
<html lang="de">
<head>
<meta charset="utf-8" />
<title>Hersteller GDL EXPORT</title>
<link rel="stylesheet" type="text/css" href="gdl.css" />
</head>
<body>
<?php
declare(strict_types=1);
error_reporting(E_ALL);
ini_set('display_errors', '1'); // Im Produktivbetrieb auf '0' setzen
require_once "includes/connect_database.inc.php";
$comment = "! ---------------------------------------------------------------------- !<br>\r\n";
$tablename = "Update_2019";
// Whitelist-Prüfung Tabellenname
if (!preg_match('/^[a-zA-Z0-9_]+$/', $tablename)) {
die("Ungültiger Tabellenname.");
}
// Alle Datensätze abrufen
$sql = "SELECT * FROM `$tablename` ORDER BY id";
$stmt = $pdo->query($sql);
$rows = $stmt->fetchAll(); // Array aller Zeilen (FETCH_ASSOC)
if (empty($rows)) {
echo "<p>Keine Datensätze gefunden.</p>";
exit;
}
// Spaltennamen aus dem ersten Datensatz ermitteln
$fieldnames = array_keys($rows[0]);
// $fieldnames[0] = id, [1] = erste Datenspalte, usw. – Indizes entsprechen dem Original
// SCHLEIFE durch alle DATENSÄTZE
foreach ($rows as $row) {
echo $comment;
// Feldnamen über numerischen Index ansprechen (wie im Original mit $fieldname[$zal])
// Hinweis: Indizes 1-basiert wie im Originalscript beibehalten
// Spalte 6 → Größe
$groesse = (string) ($row[$fieldnames[6]] ?? '');
// Nur die erste Zeile übernehmen (entspricht dem preg_split im Original)
$groesse = preg_split('/\n\s*\n/Uis', $groesse)[0] ?? $groesse;
// Spalte 24 → Dateiname (ohne Endung)
$dateiname = (string) ($row[$fieldnames[24]] ?? '');
$dateiname = substr($dateiname, 0, -4);
// Bezeichnungsfelder
$bezeichnung = (string) ($row[$fieldnames[2]] ?? '');
$produkt = (string) ($row[$fieldnames[3]] ?? '');
$artikel = (string) ($row[$fieldnames[4]] ?? '');
$farbnummer = (string) ($row[$fieldnames[9]] ?? '');
$bezeichnung_lang = $bezeichnung . "_" . $produkt . "_" . $artikel . "_" . $farbnummer;
// RGB-Werte (Spalten 28, 29, 30) normiert auf 0–1
$RGB_R = number_format((float)($row[$fieldnames[28]] ?? 0) / 255, 4);
$RGB_G = number_format((float)($row[$fieldnames[29]] ?? 0) / 255, 4);
$RGB_B = number_format((float)($row[$fieldnames[30]] ?? 0) / 255, 4);
// TEXTUR-Definition (Spalten 25, 26)
$tex_w = (float)($row[$fieldnames[25]] ?? 0) / 1000;
$tex_h = (float)($row[$fieldnames[26]] ?? 0) / 1000;
echo "DEFINE TEXTURE '" . htmlspecialchars($bezeichnung_lang) . "_tex' '"
. htmlspecialchars($dateiname) . ".jpg', "
. $tex_w . ", " . $tex_h . ", 5, 0 <br>\r\n";
// MATERIAL-Definition
echo "DEFINE MATERIAL '" . htmlspecialchars($bezeichnung_lang) . "' 24, "
. $RGB_R . ", " . $RGB_G . ", " . $RGB_B . ", "
. "ind(fill, '" . htmlspecialchars($groesse) . "'), 0, "
. "ind(texture, '" . htmlspecialchars($bezeichnung_lang) . "_tex')<br>\r\n";
}
?>
</body>
</html>
And part of the output:
GDL
! ---------------------------------------------------------------------- !
DEFINE TEXTURE 'hersteller_xyz_00_tex' 'hersteller_xyz_00.jpg', 0.3, 0.3, 5, 0
DEFINE MATERIAL 'hersteller_xyz_00' 24, 0.8941, 0.8667, 0.7922, ind(fill, '15,00 m x 1,22 m'), 0, ind(texture, 'hersteller_xyz_00_tex')
! ---------------------------------------------------------------------- !
DEFINE TEXTURE 'hersteller_xyz_01_tex' 'hersteller_xyz_01.jpg', 0.3, 0.3, 5, 0
DEFINE MATERIAL 'hersteller_xyz_01' 24, 0.7961, 0.7765, 0.7529, ind(fill, '15,00 m x 1,22 m'), 0, ind(texture, 'hersteller_xyz_01_tex')
! ---------------------------------------------------------------------- !
DEFINE TEXTURE 'hersteller_xyz_02_tex' 'hersteller_xyz_02.jpg', 0.3, 0.3, 5, 0
DEFINE MATERIAL 'hersteller_xyz_02' 24, 0.5569, 0.5255, 0.5137, ind(fill, '15,00 m x 1,22 m'), 0, ind(texture, 'hersteller_xyz_02_tex')
! ---------------------------------------------------------------------- !
DEFINE TEXTURE 'hersteller_xyz_03_tex' 'hersteller_xyz_03.jpg', 0.3, 0.3, 5, 0
DEFINE MATERIAL 'hersteller_xyz_03' 24, 0.3529, 0.3216, 0.3098, ind(fill, '15,00 m x 1,22 m'), 0, ind(texture, 'hersteller_xyz_03_tex')
! ---------------------------------------------------------------------- !
DEFINE TEXTURE 'hersteller_xyz_04_tex' 'hersteller_xyz_04.jpg', 0.3, 0.3, 5, 0
DEFINE MATERIAL 'hersteller_xyz_04' 24, 0.9490, 0.9098, 0.8039, ind(fill, '15,00 m x 1,22 m'), 0, ind(texture, 'hersteller_xyz_04_tex')
! ---------------------------------------------------------------------- !
DEFINE TEXTURE 'hersteller_xyz_05_tex' 'hersteller_xyz_05.jpg', 0.3, 0.3, 5, 0
DEFINE MATERIAL 'hersteller_xyz_05' 24, 0.9255, 0.8745, 0.7490, ind(fill, '15,00 m x 1,22 m'), 0, ind(texture, 'hersteller_xyz_05_tex')
! ---------------------------------------------------------------------- !