mysql4u.com

Before you actually start building your database scripts, you must have a database to place information into and read it from. In this section I will show you how to create a database in MySQL and prepare it for the data. I will also begin to show you how to create the contacts management database.

Database Construction

MySQL databases have a standard setup. They are made up of a database, in which is contained tables. Each of these tables is quite separate and can have different fields etc. even though it is part of one database. Each table contains records which are made up of fields.

Databases And Logins

The process of setting up a MySQL database varies from host to host, you will however end up with a database name, a user name and a password. This information will be required to log in to the database.

If you have PHPMyAdmin (or a similar program) installed you can just go to it to log in with your user name and password. If not you must do all your database administration using PHP scripts.

Creating A Table

Before you can do anything with your database, you must create a table. A table is a section of the database for storing related information. In a table you will set up the different fields which will be used in that table. Because of this construction, nearly all of a site’s database needs can be satisfied using just one database.

Creating a table in PHPMyAdmin is simple, just type the name, select the number of fields and click the button. You will then be taken to a setup screen where you must create the fields for the database. If you are using a PHP script to create your database, the whole creation and setup will be done in one command.

Fields

There are a wide variety of fields and attributes available in MySQL and I will cover a few of these here:

Field Type Description
======== ==========
TINYINT Small Integer Number
SMALLINT Small Integer Number
MEDIUMINT Integer Number
INT Integer Number

VARCHAR Text (maximum 256 characters)
TEXT Text

These are just a few of the fields which are available. A search on the internet will provide lists of all the field types allowed.

Creating A Table With PHP

To create a table in PHP is slightly more difficult than with MySQL. It takes the following format: ————————————————————————————————————– CREATE TABLE tablename {

Fields

} ————————————————————————————————————– The fields are defined as follows:

fieldname type(length) extra info,

The final field entered should not have a comma after it.

I will give full an example of using these later in the section.

The Contacts Database

The contacts database will contain all the conact information for the people you enter and the information will be able to be edited and viewed on the internet. The following fields will be used in the database:

Name Type Length Description
id INT 6 A unique identifier for each record
first VARCHAR 15 The person’s first name
last VARCHAR 15 The person’s last name
phone VARCHAR 20 The person’s phone number
mobile VARCHAR 20 The person’s mobile number
fax VARCHAR 20 The person’s fax number
email VARCHAR 30 The person’s e-mail address
web VARCHAR 30 The person’s web address

You may be wondering why I have used VARCHAR fields for the phone/fax numbers even though they are made up of digits. You could use INT fields but I prefer to use VARCHAR as it will allow dashes and spaces in the number, as well as textual numbers (like 1800-COMPANY) and as we will not be initiating phone calls from the web it is not a problem.

There is one other thing you should be aware of in this database. The id field will also be set as PRIMARY, INDEX, UNIQUE and will be set to auto_increment (found under Extra in PH PMyAdmin). The reason for this is that this will be the field identifier (primary and index) and so must be unique. The auto increment setting means that whenever you add a record, as long as you don’t specify an id, it will be given the next number.

If you are using PHPMyAdmin or a management program you can now create this in a table called contacts.

Creating The Table In PHP

The following code should be used to create this table in PHP. Some of the code has not been covered yet but I will explain it fully in the next part. ————————————————————————————————————-

$user=”username”;

$password=”password”;
$database=”database”;
mysql_connect(localhost,$user,$password);
@mysql_select_db($database) or die( “Unable to select database”);
$query=”CREATE TABLE contacts (id int(6) NOT NULL auto_increment,first varchar(15) NOT NULL,last varchar(15) NOT NULL,phone varchar(20) NOT NULL,mobile varchar(20) NOT NULL,fax varchar(20) NOT NULL,email varchar(30) NOT NULL,web varchar(30) NOT NULL,PRIMARY KEY (id),UNIQUE id (id),KEY id_2 (id))”;

mysql_query($query);
mysql_close();

————————————————————————————————————- Enter your database, MySQL username and MySQL password in the appropriate positions on the first three lines above.

In this article I describe how to use PHP and MySQL to produce a simple counter that can be placed on a web page. PHP and MySQL work very well together, and this article shows, hopefully, how easy they are to use to produce a useful little utility.

In order for the counter to work, the web server you upload the files to needs to support PHP and MySQL. Most good hosting solutions do.

You can download the various scripts used to produce the counter from the following web address: http://www.computernostalgia.net/counter/counter.zip. The scripts are also listed below.

The counter needs a database called ‘counter’, a table in that database called ‘countertable’, and a field in the table called ‘count’. If you want to use a different database, table, or field name, make sure you change the appropriate references to these names in the scripts.

Files

The zip file (counter.zip) contains the following files:

create_database.php
create_table.php
reset_counter.php
counter.php

Note that for display considerations, in the following listings, opening and closing angle brackets for tag names (’<..>‘) are replaced by opening and closing square brackets (’[..]’).

create_database.php

This script creates a MySQL database called ‘counter’. Upload this script to your web server and run it first to create the database.

[html][head][title]Create MySQL Database[/title][/head]
[body]
[?php

//This script creates a database on the MySQL server.
//The name of the database is counter.

//Connect to MySQL server
$link = mysql_connect(”localhost”);

//If you need to supply a username and password, then use the following line
//of code instead of the one above, substituting the correct username and password.
//$link = mysql_connect(”localhost”, “username”, “password”);

//If the connection cannot be made, display an error message
if (! $link)
die(”Cannot connect to MySQL”);

//Create a database called counter
mysql_create_db(”counter”)or die(”Error: “.mysql_error());

//Close the connection to the MySQL server
mysql_close($link);
?]
[/body]
[/html]

create_table.php

This script creates a table (countertable) in the counter database. The table has one field, called ‘count’, which can store an eight digit number. This allows a counter value up to 99,999,999. Upload this and run it once the database has been created.

[html][head][title]Create Table in Database[/title][/head]
[body]
[?php

//This script creates a table (countertable) in the database (counter).

//Assign the name of the database (counter) to the variable $db.
$db=”counter”;

//Connect to MySQL server.
$link = mysql_connect(”localhost”);

//If you need to supply a username and password, then use the following line
//of code instead of the one above, substituting the correct username and password.
//$link = mysql_connect(”localhost”, “username”, “password”);

//If the connection cannot be made, display an error message.
if (! $link)
die(”Cannot connect to MySQL”);

//Select the database. If the database cannot be selected, display an error message.
mysql_select_db($db , $link)
or die(”Select DB Error: “.mysql_error());

//Create a table called countertable in the database.
//The table contains one field: count, which should allow up to 99,999,999 hits
mysql_query(”CREATE TABLE countertable( count INT(8))”)or die(”Create table Error: “.mysql_error());

//Close connection to MySQL server.
mysql_close($link);

?]
[/body]
[/html]

reset_counter.php

This script sets/resets the counter to zero. Upload this and run it to initialise the counter to zero. You can run it at any time to reset the counter to zero.

[html][head][title]Reset Counter[/title][/head]
[body]

[?php

//Point your browser at this page to set/reset the counter to zero.

$db=”counter”;

$link = mysql_connect(”localhost”);

//If you need to supply a username and password, then use the following line
//of code instead of the one above, substituting the correct username and password.
//$link = mysql_connect(”localhost”, “username”, “password”);

if (! $link) die(”Cannot connect to MySQL”);
mysql_select_db($db , $link) or die(”Cannot open $db: “.mysql_error());

// Set the counter to zero
mysql_query(”INSERT INTO countertable (count) VALUES (’0′)”);

//close link to MySQL server
mysql_close($link);
?]

[/body]
[/html]

counter.php

This is the actual counter. The code in this file should be pasted into the web page that will contain the counter (or it can be run on its own). This web page, which will typically be part of a web site, must have a .php file extension, otherwise the PHP code will be ignored by the web server.

[html][head][title]Increment Counter[/title][/head]
[body]

[comment]
Include everything below this comment (down to the closing body tag) in the page
on which you want to put the counter.
[/comment]

[?php

//Set database to counter
$db=”counter”;

//connect to server and database
$link = mysql_connect(”localhost”);

//If you need to supply a username and password, then use the following line
//of code instead of the one above, substituting the correct username and password.
//$link = mysql_connect(”localhost”, “username”, “password”);

if (! $link) die(”Cannot connect to MySQL”);
mysql_select_db($db , $link) or die(”Cannot open $db: “.mysql_error());

//Increment counter
mysql_query(”UPDATE countertable SET count=count+1″);

//extract count from database table
$counter = mysql_query(”SELECT * FROM countertable”);

//Display counter. If you want to change the appearance of the counter, edit
//the following table and font settings.
print “[table border=1 cellpadding=3 cellspacing=0 width=80]”;
while ($get_count = mysql_fetch_row($counter)){
print “[tr]”;
foreach ($get_count as $field)
print “[td align=right][font face=arial size=2]$field[/font][/td]”;
print “[/tr]”;
print “[/table]”;
}

//close link to MySQL server
mysql_close($link);
?]

[/body]
[/html]

That’s it!

If you run MySQL dependent applications, you have probably hit this scenario at some stage. You’re trying to install some software that requires MySQL and are using the correct details; but a MySQL connection can’t be made.

Your hosting service says the problem is in the installation routine of the software, the software author says it’s your hosting service – and back and forth you go between host and vendor until someone finally accepts responsibility and fixes the issue. A lot of time can be wasted, and time is money; not to mention the great deal of frustration that can occur.

There’s a simple script for testing connectivity that should help settle the issue. Here’s what to do:

1. Check your hosting account control interface to ensure that the database is present and you have a user set up for that database. Also ensure you have the right password for the db.

2. Create an empty file with a text editor and save it as mysqltest.php

3. Add the following to that file:

Replace hostname (usually localhost), username and password with the details you have for your database. There’s no need to add the database name.

4. Upload to your server to a folder that’s viewable via a browser.

5. Load the file in your browser

If a connection is made, the resulting page will display ‘Connection OK’. Talk again with the software vendor and give them the URL to the script.

If a connection cannot be made, then you’ll probably see something like:

Warning: mysql_connect(): Access denied for user ‘dbuser’@'localhost’ (using password: YES) in /path/to/mysqltest.php on line 2 Could not connect to MySQL: Access denied for user ‘dbuser’@'localhost’ (using password: YES)

Copy and paste that error and send it to your web host, along with the details of your database.

Don’t forget to remove the file once the issue is settled.

For small and large database projects, Microsoft Access works wonderfully. If you have a database with hundreds of thousands of records, maybe even connecting to other databases remotely, Access will work just fine. Security isn’t too much of a problem (no Access database is truly secure), and with a wide range of options for publishing and reporting on your data, in many cases Access is as good as it gets.

That is, until you try to put it online. That’s where converting your Access database to MySQL comes in.

While Microsoft Access is great as a desktop solution, it weakens greatly when transferred online. The limits on users and speed still exist, and it’s not built to work well on platforms other than Microsoft. Unfortunately for Microsoft Access, most web servers run a form of Unix, not Windows.

Enter MySQL.

MySQL is a relational database system designed specifically for use online and housed on the most common servers online. It’s fast, robust, clean, free, and fairly intuitive to work with as long as you have a very solid understanding of all things SQL. Converting to MySQL from Microsoft Access isn’t without it’s set of problems, however there are a few things you can do to make sure that this conversion goes smoothly.

1. Organize and clean your Microsoft Access database before converting to MySQL! This can’t be stressed enough and it’s pretty much the sole reason your conversion from Access to MySQL will fail or succeed. Think of upgrading from Access to MySQL like a complicated high-wire act. If one person has trouble going across that wire, chances are making him ride a bicycle across it while balancing a ball on his nose will only end up in disaster … and a broken bicycle. Re-design your Microsoft Access database if you have to, but be absolutely sure that your database is designed flawlessly.

2. Set up your MySQL database. Just as making sure your Access database is designed flawlessly, your MySQL database must be designed just as flawlessly. MySQL has different data types than Microsoft Access does - you won’t find memo fields, for instance - so be sure that your existing data will fit into the structure you build for MySQL. Otherwise, all you’re going to get is a mess of mish-mashed data.

3. Export your Microsoft Access data to text files. Fortunately, MySQL has a very handy interface called phpMyAdmin. This allows you to interact directly with your tables through a somewhat graphical UI. All you need to have are tables and fields set up that match your data, and you can click, “Import Data from Text File” through phpMyAdmin. Browse to the text file, import it, and congratulations, your data is online!

While there is certainly more details to be learned regarding the process of converting from Microsoft Access to MySQL, always keep in mind the fundamentals. Databases that are designed perfectly will circumvent 95% of the problems that generally befall poorly designed ones. Converting Access to MySQL isn’t terribly difficult or time consuming, but it can be very tricky. Back up your data, make sure you’ve designed databases properly on both sides, and your data could be up in no time. Then, it’s on to PHP!