NORTH43 DESIGN : Official Blog

Intro to PHP and MySQL

Photo by: Adam

What are PHP and MySQL?

PHP is a server-side scripting language. This means that all the processing of the PHP is done on the server before the content even hits your computer. For example, if we had a script that calculated 4x4, we would only see the 16, not the number sentence. MySQL is a type of database, kind of like a spreadsheet that you might make in Microsoft Excel or Numbers on a Mac. In simpler terms, it's an organizational table. Databases store - you guessed it - data, which can be imported from a whole number of ways, but usually using HTML forms.

Why are they related?

PHP is used to talk to the MySQL database to modify, add, or remove content from the database. For example, you might have a registration form on your website to add users to your database. The form would be filled in and the information would be sent to a PHP script. The PHP script would then take that information and format it, and then send a command to the MySQL database telling it to add the information to itself.

How do I set up a database?

If you have your own servers, you have to intstall MySQL to them. You can go to the official MySQL website to download their package. You have to have a host (make sure they include databases in your plan!), and you can usually set up the database via the control panel for your host - if not, we'll cover that in the PHP section. You should get four pieces of information once the database is set up: the host address, the user, the password, and the database name. The host address is often just 'localhost', however if they host the databases seperate from their regular hosting, it would be an IP address (ie. 200.200.200.200).

Once the database is set up, you need to create tables. The easiest way to do this is through PHPmyAdmin, which is often automatically installed with your host or when you create a databse. If you can't get PHPmyAdmin, we'll cover how to create a database table later on in the PHP section.

PHP 101

A PHP file is basically just a text file. To create one, just open a simple text editor (like Notepad or TextEdit) and paste in the following code:

<?PHP

?>

Now save it as file.php and that's it! You've made your first PHP file. Unfortunately right now, it doesn't do a thing. Now insert a new line to make it look like this:

<?PHP
echo "Hello!";
?>

Re-save the file and upload it to your host, then open the address that the file is located in your browser (ie. www.website.com/myfile.php). You should get a page that just says "Hello!" at the top left. Now why does all that code only render a simple phrase!? Let's go through the code and discuss what each part means. The first line just tells the computer that it's going to make a PHP statement, and the last line says that's the end of the PHP statement.

The middle line is the interesting one. We already know what it does, but let's look at the syntax. The 'echo' is the actual command that says to print something on the screen. Whatever comes after that it will repeat. You will notice that it has to be in quotations, and the end of the line is a semi-colon. The quotes define what will be printed, and the semi-colon defines the end of the command.

Now, back to our databases. If you haven't already created a table, let's do that now. Create a new PHP file and paste in the following code:

<?php
$c = mysql_connect("host","user","pass");
if (!$c) {
die('No connection: ' . mysql_error());
}
if (mysql_query("CREATE DATABASE my_db",$c)) {
echo "Database created";
} else {
echo "Could not create database: " . mysql_error();
}

mysql_select_db("my_db", $con);
$q = "CREATE TABLE registrants (
Name varchar(50),
Email varchar(50),
id INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT)";

mysql_query($q,$c) or die ("Could not create table:" . mysql_error());
mysql_close($c);
?>

Don't worry, it's more simple than it looks. One of the great things about PHP is that it already has a number of MySQL related functions already built in. mysql_connect, mysql_query, and mysql_select_db are all examples of these functions. Each of these functions does a different thing with the database you're accessing. Now let's set this up so it'll work for you.

The first thing you're going to do is replace the 'host', 'user' and 'pass' in line 2 with your own information. If you like, you can modify the information on line 14 and 15 by changing 'Name' and 'Email' to values for your own needs, or add in extra lines below that to create extra fields. Now save, upload, and visit the page. If all went well, you should see the following message on the page 'Database created'. DO NOT RELOAD THE PAGE. In fact, now you're going to delete that file off of your server, because if it gets visited again it might create more databases or tables, or cause other problems down the road. We don't want that.

Now what?

Okay, so we've made a database, we've created the table, now we have to input the information and call it back. Create a new text file and paste the following code into it, save it and upload it. We'll talk about it's inner workings next.

<html>
<head>
<title>My New Database</title>
</head>

<body>
<?PHP
$c = mysql_connect("host","user","pass");
if (!$c) {
die('No connection: ' . mysql_error());
}
mysql_select_db("my_db", $c);
if($_POST['submit']){
$name = $_POST['Name'];
$email = $_POST['Email'];

$q = mysql_query("INSERT INTO registrants (Name, Email) VALUES ('$name', '$email')");

if($q){
echo "Inserted into database";
} else {
echo "Could not insert into database";
}

} else {
?>

<br /><br />

<form method="POST" action"myform.php">
Name:<input type="text" name="Name" /><br />
Email:<input type="text" name="Email" /><br />
<input type="submit" name="submit" value="Add User" /><br />
</form>

<br /><br />

Current Registrants:<br /><br />

<?PHP
$q = mysql_query("SELECT * FROM registrants");
$num = mysql_num_rows($q);

for($i=0; $i<$num; $i++){
$name = mysql_result($q, $i, "Name");
$email = mysql_result($q, $i, "Email");

echo "Name: $name <br />";
echo "Email: $email<br /><br />";
}

} // End else statement
?>

</body>
</html>

To make this a little easier to digest, we're going to take this piece by piece. This first piece...

<html>
<head>
<title>My New Database</title>
</head>

<body>
<?PHP
$c = mysql_connect("host","user","pass");
if (!$c) {
die('No connection: ' . mysql_error());
}
mysql_select_db("my_db", $c);

... simply begins the HTML document, and connects to the database. Again, you would need to fill in your own information for your database on the 8th line.

The next chunk...

if($_POST['submit']){
$name = $_POST['Name'];
$email = $_POST['Email'];

$q = mysql_query("INSERT INTO registrants (Name, Email) VALUES ('$name', '$email')");

if($q){
echo "Inserted into database";
} else {
echo "Could not insert into database";
}

... checks to see if the form has been sent, and if it has, inserts the data filled out in the form into the database.

Now here's where it gets kinda cool.

} else {
?>

<br /><br />

<form method="POST" action"myform.php">
Name:<input type="text" name="Name" /><br />
Email:<input type="text" name="Email" /><br />
<input type="submit" name="submit" value="Add User" /><br />
</form>

<br /><br />

Remember the if() statement that checked to see if the form had been sent? The '}else{' at the beginning of this section is a continuation of that if() statement. So, basically if the form hasn't been sent, then it'll output everything between '} else {' and the closing else bracket (in the next section). There isn't any PHP except for the very beginning, so this should be pretty straight forward if you're familiar with HTML. If not, check out our Basic HTML tutorial.

Last but not least:

Current Registrants:<br /><br />

<?PHP
$q = mysql_query("SELECT * FROM registrants");
$num = mysql_num_rows($q);

for($i=0; $i<$num; $i++){
$name = mysql_result($q, $i, "Name");
$email = mysql_result($q, $i, "Email");

echo "Name: $name <br />";
echo "Email: $email<br /><br />";
}

} // End else statement
?>

</body>
</html>

This section creates a loop that outputs the result of the database. Since we've already connected to the database, we don't need to do it again. All we need is our query and the loop. The SQL query is "SELECTING [everything] FROM [the table] registrants. The next line counts how many results we'll have in the end.

Here's where it gets a little tricky. A for() statement has three sections in between the parenthesis, separated by semi-colons. The first is the starting value, the second is the terms, and the third is the next step. To put it in laymans terms, we might phrase a for() statement like this:

for(first_number = 0 ; the_number < total_entries ; add one to the_number)

This is essentially what the for statement in the code is saying. So, as long as the_number is less than the total_entries, it will run the script, then add one to the_number, then it'll run again and continue until the_number is larger or equal to the total entries.

After that it pulls the information from the database, using the_number as the line number of the results, in the mysql_result function. We create 'variables' which are placeholders for information by putting a '$' in front of a single letter combonation, and set what it's equal to. As we saw before, we use the 'echo' function to output the results onto the web page, only this time we're using the variables inside the quotes on the echo lines.

Finally, the '} else {' statement is ended, and the closing tags for the body and html are at the end. Enjoy!

Posted by Adam on 25.02.2009.

Our Links

North43 Design
Our Latest
Client Login
Terms of Use