PHP MySQL

Connection

The easiest way to do this is to create a file and save it as connection.php. Your connection file should have the following information inside it:

<?php
 $link = mysqli_connect('localhost', 'my_user', 'my_password', 'my_db');
?>

Obviously, fill in your own MySQL user, password and database name! Then, whenever you need to connect to the database in a program, use the line:

<?php require_once("connection.php"); ?>

This will grab the contents of the connection.php file and insert it into the page so that you can use the $link variable as the connection you’ve already set up.

Inserting Data

Assuming you have already included your connection file, this is the basic format for a query

// Create the query
$query = "INSERT INTO tablename(field1, field2, field3) VALUES ('value1', 'value2', 'value3')";

// Execute the query
$result = mysqli_query($link, $query);

You need to put in your own table name, field names and values. The values should be in the same order as the field names, i.e. value1 will go into field1 etc.

This method can also be used to delete, update etc with the appropriate SQL.

Selecting Data

Here is a general example of how to write a SELECT query to get data from your database:

// Write the query
$query = "SELECT fieldname FROM tablename";

// Execute the query
$result = mysqli_query($link, $query);

// If anything was found, get the results
while($data = mysqli_fetch_assoc($result)){
   print $data['fieldname'];
}

In this example, you specify which fields you would like from which table, when you write the query. In this example, the results are put one by one into an array called $data:

$data = mysqli_fetch_assoc($result);

You can then reference each field by its name e.g. if you had a field called username you could use:

print $data['username'];

if you had a field called dateofbirth you could use:

print $data['dateofbirth'];

Limiting Data

MySQL provides a LIMIT clause that is used to specify the number of records to return. The LIMIT clause makes it easy to code multi page results or pagination with SQL, and is very useful on large tables. Returning a large number of records can impact on performance.

Assume we wish to select all records from 1 - 30 (inclusive) from a table called "Orders". The SQL query would then look like this:

$sql = "SELECT * FROM Orders LIMIT 30";

When the SQL query above is run, it will return the first 30 records. What if we want to select records 16 - 25 (inclusive)? Mysql also provides a way to handle this: by using OFFSET. The SQL query below says "return only 10 records, start on record 16 (OFFSET 15)":

$sql = "SELECT * FROM Orders LIMIT 10 OFFSET 15";

You could also use a shorter syntax to achieve the same result:

$sql = "SELECT * FROM Orders LIMIT 15, 10";

Notice that the numbers are reversed when you use a comma.