Load and execute SQL files with PHP

How to load a SQL query from a file instead of having an inline query. This is done to help with formatting and easier understanding of the code.

All that is needed is the simple file_get_contents()  in the place of the query string.

fetch_all_colors.sql in the sql/ directory

SELECT `color` FROM `objects` ORDER BY `color` DESC;

PHP file running the PDO MySQL query:

$db = new PDO("mysql:host=127.0.0.1;dbname=test;charset=utf8mb4", 'root', '');

$select = $db->query(file_get_contents('sql/fetch_all_colors.sql'));
while ($row = $select->fetch(PDO::FETCH_ASSOC)) {
    echo $row['color'].'<br>';
}

This loads the fetch_all_colors.sql and executes it.

Prevent index and viewing of the SQL files

By using .htaccess and deny from all on SQL only files this prevents the viewing and indexing for the SQL files.

<Files ~ "\.sql$">
    Order allow,deny
    Deny from all
</Files>

Better use case:

The example uses a small SQL query were putting it inline would make perfect sense. Loading the SQL query from a file would be more beneficial for large join queries like this query taken from here:

SELECT 
  country.country_name_eng,
  SUM(CASE WHEN call.id IS NOT NULL THEN 1 ELSE 0 END) AS calls,
  AVG(ISNULL(DATEDIFF(SECOND, call.start_time, call.end_time),0)) AS avg_difference
FROM country 
LEFT JOIN city ON city.country_id = country.id
LEFT JOIN customer ON city.id = customer.city_id
LEFT JOIN call ON call.customer_id = customer.id
GROUP BY 
  country.id,
  country.country_name_eng
HAVING AVG(ISNULL(DATEDIFF(SECOND, call.start_time, call.end_time),0)) > (SELECT AVG(DATEDIFF(SECOND, call.start_time, call.end_time)) FROM call)
ORDER BY calls DESC, country.id ASC;