Right, so, MySQL is a database system used mainly on the web. It also goes hand in hand with PHP, and normally if you’re developing a dynamic website, PHP and MySQL are the way to go. Now usually, if you have a good host, you’ll get a MySQL “editing” system with it, such as PHPMyAdmin. You can use this to create new databases, rows, and insert data into a database. However, this is all possibly with the use of PHP, also.

Well in MySQL we can use MySQL queries to create rows, data, etc in a MySQL Database. There is a certain hierachy in MySQL, that goes like this:

» MySQL Server
  » MySQL Database
    » Tables
      » Rows (Data goes into Rows)

So let me help explain this in more detail. There is a MySQL server, which contains MySQL Databases. You can create users, which are allowed to view the MySQL Databases, and give each user privledges. In these databases there are tables, which help to seperate the types of data further. Finally, There are rows which are inside the tables. Each row represents a set of data. You can really imagine MySQL as a table, as shown below in PHPMyAdmin:

Okay so as you can see, each row is a set of cells, which correspond to the column header (Columns go down, like real columns, rows go across). In other words, the column headers represent the data shown in each column. Don’t forget, however, that each row is a seperate set of data. In the end however, all this is is just data. To turn this into useful information you have to use PHP. Before we get to that however, lets go over the basic theory for MySQL queries.

In this part we’re going to go over MySQL Queries. You can run these from PHPMyAdmin, or PHP, as I’ll show you later.

1

SELECT * FROM table

What’s that, you’re probably wondering. Well that, my friends, is the most basic MySQL query you can do. All it does is SELECTs all the data (The asterik tells MySQL to select all columns) FROM the table, table. You can change the asterik to a column title however. So lets say you only wanted to select only IDs, you’d replace the asterik with “ID”:

1

SELECT ID FROM table

That will only show the data in the ID Column. So lets say you want to create a table. To do this, we use the following query:

1
2
3
4

CREATE TABLE table_example(id INT,
                           name VARCHAR(255), 
                           user VARCHAR(255),
                           PRIMARY KEY(id))

So what we’re saying is CREATE the TABLE, mysql_example, with the column headers as: id (which is an INTeger, as in, a number), name (which has a Character length of 255), and user with the same char length. We also set the primary key to id. This is going to be the key that we can identify the row by, easily. Now lets say you wanted to insert data into a database.

1

INSERT INTO table_example(id, name, user) VALUES('1', 'hello', 'username')

You can also insert data into only a few columns, like this:

1

INSERT INTO table_example(id) VALUES('1')

It’s also possible to use conditions in MySQL queries, as shown below:

1

SELECT * FROM table_example WHERE name = 'hello' ORDER BY id ASC

This will select all the rows from table_example, WHERE the id is equal to 1, and ordered by the id, in ascending order. You can also replace ASC with DESC to order the results in descending order.

You can also update a mysql database using UPDATE and DELETE things using MySQL:

1
2

UPDATE table_example SET name = 'booo' WHERE name = 'hello'
DELETE FROM table_example WHERE name = 'booo'

I think this is pretty straight forward, if you understand how WHERE works. The SET bit just says what to SET name to. Another interesting part of MySQL queries is the ability to use conditions:

1
2
3

SELECT * FROM `table_example` WHERE name = 'hello' AND NOT 'name'
SELECT * FROM `table_example` WHERE name = 'hello' AND id = '1'
SELECT * FROM `table_example` WHERE name = 'hello' OR name = 'name'

In the first query we’re getting all results where name equals ‘hello’ and not ‘name’. In the second query, we’re finding all results where name equals hello and the id also equals 1. To get results for this query we need both of these things to be true. In the final query we’re finding all rows where name equals hello, or name equals ‘name’.

Now that we have some of the basics of MySQL queries down, lets discover how to use PHP to get this data from a MySQL database.

Okay, so first thing you’re going to need to do is to connect to the mysql server;

1
2
3

<?php
 
mysql_connect("localhost", "username", "password") or die(mysql_error());

msyql_connect has three main parameters. the host (usually localhost, you probably wont have to change this), the username, and the password. All of these are required to connect to the mysql server. If the connection doesn’t work, you can use “or die(mysql_error());”, which will display an error if the connect fails. The next step is to select the database we want to alter.

1
2
3
4
5

<?php 
 
mysql_select_db("database") or die(mysql_error());
 
?>

This will select the database, “database”, or display a mysql_error. In most scripts, these two lines will usually be required in most php/mysql scripts. This is all well and good, but we need to be able to alter the data with php. We do this by using mysql_query().

1
2
3
4
5

<?php
 
mysql_query("SELECT * FROM table_example");
 
?>

This will run the MySQL query, “SELECT * FROM table_example”, inside the PHP script. Still, this doesn’t really do anything particularly useful. We can’t actually see this information. Common practice in php to get the information (and the simplest way to get the information) is to use the mysql_fetch_array function in php. So here’s a simple script where I get all the ids from table_example.

1
2
3
4
5
6
7
8
9
10
11

<?php
 
mysql_connect('localhost', 'username', 'password') or die(mysql_error());
mysql_select_db('database') or die(mysql_error());
 
$query = mysql_query("SELECT * FROM table_example");
 
while($row = mysql_fetch_array($query)) {
	echo $row[id]."<br />";	
}
?>

Okay, so hopefully you know how the while loop works. If you don’t, check out our basic tutorial on php, which you can find here. Basically what we’re doing here, is connecting to a mysql server, and selecting the database. Then we put a mysql query into a variable, called query. mysql_fetch_array turns the query into an array with all the information you need in it. I then put this into a variable called $row. Then we echo out the data by pulling it out of the array, by saying $row[id], and then displaying a line break. So in my case, all this did was display the numbers 1 and 2, since there are only two ids in this table. There are also so many other useful functions in PHP which we can use to do interesting things. Here are some of the more interesting functions. I’m going to leave out the mysql_connect and mysql_select_db functions since they’re pretty straight forward.

1
2
3
4
5
6
7

<?php
 
$query = mysql_query("SELECT * FROM table_example");
$count = mysql_num_rows($query);
echo $count;
 
?>

This will count the number of rows from the query, and show the number, by echoing it. Another neat thing to do is to use the url, such as, go to the url ?name=hello&id=1, and then doing a script like this:

1
2
3
4
5
6
7
8

<?php
 
$name = $_GET['name'];
$id = $_GET['id'];
 
$query = mysql_query("SELECT * FROM table_example WHERE name = $name AND id = $id");
 
?>

Again, consult our basic guide again if you need more detailed basics. Here’s a really important function that you should always use is mysql_real_escape_string. This is mainly uses a security measure. It’ll add a slash infront of characters, such as \x00, \n, \r, \, ‘, ” and \x1a. It mainly stops mysql injections. You would use it like this:

1
2
3
4
5

<?php
 
$escape = mysql_real_escape_string($_POST['name']);
 
?>

You would then insert that variable into your database. This will only work if you’re using a form where the method is “post”.

Yeah, hopefully now you have a better grasp of php and mysql, and how they work together. Why not experiment with some things to try and understand PHP and MySQL a bit better. Thanks for reading. Please subscribe or follow us on twitter!