MySQL is known as the world’s most popular open source database system that uses structured query language (SQL). SQL is the most popular language for managing any content in a database. Many large companies and organization rely on MySQL including Facebook, google, and Adobe.
So what is it used for?
MySQL is used to store and retrieve data. We can think of it like a spread sheet. Inside any given database we would have tables and inside that table we would have various columns. So imagine storing product information. We would create a “products” table and inside that table the following columns: ID, title, description, price. Each column needs to be defined as a specific type and there are many types to choose from such as: INT, varchar, text, date. Defining an INT will format that column to only accept numeric values where a text column will accept any character. Varchar columns are similar to text columns however you must supply a character limit and lastly a date column will format to a date.
Creating our table with PHP
mysql_query('CREATE TABLE IF NOT EXISTS products (
ID int NOT NULL AUTO_INCREMENT,
title varchar(250),
description text,
price decimal(10,2),
PRIMARY KEY (banner_id)
)
');
The above code is fairly straight forward and you will notice quickly MySQL reads similarly to English. So what’s happening here? We are creating the table products if it has not already been created and defining 4 fields: ID, title, description, and price. Our ID field is an integer, can never have a NULL(empty) value, and will always increase by 1 every time a new entry Is added. Our title is a Varchar with a max length of 250 characters. Our description is a text field and lastly our price is defined as a decimal allowing 10 leading numbers and 2 decimal places. In turn the price can never be higher than 9999999999.99.
Now what?
Running with the products theme we will probably want to create a simple html form allowing us to add a product. Since we are discussing MySQL I will assume you know how to create a form that submits to a PHP script.
INSERT INTO products (ID,title,description,price) VALUES(null,”’.$_POST[‘title’].’” ,”’.$_POST[‘description].’” ,”’.$_POST[‘price].’”)
The above script will insert a row into our products table. The first set of brackets
(ID,title,description,price)
will define the order that are values insert. The second set of brackets
VALUES(null,”’.$_POST[‘title’].’” ,”’.$_POST[‘description].’” ,”’.$_POST[‘price].’”)
are the values being inserter. Notice that the ID value is set to NULL. This is because we have set the ID field to auto increment and NOT NULL meaning it will always be equal to the id of that last row inserted plus 1.
Selecting your products
So now we want to write a query that will grab all of our products from our database. We can do that like so.
SELECT * FROM products
With an order
SELECT * FROM products ORDER BY name ASC
Descending order
SELECT * FROM products ORDER BY name DESC
Selecting a product by ID
SELECT * FROM products WHERE ID = 2
We can even run a search by using a wild card before and after your search value
SELECT * FROM products WHERE description LIKE “% your search term %”
Updating a product
An update query will allow us to update an existing row in our database with new values.
UPDATE products SET name = “New Name” AND description = “New Description” WHERE ID = 2
Removing a product
Now lastly we would want to be able to delete a product from our database.
DELETE FROM products WHERE ID = 3
Keep in mind we want to be very careful with delete scripts. In fact it is usually a much better strategy to never delete a product rather create a “active” Boolean column and set a product to in active. In turn you will need to update your select quires to only grab active products.
If you want to learn more about MySQL take a look at some of these great resources
http://www.mysql.com/
http://www.w3schools.com/php/php_mysql_intro.asp
http://dev.mysql.com/doc/refman/5.0/en/func-op-summary-ref.html