Variable binding in aura/sqlquery when using mysqli_* connection

Question

I've got a legacy app that uses mysqli_*() functions (actually, it uses mysql_*() functions. Gah!). I am using aura/sqlquery as a SQL query generator. For example:

$queryFactory = new Aura\SqlQuery\QueryFactory('mysql');
$select = $queryFactory->newSelect();
$select->from('sometable AS t')
    ->where('t.field1 = 0')
    ->where("t.field2 <> ''");

Then we get the raw SQL by casting to string:

$sql = (string) $select;

Now I want to do do some variable binding in a where():

$select->where('t.somefield = ?', $somevalue);

When I cast to string, the escaping/binding never seems to be occur. It appears that the binding only takes place when one uses PDO and prepared statements.

Any ideas how to get variable binding in aura/sqlquery when using a mysqli connection?

Answer 1

You can use $select->getBindValues() to get the bind values.

I will say make use of Aura.Sql than pdo for it helps you in certain other cases like IN () query.

Taking an example from readme.

// a PDO connection
$pdo = new PDO(...);

// prepare the statment
$sth = $pdo->prepare($select->getStatement());

// bind the values and execute
$sth->execute($select->getBindValues());

Let me know in case you need more clarification for the same.

Thank you.

Answer 2

If your PHP version is >= 5.6, here is a function that you can use to run a query from aura/sqlquery against mysqli

function mysqli_query_params($mysqli, $query, $params, $types = NULL)
{
    $statement = $mysqli->prepare($select);
    $types = $types ?: str_repeat('s', count($params));
    $statement->bind_param($types, ...$params);
    $statement->execute();
    return $statement;
}

used like this

mysqli_query_params($mysqli, $select->getStatement(), $select->getBindValues())