PHP Tutorials Lesson 4
Note: The links to images have long since stopped working.
Introduction:
In this tutorial you will learn
1. Introduction to MySQL & phpMyAdmin
2. Connecting To Your Database
3. SQL Queries
4. Putting Information Into A Database
5. Extracting Information From A Database
Introduction to MySQL & phpMyAdmin:
MySQL:
What is MySQL?
Simply put, MySQL is a database system. SQL stands for Structured Query Language, which is exactly what it is: a language that queries information out of a database. MySQL is the most popular open source database because of it's speed and reliability. Most free hosts do NOT give you a MySQL database with their hosting package. Below is a list of the few that do.
1.
Spunge
2.
FreeSQL*
3.
HomepageHost
4.
HostRave
* Is only a database host, does not host sites
phpMyAdmin:
What is phpMyAdmin?
phpMyAdmin is a frontend to the MySQL server. It allows you to create, drop, and alter databases, run any line of SQL code, delete, edit, and add fields, and manage field keys.
NOTE: I censored my pics since they are vital to a site I am making now... you can never be too safe
Here is the welcome screen to phpMyAdmin
Over on the left under "Home" it shows you
Database Name
Table 1
Table 2
Now you click on a table and you are immediately brought to the Structure page of it
At the bottom you can see a textbox, this is where you can run your SQL statements. Above you can see the fields their type and their other info. The other pages that you can navigate to are shown at the top. For this tutorial I will be giving the most info on Browse and Structure pages. These are the most commonly used pages.
Click on the browse tab and you are brought here
Here you are shown the current contents of the table and you are given options to Edit it or Delete it. Although you cannot see it in this picture there is also a SQL textbox on the bottom.
SQL:
Just an SQL textbox.
Select:
Fancy options to extract data.
Insert:
Fancy options to insert data.
Export:
Back up your database.
Operations:
Move table, alter table, rename table, etc.
Empty:
Delete all info from the table
Drop:
Delete table.
Connecting To Your Database:
Includes:
Before you begin with this you must learn about the include() function. What the include does is INCLUDE a file within your file, for example if I had a site where on every page at the top it said "abcdedfghijklmnopqrstuvwxyz012345679" now I don't want to keep typing that over and over again so what I would do is I would make a file called alphabet.php that echo'ed the text. Then at the top of every page I would put
Code:
include("alphabet.php");
and of course it would show "abcdefghijklmnopqrstuvwxyz0123456789".
Connecting:
To connect to your database you need 4 things. A username, password, host, and database name. With most sites you need to connect to your database on every page so you make an include file called connect.php, in that file you have the following code.
Code:
<?php
mysql_connect("server", "username", "password") or die("Could not connect to server");
mysql_select_db("database_name") or die("Could not open database");
?>
What this does is connect to the server first, if it can't then it stops loading and displays an error message. Then it tries to select the database, again if it can't it stops loading and loads an error message.
SQL Queries:
*:
* in SQL means everything, so instead of using
SELECT `field`, `field2`, `field3` FROM `table` WHERE `condition` = 'constraint';
You would do
SELECT * FROM `table` WHERE `condition` = 'constraint';
%:
% in SQL is a wildcard, say you wanted to find a user with a bad word in their profile, you would use
SELECT `name` FROM `users` WHERE `profile` LIKE '%badword%';
Delete:
This statement will delete that row of fields. The syntax is
DELETE FROM `table` WHERE `field` = 'value';
So if you wanted to delete a user with id #1 the code would be
DELETE FROM `users` WHERE `id` = '1';
Where:
Say you wanted to make a users clicks go up by one, but you don't want everyones clicks to go up by one. You would use WHERE. First you would get some data that is unique to the user (id, name, etc) and use the code
UPDATE `table` SET `clicks` = $clicks WHERE `id` = 1;
Where $clicks is the amount of clicks the user currently has and 1 is the users ID. Don't worry about the UPDATE... that will be explained soon.
Like:
Like is somewhat like the WHERE query in the fact that it's a constraint. The syntax is
SELECT * FROM `table` WHERE `field` LIKE '%constraint%';
For example, if you wanted to find all users with "mike" in their name you would use
SELECT * FROM `users` WHERE `name` LIKE '%mike%';
Putting Info Into Your Database:
Insert:
The first method of doing this is the INSERT statement, the syntax of this is
INSERT INTO `table` (field1, field2) VALUES ('data', 'data2');
Take notice that the order of field names corresponds with the order of info to be inserted.
Update:
UPDATE is used when you want to update existing data. The syntax is
UPDATE `table` SET `field` = 'new data' WHERE `id` = 1;
Now to use these you would have to find a way to run them, once connected to your server you would have the following code
Code:
<?php
include("connect.php");
mysql_query("Your Query");
?>
Extracting Info From Your Database:
Select:
This is the only method of getting information out of the database, the syntax is like so
SELECT `field` FROM `table` WHERE `condition` = 'constraint';
To put this to use you would have
Code:
<?php
include("connect.php");
$name = mysql_query("SELECT `name` FROM `users` WHERE `id` = '1'");
echo $name;
?>
Go ahead, try it. You get some funky Resource ID huh. The way to fix this is to put the information you get into an array. To do this you have the following code.
Code:
<?php
include("connect.php"); // connect to your server
$sql = mysql_query("SELECT * FROM `users` WHERE `id` = '1'"); // put our query in a variable
$user = mysql_fetch_array($sql); // get the info and put it in an array
/*
Lets assume for a minute that in the user table you have the fields name, id, and email
The name is Mike, the ID is 1, and the email is email@domain.com
*/
echo $user['name']; // will echo name
echo $user['id']; // will echo 1
echo $user['email']; // will echo email
?>
Voila! It works! So go ahead and try it out, experiment, build a user login with a database! Now is when you really start learning how an online game works.