And yet, how is it “correct” to execute complex sql queries from PHP?

Question

PHP

About three years ago they asked me to write a simple PHP software for printing invoices. It just so happened that the logic of the DBMS (MSSQL), on which this software was based, was very monstrous - about eight hundred tables mixed up with mutual relations. Therefore, most requests included conditional calls to aggregate functions (the simplest is sum(when eq then a else 0 end)), PIVOT\UNPIVOT, and the like.

Taking into account my not too much experience at that time, I decided not to get involved in data mapping \ active record and simply threw queries into text files, made text placeholders like {PARAM1} in them and wrote a function that replaces the placeholder text with the values ​​of variables from the associative array.

A lot of time has passed since then - the softinka acquired an Ajax muzzle, migrated to my favorite CI, acquired two dozen modules containing all sorts of analytics. Only the method of calling SQL queries remained unchanged - an associative array, a text file, placeholders.

And, surprisingly, all this disgrace works to this day. True, the number of crutches and props is already starting to irritate a little.
Namely - manual escaping, type-checking, in the end - ctrl + space does not work. I won’t say anything about the fact that query debugging does not work normally due to “non-native” placeholders, and syntax check in PhpStorm goes crazy because of this. And along with him and I, slowly.

Doctrine turns a two-line call into something incredible on such complexity of queries, but I will simply tactfully keep silent about the size of the schema. Native placeholders (:PARAM) cannot contain query chunks. And we have auto-generation in all fields, custom filters, disconnected joins from external DBMS located 4-5 thousand km from our server room. Panic, pain, humiliation.

In short, a plan was born. We make a Query class with type-checking, returning results in XML (yes, there is such a thing) or in an array. Next, we make a bunch of heirs for each individual SQL query, which have a class with the listed input parameters in the public section. And then everything is the same - a text file and placeholders.

The other way around is either an array, or XML, or some class like ResultSet->First (next,last).

All this is painfully reminiscent of the invention of the bicycle. I suspect that all this has already been written before us. But a quick google didn't help. Can, who faced such things?

Answer 1

No need to overdo it, solve only one main problem of passing parameters - escaping and quoting parameters, no monstrous bicycles, just fix the method that executes your queries, let :param go to the sql query without changes (and make the query itself parameterized, the list of parameters is obtained simply by searching all occurrences of /\:(\w+)/ ), and {param} are replaced in the old fashioned way, let pieces of the query itself with expressions remain there.

Answer 2

I use DBAL from the doctrine.
It looks somehow poor quality, but I have not seen anything better.

returning results in XML

What for?

Answer 3

I probably didn’t understand something, but it seems to be talking about the functionality of stored procedures

create procedure BigBadQueryWithParams(@p1 int, @p2 nvarchar(1024))
as
begin
select * from MyTable where Field1 = @p1;
end;

mssql_init, mssql_bind parameters and mssql_execute...