Tutorials MySQL Database Basics

Database Basics

This tutorial will focus on MySQL. MySQL is a database that is very popular with web servers and it works very well with PHP.

What is a database?

Let's get the basics down first. A database is a bunch of tables. If you ever used Microsoft Excel, it's basically the same thing. You have different columns and rows and every table has its own name.

| id | name | email | age ||----+------+-------+-----|| 1  | Dave | a@b.c | 10  || 2  | Lisa | 1@2.3 | 15  || 3  | Mike | A@B.C | 20  |

Before you setup

Every database needs at least one primary key. This key has to be different for every row. It is accepted and expected in the industry to name this row "id", make it a integer, and let it auto-increment. Then you can forget all about it.

I've created a MySQL database. How do I create a new table?

After you create a new database, you can either create a new table inside phpMyAdmin or code it with PHP. I'll show you how to create a table in PHP another time. To create it in phpMyAdmin, choose your database and find the create new table form. Enter your table name and number or columns and click go.

A picture is worth a thousand words.....

Your phpMyAdmin might look a bit different from mine.

Create new table form:

Enter column details:

What do the fields mean?

Field Name of column.
Type The allowed characters in field. (more info below)
Length The maximum number of characters in the column.
Default The default value if no value is assigned.
Collation The encoding of the characters. No need to change unless you're not using the alphabet. eg. Chinese or Japanese
Attributes Determines binary information. You can leave this alone most of the time.
Null Determines if you allow the column to be empty. If you don't use null and don't have a value, default, or auto-increment, then it assigns a 0.
Index Choose "primary" for primary key (every table should have one primary key, no more no less), "unique" if you want every row's value to be different, "index" for increasing database speed at the cost of performance (more on this in a later tutorial), "fulltext" for increasing database speed (similar to index but for searches with match and against), otherwise leave blank
AUTO_INCREMENT This will automatically assign a different number to every new row, which is very useful for primary keys.
Comments Your own comments for the particular column.

What are the different "type" fields?

You only really need to learn the different integers and texts. Note that it is still possible to put numbers in text types.

VARCHARHolds about 255 characters. Newer versions of MySQL have a larger capacity but you should just use text for long strings.
TINYTEXTMaximum 256 bytes.
TEXTMaximum 65535 bytes.
MEDIUMTEXTMaximum 16777215 bytes.
LONGTEXTMaximum 4294967295 bytes.
TINYINTHolds a number between 0 to 255 unsigned, or -128 to 127 signed.
SMALLINTHolds a number between 0 to 255 unsigned, or -32768 to 32767 signed.
MEDIUMINTHolds a number between 0 to 16777215 unsigned, or -8388608 to 8388607 signed.
INTHolds a number between 0 to 4294967295 unsigned, or -2147483648 to 2147483647 signed.
BIGINT Holds a number between 0 to 18446744073709551615 unsigned, or -9223372036854775808 to 9223372036854775807 signed.

PS. If you don't know what signed or unsigned is, you have probably left it blank and you can use the unsigned value as unsigned is default.

Note: This is not the complete list. This is my shortened version for 80% of needs. For the full list, visit: MySQL.

Should I use varchar or text?

It's like asking what's the best version of Windows. You're going to get a different answer every time. This debate goes back to over a decade ago when servers were expensive and slow. Varchar was a easier way to handle small amounts of data but couldn't take large chunks (prior to PHP 5.0, varchar could only hold 255 bytes). These days with cheap servers, you can safely do everything with text.

My personal preference is to use varchar for anything less than 200 characters, and use text for anything more.

What are the bare minimum requirements?

The inputs that you must enter are "field" and "type". The rest are optional but it's recommended that you fill it out to the best of your ability. Setting a length will decrease your server load which will decrease your bandwidth and speed up your page loads.

Why use tinyint when you can use bigint? Why set a length?

Smaller types means smaller requirements. It is possible to set varchar for all your types and leave the length and your database would still work, but your bandwidth will take a major strain and the page will load very slowly. There is no reason to use bigint if tinyint is enough because you can change the column detail anytime.

How do I access the database?

You can use PHP to connect to your database. Your hostname is usually "localhost". The rest of the information you get when you create your database.

<?php// Database info$hostname   = 'localhost';$dbusername = 'admin';$dbpassword = '123';$dbname     = 'superdatabase';// Connect to Database$link = new PDO('mysql:host='.$hostname.';dbname='.$dbname, $dbusername, $dbpassword);// Show errors for debugging$link->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);?>

How do I close the database?

This part is extremely important, especially if you're displaying the page to the general public. Most servers and scripts close the database connection after the script is finished, but better safe than sorry. Closing your database only requires you to override the connection.

$link = NULL;

And that's all for today folks. Try to setup your own database and play around. I'll show you how to put it to use with PHP next time.

Posted by on . Category: MySQL


No comments posted yet

You need to register or login to post new comments.