Database Connections Outside of Webroot

For extra security many often store their database connection credentials outside of the web root. This is fairly easy to achieve without to much work.

For those of you who are unsure how to store database connection credentials in a separate file please refer to my article on Connecting to MySQL with PHP

Once you have your connections in a php file (this is also relevant to other languages such as asp etc) you need to store it somewhere on your server outside of the web root. In this example we will call the file MyCat.php.

Linux Systems

Put this file outside of the web root eg in /home/myuser/ the web root for example (where all your web files are stored) may well be /home/myuser/htdocs/

Now to include this file into your script use the following:-

include("/home/myuser/MyCat.php");

This will call the file from outside of the webroot.

Windows Systems

This is very similar to above. Place the MyCat.php file in the root of the C drive in a directory called MyInclude eh C:MyInclude

Now to include this file into your script use the following:-

include("C:MyIncludeMyCat.php");

Getting The Last Auto Increment Field With PHP

On many occasions you need to find the value of the last auto increment field inserted into a MySQL table. For example within my Content Management System if someone creates a new web page and saves it the user is taken back to edit this new page. In order to do this I must retrieve the value of the last auto increment field inserted into the database.

To do this I use the php mysql_insert_id(); function.

You can add this value to a variable by using the following code:-

$MyValue = mysql_insert_id();

So with this variable you could select an item from a database eg:

$MyQuery = "SELECT * FROM MyTable WHERE UniqueField = $MyValue";

Of course you should always check that $MyValue is numeric for security so we don’t break the query. For this we could use the php intval function.

intval($MyValue);

Resetting MySQL Auto Increment Field

After developing a number of systems in the past it is of course essential to completely test any piece of software. This of course means that any tables that use an auto_increment field will not start from 1 when the system goes live. Not a massive problem but it is nice sometimes to reset the value to 1.

After searching around the web what seemed like an age I finally found the SQL that carry s out just the job.

Enter the following statement into your MySQL database replacing tablename with the name of your table and the value x to the number you want the AUTO_INCREMENT value to start from:

ALTER TABLE tablename AUTO_INCREMENT = x

There you go the AUTO_INCREMENT field in this case will now be reset to 1

Connecting to MySQL with PHP

Connecting to a mysql database is pretty easy using php as it gives you all the commands you need in order to do it. This example is a basic way to do, there are other methods but I found this way useful when I was just starting out!

Here is an example of connecting and quering a mysql database


I usually store my connection attributes in another file and use a php include to call them. For added security I store my database connection details outside the web root.

The Include File

Create a new php file and call it something obscure as this, I always do this from habit, I never call my include file DatabaseConnect.php for example, if someone was browsing it would give the game away!

For now create the file MyCat.php and save it outside of your document root, for example in windows you could store it in c: and on a linux system in /home/me/ for example.

The MyCat.php file



Now when connecting and querying a database you would include the above file



This is now easier to manage as well, if you have multiple scripts that connect to a database then there is no need to keep adding your database connection information to the start of each script, you just have the one line to include it!