Introduction to mySQL

What is mySQL and what can it do for me?
mySQL is an open source server database language that is similar to Microsoft Access. What this means to most webmasters is that mySQL is a free database you can set up and install on your server that will allow you to dynamically create content for your users. mySQL is the database itself; PHP, a server side language, lets you create, modify, and delete mySQL databases. A myriad of things on Spoono use mySQL combined with PHP including news posts, tutorials, discussion board, advertisement trackers, etc. You can use mySQL along with PHP to create just about anything from storing images, to creating a discussion board, to storing your blogs. An example we will be working on through out this tutorial will be how Spoono uses PHP and mySQL at the PHP Tutorials page to show an index of PHP tutorials available on our site.

How do I tell if my server has mySQL?
A simple test to see if you do have mySQL installed on your server is to create a blank page and write:

<?
phpinfo();
?>

Save the file as info.php and upload it to your server. Open up the file in your browser and look for a mySQL section. If it says enablead and installed, you are good to go and ready to start learning about mySQL. If not, then go to mySQL.com, download and install it, or ask your hosting provider if you're on shared hosting.

How is PhpMyAdmin and what does it do?
This is an excellent oppurtunity to talk about PhpMyAdmin and uses for it. Though I encourage to learn how to use mySQL commands using PHP, PhpMyAdmin is a freeware software that you can download and install on your server (most shared hosting servers that have mySQL also have PhpMyAdmin). It give you a nice user interface that makes creating, editing, and deleting databses and tables as easy as selecting a few drop downs. PhpMyAdmin gives you an easier way to deal with mySQL and is a short cut to manually typing things in with PHP.

How do I log on to mySQL and create a database using PHP?
Once you have configured or installed mySQL, you are ready to start creating databases using PHP. It is important to know that just like other standardized databases, mySQL can create an infinite number of databases. Inside databases, you can create an infinite number of tables. These tables actually store all the information. So the first thing we must do is log in and create a database. You can read all of the mySQL functions available in PHP by clicking here. Open up a new blank document and type the following line:

<?php
$connection = mysql_connect('localhost', 'mysql_user', 'mysql_password');
if (!$connection) 
{
   die('Could not connect: ' . mysql_error());
}

if (mysql_create_db('tutorial_db')) 
{
   echo "Database created successfully\n";
} 
else 
{
   echo 'Error creating database: ' . mysql_error() . "\n";
}
?> 

In this script, replace "mysql_user" and "mysql_password" with your mySQL login and password either supplied by your virtual host provider or by the information you used when you installed mySQL. Save this file, upload to your server, and open the URL in Internet Explorer. Hopefully, it will display "Database created successfully". Looking at the code, it uses the mysql_connect() function to connect to mySQL and mysql_create_db() function to create the "tutorial_db" database.

Great, now how do I create a mySQL table using PHP inside my database?
Now that we have the "tutorial_db" database created, we need to create a table inside it to store all our data. We can do that by creating a new file:

<?
mysql_connect("localhost","username","password");
mysql_select_db("tutorial_db");
$sql='CREATE TABLE `tutorials` ( `id` int( 11 ) NOT NULL AUTO_INCREMENT ,'
   . ' `title` text NOT NULL ,'
   . ' `url` text NOT NULL ,'
   . ' PRIMARY KEY ( `id` ) ,'
   . ' KEY `id_2` ( `id` ) ) TYPE = MYISAM '; 
mysql_query($sql);
mysql_close();
?> 

The first two lines are the same from the previous questions. The next line lists the mySQL code needed to create a "tutorials" table that has 3 fields: id, title, and url. The "id" is auto-incremented meaning it will be unique for each row you add. The mysql_query() function does the command you tell it to, and then we close the mySQL connection.

Now what?
Now you're on your own! You have your database and table set up and are ready to use your mySQL powers. You can check out different tutorials we have here on Spoono like how to add a row, how to edit, delete, etc. Good luck and if you have any questions, just drop a message in the discussion board.