PHP and MySQL Tutorial

In this tutorial I will show a basic approach on how to use PHP and MySQL in your applications.
What this tutorial covers:

• Some info about SQL.
• PHP functions to use with MySQL.
• Good practices in programming with MySQL.

I will start with a short intro: SQL (Structured Query Language) was developed at IBM in early 70’s. It’s a database computer language and it used in managing databases. It’s known for it’s flexibility, security and ease of use (it’s like natural language). Everybody I think at least heard of MySQL. It’s a free, powerful SQL. You can integrate easily on every application, even if it’s running locally (as a Java desktop application for example) or on an Internet web server. In the present time almost every website is using SQL, because it allows more dinamic content. Also when you develop applications it’s a good practice to use it locally (like a XAMP installation on Windows) . I explained this in creating your first PHP page.

Okay! Enough with the speaking, I will start with some code:

<?php if (mysql_connect("localhost", "root", "123")) { echo "Connected to MySQL<br />“;<br /> }<br /> else<br /> {<br /> echo “Error in connecting to MySQL <br />“;<br /> die(mysql_error());<br /> }<br /> ?>

I will explain this part of PHP code: mysql_connect is a function that allows you to connect to the database. It has three parameters:

• Host address of your MySQL.
• User assigned to MySQL.
• The password.

mysql_error() is a PHP functions that returns the error in case the PHP script couldn’t connect to that database.
It’s a good practice to not use root user in your applications and weak password if you like to keep your data safe.

<?php if (mysql_connect("localhost", "root", "root")) { echo "Connected to MySQL<br />“;<br /> mysql_select_db(“test”) or die(mysql_error());</p> <p>}<br /> else {<br /> echo “Error in connecting to MySQL <br />“;<br /> die(mysql_error());<br /> }</p> <p>?>

With mysql_select_db I am connecting to an existing database or give an error if it’s not possible.
Note that I created my database using phpmyadmin tool, but for beginners I recomend using MySQL terminal. The table I created is named iwebbuddy and can be done as follows:

mysql_query(“CREATE TABLE iwebbuddy(<br /> id INT NOT NULL AUTO_INCREMENT,<br /> PRIMARY KEY(id),<br /> name VARCHAR(30),<br /> like INT)”)<br /> or die(mysql_error());

It has three columns : id used for a unique data entry, name and like.
We can populate the table:

mysql_query(“INSERT INTO iwebbuddy (name, like) VALUES(‘andreic’, ‘1’ ) “) or die(mysql_error());

Like column is of int type and we can used to store 0 for dislike and 1 for like. Then also we can use TINYINT instead of INT.

<?php if (mysql_connect("localhost", "root", "root")) { echo "Connected to MySQL<br />“;<br /> mysql_select_db(“test”) or die(mysql_error());<br /> $result = mysql_query(“SELECT * FROM iwebbuddy”);</p> <p>$row = mysql_fetch_array($result);<br /> echo “Name: “.$row[‘name’];</p> <p>echo “<br /> Type: “;<br /> if ($row[‘like’] == 1) echo ” Likes”;<br /> else echo “Dislikes”;<br /> }<br /> else {<br /> echo “Error in connecting to MySQL <br />“;<br /> die(mysql_error());<br /> }<br /> ?>

Okay! Let’s see. In variable $result I am keeping all the data taken from iwebbuddy table. The I am using mysql_fetch_array which makes an associative array (row) for result variable. Then I can call easily $row[‘name’] which refers to name column in iwebbuddy and like column as above.
If we want all of our data from the table to be displayed then we can use a WHILE Loop:

while($row = mysql_fetch_array($result)) {</p> <p> echo $row[‘name’];<br /> echo $row[‘like’];<br /> }

That’s it. I will continue with MySQL and PHP tutorial in the near future.
I hope that you enjoy this and any feedback is appreciated.