Database connection incl. insert, get update, and delete data.

[LeonLatex]

Maybe it's hard to understand what I'm trying to achieve, but I'm tired of having to go into the script and change it as I change the number of tables (add or delete), and if I change the rows of the tables, and if I need to add, delete or change names somewhere, the logic (is that what it's called?) will not need to be added or subtracted. I remember that there was something about a "global" (or something) that had to be included.

I'm trying to develop a database connection script with the logic included no matter what changes in the database tables or rows. incl. form fields. What have I done wrong?

 

<?php
// db.php
// Databaseforbindelse

$host = '*****.****.********.**';
$db   = '**********_**********';
$user = '**********_**********';
$pass = '**********';
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];

try {
    $pdo = new PDO($dsn, $user, $pass, $options);
} catch (\PDOException $e) {
    throw new \PDOException($e->getMessage(), (int)$e->getCode());
}

/**
 * Legger til data i en spesifisert tabell
 */
function insertData($table, $data) {
    global $pdo;

    if (empty($data) || !is_array($data)) {
        throw new InvalidArgumentException("Data må være en ikke-tom assosiativ array.");
    }

    $columns = implode(", ", array_keys($data));
    $placeholders = implode(", ", array_fill(0, count($data), '?'));
    $sql = "INSERT INTO $table ($columns) VALUES ($placeholders)";
    
    $stmt = $pdo->prepare($sql);
    
    if ($stmt->execute(array_values($data))) {
        return $pdo->lastInsertId();
    }
    return false;
}

/**
 * Sletter data fra en spesifisert tabell
 */
function deleteData($table, $conditions) {
    global $pdo;

    if (empty($table) || !is_string($table)) {
        throw new InvalidArgumentException("Tabellnavn må være en ikke-tom streng.");
    }

    $where = [];
    foreach ($conditions as $column => $value) {
        $where[] = "$column = ?";
    }
    $sql = "DELETE FROM $table WHERE " . implode(" AND ", $where);
    
    $stmt = $pdo->prepare($sql);
    return $stmt->execute(array_values($conditions));
}

/**
 * Henter data fra en spesifisert tabell
 */
function fetchData($table, $conditions = null, $fields = null) {
    global $pdo;

    if (empty($table) || !is_string($table)) {
        throw new InvalidArgumentException("Tabellnavn må være en ikke-tom streng.");
    }

    $selectFields = $fields ? implode(", ", $fields) : '*';
    $sql = "SELECT $selectFields FROM $table";
    
    if ($conditions) {
        $where = [];
        foreach ($conditions as $column => $value) {
            $where[] = "$column = ?";
        }
        $sql .= " WHERE " . implode(" AND ", $where);
    }

    $stmt = $pdo->prepare($sql);
    $stmt->execute($conditions ? array_values($conditions) : []);
    
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}
?>

 

[gw1500se]

I guess the first question is why are you adding and deleting tables? Those should be stable for the most part and from that just adding/deleting rows. The implication is that there is something wrong with your schema. Please explain you thinking on this.

[Psycho]

I see what you are trying to do, and I can see why someone might want to do that, but in my opinion that is not a good idea for many reasons. Here are just a few:

1. Any application with a database should have some queries that rely upon JOINs. The above logic could not support JOINs without becoming overly complicated/burdensome. The fact that the code does not seem to rely on any JOINs would seem to indicate the DB structure and usage is not ideal.
2. The code relies upon too many assumptions which may not be appropriate for every table - then you are back to writing queries for those one off situations and are then left with a mix of different processes for interacting with the data
3. Using those functions means you are now interacting with the data in a non-standard way which can create problems for maintainability and readability.

A much better approach would be to create classes/objects to interact with the data. It will be more verbose, but the code for each "type" of data (not necessarily data in a single table) can be segregated into separate files for easy maintainability. Then, in your code, you just create an object and interact with the data that way.

[LeonLatex]

10 hours ago, gw1500se said:

I guess the first question is why are you adding and deleting tables? Those should be stable for the most part and from that just adding/deleting rows. The implication is that there is something wrong with your schema. Please explain you thinking on this.

Explanation to both of you who answered me:

The generic input:
The functions now accept data without me having to specify the column names in the code itself. I just pass in an associative array of column names and values (or it should be).

The dynamic handling:
As long as the inputs from the forms match the columns in the database table, I had a hope that this code would work. That I wouldn't have to edit the code every time I make changes to the forms or the database structure. That's what SHOULD be the point.

Intended use:
When I call insertData, deleteData, or fetchData, I just need to make sure that the data I'm passing in is in the correct format.
I hoped I could easily change the forms and database tables without having to go into the script and update the tables or columns vs.

10 hours ago, Psycho said:

I see what you are trying to do, and I can see why someone might want to do that, but in my opinion that is not a good idea for many reasons. Here are just a few:

1. Any application with a database should have some queries that rely upon JOINs. The above logic could not support JOINs without becoming overly complicated/burdensome. The fact that the code does not seem to rely on any JOINs would seem to indicate the DB structure and usage is not ideal.
2. The code relies upon too many assumptions which may not be appropriate for every table - then you are back to writing queries for those one off situations and are then left with a mix of different processes for interacting with the data
3. Using those functions means you are now interacting with the data in a non-standard way which can create problems for maintainability and readability.

A much better approach would be to create classes/objects to interact with the data. It will be more verbose, but the code for each "type" of data (not necessarily data in a single table) can be segregated into separate files for easy maintainability. Then, in your code, you just create an object and interact with the data that way.

Much of my answer to you, Psycho, is in my answer abow. But I will do my best to give you a better explanation.
The reason why I am including this within my DB connection script is that I want to gather all about DB handling in one file and simplify it.

Sorry if all my answers are a little blurry or answered in another way I should, but I am blurry too. I had my first of three surgeries last Wednesday, and I am still in a lot of pain and full of oxynorm (morphine). So, programming, reading my books, and lying in a hospital bed are all that I can do right now. Thank you all for your understanding. 🥴😵‍💫🤒🤕😵
Hospital with PHP Book

Edited March 12 by LeonLatex

[MeghaPatil]

I think you can fetch them dynamically using database queries like SHOW TABLES or DESCRIBE table-name. You can also use the query builder to the database action.

[Phi11W]

11 hours ago, LeonLatex said:

As long as the inputs from the forms match the columns in the database table, I had a hope that this code would work. That I wouldn't have to edit the code every time I make changes to the forms or the database structure. That's what SHOULD be the point.

So build your SQL based on the associative array provided and execute it. 
If the SQL generated doesn't match the structure of your table, then it will fail - and that's a Good Thing.  

I'm perfectly happy to have one foot in each "camp" - code and database - and, frankly, I'm a little bit wary of anyone who doesn't. 
Databases are the ultimate example of Persistence of State and there needs to be a stable relationship between your code and your database schema.  I'm sorry, but you need to understand that schema and, if it needs to change, you need to know what that change is going to impact. 
"Generic" tools, that sit somewhere between the code and the database and try to "do it all for you", do exist - but they can do some really, really stupid stuff if you don't watch them like a hawk. 

A Database will contain many, many Tables. 
The mechanism you've created, whilst appealing for single tables, precludes the possibility of using Joins between tables and relational databases just love joins. 

Regards, 
   Phill W. 

[mac_gyver]

are you asking this because something you have shown us doesn't work or for a critique of the method?

here's a list of points for the posted code -

1. i recommend naming the connection variables more uniquely, such as $db_host, $db_name, $db_user, $db_pass. you are going to require this code into a main file. depending on where it gets required at, you could have other $user and $pass variables that will conflict with this code.
2. there's no good reason to catch and handle a connection exception in your code and re-throwing the exception you just caught with only the message and code looses the file and line information, hindering debugging.
3. the only database exceptions you should catch and handle in your code are for user recoverable errors, such as when inserting/updating duplicate user submitted data. for all other query errors, all other type of queries, and for the connection, you should do noting in your code and let php catch and handle the database exceptions, where php will use its error related settings to control what happens with the raw database error information, via an uncaught exception error (database errors will 'automatically' get displayed/logged the same as php errors.)
4. don't use the global keyword to make your code 'work'. doing this breaks function scope, is not general purpose, and makes it harder if you ever need to use more than one database connection in a script. you should supply inputs to a function as call-time arguments.
5. query builders, like this, usually have a 'raw' query function/method to handle cases beyond what the code itself can handle.
6. i hope you are NOT planning on directly using the submitted form data to supply the column definitions to these functions, as this is not secure. your code must be where the defining array is defined at, so that YOU have control over what happens with the data and what columns and data gets used when dynamically building the queries. having the definition in your code also allows you to define labels, for use in building error messages and dynamically producing form fields and validation rules, for dynamically validating data on the server before using it.
7. because you are dynamically building these queries, you should surround the table and column names with back-ticks so that you don't need to worry about what the actual identifiers are at the point of building the query.
8. because you are using exceptions for errors, the conditional logic you have for the ->execute() call in the insertData() function will never see a false value and should be removed. one of the main points of exceptions is that your main code only 'sees' error free execution, since execution goes elsewhere upon an error. if execution continues past a statement that can throw an exception, you know there was no error without needing to test using conditional logic.
9. as already mentioned, you need the ability to test for a duplicate index error (number) from a query in the exception catch logic to handle the case of inserting/updating duplicate user submitted data.
10. for both an insert and an update query, they produce a PDOStatment rowCount() and a PDO lastInsertId() values (yes you can get a lastInsertId() value from an update query, which can be used to get an arbitrary value from the query, such as the id of the row that was updated or the initial or final value that was updated.) you should return an array with both of these values in it.
11. you need to consistently validate the $table and $data/$conditions inputs before using them.
12. the deleteData function() should return the rowCount() value, not the boolean value from the ->execute() call, which again due to using exceptions will always be a true value.
13. when you get to the point of writing the update function() you need to distinguish between the columns/values used in the SET ... part of the query and the columns/values used in the WHERE ... part of the query.
14. WHERE ... terms often involve more than just = (equal) comparisons and only ANDing multiple terms. there are LIKE, IN(), BETWEEN, greater/less, ... comparisons and combinations using AND/OR between terms.
15. insert, update, delete, and select queries can get prepared once, then executed multiple times with different input values. to handle this you can define the variable holding the PDOStatement object as a static variable inside the function, then only prepare the query if the PDOStatement object doesn't already exist. you need a way of destroying the PDOStatement object when you are done using it.
16. you have set the default fetch mode to assoc when you made the database connection, why are you also putting it in the fetch call?

Edited March 12 by mac_gyver