An Introduction to MySQL
- Naming Database Elements
- Choosing Your Column Types
- Choosing Other Column Properties
- Accessing MySQL
- Review and Pursue
Save 35% off the list price* of the related book or multi-format eBook (EPUB + MOBI + PDF) with discount code ARTICLE.
* See informit.com/terms
Save 35% off the list price* of the related book or multi-format eBook (EPUB + MOBI + PDF) with discount code ARTICLE.
* See informit.com/terms
This chapter is from the book
This chapter is from the book
This chapter is from the book
Because this book discusses how to integrate several technologies—primarily PHP, SQL, and MySQL—a solid understanding of each is important before you begin writing PHP scripts that use SQL to interact with MySQL. This chapter is a departure from its predecessors in that it temporarily leaves PHP behind to delve into MySQL.
MySQL is the world’s most popular open source database application (according to MySQL’s web site, www.mysql.com) and is commonly used with PHP. The MySQL software comes with the database server that stores the actual data, different client applications for interacting with the database server, and several utilities. In this chapter, you’ll see how to define a simple table using MySQL’s allowed data types and other properties. Then you’ll learn how to interact with the MySQL server using two different client applications. This information will be the foundation for the SQL taught in the next chapter.
Naming Database Elements
Before you start working with databases, you have to identify your needs. The purpose of the application (or web site, in this case) dictates how the database should be designed. With that in mind, the examples in this chapter and the next will use a database that stores some user registration information.
When creating databases and tables, you should come up with names (formally called identifiers) that are clear, meaningful, and easy to type. Also, identifiers
Should only contain letters, numbers, and the underscore (no spaces)
Should not be the same as an existing keyword (like an SQL term or a function name)
Should be treated as case-sensitive
Cannot be longer than 64 characters (approximately)
Must be unique within its realm
This last rule means that a table cannot have two columns with the same name and a database cannot have two tables with the same name. You can, however, use the same column name in two different tables in the same database; in fact, you often will do this.
As for the first three rules, I use the word should, as these are good policies more than exact requirements. Exceptions can be made to these rules, but the syntax for doing so can be complicated. Abiding by these suggestions is a reasonable limitation and will help avoid complications.
To name a database’s elements:
Determine the database’s name.
This is the easiest and, arguably, least important step. Just make sure that the database name is unique for that MySQL server. If you’re using a hosted server, your web host will likely provide a database name that may or may not include your account or domain name.
For this first example, the database will be called sitename, since the information and techniques could apply to any generic site.
Determine the table names.
The table names just need to be unique within this database, which shouldn’t be a problem. For this example, which stores user registration information, the only table will be called users.
Determine the column names for each table.
The users table will have columns to store a user ID, a first name, a last name, an email address, a password, and the registration date. Table 4.1 shows these columns, with sample data, using proper identifiers. Because MySQL has a function called password, I’ve changed the name of that column to just pass. This isn’t strictly necessary but is really a good idea.
Table 4.1 users Table
Column Name |
Example |
user_id |
834 |
first_name |
Larry |
last_name |
David |
ld@example.com |
|
pass |
emily07 |
registration_date |
2017-08-31 19:21:03 |
For the user_id column, there are two common approaches. Some use simply id as the identifying column name in any table so that all tables have an id column. Others use a variation on tablename_id: user_id or users_id.