1

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?

David Weinraub
  • 14,144
  • 4
  • 42
  • 64
  • 1
    I suppose that a query builder is a requirement and you don't want a solution that works with raw SQL? – Your Common Sense May 25 '16 at 04:43
  • @YourCommonSense I prefer to use a query-builder to deal with complex joins. For example, if a User model has several methods which need several joins, then it's easy to perform those common joins in a single helper method and then pass around the `$select` object. Managing raw SQL text feels cumbersome to me. – David Weinraub May 25 '16 at 04:54
  • Yes, I see. But still. Your requirement is not clear to me. You need to get a raw SQL only, to be used with mysql_ functions and that's the only option? – Your Common Sense May 25 '16 at 05:14
  • Yep, unless I convert to mysqli (which I should probably do anyway) and use `$mysqli->prepare()`, as you suggest (providing that the PHP ver is high enough, which really *is* out of my immediate control). – David Weinraub May 25 '16 at 06:08

2 Answers2

1

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())
Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
  • 1
    I think `David Weinraub` this is the answer you may need for you are looking to make use of mysqli connection and not pdo. – Hari K T May 25 '16 at 04:55
0

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.

Hari K T
  • 4,174
  • 3
  • 32
  • 51