MySQL query to find country and state of active users

Is there any reason NOT to wrap a long query over multiple line via new lines vs using concatenation?

For example, consider this:

$this->query("SELECT u.*, COALESCE(c2.name,u.state) AS real_state, c.name AS real_country, bc.alpha3 AS country_iso, bs.code AS state_iso
                    FROM " . MAIN_DB . ".users AS u
                    INNER JOIN " . MAIN_DB . ".countries AS c ON u.country=c.id 
                    LEFT JOIN " . MAIN_DB . ".countries AS c2 ON u.state=c2.id 
                    LEFT JOIN " . BLESTA_DB . ".countries AS bc ON c.country_iso_code=bc.alpha2
                    LEFT JOIN " . BLESTA_DB . ".states AS bs ON bc.alpha2=bs.country_alpha2 AND COALESCE(c2.name,u.state)=bs.name
                    WHERE u.active=1 AND u.mail_activated=1 AND u.blesta_id=0");

VS

$this->query("SELECT u.*, COALESCE(c2.name,u.state) AS real_state, c.name AS real_country, bc.alpha3 AS country_iso, bs.code AS state_iso " . 
                    "FROM " . MAIN_DB . ".users AS u " . 
                    "INNER JOIN " . MAIN_DB . ".countries AS c ON u.country=c.id " . 
                    "LEFT JOIN " . MAIN_DB . ".countries AS c2 ON u.state=c2.id " . 
                    "LEFT JOIN " . BLESTA_DB . ".countries AS bc ON c.country_iso_code=bc.alpha2 " . 
                    "LEFT JOIN " . BLESTA_DB . ".states AS bs ON bc.alpha2=bs.country_alpha2 AND COALESCE(c2.name,u.state)=bs.name " . 
                    "WHERE u.active=1 AND u.mail_activated=1 AND u.blesta_id=0");

Are there any possible problems with the first code or is either fine?

The first way is less error-prone. When you write like this:

"FROM " . MAIN_DB . ".users AS u " . 
"INNER JOIN " . MAIN_DB . ".countries AS c ON u.country=c.id " .

... it's easy to "forget" to add a trailing space on the previous line, which is necessary to separate the clauses of the statement.

This might be slightly better, though it might look somewhat awkward:

" FROM " . MAIN_DB . ".users AS u " . 
" INNER JOIN " . MAIN_DB . ".countries AS c ON u.country=c.id " .

Here, it's easier to see the space before the expressions, as they are all aligned to the left, your eyes don't have to scan the right ends to see if you put a space there.

With your first version, this problem doesn't exist: the embedded newlines ensure that the clauses are separated, you cannot accidentally forget whitespace in between, it's naturally there. Another advantage of the first version is that it's easier to copy and paste to a query runner program.

Finally, when embedding variables in long strings, I find the interruptions caused by concatenation quite annoying, so I prefer to use formatting with a template, like this:

$query = sprintf(
    "SELECT u.*, COALESCE(c2.name,u.state) AS real_state, c.name AS real_country, bc.alpha3 AS country_iso, bs.code AS state_iso 
    FROM %s.users AS u 
    INNER JOIN %s.countries AS c ON u.country=c.id 
    LEFT JOIN %s.countries AS c2 ON u.state=c2.id 
    LEFT JOIN %s.countries AS bc ON c.country_iso_code=bc.alpha2 
    LEFT JOIN %s.states AS bs ON bc.alpha2=bs.country_alpha2 AND COALESCE(c2.name,u.state)=bs.name 
    WHERE u.active=1 AND u.mail_activated=1 AND u.blesta_id=0",
    MAIN_DB, MAIN_DB, MAIN_DB, BLESTA_DB, BLESTA_DB);

The drawback of this approach is that now the table names are not so easily visible in their contexts, which can invite mistakes in the argument list.

A helper function from this post on Stack Overflow could improve that:

function vsprintf_named($format, $args) {
    $names = preg_match_all('/%\((.*?)\)/', $format, $matches, PREG_SET_ORDER);

    $values = array();
    foreach($matches as $match) {
        $values[] = $args[$match[1]];
    }

    $format = preg_replace('/%\((.*?)\)/', '%', $format);
    return vsprintf($format, $values);
}

Like this:

$query = vsprintf_named(
    "SELECT u.*, COALESCE(c2.name,u.state) AS real_state, c.name AS real_country, bc.alpha3 AS country_iso, bs.code AS state_iso 
    FROM %(main).users AS u 
    INNER JOIN %(main).countries AS c ON u.country=c.id 
    LEFT JOIN %(main).countries AS c2 ON u.state=c2.id 
    LEFT JOIN %(blesta).countries AS bc ON c.country_iso_code=bc.alpha2 
    LEFT JOIN %(blesta).states AS bs ON bc.alpha2=bs.country_alpha2 AND COALESCE(c2.name,u.state)=bs.name 
    WHERE u.active=1 AND u.mail_activated=1 AND u.blesta_id=0",
    array('main' => MAIN_DB, 'blesta' => BLESTA_DB));

StackExchange Code Review Q#62812, answer score: 5