Beginning PHP - Lesson 22 - Starting off with Databases

[fattwam]

Now is the time you have all been waiting for! Lets get into Databases!

However, before we can start programming - I want to teach you how to think in databases.

 

A database (or 'DB' as they're sometimes called) is basically like a large file cabinet for different folders. Here is what a DB would look like as a picture:

 

 

http://www.ibotmodz.net/forum/uploads/remoteimages/10110-414.jpg

 

The Database is called database_1 and inside the database are two tables "contacts" and "orders". Both of those tables have 3 columns in them:

The table "orders" has the columns "id", "date", and "item".

The table "contacts" has the columns "name", "phone", and "address".

 

In the end, all of the information in a database is stored in one column or another; the whole point of tables is just to help group columns that have similar or related information.

 

After each of the columns that will be in a table are made, you start getting into rows. (Think of an Excel Spreadsheet made up of rows and columns, or for those of you who have done HTML; think of

). Each row of a table will have the information for that tables columns. For example, row 1 has the information "Bob" for the column "name", "#" for the column "phone", and "TX" for the column "address". Each new record (row) in the table will have the information for that tables columns.

 

Note: Database's can have hundreds of tables or just one. Likewise, a table can have hundreds of columns or just one. Also, all of the different tables in a DB can have different numbers of columns; just because both of the tables in this example have 3 columns doesn't mean that they have to have the same number.

 

 

 

 

All of the interaction with a MySQL database will be done through something called a "query". A query is just another way of saying a request for something. So next time you hear someone say "run a query on your database" you will know that they just mean give your DB a request (ask for some information from it).

 

 

 

 

Now then, let me explain the different commands you can use on a database:

 

-----------------------------------------------------------

-- Selecting Data:

-----------------------------------------------------------

The SELECT command selects the data that you want from a table or column. It is the most used MySQL command there is. You can optionally add a 'WHERE' command that tells the computer that you only want the data from where you say.

 

SELECT [columns] FROM [table] WHERE [specification];

 

For example:

SELECT * FROM `orders` WHERE `ID` = 1;

 

In English this would be:

Select everything FROM a table named "orders" WHERE A Column named "ID" is equal to '1';

 

On the example DB above this would return the values "1", "6/06", and "car". Because this is all of the information in the row of the table "orders" that has an ID equal to "1".

 

 

Another example is:

SELECT `phone`,`name`,`address` FROM `contacts`;

 

This would select all of the information in the columns named "phone", "name", and "address" from the table named "contacts". It would return all of the data based on the rows that the data was in. For example, the result of this command on the above database would return an array with each element a row from the table:

$result['1']['name']; would be equal to "Bob"
$result['1']['phone']; would be equal to "#"
$result['1']['address']; would be equal to "TX"

$result['2']['name']; would be equal to "Sam"
$result['2']['phone']; would be equal to "#"
$result['2']['address']; would be equal to "CA"

$result['3']['name']; would be equal to "Joe"
$result['3']['phone']; would be equal to "#"
$result['3']['address']; would be equal to "FL"

?>

 

There are three elements in the array $result because there are three rows in the table "contacts".

Then there are three sub-elements in each of the array's elements because there are three columns for each row.

 

 

Have I lost you yet? Actually, this might be a little bit much if you are new to this kind of thing. So just try to understand as much as you can and just come back after we have done a little with DB's.

 

 

-----------------------------------------------------------

-- Creating Data:

-----------------------------------------------------------

Second you need to know how to INSERT data into the DB.

Inserting data is how you get data into the database.

 

The syntax for inserting information is:

INSERT INTO `tablename` ( `columne_1` , `columne_2` ) VALUES ('valueforcolumn_1', 'valueforcolumn_2');

 

 

Example Query:

INSERT INTO `contacts` ( `name` , `phone` , `address` ) VALUES ('Bob', '#', 'TX');

 

If I ran this code on the database above it would make the information in row "1".

 

 

 

Now then, enough of this - lets make something!

 

 

 

-----------------------------------------------------------

-- Making Your First Database:

-----------------------------------------------------------

 

For this next project you will need to know what your database username and password is, and what the database server name ("host") is. If you don't have access to some kind of control panel where you can find out (like cPanel or 1and1's MySQL Administration) you need to call your web host and ask them for this information. If you don't have a database on your web host you are being ripped-off. In our day and age they are given out like candy so there is no reason for your host not to give you one! (If you need a new host I recommend 1and1.com, or if you are out-side of the USA I recommend Bluehost.com)

 

Also, you need your database to be a "MySQL" database. This script will not work on PostSQL, Oracle, SQL, or the like.

 

If you have easyPHP, WAMP, AMP, or some other kind of server on your own PC then your username is probably "root", your password is "" (nothing), and your host will be "localhost".

 

Open up SciTE (in our downloads section) and type this into it:

 

// Create the database access variables:
$hostname = "localhost";
$database = "testdatabase";
$username = "root";
$password = "";


// Open a connection to the server
$connection = mysql_connect($hostname, $username, $password) 
or die ("Unable to connect to the DB Server!");


// Make the query we wan to use
$query = 'CREATE DATABASE '. $database. ';';


// Now lets make a Database
$result = mysql_query($query) or die ("Error in query: $query. 
".mysql_error());


echo "Made Database $database";

?> 

Save it as "makedb.php" and upload it to your server.

 

If you have the "RIGHTS" to make a Database it should run just fine. If not you need to make sure and check that you entered the right variables, then call your host to see if you have the "RIGHTS" to make a Database. If you still can't get this to work just post a request for help in the MySQL forum.

 

If you try to run this script a second time you will see the error:

Error in query: CREATE DATABASE testdatabase;. Can't create database 'testdatabase'; database exists

 

That is because MySQL has a safety feature that prevents you from creating a database when one already exists.

 

Also, note that we left the echo statement outside of any kind of checking loop that would make sure that the database WAS made. You might be thinking that it will still tell you that it made the database even if something goes wrong. However, if anything does go wrong in the script the "or die" command before the echo will KILL the script and the rest of the code below the "or die" will NOT run. Therefore, you will only see "Made Database $database" if the script works.

 

Well, anyway - we just made a database!

 

 

 

-----------------------------------------------------------

-- Making a Table:

-----------------------------------------------------------

 

Now we need to make a table and some columns for the data we will put in our database.

Open up makedb.php and change the code in it to this:

 

// Create the database access variables:
$hostname = "localhost";
$database = "testdatabase";
$username = "root";
$password = "";


// Open a connection to the server
$connection = mysql_connect($hostname, $username, $password) or die ("Unable to connect to the DB Server!");


//////////
//NEW CODE
//////////

// Select the database
mysql_select_db($database) or die ("Can't select database!");


// Make Table Query
$query = "CREATE TABLE `contacts` 
(
`name` TEXT NOT NULL ,
`phone` TEXT NOT NULL ,
`address` TEXT NOT NULL
)";

// execute query
$result = mysql_query($query) or die ("Error in query: 
$query. ".mysql_error());

echo "Made table 'contacts'";

//////////
//NEW CODE
//////////
?> 

 

Save it as "maketable.php" and upload it to your web server. If everything goes OK then you now have one table named "contacts" in your database.

 

If you try to run this script a second time you will see the error:

Error in query: CREATE TABLE `contacts` ( `name` TEXT NOT NULL , `phone` TEXT NOT NULL , `address` TEXT NOT NULL ). Table 'contacts' already exists

 

Again, this is becuase MySQL has a safety feature that prevents you from creating something when it already exists.

 

 

-----------------------------------------------------------

-- Adding Data to Your Database:

-----------------------------------------------------------

 

Now we need to add data to our database.

Open up maketable.php and change the code in it to this:

 

 

// Create the database access variables:
$hostname = "localhost";
$database = "testdatabase";
$username = "root";
$password = "";


// Open a connection to the server
$connection = mysql_connect($hostname, $username, $password) or die ("Unable to connect to the DB Server!");


// Select the database
mysql_select_db($database) or die ("Can't select database!");



//////////
//NEW CODE
//////////


// Make data query
$query = 'INSERT INTO `contacts` (`name`, `phone`, `address`) VALUES ('Bob', '999-555-5555', 'TX');';

// execute query
$result = mysql_query($query) or die ("Error in query: $query. 
".mysql_error());

echo "inserted Bob and his info into 'contacts'";

//////////
//NEW CODE
//////////
?> 

 

 

Save it as "makebob.php" and upload it to your web server. If everything goes OK then you will now have all of Bob's info in your database.

 

Note: If you try to run this script a second time you will NOT see an error. This is because you are now adding information to the database - and there is no limit on how much data you can add! If you run the script several times you will find that you will just have several rows all with Bob's data.

 

 

 

 

-----------------------------------------------------------

-- Showing your data:

-----------------------------------------------------------

 

Open back up makebob.php and change the code to this:

 

// Create the database access variables:
$hostname = "localhost";
$database = "testdatabase";
$username = "root";
$password = "";


// Open a connection to the server
$connection = mysql_connect($hostname, $username, $password) or die ("Unable to connect to the DB Server!");


// Select the database
mysql_select_db($database) or die ("Can't select database!");



//////////
//NEW CODE
//////////


// Show data query
$query = 'SELECT * FROM `contacts`';

// execute query
$result = mysql_query($query) or die ("Error in query: $query. 
".mysql_error());


echo '</pre><table border="1">'
   . ''
   . 'Name'
   . 'Phone'
   . 'Address'
   . '';
   
while($row = mysql_fetch_row($result)) {
   echo "";
   echo "". $row[0]. "";
   echo "". $row[1]. "";
   echo "". $row[2]. "";
   echo "";
}
   
echo '</table>'; <br><br>//////////<br>//NEW CODE<br>//////////<br>?&gt

 

Save the new file as "showdata.php" and upload it to your web server. When you run it you should see a table with Name, Phone, and Address at the top and below it is each row from the database.

 

The only code I need to explain is the while loop:

 

while($row = mysql_fetch_row($result)) {
   echo "";
   echo "". $row[0]. "";
   echo "". $row[1]. "";
   echo "". $row[2]. "";
   echo "";
}

 

This code would read like this in English:

 

while(the variable "$row" is equal to a_row_fetched_from_the($result)) {
   print a new row in HTML "";
   print a new column in HTML "". then print out the value of the first element in $row ($row[0]). "";
   print a new column in HTML "". then print out the value of the second element in $row ($row[1]). "";
   print a new column in HTML "". then print out the value of the third element in $row ($row[2]). "";
   print a new end-row in HTML "";
}

 

After, the loop runs one time it will go back to the start and it will see if there is another row in the result ($row = mysql_fetch_row($result)) if there is; then code will run again and if NOT it will stop.

 

I also would like to say that you could replace:

// Show data query
$query = 'SELECT * FROM `contacts`';

with:

// Show data query
$query = 'SELECT `name`, `phone`, `address` FROM `contacts`';

The script would still give the same result! This is because the first code says "SELECT ALL" and the second code says "SELECT `name`, `phone`, `address`" but since that is all of the columns in the table it is the same as saying "SELECT ALL"!

 

 

That's it!

 

 

 

-----------------------------------------------------------

-- Other MySQL Queries:

-----------------------------------------------------------

Here are some other queries you can do with MySQL.

 

DELETING:

DELETE FROM `contacts` WHERE name=bob;

 

DELETE * FROM `contacts`;

 

Update:

UPDATE `contacts` SET name='newbob' WHERE name='Bob';

 

Creating a Database:

CREATE DATABASE `newdatabase`;

 

Showing what Databases are Available:

SHOW DATABASES;

 

Showing what Tables are available in a database:

SHOW TABLES;

 

Select Everything from a table in a Database:

SELECT * FROM `yourtablename`;

 

Deleting a Database:

DROP DATABASE `databasename`;

 

-------------------------------------------------------------

 

 

 

Next lesson I'll show you how to do more advanced things with databases.

(We'll also get rid of all of that data with DELETE )