Here you will find a PHP code example that pulls data from a MySQL database and displays it on the form. When developing a PHP application displaying records from different tables with the different columns, numbers, and names, you must write the HTML code for each.
The PHP code example below is universal for any query.
<?php
$link=mysqli_connect (“localhost”, “michael”, “Password7@”, “test”);
if (mysqli_connect_errno ())
{
$msg=“Failed to connect to MySQL:”. mysqli_connect_error ();
}
$sql=“select userid, lastname, firstname, username, email, role, active from visitors order by username”;
if (!$result=mysqli_query ($link, $sql))
{
$msg=mysqli_error ($link);
}
else
{
$c=0;
while ($row = mysqli_fetch_array ($result, MYSQLI_ASSOC)) {
if ($c==0)
{
$headers = array_keys ($row);
print (”<table> <tr> <th>”);
echo implode (”</th> <th>”, $headers);
print (”</th> </tr>”);
}
print (”<tr> <td>”);
echo implode (”</td> <td>”, $row);
print (”</td> </tr>”);
$c++;
}
print (”</table>”);
}
?>
PDO is a Database Access Abstraction Layer, an application programming interface which unifies the communication between a computer application and different databases. In this chapter we will discuss how to use PDO with MySQL.
According to PHP.net, a PDO class has been supported in PHP since version 5.1.0
To connect to MySQL, use the following code.
pdo_conn.php
<?php
$host = ‘localhost’;
$db=‘yourdb’;
$charset = ‘utf8”;
$userdb = ‘youruser’;
$passdb = ‘yourpassword’;
$pdo=null;
$dsn = “mysql: host= $host; dbname= $db; charset=
$charset”;
$options = array (
PDO: ATTR_ERRMODE => PDO: ERRMODE_EXCEPTION,
PDO: ATTR_EMULATE_PREPARES => false,
);
try {
$pdo = new PDO ($dsn, $userdb, $passdb, $options);
} catch (PDOException $e) {
echo “Could not connect to database!”;
}
?>
Let’s insert a record in our usernames table. In this code we will use the bindParam method.
pdo_insert_user.php
<?php
// insert using bindParam method
include ('pdo_conn.php’);
$isql=“insert into usernames (lastname, firstname, username, password, email, role, active)
values (:lastname,:firstname,:username,:password,:email,:role,:active)”;
$lastname=“Smith”;
$firstname=“John”;
$username=“johnsm”;
$password=“secret”;
$email="johnsm@yahoo.com”;
$role=“user”;
$active=1;
try {
$stmt = $pdo-> prepare ($isql);
$stmt-> bindParam (”: lastname’, $lastname, PDO: PARAM_STR);
$stmt-> bindParam (”: firstname’, $firstname, PDO: PARAM_STR);
$stmt-> bindParam (”: username’, $username, PDO: PARAM_STR);
$stmt-> bindParam (”: password’, $password, PDO: PARAM_STR);
$stmt-> bindParam (”: email’, $email, PDO: PARAM_STR);
$stmt-> bindParam (”: role’, $role, PDO: PARAM_STR);
$stmt-> bindParam (”: active’, $active, PDO: PARAM_INT);
$stmt-> execute ();
$insertId = $pdo-> lastInsertId ();
echo “LastID=”. $insertId;
echo “New records inserted successfully”;
}
catch (PDOException $e)
{
echo “Error:”. $e-> getMessage ();
}
?>
Output:
LastID=1 New records created successfully
There is a second way to insert a record with PDO. In this method we are using a placeholder.
pdo_insert_user2.php
<?php
//insert using placeholder
include ('pdo_conn.php’);
$isql=“insert into usernames (lastname,
firstname, username, password, email, rolea, active)
values (?,?,?,?,?,?,?)”;
$lastname=“Barry”;
$firstname=“John”;
$username=“johnb”;
$password=“secret”;
$email="johnb@yahoo.com”;
$role=“user”;
$active=1;
try {
$stmt = $pdo-> prepare ($isql);
//argument for execute method must be array []
$stmt-> execute ([$lastname, $firstname, $username, $password, $email, $role, $active]);
$insertId = $pdo-> lastInsertId ();
echo “LastID=”. $insertId;
echo “New records inserted successfully”;
}
catch (PDOException $e)
{
echo “Error:”. $e-> getMessage ();
}
?>
Output:
LastID=2 New records inserted successfully
Updating a record, using the bindParameter method.
pdo_update_user.php
<?php
//update using bindParam method
include ('pdo_conn.php’);
$isql=“update usernames set role=:role where username=:username”;
$username=‘johnsm’;
$role=“ADMIN”;
try {
$stmt = $pdo-> prepare ($isql);
$stmt-> bindParam (”: username’, $username, PDO: PARAM_STR);
$stmt-> bindParam (”: role’, $role, PDO: PARAM_STR);
$stmt-> execute ();
echo $username.”‘s records updated successfully!”;
}
catch (PDOException $e)
{
echo “Error:”. $e-> getMessage ();
}
?>
Output:
johnsm’s records updated successfully!
Updating record using placeholder.
pdo_update_user2.php
<?php
//update using placeholder
include ('pdo_conn.php’);
$usql=“update usernames set role=? where username=?”;
$username=‘johns’;
$role=“OWNER”;
try {
$stmt=$pdo-> prepare ($usql);
$stmt-> execute ([$role, $username]);
echo $username.”‘s records updated successfully!”;
}
catch (PDOException $e)
{
echo “Error:”. $e-> getMessage ();
}
?>
Output:
johnsm’s records updated successfully!
Selecting users
pdo_select_user.php
<?php
include ('pdo_conn.php’);
$sql=“select userid, firstname, lastname, username, role from usernames”;
if (!$stmt=$pdo-> query ($sql))
{
echo mysql_errno (). ":”;
echo mysql_error (). "<br/>”;
}
else
{
while ($row = $stmt-> fetch (PDO: FETCH_NUM))
{
$userid=$row [0];
$firstname=$row [1];
$lastname=$row [2];
$username=$row [3];
$role=$row [4];
echo $userid.”|”. $firstname. "|”. $lastname.” |”. $username.” |”. $role.”<br/>”;
}
}
?>
Output:
11|Barry| John | johnb | user
3|Silver| John | johnsl | user
9|Smith| John | johnsm | user
6|Silver| John | johnsl | user
8|Snider| John | johns | admin
Delete user using bindParam method.
pdo_delete_user.php
<?php
//delete using bindParam method
include ('pdo_conn.php’);
$dsql=“delete from usernames where lastname=:lastname”;
$lastname=“Johnsons”;
try {
$stmt = $pdo-> prepare ($dsql);
$stmt-> bindParam (”: lastname’, $lastname,
PDO: PARAM_STR);
$stmt-> execute ();
echo $lastname.”‘s records deleted successfully”;
}
catch (PDOException $e)
{
echo “Error:”. $e-> getMessage ();
}
?>
Output:
johns’s records deleted successfully!
Delete user using placeholder.
pdo_delete_user2.php
<?php
//delete using placeholder
include ('pdo_conn.php’);
$dsql=“delete from usernames where lastname=?”;
$lastname=“Barry”;
try {
$stmt = $pdo-> prepare ($dsql);
//execute required array
$stmt-> execute ([$lastname]);
echo $lastname.”‘s records deleted successfully”;
}
catch (PDOException $e)
{
echo “Error:”. $e-> getMessage ();
}
?>
Output:
Barry’s records deleted successfully!