What is MySQL?
The engine of any modern, CMS website is run on the functionality of its database. One of the most popular Open Source database programs in the world is the MySQL database, the last three letters of which stand for Structured Query Language, is the most widely used relational database management system on the Internet primarily because it allows a website to store information in a relatively straightforward yet easily adaptable framework. MySQL is used in both content management systems such as WordPress and Drupal as well as commercial sites like Facebook.
Many programmers use a third-party language such as PHP to write queries that bridge the gap between the user interface of their website and the relational database. Content management systems frequently create tools or other php programming shortcuts that allow users to simply click a button in order to perform an action instead of having to write out the code by hand. However, this relational database management system has its own specific language that allows users to design tables, fields, and relational structures between tables. This relational database also provides a set number of commands that allow information to be inserted, edited and deleted from the larger set of information.
MySQL 101: Creating A Table
In order to briefly illustrate how the system works, we will use the example of a website that allows users to sign up to receive a newsletter. In order to receive this newsletter, the users must first create an individual account that will allow them to login to the website. They must submit their name, email address, and password.
The programmer must first create what is known as a “table” in order to hold this information. Tables act like folders in a filing cabinet. Just as a filing cabinet may have multiple folders, some of which cross-reference each other, so a MySQL database can have multiple tables that frequently use similar fields in order to link informational requests. However, each table must be designed to contain information about a specific topic.
The account table will have four fields for the user’s name, email address, password and their primary key ID. This ID tag is mandatory for every table and helps distinguish one line of information from another. As an example, if two Joe Smiths sign up to receive the newsletter, they will each have a separate ID number in the table. While they will probably have different email addresses and passwords, having a signature key for each piece of information is one of the features that makes MySQL an excellent database and allows it to mitigate any confusion. This primary key setting can be customized. As an example, the programmer may decide that each person who signs up for his newsletter should receive a “reader ID” that will allow him to target that person for future promotional campaigns.
It’s important to note that the programmer may choose to break up the name into two distinct fields, FirstName and LastName. In this case, the table would have five fields. Most professional websites design their tables so that information is stored in as small of a unit as possible. Having one field for both a first and last name can make performing more specific queries difficult, since they will only be able to search by the user’s full name. However, each programmer can design his tables however he wishes.
Each field can be further customized to accept certain types of characters, such as numbers only (INT), a string of numbers and letters of a fixed length (CHAR) or string that can have flexible lengths (VARCHAR). The programmer is allowed to specify the field length in terms of characters. CHAR should be used for a field such as a state abbreviation or zip code; VARCHAR should be used for a name field.
Once a field in a table has been created, that field can be referenced in a separate table. Imagine that the website in the example wants to add a table that allows people who have signed up for the newsletter to receive discounted offers on certain products. The “reader ID” field could be inserted into a new table that contains a list of “promotional campaigns.”
Interfacing MySQL Database With Your Website
After the table is designed, the programmer must then create a script that allows a user on the web to insert his information into the database so that he may receive his newsletter. Traditionally, each script has a preamble that contains the commands for opening up the connection. This command contains the order to connect along with the specific host, password and authorized name of the owner. It looks something like this:
Mysql_connect(“hostname”, “admin_name”, “admin_password”) or die(mysql_error()); Mysql_select_db(“newsletter_website”) or die(mysql_error());
The programmer then writes the “INSERT” code that transfers the user’s name, desired password and email from the website into the appropriate fields in the table. The user-entered information is referred to as “VALUES.” If the information does not match the requirements of the field in the table, an error message will result. As an example, a user can’t enter two numbers into a field that is designed to hold the information for a lettered state abbreviation.
Endless Possibilities with MySQL Database
The more a programmer experiments with creating and cross-referencing tables, the more he will be able to create customized tables that allow him to design increasingly complex websites. There are few very limits to what MySQL can do.