|Get best price on Samsung Galaxy Note 4, Charcoal Black 32GB (Verizon Wireless)|
MySQL PHP Database Tutorial: Create Database
Now that we know what data we want and what we want to with the data, we need to create
the database to store the data. The first thing to do is create an empty database
to hold our table. For this tutorial, I name the database linksdb. In MySQL you
use the following command from the command line to create the new database.
mysqladmin -p create linksdb
The "-p" flag is used so it will prompt you for your password to MySQL. Most setups require a username and password for security reasons. Set up the username and password using the mysqladmin tool. You can read how in the MySQL doumentation that is part of your installation or read it online at the MySQL site. It is fairly straightforward, however you may need root access.
Test connecting to the database using the mysql client. On the command line:
mysql -p linksdb
You should get a mysql> prompt if it connects to the database. Else, it returns an error saying Unknown database 'linksdb'
Creating the Database TableNext, we need to create the table in the database. Tables are created using SQL statements, and can be created using the MySQL client tool or PHP. The nice thing about using a PHP script to create the table is you can save the script to use later. If something goes wrong, you can re-create the tables. Or, you can use it to refer to the database schema.
The SQL command to create a table is:
CREATE TABLE tablename (
The table we want to create is:
Table Name: links Columns: id (integer - primary link key) sitename (50 characters) siteurl (75 characters) description (text field - lots of text) category (50 characters)
The SQL code to create the categories table is:
CREATE TABLE links (
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id) );
The id column is the primary key for this table. In order to be a primary key, the column can not be null (NOT NULL). I also set it to automatically increment the number (AUTO_INCREMENT) so when each record is added, the id will increase by one. The last line specifies that the id column will be the primary key for this table. A primary key is a unique number for that specific record or row of data.
The sitename, siteurl, and category column each are specifed as a VARCHAR or variable character field each with their respective maximum length. A VARCHAR field holds characters from 0 up to its maximum specified length. Most databases VARCHAR limit is 255 characters. If you have a field which may require more characters you should use a TEXT field, which is what is used for the description field. A text field can hold lots of data, usually dependent on the database.
The most common data types are INT, VARCHAR, TEXT and DATETIME. Look in the MySQL documentation about creating databases for information about other available datatypes in MySQL, and more specifics about the data types it supports.
Executing SQL in PHPThe code to execute a SQL statement in PHP has 3 steps:
1.The command to connect to the database is:
$cid = mysql_connect($host,$usr,$pwd);
Where $host, $usr, and $pwd are previously assigned. Host ($host) refers to the machine running MySQL. Username ($usr) and password ($pwd) connect to that MySQL machine. This command returns a connection id, which is used to identify this connection in later queries.
2.Creating the SQL statement simply consists of assigning the SQL statement such as the one above to a string. I usually have it run over multiple lines, concatanating the string together as I go. This makes it easier to read.
3.The command to actually send the database the SQL command is:
$result = mysql_db_query($db,"$SQL",$cid);
Where $db is the database to query, $SQL is the SQL statement, and $cid is the connection id created above. This returns a 1 if executed correctly, and undefined or false if an error occurred.
Putting all of this together, and adding a little error detection and displaying of the errors gives us the following script, which you can download and load on to your web server running PHP. Note: Opening the file through the web server will execute the script and create the database tables.
Download: PHP Script to create tables
Troubleshooting The most common problem when running this script is connecting to the database. Make sure you replace the username and password variables with your username and password setup to connect to your MySQL server. You can troubleshoot connection problems using the mysql client from the shell, use mysql -? for help.
Test Google Ads Ver. 2
Free Linux Admin Books
Free Linux Books for Programmers
Free Books for Linux on the Desktop
Free PHP Books
Free Java Books - Advanced
Free Java Books - Basic
Free Perl Books
Free Python Books
Quote of the Day (PHP)
Debugging Part 2
How to Test Google Ads
Most Popular Pages
Baby Name Generator
U.S. Name Generator
Wu Name Generator
Upload and Resize an Image (PHP)
How To Install Apache + PHP + MySQL
Intro to Web Databases (PHP, MySQL)
Least Popular Pages
iNews Applet (Java)
Site Monitor (Perl)