Connecting to a mySQL database and creating a mySQL table

PHP Diary | scriptschool.com | PHP Scripts | TD Scripts.com

TD Pic of Day PHP - Run your own picture of the day script from any site anywhere with this handy script

[back]

07/07/00 "Connecting to a mySQL database"

Connecting to a mySQL database using PHP

Connecting to a mySQL database through PHP first requires knowing what a few parameters are. I suggest making these parameters variables that you can easily change, so that if these parameters change you don't have to go in and alter hundreds (or worse thousands) of mySQL connections inside your scripts. Keep these variables in a setup file and require it or at the very top of scripts you write so you can change them.

// change below is your assigned mySQL username
$user = " ";

// change to the pw below is your assigned mySQL password
$pw = " ";

// change to the database you have permission to connect to
$db = " ";

If you don't know what these values are then consult your host to help you. They should be able to tell you what these parameters are.

SHOW available tables in mySQL database

Similar to opening a file to write to it, you have to open a connection to mySQL before you can do anything. The syntax of this function is as follows:

mysql_connect("localhost OR hostname:port", "httpd OR username", "" or "password");

<?
$mysql_access = mysql_connect("localhost", "username", "password");
mysql_close($mysql_access);
?>

The function mysql_close is unnecessary unless you are setting a persistent connection. To set a persistent mySQL connection use:

mysql_pconnect("localhost", "username", "password");

You would want to use persistent connections where you would have a lot of simulataneous connections from the same user through the script.

The following script below will open your mySQL database connection and show the tables available to you in your mySQL database. You must always open a connection to the mySQL database before doing anything else (you only need to open it at the beginning of a script, and then you can run multiple queries if you want). This is good for testing your ability to successfully access the mySQL database.

$mysql_access = mysql_connect("localhost", $user, $pw);
mysql_select_db($db, $mysql_access);

$result = mysql_query("SHOW tables", $mysql_access);
while($row = mysql_fetch_row($result))
{
print("$row[0]<br>");
}

Using telnet to CREATE tables in a mySQL database

Before you can begin to add data into your mySQL database you need to create a table. You can do this through a script or through telnet. Here's how to do it using telnet.

1. login into telnet.
2. at prompt enter the mysql monitor by typing mysql -p
3. at the password prompt enter your mySQL password.
4. You will be prompted by the mySQL monitor. Type use DATABASENAME where DATABASENAME is the name of your mySQL assigned database.
5. Cut and paste the test table below exactly as below:

CREATE TABLE email
(
   ID INT NOT NULL AUTO_INCREMENT,
   email VARCHAR(35) NOT NULL,
   PRIMARY KEY (ID)
);

You should receive the message like 0 rows affected. Now try entering: SHOW tables;
and you should see the table email now is present. We will use this table for our demonstration below.

How to INSERT new rows (new data) into a table in the mySQL database

The following PHP script (save it as add_email.php3) below demonstrates how to open your mySQL database connection and insert a new email address into the created table email in a mySQL database using PHP scripting. It provides a built-in form to enter email addresses into.

<?
if($email) {
// if $email valid format add email to database
if(ereg("^.+@.+\\..+$", $email))
{
   $mysql_access = mysql_connect("localhost", $user, $pw);
   mysql_select_db($db, $mysql_access);
   $query = "INSERT INTO email ";
   $query .= "VALUES(0, '$email')";
   mysql_query($query, $mysql_access);
   print("successfully added your email to the mySQL database!");

} else { print("sorry your email address does not appear valid"); }

} else {

print("<form method=\"POST\" action=\"add_email.php3\">");
print("Please enter your email address: ");
print("<input type=\"text\" name=\"email\" size=\"30\">");
print("<input type=\"submit\" value=\"submit\"></form>");
}
?>

How to QUERY rows (existing data) in a table in the mySQL database

Queries can be executed by using the PHP mysql_query function. First you need to build the query. Let's say we entered in abc@123.com into the script above and my email is in the database. Here is how we'd run a query to find if it was entered into the database successfully:

$query = "SELECT email FROM email WHERE email='abc@123.com' ";
$result = mysql_query($query, $mysql_access);
if(mysql_num_rows($result)) {
    // it is true (the email exists)
    print("<strong>$email</strong> exists in the database.");
} else {
   // false, so it doesn't exist
}

Now what if we wanted to find all emails from the tdscripts.com domain? Use the code below:

$query = "SELECT email FROM email LIKE '%tdscripts.com' ";
$result = mysql_query($query, $mysql_access);
if(mysql_num_rows($result)) {
   // it is true, so let's print the results to the browser
   while($row = mysql_fetch_row($result))
{
      print("$row[0]<br>");
}
} else {
   // false, no results
}

What if we want to find all email addresses that begin with the letter "a"?

$query = "SELECT email FROM email LIKE 'a%' ";
$result = mysql_query($query, $mysql_access);
if(mysql_num_rows($result)) {
    // it is true, so let's print the results to the browser
   while($row = mysql_fetch_row($result))
{
      print("$row[0]<br>");
}
} else {
   // false, no results
}

How to UPDATE rows (change existing data) in a table in the mySQL database

Let's say we mispelled my email address when entering it into the script above. Instead of tdscripts we spelled it tdscipts.com (missing the "r"). Here is how we would build a query to change it.

$query = "UPDATE email set email='abc@123.com' ";
$query = " WHERE email='abc@123.com' ";
mysql_query($query, $mysql_access);

Pretty slick huh? Important note: If you omit the WHERE clause above then it would change every email address in the table to be the same, so be careful not to omit the WHERE clause which effectively singles out a specific row of data.

How to DELETE rows (remove existing data) in a table in the mySQL database

What if we want to remove a duplicate email address from the database? This is a little more tricky since as mentioned above, if you omit the WHERE clause mySQL will remove ALL instances. Fortunately when we CREATEd the email table we used a unique primary key known as ID. So you can remove the duplicate row by using the WHERE clause with the unique ID number. Let's say the duplicate email address is ID 2, here is how we'd construct the query to delete the duplicate email address from the table based on the unique id.

$query = "DELETE FROM email WHERE ID='2' ";
mysql_query($query, $mysql_access);

It is better of course to query the database first before adding data which might be the same as existing data, but this shows how to remove duplicates if should they exist. One last note about DELETE. It is a very powerful SQL function and can erase the entire contents of a table quite easily, so always make sure you use a WHERE clause with it.

How to ALTER (add columns to the existing table) a table in the mySQL database

Let's say we want to add a column to our email table which will datestamp when a new email is added or last updated. Let's use telnet again to do this. Use the following query:

ALTER TABLE email ADD last_update DATETIME;

Now check the rows by using the following query:

SHOW COLUMNS from email;

You will now need to modify your scripts when inserting data, as the new column "last_updated" is present and when using INSERT to add a new row the inserted data must match the fieldtypes. I have modified the script to add an email address now which will also datestamp it when it is added below. It only involves altering the query:

$query = "INSERT INTO email ";
$query .= "VALUES(0, '$email', SYSDATE() )";

Now SYSDATE() will be replaced with a date/time stamp using the server date/time when the email was last added. If you want to update the time just follow the instructions above to UPDATE and make it

set last_update=SYSDATE();

Commenting Your mySQL Tables

I am kind of a nut about keeping good notes in my code, so I like to use those comment tags -- at least in my working versions -- of scripts I write. I may remove some of these comments from public versions, but in my working versions I always keep good notes. When settiing up mySQL tables I like to put that information in between comments tags in PHP as follows:

/* mySQL Table create
USE: tracks ratings at php-scripts.com

create table structure here...

a "test" SQL insert to the database

I always include a test SQL insert statement to show how the data will be inserted into the rows if I was typing it directly into telnet. I am going to use PHP to insert, query, and delete data from the tables, but I want to make sure that I am making a valid insert from telnet, before moving onto creating the PHP code to interact with mySQL. You don't have to do the above step, but when you have problems later on, you can refer to this as a reference point for what each field name is and how to insert information into the table properly. It serves as a pretty decent guide.

Creating mySQL tables

Now let's create a table for the rating box used by php-scripts site. Currently the only thing we track is the vote number. However I would like to add a couple more fields to this. The first is the filename which is the 6 digit date, the second is the vote the user is registering, the third is the unique IP address, and lastly the date/time that they are voting. So basically we are taking all the files and breaking them down into one TABLE with the information. It will look like this

filename | vote | IP address | date/time of vote

This is quite an improvement in tracking information over just tracking the vote and recording in the appropriate file. It may not be apparent how useful this is right now, but it will in future diary entries when you see how we can query this data and present it in interesting ways. Here is how I would go about creating the above table using telnet:

CREATE TABLE rating
(
   RATE_ID INT NOT NULL AUTO_INCREMENT,
   filename CHAR(6),
   vote CHAR(1),
   ip_ad VARCHAR(16),
   log_date DATETIME,
   PRIMARY KEY (RATE_ID)
);

Notice how I used CHAR instead of VARCHAR for the filename. Because I know that every filename will always be 6 characters long, never less, never more. When you know the length of a field remember to use CHAR instead of VARCHAR. Now in the year 2100 this would be y2.1k bug (since the year field is only 2 digits), I realize, but I'll show you later how easy it is to ALTER tables that are already constructed.

Please vote on the usefulness of this diary entry so other people will know if it is worth their time to read :)

[back]

07/07/00 "Connecting to a mySQL database"

[next]

PHP Diary | scriptschool.com | PHP Scripts | TD Scripts.com