Optimization of PHP functions for database querys

Question

I often see different functions in PHP for each query. I wonder if there is any other way to do the following, in a connection with PDO:

function get_user_data($table, $columm, $required) {

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

  $sql = ("SELECT * FROM {$table} WHERE {$columm} = :value");
  $result = $db->prepare($sql);
  $result->execute($data);

  $result->rowCount();
  return $result->fetchAll(PDO::FETCH_ASSOC);

}

This way we only have to make a PHP function to query by changing only the arguments as required.

As such (there being but this design) we could even go further?

function update_tables($table, $collum1, $value, $collum2, $id) {

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

  $sql = ("UPDATE {$table} SET {$collum1} = :value WHERE {$collum2} = :id");
  $result = $db->prepare($sql);
  $result->execute($data);

}

Answer 1

There is no problem in what indicates, the implementation depends a lot on each one being that results in a call at the end within the rules of PDO.

Of course, it also depends a lot on the project, and for each one we will certainly have different approaches. I prefer almost always... ok... always... using OOP/classes and objects and when we implement any model is essential its use nowadays. The semantics used is also important.

Again, not being the best way for the reasons I explained, I will now respond within the model of the question in order to make you think differently, like this and in your specific case of the function 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;
    }

}

The implementations may vary, but what I present here is in the sense of explaining the following:

1. the name of the method update_table may be another because it is important to reduce writing whenever possible but make it logical within the context. It will always be a TABLE UPDATE in this case.

2. Changing $id is also important as the concept has table orders > column then will come identifier, etc.

3. Adding some added value to the function is also important. Know, for example, if you have updated well or if you have even updated... for this purpose just return true or false. By encapsulating the possible errors that may happen we are also avoiding many Try and catch throughout the project.

4. In an UPDATE it is normal to try to understand if we have updated one or more items but this depends on the logic required.

It makes sense to use functions in order to simplify our life as programmers, but when we do it we have to do it as comprehensively as possible in order to get "almost" a text that tells us everything when we read our code.

It is important the context, the assignment of names, the validation of possible errors and the return of the result.

In conclusion, to say that in relation to the code that is built for use during a documentation is essential.

Answer 2

I don’t use PDO, use mysqli_ but anyway it is an optimization of a generic insertion 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 use:

/*********************** 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);
*******************************************************************************/

Answer 3

To do this kind of functionality at hand for me is to reinvent the wheel.

I like to use the Doctrine DBAL to work with database abstraction. In it I write the code at once and it will run independently of the Database or the 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')

Beyond the SELECT he also included 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 on Github.