How to get SQL with values embedded instead of paramaterialised SQL?

[srth12]

How to get a values like this:

INSERT INTO user (user.user_id, user.name, user.contact) VALUES ('ab', 'cd', 'ef)

Instead of

INSERT INTO user (user.user_id, user.name, user.contact) VALUES (:v1, :v2, :v3)

I tried various SQL write() but nothing helped.

$sql = $builder->write($query);

[KarsjenKoop]

I am having the same issue, is there a flexible, foolproof way to have the variables written with the query?

[mrAndersen]

+1 on this

[webermar]

For the next poor guy forced to use this far from elegant library, here a solution to print the query with placeholders replaced with their values:

protected const SQL_QUERY_SEPARATOR = ";";
protected const SQL_VALUE_QUOTE = "'";
 /**
  * Poor man's version of binding parameter values to sql parameters.
  * Example:
  * Query: "INSERT INTO test (test.col1, test.col2, test.col3) VALUES (:v1, :v2, :v3)"
  * Params: [":v1" => "abc", ":v2" => 123, ":v3" => "1"]
  * Result: "INSERT INTO test (test.col1, test.col2, test.col3) VALUES (abc, 123, 1)"
  *
  * @param string $sql_query SQL query string containing parameters.
  * @param array $params Array containing parameters as keys for (stringable) values to be replaced with.
  * @return string SQL query with parameters replaced by their value from $params.
  */
function bindParams(string $sql_query, array $params, bool $quoteStrings = true): string {
    /* IMPORTANT: We need to replace numbered placeholders backwards (from highest to lowest)!
     * That's what the array_reverse() is for.
     * If we didn't do this, str_replace() would corrupt placeholders who share the first letters.
     * Example: Placeholder ":v1" and ":v12" -> str_replace() would replace :v1 with its designated value and the first part of :v12->":v1"2, leaving the 2.
     */
    $placeholders = array_reverse(array_keys($params));
    $placeholderVals = array_reverse(array_values($params));
    if ($quoteStrings) {
        $placeholderVals = array_map(
        // quote strings (only), let all other values pass through
            function (null|string|float|int|bool $value) {
                if (is_string($value)
                    && !str_starts_with($value, self::SQL_VALUE_QUOTE)
                    && !str_ends_with($value, self::SQL_VALUE_QUOTE)) {
                    return self::SQL_VALUE_QUOTE . $value . self::SQL_VALUE_QUOTE;
                }
                return $value;
            },
            $placeholderVals);
    }
    return str_replace($placeholders, $placeholderVals, $sql_query);
}

function bindQueryParams(AbstractBaseQuery $query): string {
    // IMPORTANT: $query->getBuilder()->getValues() is completely different from $query->getValues()!!
    return self::bindParams($query->getSql(formatted: false), $query->getBuilder()->getValues());
}

/**
 * Return query as full sql query string.
 *
 * @param AbstractBaseQuery $query
 * @param bool $quoteValues
 * @return string
 */
function prepareStatement(AbstractBaseQuery $query, bool $quoteValues = true): string {
    $sql = self::bindQueryParams($query);
    if (!str_ends_with($sql, self::SQL_QUERY_SEPARATOR)) {
        $sql .= self::SQL_QUERY_SEPARATOR;
    }
    return $sql;
}