[Legolas]

Hello [username].
I decided to start a big series of Php tutorials.
In this very analitic tutorial, I will show you how you can use PHP to write code and use commands to connect to a Mysql database, add records , edit them, remove records and finally how to display them.

___________________________________________________________________________________________________________


Connecting to the database

PHP Code:

<?php function dbconnect() { // Connection to the Server $db_connect = mysql_connect("localhost", "db_user", "db_password") or die ("Failed to connect to the server"); // If use utf8 encoding and it does not display something correctly. // mysql_query("SET NAMES 'utf8'", $db_connect); // Database Selection $db_select = mysql_select_db("db_name", $db_connect) or die ("Failed to select the database"); } ?>

With the following, we connect to mysql :

PHP Code:

mysql_connect("localhost", "db_user", "db_password")

At the end, we select the database name :

PHP Code:

$db_select = mysql_select_db("db_name", $db_connect)

It helps us to have the above function in a separate file, and whenever we need it, we import it. So, if we want to change for example the password or user, we do it in this file (dbconn.php).
To import the file of the function in another php file, we use the include() command as follows:

PHP Code:

<?php // Importing the file with the function include(“dbconn.php”); // We call the Function dbconnect();

Commands

PHP Code:

// Disconnection from the Server /mysql_close($db_connect); ?>

Although it is not necessary to do disconnection, it would be good, if we do not forget this.

___________________________________________________________________________________________________________


Adding and updating records in a MySql database

PHP Code:

<?php // Importing the file with the function include(“dbconn.php”); // We call the function dbconnect(); … // Disconnection from the Server /mysql_close($db_connect); ?>

Then, with the following code, we declare what we want to assign and where.

PHP Code:

// Adding Data $qAdd = mysql_query("INSERT INTO clients (id, name, surname) VALUES ('', 'Legolas', 'Greenleaf')") or die (There was a problem importing the data.")); if ($qAdd) { echo "The Data has been added!"; }

While, with the following we declare what we want to change/edit.

PHP Code:

// Updating Data $qUpdate=mysql_query("UPDATE clients SET name = 'name', surname = 'surname' WHERE id='1' "); if ($qUpdate) { echo "The Data has been updated!"; }

So, the add.php file takes the following form :

PHP Code:

<?php // Importing the file with the function include(“dbconn.php”); // We call the function dbconnect(); // Adding Data $qAdd = mysql_query("INSERT INTO clients (id, name, surname) VALUES ('', 'Legolas', 'Greenleaf')") or die ("There was a problem adding the data.")); if ($qAdd) { echo "The Data has been added!"; } // Disconnection from the Server /mysql_close($db_connect); ?>

While, the update.php the following:

PHP Code:

<?php // Importing the file with the function include(“dbconn.php”); // We call the function dbconnect(); // Updating Data $qUpdate=mysql_query("UPDATE clients SET name = 'name', surname = 'surname' WHERE id='1' "); if ($qUpdate) { echo "The Data has been updated!"; } // Disconnection from the Server /mysql_close($db_connect); ?>

___________________________________________________________________________________________________________


Displaying records from a MySql Database

At first, we create an index.php file and we call the function, to connect to the database, which we created before.

PHP Code:

<?php // Importing the file with the function include(“dbconn.php”); // We call the Function dbconnect(); … // Disconnection from the Server /mysql_close($db_connect); ?>

Then, with the following code, we extract the results out of our database.

PHP Code:

// Query the database $q=mysql_query("SELECT * FROM clients "); $num = mysql_num_rows($q); // If there are Results if ($num > 0 ) { $i=0; while ($i < $num) { // We create names for the variables $name = mysql_result($q,$i,"name"); $surname = mysql_result($q,$i,"surname"); // Displaying Variables echo "Name: $name<br />"; echo "Surname: $surname <br />"; // If there are not Results ++$i; } } else { echo "They have not been registered data for you yet."; }

So, this file takes the following form.

PHP Code:

<?php // Importing the file with the function include(“dbconn.php”); // We call the Function dbconnect(); // Query the database $q=mysql_query("SELECT * FROM clients "); $num = mysql_num_rows($q); // If there are Results if ($num > 0 ) { $i=0; while ($i < $num) { // We create names for the variables $name = mysql_result($q,$i,"name"); $surname = mysql_result($q,$i,"surname"); // Displaying Variables echo "Name: $name<br />"; echo "Surname: $surname <br />"; // If there are not Results ++$i; } } else { echo "They have not been registered data for you yet."; } // Disconnection from the Server /mysql_close($db_connect); ?>

We can include/comprise different criteria in our search, such as if the name is "Legolas" .
This is done by adding WHERE name=’Legolas’ to the mysql query.

PHP Code:

$q=mysql_query("SELECT * FROM clients WHERE name=’Legolas’");

___________________________________________________________________________________________________________


Deleting records in a MySql Database

At first, we create a delete.php file and we call the function, to connect to the database, which we created before.

PHP Code:

<?php // Importing the file with the function include(“dbconn.php”); // We call the function dbconnect(); … // Disconnection from the Server /mysql_close($db_connect); ?>

Then, with the following code, we declare what we want to delete.

PHP Code:

// Removing Data $qRemove = mysql_query("DELETE FROM clients WHERE id='1' "); if ($qRemove) { echo "The Data has been removed!"; } // So, the file takes the following form. <?php // Importing the file with the function include(“dbconn.php”); // We call the function dbconnect(); // Removing Data $qRemove = mysql_query("DELETE FROM clients WHERE id='1' "); if ($qRemove) { echo "The Data has been removed!"; } // Disconnection from the Server /mysql_close($db_connect); ?>

___________________________________________________________________________________________________________



I hope you enjoyed this.
More Php tutorials are coming soon.

Legolas