Building a website | Part 8 – Intro to databases

BUILDING A WEBSITE | PART 8 – INTRO TO DATABASES

POSTED ON NOVEMBER 25, 2011 BY MATTSICH |

NO COMMENTS

PHP and mySQL are a powerful combination. In the hands of a person well versed in both, it can help organize huge amounts of data. PHP gives us an array of functions that help us open connections to a database. When registering for a website hosting service, you are given FTP access to a server, and access to a database (and nowadays they also include a domain). The FTP server is where you place your files. The database is where you store data for the files in your ftp.

First of all, I need to cover arrays. I forgot to talk about them in the php tutorial (Duh!) but I remembered now because they are essential to understanding databases.

ARRAYS

An array is basically a list of values. You can make an array like this:

1	$myarray = array("value 1", "value 2", "value 3");

I only input three values but you can use as many as you want. Now if I want to access those files, I can do it like this:

1	echo $myarray[0] . " " . $myarray[1] . " " . $myarray[3];

Notice that the first value is at the zero index. Arrays start with zeros by default.

With that in mind, you could also have declared the values of the array like this:

1
2
3

$myarray[0] = "value 1";
$myarray[1] = "value 2";
$myarray[2] = "value 3";

All these are numerical arrays though (they are indexed by 0, 1, 2 etc). Instead of using numbers, we can associate the numbers to words so that we can use those given names instead of the numbers.

1
2
3
4
5
6
7

$myarray = array("TheZero" => "value 1", "TheOne" => "value 2","TheTwo" => "value 3");

//or

$myarray['TheZero'] = "value 1";
$myarray['TheOne'] = "value 2";
$myarray['TheTwo'] = "value 3";

Hopefully that gives you a somewhat okay understanding of arrays. Don’t worry, you will get to understand them better through experience.

DATABASES

Let’s get started with databases right away.
The following is the way you usually first learn to connect to a database.

1
2
3
4
5
6
7
8
9
10
11
12
13
14

With the comments I added, the code should be easy to understand. For us (since we are using a local hosting app), the url to the database is simply ‘localhost’ but if you buy a hosting service, they will supply you with it… along with the username and password.

To find the username and password of the local hosting app you have, just read the documentation or search for it on google.

So once we connect to the database server, we need to actually create a database where we will make tables which will hold our data.

If you have ever taken calculus, you may have had a sneak peek of matrix theory and if you have ever taken a programing class, you may have been introduced to arrays. The standard database table is like a two dimensional array.

Now that the connection is open, let’s make a database. It’s actually really easy. Write the following code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14

I think I should explain a little more now… I realize some things may be common sense for me and not for others. Please ask questions if you don’t understand something.

the mysql_connect function is easy to think of as the pass key to your databases. It stores your login information and you use it whenever you want to change something in the database.

The mysql_query function has two operands. The fist is the actual SQL syntax and the second is where you declare the connection to be used.

SQL is not case sensitive but it’s common practice to keep the commands uppercased.

We don’t usually write the SQL commands directly in the function unless they are short because… they’re not usually that short.

Now let’s create a table but instead of writing inline we will use a variable. Edit your code to look like the following:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

<?php

//connect to database with location of database, username and password.
$connection = mysql_connect("localhost","name","password");

if(!$connection){ //if the connection is not set
die('I couldn't connect to the database for these reasons: ' . mysql_error());
}

mysql_query("CREATE DATABASE test_db", $connection); //create the database

mysql_select_db("test_db",$connection); //select it

//create the table with fields
$sql_commands = "CREATE TABLE myTable(
KeyInput int NOT NULL AUTO_INCREMENT,
PRIMARY KEY(KeyInput),
FirstInput varchar(15),
SecondInput varchar(10)
)";

mysql_query($sql_commands, $connection); //run the sql commands

mysql_close($connection); //close the connection

?>

Let’s look a little closer at how I created the table. Creating the table is simple but we don’t want to leave it empty. The first field I make is named “FirstInput,” it’s an integer, it doesn’t start with a zero and it is auto incremented (goes 1,2,3 etc every time new data is input). On the second line I don’t add another field; I make the FirstInput field the primary key of the table. Each table should have a primary key but can only have one because it’s a unique identifier for each input. I could have written that command after the ThirdInput line but as long as it’s after the column declaration which it is affecting, you will be fine.

An int column is, you may have guessed, a column that only accepts int values. The varchar column can hold any string of characters and limits the length to the number set in the parentheses.

Ok now that we have the table, we will look at five ways to manipulate the data.

INSERT

To input data into the table, you we use the ‘insert’ command. Take a look at the following code (note that I’m not creating the database or table anymore.. we don’t need to because we already made them before):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

Notice that I used single quotes for the input strings. I did this because if I would have used a double quote, it would pick that up as the closing double quote for the $sql_commands string variable… still needs to be in single quotes though because it’s a string.

SELECT

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

<?php

//connect to database with location of database, username and password.
$connection = mysql_connect("localhost","name","password");

if(!$connection){ //if the connection is not set
die('I couldn't connect to the database for these reasons: ' . mysql_error());
}

mysql_select_db("test_db",$connection); //select it

//create the table with fields
$output = mysql_query("SELECT * FROM myTable");

while($info = mysql_fetch_array($output)){ //while the array exists, it will go though all the values in the table row.
echo $info['FirstField'] . " "; //echo result followed by a space.
}

while($info = mysql_fetch_array($output)){ //while the array exists, it will go though all the values in the table row.
echo $info['SecondField'] . " "; //echo result followed by a space.
}

mysql_close($connection); //close the connection

?>

This is a little more complicated, I’ll admit. This is where knowledge of arrays really comes in handy. Let’s start on the line with the mysql_query function. All we do is SELECT all ( the asterisk means “all”) FROM the table.
When we look at one of the while loops, you will see that I call on the values in the table using the mysql_fetch_array function which means that the variable will be an array.. we can work with that. We can now echo out the fields in the array. The array slots aren’t numbered 1,2,3 etc… they are named (look back at the little intro I gave on arrays).

WHERE

Using the where command is easy. It’s just adding another command to the sql syntax.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

<?php

//connect to database with location of database, username and password.
$connection = mysql_connect("localhost","name","password");

if(!$connection){ //if the connection is not set
die('I couldn't connect to the database for these reasons: ' . mysql_error());
}

mysql_select_db("test_db",$connection); //select it

//create the table with fields
$output = mysql_query("SELECT * FROM myTable WHERE FirstField='somevalue' "); // <<<<<<<======== here's what changed

while($info = mysql_fetch_array($output)){ //while the array exists, it will go though all the values in the table row.
echo $info['FirstField'] . " "; //echo result followed by a space.
}

while($info = mysql_fetch_array($output)){ //while the array exists, it will go though all the values in the table row.
echo $info['SecondField'] . " "; //echo result followed by a space.
}

mysql_close($connection); //close the connection

?>

Obviously this will not return anything because none of the values in ‘firstField’ are ‘some value’ … but if one of the values was, you would be able to isolate only those columns.

UPDATE

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

<?php

//connect to database with location of database, username and password.
$connection = mysql_connect("localhost","name","password");

if(!$connection){ //if the connection is not set
die('I couldn't connect to the database for these reasons: ' . mysql_error());
}

mysql_select_db("test_db",$connection); //select it

//create the table with fields
$output = mysql_query("UPDATE myTable SET SecondInput = 'something' WHERE FirstField='another' ");

while($info = mysql_fetch_array($output)){ //while the array exists, it will go though all the values in the table row.
echo $info['FirstField'] . " "; //echo result followed by a space.
}

while($info = mysql_fetch_array($output)){ //while the array exists, it will go though all the values in the table row.
echo $info['SecondField'] . " "; //echo result followed by a space.
}

mysql_close($connection); //close the connection

?>

Won’t give much of an explanation for this… should be relatively easy to understand

DELETE

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

This one should also be really easy to understand.

So there you have it! A quick and dirty intro to databases. You now know everything you need to know for us to move on to wordpress and make some custom themes! See ya in the next tutorial.