The first step in using a MySQL database is to make sure your host has the necessities set up. Typically, your web host’s control panel will allow the creation of a database/user pair which will allow you to connect. However, most do not allow the creation of these directly through a PHP script. Check with your host to see how you can get your MySQL database running.

The next step, and probably the first in your running script is connecting to the user and database, using the following two functions:

<?php

...

mysql_connect(host, user, password);

mysql_select_db(database [, link]);

...

?>

Again, your host will work with you with respect to establishing the user, password, and database, and host is typically “localhost” when accessing the database on the same server as your website, but check with your host if you are having problems. The link, which is optional, would refer to the variable holding your mysql_connect() reference.

Now you have a database ready to go, but without a table, there’s no place for your streams of data to go, so let’s set one up. Most of our queries to MySQL are going to be run through the appropriately named mysql_query() function, and the text within will change with each different application. To create a table, we run a query like this:

<?php

//Connect to MySQL before running any queries, every time

mysql_query("CREATE TABLE table_name(

column_one ATTRIBUTES,

column_two ATTRIBUTES,

...

)");

mysql_close(link);

?>

You should take the time before creating a table to decide what all needs to be stored and what would be the best manner to do so, and then use one of the column types and attributes name at this resource. Some may seem confusing, but there are plenty of elaborations and tutorials available to describe the uses of each type. The mysql_close() function simply closes our the defined connection and is typically unnecessary as the connection will be closed at the end of the script anyway.

With a table available for queries, we can begin adding and working with our data. The main abilities available with the mysql_query() function our SELECT, SHOW, DESCRIBE, EXPLAIN, INSERT, UPDATE, DELETE, and DROP. The first four return a resource (explained in detail in a minute) and the latter four return a boolean value depending on the success.

SHOW, DESCRIBE, and EXPLAIN are honestly rarely used within the context of a functioning, user-run website. They are moreso used on the administrator’s end and even then are not as functional as the others. DELETE and DROP should certainly only be used on the administrator’s end and probably, again, only through MySQL itself and not through a publicly-available executable file. Only through proper authentication should someone be able to delete a record or even an entire MySQL table.

Let’s start with INSERT, as that is where you must start to use the other functions. To insert data into a table, run the function like this:

<?php

//Connect to MySQL before running any queries, every time

mysql_query("INSERT INTO `table_name` (col1, col2, ...)

VALUES('val1', 'val2', ...)") or die(mysql_error());

...

?>

The column names in the first set of parentheses should correspond with the correct value in the second set. You may also opt, with each execution of mysql_query() to use the mysql_error() function to report an error should one occur, though hopefully you’ve worked your script well and an error is handled by your own error-checking. However, some things do slip by and happen unpreventably.

Next is the SELECT application. Just as it sounds, you select records from an identified table and can then use the data however you wish, by displaying it, checking it against variables, etc. Either way, a SELECT application looks like this:

<?php

//Connect to MySQL before running any queries, every time

$getRows = mysql_query("SELECT * FROM `table_name` WHERE `col_name`='val'");

while ($result = mysql_fetch_array($getRows)) {

echo $result['col_name'];

...

}

...

?>

The * can be replaced with each column you wish to extract, separated by commas, and the WHERE clause is in the query is optional but should be used when looking for particular records. You should note that SELECT returns a resource, so we have to loop through that resource using the mysql_fetch_array(), mysql_fetch_assoc(), mysql_fetch_row() or an appropriate alternative. Then the data from the records is stored in an array where each column is referenced by its respective name.

To delete a record (or records) use a similar construct to the SELECT application, but no looping or variable-calling is necessary. It simply works through the query:

<?php

//Connect to MySQL before running any queries, every time

mysql_query("DELETE FROM `table_name` WHERE `col_name`='val'");

...

?>

To delete an entire table, use the DROP application, though realize that every record will be obliterated:

<?php

//Connect to MySQL before running any queries, every time

mysql_query("DROP TABLE `table_name`");

...

?>

Lastly, editing records is fairly easy though it can be more cumbersome. Using the UPDATE application like so can allow for the altering of preexisting rows.

<?php

//Connect to MySQL before running any queries, every time

mysql_query("UPDATE `table_name`

SET `col_name` = 'val', `col2_name` = 'val2', ...

WHERE `col_name` = 'val'");

...
?>

You can change as many columns as you need, separating each with a comma, but be careful with spacing because the success of the update can depend highly on the spacing as I have seen in the past, though I can’t find a good reference on what the exact spacing should look like.

With these basic applications in tow, little cannot be accomplished with your MySQL database. With a dynamic, user-driven website these will certainly be integrated into your website. To keep a more watchful eye on your database and perform certain one-time actions on your tables, you may also choose to employ a GUI like PHPMyAdmin or something similar which your host may already have available through the control panel.

Happy querying!