ColdFusion to PHP: Can I make queries as easily?

Question

I'm working for a large company that has a ton of ColdFusion web applications. They're requiring all these apps to transition to new platforms/languages.

One of these applications is quite large and complex. Its target is PHP due to resource familiarity and support.

One of the nice things I liked about ColdFusion was the ability to sort of "free-form" a SQL query between <cfquery> tags. I'm wondering if there's a way to do that in PHP.

Here's an example of a query that I might have embedded in a ColdFusion component (CFC). What the query does isn't as important as how all parts of the query between cfquery tags are conditionally built:

<cfquery>
    select nametable.uid, firstname, lastname

    <cfif isdefined('alldata')>
        ,phone
        ,email
    </cfif>

    from
        nametable

    <cfif isdefined('alldata')>
        inner join contactdata
        on nametable.uid=contactdata.uid
    </cfif>

    where 1=1

    <cfif isdefined('firstname')>
        and firstname like <cfqueryparam cfsqltype="cf_sql_varchar" value="#firstname#">
    </cfif>

    <cfif isdefined('lastname')>
        and firstname like <cfqueryparam cfsqltype="cf_sql_varchar" value="#lastname#">
    </cfif>

    <cfif isdefined('uidlist')>
        and nametable.uid in <cfqueryparam cfsqltype="cf_sql_varchar" value="#uidlist#" list="true">
    </cfif>

    <cfif searchByDate>
        and cast(datafield as date)
    <cfelse>
        and datafield
    </cfif>

    between
        <cfqueryparam cfsqltype="cf_sql_varchar" value="#attrValue1#">
    and
        <cfqueryparam cfsqltype="cf_sql_varchar" value="#attrValue2#">

    <cfif isdefined("attrVals")>
        <cfloop from="1" to="#attrVals.length()#" index="i">
            and dbo.getPersonAttr(nametable.uid,'#attrvals[i].cd()#') = 
            <cfqueryparam cfsqltype="cf_sql_varchar" value="#attrvals[i].val()#">
        </cfloop>
    </cfif>

    order by

    <cfif orderby eq "uid">nametable.uid<cfelse>lastname</cfif>

</cfquery>

To me, even though there is a variety of logical paths, that code is:

• very easy to read,
• simple to understand, and
• easy to debug syntactically

Several other features that this dynamically built query has:

• inline if/else logic and looping - across all SQL clauses (Select, joins, where, order by, group, etc.)
• inline, variable data binding: there might be 0 bound parameters, or 50+; we won't know or care until runtime
• native data binding for list values (...where uid in ('12345','98765')...) (in coldfusion, <cfqueryparam ... list="true">)

I don't think I need a lesson on just how you can build a PHP query; I know you can sit there and rather tediously concatenate small strings together to accomplish something similar.

What I'd like to ask is:

1. Is there a way to construct queries in PHP similar to the way they're constructed in ColdFusion, by using the <cfquery></cfquery> tags as the boundaries of the buffer, with clean logic and syntax in between, and
2. Regardless of the answer to #1, is there any straightforward way in PHP to perform data binding when the number of bound parameters varies, is not known until runtime, and the number of which might be determined with multi-variable logic? To do it in the above example is trivial - you simply use <cfqueryparam> where and when you need it.

Because we don't have the time or resources to re-architect the entire application, I'm not looking, for example, to map all our objects via ORM. I really just want to construct queries.

Thanks in advance.

Answer 1

The closest you can get is using a query builder in PHP. This would look like this (incomplete example):

$columns = [ 'nametable.uid', 'firstname', 'lastname' ];

if isset($alldata) {
    array_push($columns, 'phone', 'email');
}

$builder = new GenericBuilder(); 

$query = $builder
    ->select()
    ->setTable('nametable')
    ->setColumns($columns)
;

if isset($firstname) {
    $query
        ->where()
        ->like('firstname', $firstname)
}

etc.

It will take some time to get used to it, but I consider this more readable than the mentioned string concatenation hell.