MySQL is a database. A database is a data storage feature. It can be used to store, sort, arrange, and display information. MySQL is a functional feature on it's own. For our tutorials, we will be using PHP commands to use the functions of a MySQL database.
phpMyAdmin is a graphical interface program that allows you to use the functions of a MySQL database. Some hosts may have this program available.
MySQL is a data storage area. In this storage area, there are small sections called TABLES. Very similar to a normal HTML table, the MySQL tables consist of rows, columns, and cells.
You can learn about MySQL from their source website here...
http://www.mysql.com
You can learn about phpMyAdmin from their source website here...
http://www.phpmyadmin.net/home_page/.
Other tutorials will go through the process of installing and operating the MySQL database. We will assume you have an online host that supports and offers a MySQL database for your site.
The first part, and most important, of a database is the planning. What kind of information are you going to be working with? What type of table structure would best suit it? Will you need to use more than one table format?
Without knowing much about how MySQL really works, it may be hard to do all of this planning. Before going head on into programming a database, take a step back and think about what you are using the database for.
An address book application may need only one table. It can hold columns representing name, address, phone number, and so forth.
Address_Book
name address phone number
Some elaborate applications may entail many tables. One that will hold product information, one to hold buyer information, one to hold seller information, one to hold catagories information, and so on.
My_Store
Catagory number Description Quantity
Vendor number Company Name Contact number
Buyer number Company Name Contact number
Take a pencil and paper, sit down for a while, and try to organize the layout. Draw a grid of rows and columns. Put titles in the top of the columns. Start a new grid page if you have a new catagory.
This can be the hardest part of creating a database. Don't worry about the exact layout. It may change as you progress through the database creation anyways. Just try to have a good idea on the kind of layout you would like to achieve.
Before getting to far into planning, there is a number of words that you cannot use for your table and column names. MySQL uses some specific words for command processing, so using them in your own coding may confuse things a bit.
ADD ALL ALTER
ANALYZE AND AS
ASC ASENSITIVE AUTO_INCREMENT
BDB BEFORE BERKELEYDB
BETWEEN BIGINT BINARY
BLOB BOTH BY
CALL CASCADE CASE
CHANGE CHAR CHARACTER
CHECK COLLATE COLUMN
COLUMNS CONDITION CONNECTION
CONSTRAINT CONTINUE CREATE
CROSS CURRENT_DATE CURRENT_TIME
CURRENT_TIMESTAMP CURSOR DATABASE
DATABASES DAY_HOUR DAY_MICROSECOND
DAY_MINUTE DAY_SECOND DEC
DECIMAL DECLARE DEFAULT
DELAYED DELETE DESC
DESCRIBE DETERMINISTIC DISTINCT
DISTINCTROW DIV DOUBLE
DROP ELSE ELSEIF
ENCLOSED ESCAPED EXISTS
EXIT EXPLAIN FALSE
FETCH FIELDS FLOAT
FOR FORCE FOREIGN
FOUND FRAC_SECOND FROM
FULLTEXT GRANT GROUP
HAVING HIGH_PRIORITY HOUR_MICROSECOND
HOUR_MINUTE HOUR_SECOND IF
IGNORE IN INDEX
INFILE INNER INNODB
INOUT INSENSITIVE INSERT
INT INTEGER INTERVAL
INTO IO_THREAD IS
ITERATE JOIN KEY
KEYS KILL LEADING
LEAVE LEFT LIKE
LIMIT LINES LOAD
LOCALTIME LOCALTIMESTAMP LOCK
LONG LONGBLOB LONGTEXT
LOOP LOW_PRIORITY MASTER_SERVER_ID
MATCH MEDIUMBLOB MEDIUMINT
MEDIUMTEXT MIDDLEINT MINUTE_MICROSECOND
MINUTE_SECOND MOD NATURAL
NOT NO_WRITE_TO_BINLOG NULL
NUMERIC ON OPTIMIZE
OPTION OPTIONALLY OR
ORDER OUT OUTER
OUTFILE PRECISION PRIMARY
PRIVILEGES PROCEDURE PURGE
READ REAL REFERENCES
REGEXP RENAME REPEAT
REPLACE REQUIRE RESTRICT
RETURN REVOKE RIGHT
RLIKE SECOND_MICROSECOND SELECT
SENSITIVE SEPARATOR SET
SHOW SMALLINT SOME
SONAME SPATIAL SPECIFIC
SQL SQLEXCEPTION SQLSTATE
SQLWARNING SQL_BIG_RESULT SQL_CALC_FOUND_ROWS
SQL_SMALL_RESULT SQL_TSI_DAY SQL_TSI_FRAC_SECOND
SQL_TSI_HOUR SQL_TSI_MINUTE SQL_TSI_MONTH
SQL_TSI_QUARTER SQL_TSI_SECOND SQL_TSI_WEEK
SQL_TSI_YEAR SSL STARTING
STRAIGHT_JOIN STRIPED TABLE
TABLES TERMINATED THEN
TIMESTAMPADD TIMESTAMPDIFF TINYBLOB
TINYINT TINYTEXT TO
TRAILING TRUE UNDO
UNION UNIQUE UNLOCK
UNSIGNED UPDATE USAGE
USE USER_RESOURCES USING
UTC_DATE UTC_TIME UTC_TIMESTAMP
VALUES VARBINARY VARCHAR
VARCHARACTER VARYING WHEN
WHERE WHILE WITH
WRITE XOR YEAR_MONTH
ZEROFILL
Once you have your table data organized, the next step is to figure out the data type. There are three main types : text, numbers, and Dates/Times. Choosing the column types specifies what information can or can't be stored in a table cell. Using the most correct option for each column is important as it may affect the database's overall performance.
TEXT TYPES
CHAR( ) A fixed section from 0 to 255 characters long.
VARCHAR( ) A variable section from 0 to 255 characters long.
TINYTEXT A string with a maximum length of 255 characters.
TEXT A string with a maximum length of 65535 characters.
BLOB A string with a maximum length of 65535 characters.
MEDIUMTEXT A string with a maximum length of 16777215 characters.
MEDIUMBLOB A string with a maximum length of 16777215 characters.
LONGTEXT A string with a maximum length of 4294967295 characters.
LONGBLOB A string with a maximum length of 4294967295 characters.
The ( ) brackets allow you to enter a maximum number of characters will be used in the column.
VARCHAR(20)
CHAR and VARCHAR are the most widely used types. CHAR is a fixed length string and is mainly used when the data is not going to vary much in it's length. VARCHAR is a variable length string and is mainly used when the data may vary in length.
CHAR may be faster for the database to process considering the fields stay the same length down the column. VARCHAR may be a bit slower as it calculates each field down the column, but it saves on memory space. Which one to ultimatly use is up to you.
Using both a CHAR and VARCHAR option in the same table, MySQL will automatically change the CHAR into VARCHAR for compatability reasons.
BLOB stands for Binary Large OBject. Both TEXT and BLOB are variable length types that store large amounts of data. They are similar to a larger version of VARCHAR. These types can store a large piece of data information, but they are also processed much slower.
NUMBER TYPES
TINYINT( ) -128 to 127 normal
0 to 255 UNSIGNED.
SMALLINT( ) -32768 to 32767 normal
0 to 65535 UNSIGNED.
MEDIUMINT( ) -8388608 to 8388607 normal
0 to 16777215 UNSIGNED.
INT( ) -2147483648 to 2147483647 normal
0 to 4294967295 UNSIGNED.
BIGINT( ) -9223372036854775808 to 9223372036854775807 normal
0 to 18446744073709551615 UNSIGNED.
FLOAT A small number with a floating decimal point.
DOUBLE( , ) A large number with a floating decimal point.
DECIMAL( , ) A DOUBLE stored as a string , allowing for a fixed decimal point.
The integer types have an extra option called UNSIGNED. Normally, the integer goes from an negative to positive value. Using an UNSIGNED command will move that range up so it starts at zero instead of a negative number.
DATE TYPES
DATE YYYY-MM-DD.
DATETIME YYYY-MM-DD HH:MM:SS.
TIMESTAMP YYYYMMDDHHMMSS.
TIME HH:MM:SS.
MISC TYPES
ENUM ( ) Short for ENUMERATION which means that each column may have one of a specified possible values.
SET Similar to ENUM except each column may have more than one of the specified possible values.
ENUM is short for ENUMERATED list. This column can only store one of the values that are declared in the specified list contained in the ( ) brackets.
ENUM('y','n')
You can list up to 65535 values in an ENUM list. If a value is inserted that is not in the list, a blank value will be inserted.
SET is similar to ENUM except SET may contain up to 64 list items and can store more than one choice.
A column modifier is an extra addon command to help the database organize and work better.
As mentioned on the previous page, the integer types have an extra option called UNSIGNED. Normally, the integer goes from an negative to positive value. Using an UNSIGNED command will move that range up so it starts at zero instead of a negative number.
Other modifier commands are : INDEX, UNIQUE, PRIMARY KEY, AUTO_INCREMENT, NULL, NOT NULL, DEFAULT, BINARY, and ZEROFILL.
Indexing is a way to improve a database performance. You are telling the database that THIS specific column is special and may help organize the data. If there is a column you will be referring to often, it would probably be the best to be an INDEX column.
An INDEX column may have more than one cell holding the same data value. The two other index types are UNIQUE and PRIMARY KEY. UNIQUE states that each cell in the column should have a unique value. PRIMARY KEY is a special variety of the UNIQUE command.
The AUTO_INCREMENT modifier works on any of the integer types. Each time a new row is added into the database table, the number in this column will appear and automatically increase by one from the previous row.
NULL is no value. It is not space, it is not zero. A majority of the time you will want to specify a field to be NOT NULL so that any blank entries will be considered as "something".
DEFAULT will assign a default value to a cell if nothing is entered for the value. It will work on most data types except BLOB and TEXT.
BINARY is used with CHAR and VARCHAR types. It causes the values to be treated as "binary strings" making them CaSe SeNsItIvE.
ZEROFILL is used with numeric data types. It will display leading zeros of a number based on the display width.
Is you head spinning from reading over all of the preliminary planning and trying to figure out which data types to use? On to some actual coding... but first... you need to know or find out a bit more information called host, username, and password.
The HOST value is usually set as localhost. You may want to check your host server's FAQ/HELP area or email them to be sure.
The USERNAME and PASSWORD are relatively easy considering they are the same ones you use to log onto your hosting account normally.
Now you should be ready to create your first database with the help of PHP. This example shows three parts to do this.
The first section declares your basic variables. These let you easily use them throughout the rest of the coding.
The second section connects to the database server. We are using a variable to activate a MySQL command. The variable will hold a positive or negative value depending on the successful connection or not. mysql_connect is the database command to connect to the database server using the initial settings.
The third section is doing a few things. First it is altering the value of the database name. On some systems, the databases are arranged in a "UserName_DataBaseName" setup. Thus to be compatable, the script is creating this effect for us. The example will produce a database file called "david_test".
The next line is using an IF statement. If the statement holds a true value (being a negative result from the query), it will stop the current script (die) and print out the specific SQL error. mysql_query is the command being used to activate the specified information which happens to be to CREATE a DATABASE called test. As long as this line did not "die", the last echo command will print out a successful creation.
If that is all successful, this is what you will have so far visually...
david_test
You have your database area created, but that is just giving a name to the area. It is a big blank space. The next step is to put at least one table in it.
Tables are the main part of a database. A database will hold one to many tables depending on what you are trying to create.
Just like a normal HTML table, a MySQL table is a big square divided into rows and columns. Each square is called a cell or field.
Each table in a database needs a title or name.
Each column in a table needs a title or name.
There are 4 sections to the above example coding. The first two sections initialize the main variable information and use it to connect to the database server.
The third section tells the database server which database we want to work with. In this case, it is the "david_test" database we created on the previous tutorial page. The IF statement will stop the script and print out the SQL error if the mysql_select_db command produces a false result. If there is a positive result, the script will continue on.
The last section will create the TABLE giving it a name. It also creates the columns giving them a name and declaring what type of data will be entered into them. Taking a closer look...
$result="CREATE TABLE address_book (first_name VARCHAR(25), last_name VARCHAR(25), phone_number VARCHAR(15))";
A variable $result is being used to store the information for the up coming query command. This information can be entered directly into the query command itself, but storing it into a variable makes it easier to control and edit.
CREATE TABLE address_book( ) will create a table named "address_book" and create the following columns in it. The comma seperated list in the brackets will become the columns in the table. first_name VARCHAR(25) will name the first column as "first_name" and states that the data entered into this column will probably be a VARiable CHARacter length being 25 characters maximum. And so on for the second and third columns.
The IF statement will execute the actual mysql_query using the information stored in the variable. It will print out a success or failure depending on the results.
If that is all successful, this is what you will have so far visually...
david_test
address_book
first_name
VARCHAR(25) last_name
VARCHAR(25) phone_number
VARCHAR(15)
Before getting any further into database coding, it will be good practice to call upon the PHP command of include.
The first part of server connection, database selection, and table selection is a repeated snippit, so to save time and effort, we'll put that part into a seperate file and just call it into play when we need it.
db_connect.inc
The PHP command to call that section of code into action :
include ("db_connect.inc");
The INSERT command is used to enter new information into a database. A common way to gather information is using a form.
form_page.php
Once the SUBMIT button is clicked on, the information entered will pass over to the "enter_it.php" page through the $_POST[ ] variable.
enter_it.php
";
echo "Click here to return to the form page.";
?>
To explain the new coding snippit :
$query="INSERT INTO address_book (first_name,last_name,phone_number) VALUES ('$_POST[first_name]','$_POST[last_name]','$_POST[phone_number]')";
We are using a variable $query to contain the command information we want to perform. This keeps our coding easy to maintain.
$query="INSERT INTO address_book (first_name,last_name,phone_number) VALUES ('$_POST[first_name]','$_POST[last_name]','$_POST[phone_number]')";
INSERT INTO prepares the database for new incoming data. address_book is stating which table is going to receive the new information and what columns (by name) will be affected.
$query="INSERT INTO address_book (first_name,last_name,phone_number) VALUES ('$_POST[first_name]','$_POST[last_name]','$_POST[phone_number]')";
The VALUES of the following variables contain the new information to be entered into the specified columns.
if(!mysql_db_query($dbname,$query,$link_id)) die(mysql_error());
IF the following value is not true, the script will stop (die) and print out the error.
mysql_db_query is a command used to perform the actual insert command. it uses the database name, query command, and server link to make it all happen.
Query, as in, to ask a question. Working with a database, this concept will be put to very good use.
On the INSERT tutorial page, you've already seen a QUERY in action. The query asked the database to insert the new information into the specified database table.
The SELECT property can be used in a mysql_query command to choose specific information in the database table.
$sql = mysql_query("SELECT * FROM table_name");
The variable $sql is being used to find the information in the address_book table. The * is a symbol meaning "all". It will look at all of the records FROM the specified table.
To look at each row in the specified area, the next step is to use a loop and the mysql_fetch_row command with the SELECT information.
while ($row = mysql_fetch_row($sql)) {
echo "$row[0] $row[1] $row[2]
";
}
WHILE the statement is TRUE, the loop will continue.
The mysql_fetch_row command will take the supplied information and read the data from the next available row of the database.
The ECHO command being used to display the data during each row loop is a simple one for example purposes. The columns of a database are numbered starting at zero. The variable $row is assigned the current row contents during each loop.
david_test
address_book
first_name
VARCHAR(25)
0 last_name
VARCHAR(25)
1 phone_number
VARCHAR(15)
2
row1 data row1 data row1 data
row2 data row2 data row2 data
row3 data row3 data row3 data
row4 data
The WHERE property of a query makes it possible to single out specific rows.
$sql = mysql_query("SELECT * FROM table_name WHERE some equation");
while ($row = mysql_fetch_row($sql)) {
echo "$row[0] $row[1] $row[2]
";
}
The query will only pick out the rows that provide a TRUE result according to the WHERE equation. Example...
$sql = mysql_query("SELECT * FROM address_book WHERE first_name='David'");
while ($row = mysql_fetch_row($sql)) {
echo "$row[0] $row[1] $row[2]
";
}
The query would search the address_book table and compare all of the data in the first_name column for the specified value. The WHILE loop then prints out the results found.
The equation used for the WHERE property can be a large number of possibilities. You can use variables, additional equations with &&, alternative equations with ||, and so on.
CONDITIONALS are operators which allow you to get more specific (or general) in your query.
= equals
< less than
> greater than
<= less than or equal to
>= greater than or equal to
!= not equal to
IS NOT NULL has any value
IS NULL has no value at all
BETWEEN within a specified range
NOT BETWEEN outide of a specified range
OR one of two equations are true
|| same as OR
AND both equations are true
&& same as AND
NOT both equations are false
! same as NOT
$sql = mysql_query("SELECT * FROM table_name WHERE some equation && another equation");
while ($row = mysql_fetch_row($sql)) {
echo "$row[0] $row[1] $row[2]
";
}
The query will only pick out the rows that provide a TRUE result according to the WHERE equation. In this case, there would be TWO parts that must be true to return a result. Example...
$sql = mysql_query("SELECT * FROM address_book WHERE first_name='David' && last_name='Stanley'");
while ($row = mysql_fetch_row($sql)) {
echo "$row[0] $row[1] $row[2]
";
}
The query would search the address_book table and compare all of the data in the first_name column AND the last_name column for the specified values. The WHILE loop then prints out the results found if both equations are found TRUE.
Doing a search through data is easy enough when the equations produce a definite yes or no situation. There may be times though you will want to perform a more general search.
The LIKE and NOT LIKE have two search helper symobls. The underscore _ character that looks for one character and the percentage % character that looks for zero or more characters.
$sql = mysql_query("SELECT * FROM table_name WHERE columnname LIKE value%");
while ($row = mysql_fetch_row($sql)) {
echo "$row[0] $row[1] $row[2]
";
}
The query will only pick out the rows that provide a TRUE result according to the WHERE equation. The equation will equal the LIKE VALUE plus some possible extra characters afterwards. Example...
$sql = mysql_query("SELECT * FROM address_book WHERE last_name LIKE 'Stan%'");
while ($row = mysql_fetch_row($sql)) {
echo "$row[0] $row[1] $row[2]
";
}
The query would search the address_book table and compare all of the data in the last_name column for any values starting with 'Stan' and ending with zero or more characters afterwards. The LIKE search is not case sensitive, so it will accept anything starting with 'stan' as well. The WHILE loop then prints out the results found if both equations are found TRUE.
Queries using the LIKE or NOT LIKE parameters may be a bit slower than a normal query search considering they are a broader value and do not take advantage of any indexing.
The underscore and percentage characters (also known as wildcard characters) can be used in front, at the end, or both ends of a value.
If you want to have an underscore or percentage character actually be part of the search value, put an escape slash \ in front of the character.
The underscore wildcard can be used a number of times to find a specific number of characters. Example, this would be used in an equation to return a value of 'Stan' plus 3 characters (since there are 3 underscores)...
$sql = mysql_query("SELECT * FROM address_book WHERE last_name LIKE 'Stan___'");
while ($row = mysql_fetch_row($sql)) {
echo "$row[0] $row[1] $row[2]
";
}
As you may have guessed, the NOT LIKE parameter will do the opposite of LIKE. It will produce results that are NOT LIKE the specified criteria.
Many people refer to a database as a "relational" database. More than one table is in relation to another table. Two smaller pieces of information that make a whole when combined.
There are going to be times when you will want to take a bit of information from two (or more) tables with a certain criteria. Using an address book for example, you may have one table of friends and another table of how much money they owe you. You can set the criteria to find a specific criteria that appears in both tables and produce the results. You are "joining" the information from both tables to create a full result.
Address Database
phone
first_name ph
Christine 516.6785
Chris 418.2594
David 725.1581
Jennifer 458.2158
Lorna 942.4827
owes
first_name money
Chris 5
Lorna 5
George 10
Chris 2
Sam 10
A popular join is called the INNER JOIN.
$sql = mysql_query("SELECT * FROM phone, owes WHERE phone.first_name=owes.first_name");
while ($row = mysql_fetch_row($sql)) {
echo "$row[0] $row [1] $row[2] $row[3]
";
}
The above example will compares or joins the two tables PHONE and OWES. It will look in the column called FIRST_NAME in the PHONE table and it will look in the column called FIRST_NAME in the OWES table. If any information matches in those two columns in the seperate tables, it will be put into the results.
In total, there are 4 columns of information being looked at. The above example is printing out all of the columns just to show which information is being passed into the results. You may choose exactly which data to use and display as required for a real application.
The important part of an INNER join is that if no matches are found, no results are created.
Another form of join is called the LEFT JOIN. It behaves in the same way as the inner join, but it will produce extra results if there is a match in one table but not the other.
$sql = mysql_query("SELECT * FROM phone LEFT JOIN owes ON phone.first_name=owes.first_name");
while ($row = mysql_fetch_row($sql)) {
echo "$row[0] $row[1] $row[2] $row[3]
";
}
The comma between the table names have been replaced by the words LEFT JOIN. The word WHERE has been replaced by the word ON.
During an inner join, only records matching in both tables would be placed into the results. During a left join, all of the records of the first table will be placed into the results. If the records match in the second table, they will be added into the results otherwise a NULL result will be added.
