Why I love PhpStorm: SQL GROUP BY handling

Here’s another post from my series why I love JetBrains PhpStorm. This is a SQL query, where I have I list of column names. The IDE not only marks the columns missing in the GROUP BY clause as errors, it also offers an option to fix it for you by adding them to the GROUP […]

Streaming large data (LOBs) directly into an Oracle database with PHP

The OCI library allows you to stream large data in chunks directly into a LOB instead of loading it completely into memory first. This technique is only mentioned in the article Working with LOBs in Oracle and PHP on the Oracle Technology Network, but no code example is given there, so here’s my take: $sql […]

SQL tip: Update add instead of replace

A simple way to add to a database record instead of replacing it with Oracle: UPDATE myTable SET textField = :text||textField WHERE … This comes in handy, when you have a form field, which can be empty, but should not overwrite an existing record when posted (This should also be easily adapted to other SQL […]

PHP Tip: Binding variables in a SQL WHERE IN clause

Have you ever wondered if it is possible to bind an unknown amount of variables in a SQL WHERE IN clause, e.g.: SELECT id, text FROM myTable WHERE id IN (:myId1, :myId2, myId3, …) Use a OCI collection object and wonder no more: $keyList = array(100, 250, 350); $stmt = oci_parse($cnn, “SELECT id, text FROM […]

SQL tip: How to bind NULL with PHP and Oracle

Neat little trick to bind a null value without using an if clause. How often have you done something like this when a variable might be null: $sql = “SELECT * FROM someTable WHERE col1 = :firstVar”; if (isset($secondVar)) { $sql.= ” AND col2 = :secondVar”; } … oci_bind_by_name($stmt, ‘:firstVar’, $firstVar); if (isset($secondVar)) { oci_bind_by_name($stmt, […]