Using MySQL with PHP

Today I shall discuss a subject that took me ages to get my head around. It took me an incredibly long time to understand how to access SQL data within PHP.

Hence why I wish to make it easier for anyone else who is struggling to understand it.

To begin with you need a MySQL database (and a table to help speed things up). This post assumes you know how to do this for brevity’s sake. Now you need to access the database from within PHP:

This example is written in the procedural style. The object oriented style is written differently!

<?php
$db = new mysqli('server','user','password','database');

$people = $db->query("SELECT * FROM people WHERE gender='female'");
while($person = $people->fetch_assoc()) {
echo $person['name'].'<br />';
}

This code fetches all the names of people in the table 'people' who are female. Let me explain:

$db is how the script accesses the server. You can have more than one server instance. For example, I may have one accessing a database on an old server, to be moved to a database on a new server, or another database on the same server. new mysqli creates a new instance of server access. 'server' is the server address, which in most cases is either localhost or 127.0.0.1. 'user' is the username of a user who has permission to view the tables on the server. 'password' is obvious. It is the password of the aforementioned user. 'database' is the name of the database that this script uses.

I, for example may use
$db = new mysqli('localhost','ben','12345','friends');

Next is $people. This contains the query to be sent to the server to get all the records in the table 'people'. $db->query tells the server to query the database instance $db with the query to be mentioned next.

"SELECT * FROM people WHERE gender='female'" is the sql query. It is sent to the server to make it ‘do’ stuff. SELECT tells the database to fetch records from the server. ‘*’ tells the database to fetch all the columns, for example name, address, date of birth, gender, etc. FROM people tells the server to fetch all columns of information from the table called 'people'. WHERE gender='female' tells the server to fetch all columns of all records that match the WHERE clause, which in this case is records where the specified gender is female.

'while($person = $people->fetch_assoc())' Is the start of what PHP does with the data. $people is only a query, and it doesn’t contain any information. $person = $people->fetch_assoc() creates a new ‘person’ object which contains the data of the first record associated with the query contained in $people. You may have noticed that only the first record is selected, but the next records are selected when the while statement loops back, and so on until there are no more records left to process. Simple, right? 😉

After the while statement, there is then echo $person['name'].'<br />'; inside two curly braces. The curly braces define what code the while statement runs each time it loops. echo tells the script to print some stuff to the browser. Remember that the browser does not get to see any of this code, and so this line will be the first line sent to the browser. $person['name'] contains the content in the ‘name’ column of the record being currently processed. It can be changed to other names, such as $person['address'], $person['date_of_birth'], $person['gender'], etc. and it will echo different things. the dot in between the variable and <br /> tells the script to join something on. <br /> tells the browser to create a new line. Without this line, all the names would be printed in one long line.

The output of this could be:

Janet
Michelle
Emily
Susan

Changing the $person['name'] variable to $person['gender'] would be guaranteed to display:

female
female
female
female

This is because of the WHERE clause I wrote about earlier.

Of course, questions are welcome in the comments, which will be answered and included in this post!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s