How to use the built-in MySQL functions such as NOW?

[kyzima-spb]

Example:

$query = $builder
    ->insert('post', [
        'title' => 'Пост №2',
        'description' => '',
        'content' => 'Содержание второй записи',
        'created' => 'NOW()',
        'updated' => 'NOW()',
    ])
;

PDO doesn't support the NOW() as the parameter value

[joseph-montanez]

This does not seem to be supported. Look at https://github.com/nilportugues/php-sql-query-builder/blob/master/src/Builder/Syntax/PlaceholderWriter.php#L55 this is where it reviews the values and decides what to do with it. The problem here is that is that because it doesn't know the context of of query it cannot do a replacement at that level. A rather large adjustment would need to happen and overall I would think falls outside of the library. That or a custom insert writer would need to be made to support raw SQL values for insert, here is the the insert writer line that could be changed: https://github.com/nilportugues/php-sql-query-builder/blob/877d54faf8a9196e9ee4967488a9e85da9168e40/src/Builder/Syntax/InsertWriter.php#L58

A work around you'd do is something like this:

    require_once __DIR__ . '/vendor/autoload.php';
    
    use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;
    
    $builder = new GenericBuilder();
    
    $query = $builder
        ->insert('post', [
            'title' => 'Пост №2',
            'description' => '',
            'content' => 'Содержание второй записи',
            'created' => ['NOW()'],
            'updated' => ['NOW()'],
        ])
    ;
    
    $query_string = $builder->write($query);
    $query_values = $builder->getValues();
    
    $result = ['query' => $query_string, 'values' => []];
    array_walk($query_values, function ($item, $key) use (&$result) {
        if (is_array($item)) {
            $result['query'] = str_replace($key, current($item), $result['query']);
        } else {
            $result['values'][] = $item;
        }
    });
    
    print_r($result);

The output will then be

Array
(
    [query] => INSERT INTO post (post.title, post.content, post.created, post.updated) VALUES (:v1, :v2, NOW(), NOW())
    [values] => Array
        (
            [0] => Пост №2
            [1] => Содержание второй записи
        )

)

The idea here is that it expect the values to be a none generalized value like a string or integer. So in the example I provided I just wrap the value in an array and detect off that, and use str_replace to replace the value from the string. Now of course this is less idea than the query builder handling it but if you want to support future versions this may be okay.