PHP
PHP Comments, Include/Include_once, Require/Require_once
Why use Comments? If you don’t work on the source code for some time, it’s easy to forget what the code...
PHP has a rich collection of built in functions for manipulating MySQL databases.
In this tutorial, you will learn-
The PHP mysql connect function is used to connect to a MySQL database server.
It has the following syntax.
<?php; $db_handle = mysqli_connect($db_server_name, $db_user_name, $db_password); ?>
HERE,
The mysqli_select_db function is used to select a database.
It has the following syntax.
<?php mysqli_select_db($db_handle,$database_name); ?>
HERE,
The mysqli_query function is used to execute SQL queries.
The function can be used to execute the following query types;
It has the following syntax.
<?php mysqli_query($db_handle,$query) ; ?>
HERE,
It has the following syntax.
<?php mysqli_num_rows($result); ?>
HERE,
The mysqli_fetch_array function is used fetch row arrays from a query result set.
It has the following syntax.
<?php mysqli_fetch_array($result); ?>
HERE,
The mysqli_close function is used to close an open database connection.
It has the following syntax.
<?php mysqli_close($db_handle); ?>
HERE,
Let’s look at practical examples that take advantage of these functions.
Creating the MySQL database This tutorial assumes knowledge of MySQL and SQL, if these terms are unfamiliar to you, refer to our MySQL and SQL tutorials.
We will create a simple database called my_personal_contacts with one table only.
Below are the steps to create the database and table.
<?php CREATE TABLE IF NOT EXISTS `my_contacts` ( `id` int(11) NOT NULL AUTO_INCREMENT, `full_names` varchar(255) NOT NULL, `gender` varchar(6) NOT NULL, `contact_no` varchar(75) NOT NULL, `email` varchar(255) NOT NULL, `city` varchar(255) NOT NULL, `country` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ; INSERT INTO `my_contacts` (`id`, `full_names`, `gender`, `contact_no`, `email`, `city`, `country`) VALUES (1, 'Zeus', 'Male', '111', 'zeus @ olympus . mt . co', 'Agos', 'Greece'), (2, 'Anthena', 'Female', '123', 'anthena @ olympus . mt . co', 'Athens', 'Greece'), (3, 'Jupiter', 'Male', '783', 'jupiter @ planet . pt . co', 'Rome', 'Italy'), (4, 'Venus', 'Female', '987', 'venus @ planet . pt . co', 'Mars', 'Italy'); ?>
We now have a database set up that we will manipulate from PHP.
Reading records from the database We will now create a program that prints the records from the database.
<?php $dbh = mysqli_connect('localhost', 'root', 'melody'); //connect to MySQL server if (!$dbh) die("Unable to connect to MySQL: " . mysqli_error()); //if connection failed output error message if (!mysqli_select_db($dbh,'my_personal_contacts')) die("Unable to select database: " . mysqli_error()); //if selection fails output error message $sql_stmt = "SELECT * FROM my_contacts"; //SQL select query $result = mysqli_query($dbh,$sql_stmt); //execute SQL statement if (!$result) die("Database access failed: " . mysqli_error()); //output error message if query execution failed $rows = mysqli_num_rows($result); // get number of rows returned if ($rows) { while ($row = mysqli_fetch_array($result)) { echo 'ID: ' . $row['id'] . '<br>'; echo 'Full Names: ' . $row['full_names'] . '<br>'; echo 'Gender: ' . $row['gender'] . '<br>'; echo 'Contact No: ' . $row['contact_no'] . '<br>'; echo 'Email: ' . $row['email'] . '<br>'; echo 'City: ' . $row['city'] . '<br>'; echo 'Country: ' . $row['country'] . '<br><br>'; } } mysqli_close($dbh); //close the database connection ?>
Executing the above code returns the results shown in the diagram shown below
Inserting new records
Let’s now look at an example that adds a new record into our table. the code below shows the implementation.
<?php $dbh = mysqli_connect('localhost', 'root', 'melody'); //connect to MySQL server if (!$dbh) die("Unable to connect to MySQL: " . mysqli_error()); //if connection failed output error message if (!mysqli_select_db($dbh,'my_personal_contacts')) die("Unable to select database: " . mysql_error()); //if selection fails output error message $sql_stmt = "INSERT INTO `my_contacts` (`full_names`,`gender`,`contact_no`,`email`,`city`,`country`)"; $sql_stmt .= " VALUES('Poseidon','Mail','541',' poseidon @ sea . oc ','Troy','Ithaca')"; $result = mysqli_query($dbh,$sql_stmt); //execute SQL statement if (!$result) die("Adding record failed: " . mysqli_error()); //output error message if query execution failed echo "Poseidon has been successfully added to your contacts list"; mysqli_close($dbh); //close the database connection ?>
Executing the above code outputs “Poseidon has been successfully added to your contacts list” go back to the select query example and retrieval your contacts again.
See if Poseidon has been added to your list.
Updating records Let’s now look at an example that updates a record in the database.
Let’s suppose that Poseidon has changed his contact number and email address.
<?php $dbh = mysqli_connect('localhost', 'root', 'melody'); //connect to MySQL server if (!$dbh) die("Unable to connect to MySQL: " . mysqli_error()); //if connection failed output error message if (!mysqli_select_db($dbh,'my_personal_contacts')) die("Unable to select database: " . mysql_error()); //if selection fails output error message $sql_stmt = "UPDATE `my_contacts` SET `contact_no` = '785',`email` = ' poseidon @ ocean . oc '; //SQL select query $sql_stmt .= " WHERE `id` = 5"; $result = mysqli_query($dbh,$sql_stmt); //execute SQL statement if (!$result) die("Deleting record failed: " . mysqli_error()); //output error message if query execution failed echo "ID number 5 has been successfully updated"; mysqli_close($dbh); //close the database connection ?>
Deleting records
Let’s now look at an example that removes records from the database. Let’s suppose that Venus has a restraining order against us, and we must remove her contacts info from our database.
<?php $dbh = mysqli_connect('localhost', 'root', 'melody'); //connect to MySQL server if (!$dbh) die("Unable to connect to MySQL: " . mysqli_error()); //if connection failed output error message if (!mysqli_select_db($dbh,'my_personal_contacts')) die("Unable to select database: " . mysqli_error()); //if selection failes output error message $id = 4; //Venus's ID in the database $sql_stmt = "DELETE FROM `my_contacts` WHERE `id` = $id"; //SQL Delete query $result = mysqli_query($dbh,$sql_stmt); //execute SQL statement if (!$result) die("Deleting record failed: " . mysqli_error()); //output error message if query execution failed echo "ID number $id has been successfully deleted"; mysqli_close($dbh); //close the database connection ?>
The PDO is a class that allows us to manipulate different database engines such as MySQL, PostGres, MS SQL Server etc.
The code below shows the database access method using the PDO object.
Note: the code below assumes knowledge of SQL language, arrays, exception handling and foreach loop.
<?php try { $pdo = new PDO("mysql:host=localhost;dbname=my_personal_contacts", 'root', 'melody'); $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $pdo->exec('SET NAMES "utf8"'); $sql_stmt = "SELECT * FROM `my_contacts`"; $result = $pdo->query($sql_stmt); $result->setFetchMode(PDO::FETCH_ASSOC); $data = array(); foreach ($result as $row) { $data[] = $row; } print_r($data); } catch (PDOException $e) { echo $e->getMessage(); } ?>
HERE,
ODBC ODBC is the acronym for Open Database Connectivity. It has the following basic syntax.
<?php $conn = odbc_connect($dsn, $user_name, $password); ?>
HERE,
The example used assumes you;
Below is the implementation code for ODBC data access
<?php $dbh = odbc_connect('northwind', '', ''); if (!$dbh) { exit("Connection Failed: " . $dbh); } $sql_stmt = "SELECT * FROM customers"; $result = odbc_exec($dbh, $sql_stmt); if (!$result) { exit("Error access records"); } while (odbc_fetch_row($result)) { $company_name = odbc_result($result, "CompanyName"); $contact_name = odbc_result($result, "ContactName"); echo "<b>Company Name (Contact Person):</b> $company_name ($contact_name) <br>"; } odbc_close($dbh); ?>
Summary
Why use Comments? If you don’t work on the source code for some time, it’s easy to forget what the code...
Web development is the process of building and maintenance of websites. Web development has a wide...
In this tutorial, you will learn- PHP Data Types PHP Variable Use of variables Variable type...
What is Cookie? A cookie is a small file with the maximum size of 4KB that the web server stores...
What is CakePHP? CakePHP is an open-source framework for the rapid development and maintenance of web...
What is a control structure? Code execution can be grouped into categories as shown below...