web blazonry web development scripts and tutorials Get best price on Samsung Galaxy Note 4, Charcoal Black 32GB (Verizon Wireless)
   PHP       Name Generators       Perl       CSS       Javascript       Java       MySql       How Tos       Resources   

MySQL Home

MySQL Tutorial
  Getting Started
  Create Database
  Insert Data
  View Data
  Manage Data

Bookmark and Share





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 Table

Next, 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 (
column1 column1type,
column2 column2type,
etc.... )

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,
sitename VARCHAR(50),
siteurl VARCHAR(75),
description TEXT,
category VARCHAR(50),
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 PHP

The code to execute a SQL statement in PHP has 3 steps:
  1. Connect to database
  2. Create statement
  3. Execute statement

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 (create_table.phps)
Note: You must edit the username and password at the top of this script to reflect your system.

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.

Next: Putting data into the database

Related Links:



MySQL Documentation
Information on how to use MySQL, and examples on how to create databases and tables using the MySQL client tools.

Introduction to SQL
by James Hoffman. One of the best tutorials on the internet regarding SQL. It says Introduction to SQL but I have not needed anything that is not covered in this tutorial.

Tutorial Sections

 

Newest Pages
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 JavaScript 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
Popup Windows (JavaScript)
Upload and Resize an Image (PHP)
How To Install Apache + PHP + MySQL
Intro to Web Databases (PHP, MySQL)

Least Popular Pages
iNews Applet (Java)
Java Resources
Site Monitor (Perl)
PHP Resources
 
 

  privacy policy     ||     © 1997-2016. astonishinc.com   All Rights Reserved.