commit a7b202e9f121949e81b06e1b1406c7ee632a48d0
parent fd03122ec1b5c3ac7523814fc744d6f7c21f8f9e
Author: William Lindholm <william_lindholm@outlook.com>
Date: Mon, 25 Sep 2023 09:20:03 +0000
Automatically adding dropdowns foreign key columns
Diffstat:
| M | Components.php | | | 100 | ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--- |
| M | operations.php | | | 1 | + |
| M | test.php | | | 52 | +++++++++++++++++++++++++++++----------------------- |
3 files changed, 127 insertions(+), 26 deletions(-)
diff --git a/Components.php b/Components.php
@@ -135,6 +135,12 @@
$stmt->execute($values);
}
+ function getPrimaryKeysOfReferencedTable($pdo, $referencedTable, $referencedColumn) {
+ $stmt = $pdo->prepare("SELECT $referencedColumn FROM $referencedTable");
+ $stmt->execute();
+ return $stmt->fetchAll(PDO::FETCH_COLUMN, 0);
+ }
+
function generateInsertFunction($tableName) {
$db = Database::getInstance();
$pdo = $db->getPdo();
@@ -145,18 +151,106 @@
}
$columns = fetchTableColumns($pdo, $tableName);
-
+ $foreignKeys = getForeignKeys($tableName);
+
$modalBuilder = (new ModalBuilder())
->setModalId('insertModal')
->setTableName($tableName);
-
+
+ $handledForeignKeys = []; // To keep track of which foreign keys have already been handled
+
foreach ($columns as $column) {
- $modalBuilder->addColumn($column);
+ $isForeignKey = false;
+
+ foreach ($foreignKeys as $foreignKey) {
+ if ($foreignKey['COLUMN_NAME'] == $column) {
+ if (in_array($column, $handledForeignKeys)) {
+ continue; // Skip if this foreign key column is already handled
+ }
+ $referencedTable = $foreignKey['REFERENCED_TABLE_NAME'];
+ $primaryKeyValues = fetchPrimaryKeyValues($pdo, $referencedTable);
+ $modalBuilder->addDropdownColumn($column, $primaryKeyValues);
+ $isForeignKey = true;
+
+ // Mark the current foreign key column as handled
+ $handledForeignKeys[] = $column;
+
+ // Check if any other columns reference the same table (i.e., composite key)
+ foreach ($foreignKeys as $fk) {
+ if ($fk['REFERENCED_TABLE_NAME'] == $referencedTable) {
+ $handledForeignKeys[] = $fk['COLUMN_NAME'];
+ }
+ }
+
+ break;
+ }
+ }
+
+ if (!$isForeignKey) {
+ $modalBuilder->addColumn($column);
+ }
}
echo $modalBuilder->generateOpenButton("Add Data");
echo $modalBuilder->build();
}
+
+ function fetchPrimaryKeyValues($pdo, $table) {
+ // Get primary key columns of the table
+ $stmt = $pdo->prepare("SHOW KEYS FROM $table WHERE Key_name = 'PRIMARY'");
+ $stmt->execute();
+ $primaryKeys = $stmt->fetchAll(PDO::FETCH_ASSOC);
+
+ // If there's only one primary key, return its values
+ if (count($primaryKeys) === 1) {
+ $column = $primaryKeys[0]['Column_name'];
+ $stmt = $pdo->prepare("SELECT $column FROM $table");
+ $stmt->execute();
+ return $stmt->fetchAll(PDO::FETCH_COLUMN, 0);
+ }
+
+ // For composite primary keys
+ $columns = array_map(function ($item) {
+ return $item['Column_name'];
+ }, $primaryKeys);
+
+ $selectColumns = implode(", ", $columns);
+ $stmt = $pdo->prepare("SELECT $selectColumns FROM $table");
+ $stmt->execute();
+
+ $results = $stmt->fetchAll(PDO::FETCH_ASSOC);
+ $compositeKeyValues = [];
+
+ foreach ($results as $row) {
+ $compositeKeyValues[] = implode("-", $row); // Combining multiple column values
+ }
+
+ return $compositeKeyValues;
+ }
+
+ function getForeignKeys($table) {
+ // Get the database instance and PDO object
+ $db = Database::getInstance();
+ $pdo = $db->getPdo();
+
+ // Fetch the current database name
+ $currentDatabase = $pdo->query('SELECT DATABASE()')->fetchColumn();
+
+ // Prepare and execute the statement to fetch foreign keys
+ $stmt = $pdo->prepare("
+ SELECT COLUMN_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
+ FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
+ WHERE TABLE_SCHEMA = :databaseName
+ AND TABLE_NAME = :tableName
+ AND REFERENCED_TABLE_NAME IS NOT NULL;
+ ");
+
+ $stmt->bindParam(':databaseName', $currentDatabase);
+ $stmt->bindParam(':tableName', $table);
+ $stmt->execute();
+
+ return $stmt->fetchAll(PDO::FETCH_ASSOC);
+ }
function isFormSubmitted() {
return $_SERVER['REQUEST_METHOD'] === 'POST';
diff --git a/operations.php b/operations.php
@@ -16,6 +16,7 @@
echo "<h2>Operations</h2>";
displayTable("Operation");
+ generateInsertFunction("Operation");
echo "</div>";
diff --git a/test.php b/test.php
@@ -1,28 +1,34 @@
<?php
-
-
+ require 'Database.php';
include 'Components.php';
- include 'modalBuilder.php';
-
- generateHead();
- echo '<body>';
- $modalBuilder1 = (new ModalBuilder())
- ->setModalId('modal1')
- ->setTableName('SampleTable1')
- ->addColumn('FirstName')
- ->addColumn('LastName')
- ->addDropdownColumn('Foreign', ['hej', 'test', 'hello', 'asddfsdf']);
-
- $modalBuilder2 = (new ModalBuilder())
- ->setModalId('modal2')
- ->setTableName('SampleTable2')
- ->addColumn('Age');
-
- $modalBuilder1->generateOpenButton("Open Modal 1");
- $modalBuilder1->build();
- $modalBuilder2->generateOpenButton("Open Modal 2");
- $modalBuilder2->build();
+ Database::getInstance('mysql', 'a22willi', 'root', 'Safiren1');
+
+ function getForeignKeys($table) {
+ // Get the database instance and PDO object
+ $db = Database::getInstance();
+ $pdo = $db->getPdo();
+
+ // Fetch the current database name
+ $currentDatabase = $pdo->query('SELECT DATABASE()')->fetchColumn();
+
+ // Prepare and execute the statement to fetch foreign keys
+ $stmt = $pdo->prepare("
+ SELECT COLUMN_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
+ FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
+ WHERE TABLE_SCHEMA = :databaseName
+ AND TABLE_NAME = :tableName
+ AND REFERENCED_TABLE_NAME IS NOT NULL;
+ ");
+
+ $stmt->bindParam(':databaseName', $currentDatabase);
+ $stmt->bindParam(':tableName', $table);
+ $stmt->execute();
+
+ return $stmt->fetchAll(PDO::FETCH_ASSOC);
+ }
- echo '</body>';
+ foreach (getForeignKeys('Operation') AS $op) {
+ print_r($op);
+ }
?>
\ No newline at end of file
