Optimization of PHP functions for querys to the database

Making this kind of functionality work for me is reinventing the wheel.

I like to use DBAL Doctrine to work with database abstraction. In it I write the code at once only and it will run regardless of Database or Connection Driver (msqli, pdo).

If you are interested in documentation we can use this library. Here is an example of a more advanced query in the query builder:

<?php

$conn = DriverManager::getConnection(array(/*..*/));
$queryBuilder = $conn->createQueryBuilder();

$queryBuilder
    ->select('u.id', 'u.name', 'p.number')
    ->from('users', 'u')
    ->innerJoin('u', 'phonenumbers', 'p', 'u.id = p.user_id')

In addition to SELECT it also includes INSERT and UPDATE , with a syntax equal to SQL.

<?php

$queryBuilder
    ->insert('users')
    ->setValue('name', '?')
    ->setValue('password', '?')
    ->setParameter(0, $username)
    ->setParameter(1, $password)
;

// INSERT INTO users (name, password) VALUES (?, ?)

$queryBuilder
    ->update('users', 'u')
    ->set('u.logins', 'u.logins + 1')
    ->set('u.last_login', '?')
    ->setParameter(0, $userInputLastLogin)
;

// UPDATE users SET u.logins = u.logins + 1, u.last_login = ?

The implementation of all QueryBuilder methods you can find in GitHub .

There is no problem in that, implementation depends greatly on each one, resulting in a call within the PDO rules.

Of course, it depends on the project too, and for each one of us we will certainly have different approaches. I prefer almost always ... ok ... always ... using OOP / classes and objects and when implementing any template it is essential to use them nowadays. Also important is the semantics used.

Once again, not being the best form for the reasons I explained, I will start responding within the question template to make you think differently, so in your specific case of the update_tables would do the following:

function update_table($table, $collum, $id, $value) {

  $db = conn();
  $data = array(':value' => $value, ':id' => $id,);

  $sql = ("UPDATE {$table} SET {$collum} = :value WHERE 'id' = :id");
  try {
       $st = $db->prepare($sql);
       $st->execute($data);
       return true; 
    } catch (PDOException $e) {
       return false;
    }

}

Implementations may vary, but what I am going to do here is to explain the following:

It makes sense to use functions in order to simplify our life as programmers, but when we do it we have to do it in a more comprehensive way so we can "almost" text that tells us everything when we read our code.

Context, naming, validating possible errors, and returning the result are important.

By ending, say that in relation to the code that is built for use throughout a project the documentation is essential .

I do not use PDO, I use mysqli_ but in any case there is an optimization of a generic insert or update function:

/**
 * Insere dados numa tabela.
 * 
 * @param string $table nome da tabela
 * @param array  $data field=>value
 * @param array  $format %i,%d,%s,%b (int, double, string, blob)
 * @return boolean retorna verdadeiro se inseriu, senão falso.
 * 
 */
function insert( $table, $data, $format )
{

    $conn = connect();

    if ( empty( $table ) || empty( $data ) )
    {
        return false;
    }

    $data   = (array) $data;
    $format = (array) $format;

    $format = implode( '', $format );
    $format = str_replace( '%', '', $format );

    list( $fields, $placeholders, $values ) = prep_query( $data );

    array_unshift( $values, $format );

    $sql_insert = "INSERT INTO {$table} ({$fields}) VALUES ({$placeholders}) ON DUPLICATE KEY UPDATE ";

    $first = 1;
    foreach ( $data as $field => $item )
    {   

        if( $first != 1 ) $sql_insert.=", ";
        $sql_insert.= "$field=VALUES($field)";
        $first = 0;
    }

    $stmt = $conn->prepare( $sql_insert );

    call_user_func_array( array($stmt, 'bind_param'), ref_values( $values ) );


    $stmt->execute();

    if ( $stmt->affected_rows || $stmt->insert_id )
    {
        return true;
    }

    return false;
}

/**
 * Pepara os campos de dados e respetivos valores.
 * 
 * @param  $data
 * @return array
 */
function prep_query( $data )
{
    $fields       = '';
    $placeholders = '';
    $values       = array();

    foreach ( $data as $field => $value )
    {
        $fields  .= "{$field},";
        $values[] = $value;

        $placeholders .= '?,';
    }


    $fields       = substr( $fields, 0, -1 );
    $placeholders = substr( $placeholders, 0, -1 );
    return array($fields, $placeholders, $values);
}


/**
 * Referencia os valores para que estes possam ser alterados.
 * 
 * @param  $array
 * @return array
 */
function ref_values( $array )
{
    $refs = array();

    foreach ( $array as $key => $value )
    {
        $refs[$key] = &$array[$key];
    }

    return $refs;
}

Here is an example of usage:

/*********************** exemplo de inserção de um user ************************

    $data   = 
            array(
                'id'        => NULL,
                'name'      => "JorgeB.",          
                'password'  => "1234"
            );

    $format = array('i','s','s');

    $table  = "users";

    insert($table, $data, $format);
*******************************************************************************/