Home » MySqli PDO »
MySqli PDO
What is MySqli PDO?
Database connection.
File name : db.php
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "mydb";
// Create connection
$con = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($con->connect_error) {
die("Connection failed: " . $con->connect_error);
}
mysqli_report(MYSQLI_REPORT_ERROR);
?>
Index.php
File name : index.php
<html>
<head>
<body>
<?php
include 'db.php';
if(isset($_POST['submit']))
{
$name = $_POST['name'];
$address = $_POST['add'];
$mobile = $_POST['mobile'];
echo $name;
echo "<br/>";
echo $address;
echo "<br/>";
echo $mobile;
// prepare and bind
//$query = "INSERT INTO info (name, address) VALUES (?,?,?)";
//$stmt = $con->prepare($query);
$stmt = $con->prepare("INSERT INTO info (name, address, mobile) VALUES (?,?,?)");
$stmt->bind_param("ssi", $name, $address, $mobile);
// The s character tells mysql that the parameter is a string.
// here "ss", means no of inputed element is string . i means integer and d means double and b means blob
$stmt->execute();
/*
if($stmt->execute()){
print 'Successfully inserted';
}else{
die('Error : ('. $con->errno .') '. $con->error);
}
*/
$stmt->close();
$con->close();
}
?>
<form name="frm" action="" method="post">
<label>Name :</label>
<input type="text" name="name"/>
<label>Address :</label>
<input type="text" name="add"/>
<input type="number" name="mobile"/>
<input type="submit" name="submit" value="Submit"/>
</form>
</body>
</head>
</html>
<table class="table table-bordered table-condensed">
<thead>
<tr>
<th>id</th>
<th> Name</th>
<th>Address</th>
</tr>
</thead>
<tbody>
<?php
include 'db.php';
$qry = "SELECT * FROM info";
$stmt = $con->prepare($qry);
$SS = $stmt->execute();
//$stmt->bind_result($SS);
//$result = $stmt->fetch();
//printf($result);
//echo "name = " .$result;
$res = $stmt->get_result();
//print_r($res);
while($row = $res->fetch_assoc())
{
//printf("id = %d (%d)\n", $row['id'], gettype($row['id']));
//printf("label = %s (%s)\n", $row['name'], gettype($row['name']));
//printf("Name = %s (%s)\n", $row['name'], gettype($row['name']));
//printf("Name = %s \n", $row['name']);
//}
?>
<tr>
<td><?php echo $row['id']; ?></td>
<td><?php echo htmlspecialchars($row['name']); ?></td>
<td><?php echo htmlspecialchars($row['address']); ?></td>
</tr>
<?php
/* while ($stmt->fetch()) {
printf("id = %s (%s), label = %s (%s)\n", $out_id, gettype($out_id), $out_label, gettype($out_label));
}
//$res->fetch_all()
*/
}
?>
</tbody>
</table>
<table class="table table-bordered table-condensed">
<thead>
<tr>
<th>id</th>
<th> Name</th>
<th>Address</th>
</tr>
</thead>
<tbody>
<?php
include 'db.php';
$qry = "SELECT * FROM info";
$result = $con->query($qry);
if ($result->num_rows > 0) {
while($r = $result->fetch_assoc()) {
?>
<tr>
<td><?php echo htmlspecialchars($r['id']) ?></td>
<td><?php echo htmlspecialchars($r['name']); ?></td>
<td><?php echo htmlspecialchars($r['address']); ?></td>
</tr>
<?php }
}
$con->close();
?>
</tbody>
</table>
Show.php
File name : Show.php
<html>
<head>
</head>
<body>
<table width="80%">
<th>Id</th>
<th>Name</th>
<th>Address</th>
<th>Mobile</th>
<th>Action</th>
<?php
include 'db.php';
$qry = "SELECT * FROM info";
$stmt = $con->prepare($qry);
$SS = $stmt->execute();
$res = $stmt->get_result();
//print_r($res);
while($row = $res->fetch_assoc())
{
?>
<tr>
<td><?php echo $row['id']; ?></td>
<td><?php echo htmlspecialchars($row['name']); ?></td>
<td><?php echo htmlspecialchars($row['address']); ?></td>
<td><?php echo $row['mobile']; ?></td>
<td><a href="edit.php?id=<?php echo $row['id'];?>">Edit</a> <a href="delete.php?id=<?php echo $row['id'];?>">Delete</a></td>
</tr>
<?php }?>
</br>
</table>
</body>
</html>
Edit.php
File name : edit.php
<?php
include 'db.php';
//if (isset($_GET['id']))
//{
// if the form's submit button is clicked, we need to process the form
if (isset($_POST['submit']))
{
// make sure the 'id' in the URL is valid
if (is_numeric($_POST['id']))
{
// get variables from the URL/form
$id = $_POST['id'];
$name = htmlentities($_POST['name'], ENT_QUOTES);
$address = htmlentities($_POST['address'], ENT_QUOTES);
$mobile = htmlentities($_POST['mobile'], ENT_QUOTES);
// check that firstname and lastname are both not empty
if ($name == '' || $address == '' || $mobile == '')
{
// if they are empty, show an error message and display the form
$error = 'ERROR: Please fill in all required fields!';
renderForm($name, $address, $error, $id);
}
else
{
// if everything is fine, update the record in the database
if ($stmt = $con->prepare("UPDATE info SET name = ?, address = ?, mobile = ? WHERE id=?"))
{
$stmt->bind_param("ssii", $name, $address, $mobile, $id);
$stmt->execute();
$stmt->close();
}
// show an error message if the query has an error
else
{
echo "ERROR: could not prepare SQL statement.";
}
// redirect the user once the form is updated
header("Location: employee_record.php");
}
}
// if the 'id' variable is not valid, show an error message
else
{
echo "Error!";
}
}
//}
?>
<html>
<head>
</head>
<body>
<?php
include 'db.php';
$id = $_GET['id'];
$qry1 = "SELECT * FROM info where id='$id'";
$stmt = $con->prepare($qry1);
$SS = $stmt->execute();
$res = $stmt->get_result();
//print_r($res);
while($row = $res->fetch_assoc())
{
?>
<form method="post" action="" name="frm">
<input type="text" name="id" value="<?php echo $row['id']; ?>"/>
<label>Name</label><input type="text" name="name" value="<?php echo htmlspecialchars($row['name']); ?>"/><br/>
<label>Address</label><input type="text" name="address" value="<?php echo htmlspecialchars($row['address']); ?>"/><br/>
<label>mobile</label><input type="text" name="mobile" value="<?php echo htmlspecialchars($row['mobile']); ?>"/><br/>
<input type="submit" name="submit" value="Submit"/>
</form>
<?php } ?>
</body>
</html>
Delete.php
File name : delete.php
<?php
// connect to the database
include ('db.php');
// confirm that the 'id' variable has been set
if (isset($_GET['id']) && is_numeric($_GET['id']))
{
// get the 'id' variable from the URL
$id = $_GET['id'];
// delete record from database
if ($stmt = $con->prepare("DELETE FROM info WHERE id = ? LIMIT 1"))
{
$stmt->bind_param("i",$id);
$stmt->execute();
$stmt->close();
}
else
{
echo "ERROR: could not prepare SQL statement.";
}
$con->close();
// redirect user after delete is successful
header("Location: employee_record.php");
}
else
// if the 'id' variable isn't set, redirect the user
{
header("Location: employee_record.php");
}
?>
File name : db.php