In this tutorial I will be teaching you how to work with MySQL when programming in PHP. MySQL is something that every good PHP programmer has to know and this guide aims at teaching you all the basic functions for working with MySQL databases and maybe a few other little tips.

<?php
$server = ‘localhost’; // Usually you can leave it as localhost.
$user = ‘username’; // MySQL User
$pass = ‘password’; // MySQL Pass
$dbname = ‘database.name’; // Database Name
?>

This will be used by our next 2 functions which will connect to the MYSQL server and then connect to the correct database.

$server = ‘localhost’; // Usually you can leave it as localhost.
$user = ‘username’; // MySQL User
$pass = ‘password’; // MySQL Pass
$dbname = ‘database.name’; // Database Name
mysql_connect(“$server”, “$user”, “$pass”) or die(mysql_error()); // Connect to MySQL server or display error.
echo “Connected to MySQL server.<br />”;
mysql_select_db(“$dbname”) or die(mysql_error()); // Connect to our database.
echo “Connected to database.”;

Now that you can connect to your database its time for us to create a table (tables store data in a MySQL database). You run MySQL queries by using the function mysql_query(). In the next example we are going to learn how to create a table.

mysql_query(“CREATE TABLE users(
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id),
username VARCHAR(28),
password VARCHAR(40)”)
or die(mysql_error());

Theabove code will create a table called “users” in your selected database with the columns “username”, “password”, and an automatically updating ”id” number. The reason why we made the “password” column 40 characters long is because as SHA1 hash (encrypted password) is exactly 40 characters long.

For our next step we will be adding 2 users to the table. Adding data to your tables is also accomplished through the mysql_query() function however your query would be an INSERT one instead of CREATE. Below is an example of me adding our two users to our table.

REMEMBER: Never store an unencrypted password in your databases. (At least sha1() them first.)

mysql_query(“INSERT INTO users
(username, password) VALUES(‘mysql_real_escape_string($username)’, ‘sha1($password)’ ) “)
or die(mysql_error());

The above code will securely add a user to your table while encrypting the password and also guarding against MySQL injection at the same time. Always remember to use mysql_real_escape_string()  when your handling a variable a user controls to block hackers from performing a MySQL injection. Now last but not least I will show you how to retrieve data from your table. The following code will show you how to retrieve all data in a certain table.

$result = mysql_query(“SELECT * FROM users”)
or die(mysql_error());
$row = mysql_fetch_array($result);
echo “<br>Username: “.$row[‘username’].”<br>Password Hash: “.$row[‘password’];

Thats it for now! I hope this guide helped show you all how to work with MySQL databases and tables when working with php.