Due to working on a test for a new potential role, I’ve been made to take a slightly deeper look into using database calls with mysqli (the MySQL Improved extension).

I admit that since acquiring an official fave object-relational-mapping tool for PHP - Redbean - I have stopped caring too much about lower level differences in PHP database abstractions libraries/extensions because Redbean like many others just uses PDO. I hadn’t, for instance, realised that parameter binding is perfectly possible with PDO and has been for quite some time.

When I first saw mysqli in action, this feature was apparently still quite new in PDO, which explains why I might have been persuaded that it wasn’t supported at all. Since then it has stayed with me that, for this reason, mysqli is more secure (from SQL injection attacks) but now I realise the only difference seems to be that it can be slightly more performant than PDO as long as fully hard coding an application to just use MySQL as a database dependency is a-ok with you.

So I’ve ended up looking at mysqli prepared statements, keen to lay some groundwork, in order to have some code that I could potentially use as a proof of concept for showing that mysqli can outperform a very lightweight PDO active-record style and extreme legacy code ORM of mine. Of which I feel awkwardly proud to say hasn’t changed much in almost ten years. Probably because even though crude, it has been at the heart of many projects, pretty much unchanged and therefore a beacon of my understanding of backwards compatibly issues that have affected the surrounding code it lives in.

As soon as I got into parameter binding, I naturally wanted to create a little wrapper function for my querying (around mysqli procedural calls) and this led to the use of PHPs "call_user_func" callback method,  so that I could send arbitrary length parameter lists. The documentation warns of potential difficulties with parameter binding as these are expected to be in the form of references. I didn’t realise at first how much of a due warning was necessary. It turned out it was pretty justified. There is quite a lot of hits out there on the subject of references in php and particularly with foreach loops. i.e. when creating (and reusing) a loop like this:

foreach ($data as &$item) {
    // some stuff
}

Before I got to the conclusion provided by momslatin_dadsasian on the ‘lolphp’ subreddit, which I am now newly acquainted with:

My general rule with foreach and references is: fuck php and use something else

I persevered believing that what I was trying to achieve MUST be possible. I won’t try and explain the deal with references and foreach loops but in a nutshell it seems these references both exist outside of the foreach loop and also don’t exist at the same time. Or another way of looking at it seems to be a reference will exist but only if you attempt to observe it and even then it will be in a slightly different place.

Suffice to say I didn’t feel like learning quantum theory in order to execute a pretty straightforward technical test for a part time web development job. So I quickly hacked together a different approach. So instead of this:

$refs = array();
foreach ($foo as $bar){ 
    $refs[] =& $bar;
}

I tried this and I was rewarded with the expected behaviour.

$refs = array();
foreach ($foo as $index => $bar) {
    $refs[] =& $foo[$index];
}