Put all SQL Queries in one PHP file

Question

I am developing a PHP/Mysql site.

The site consists of static html templates. Data is dynamically populated into these templates with php require_once

For example this is simplified version of the Home page

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Home</title>
</head>
<body>
<?php require_once $_SERVER['DOCUMENT_ROOT']."/includes/header.php"; ?>
<?php require_once $_SERVER['DOCUMENT_ROOT']."/includes/connection.php"; ?>
<?php require_once $_SERVER['DOCUMENT_ROOT']."/includes/sql.php"; ?>
<?php require_once $_SERVER['DOCUMENT_ROOT']."/includes/content.php"; ?>
<?php require_once $_SERVER['DOCUMENT_ROOT']."/includes/footer.php"; ?>
</body

The Header and Footer are themselves static so are included as is. However to display the right content I establish a connection(connection.php) to the database, query it(sql.php) and then echo it(content.php).

I repeat this for all other pages on the site each page referencing a modified sql.php.

All this works, But I know it's not very efficient.

My question is how can I restructure my files/code so that hopefully I will end-up with one file that contains all my sql queries and somehow how "choose" the right query to execute depending on which page that requested it.

Thanks for your help.

Looks like you could use the [front controller pattern](http://stackoverflow.com/questions/6890200/what-is-a-front-controller-and-how-is-it-implemented-in-php). — bfavaretto, Aug 08 '11 at 19:02
Even though this approach might get you results here and now, managing your code this way will make your code very hard to maintain in the long run. I know it's a large task, but I highly recommend that you learn the basics of [Model-View-Controller](http://oreilly.com/php/archive/mvc-intro.html) and [make your own](http://anantgarg.com/2009/03/13/write-your-own-php-mvc-framework-part-1/) or use an existing. [Codeigniter](http://codeigniter.com/) is great for beginners. — K4emic, Aug 08 '11 at 19:10

score 0 · Accepted Answer · answered Aug 08 '11 at 18:56

0

Use functions in your files. So you can easely call functions from differend files like

$something->this_query();

http://php.net/manual/en/language.functions.php

answered Aug 08 '11 at 18:56

Dzoki

739
4
14

score 0 · Answer 2 · answered Aug 08 '11 at 18:58

If you need to have everything in one file, you could have a 2D array of queries and then only execute the queries for the desired page:

$queries = array(
    'home_page' => array( /* insert queries here */ ),
    'contact_page' => array( /* insert queries here */ ),
    'page_Y' => array( /* insert queries here */ ),
    'i_like_turtles' => array( /* insert queries here */ ),
);

Then on page_Y you would simply do foreach($queries['page_Y'] as $query) { /* Execute query */ }

However if you are looking for more efficiency, the best way would be to list the queries on each separate page. If you have thousands of queries it would probably be worth looking into. If not, the benefit will not be significant.

Put all SQL Queries in one PHP file

2 Answers2