To retrieve data from MySQL, the SELECT statement is used. We can retrieve data from a specific column or all columns of a table.
To retrieve selected column data from the database the SQL query is
SELECT column_name,column_name FROM table_name;
To retrieve all the column data from a table the SQL query is
SELECT * FROM table_name;
In the below example we retrieve the data from MySQL database.
In this example, we used 2 files for retrieve data
- database.php- To connecting database.
- retrieve.php- For retrieve data from the database
1. database
CREATE TABLE `employee` (
`userid` int(8) NOT NULL,
`first_name` varchar(55) NOT NULL,
`last_name` varchar(55) NOT NULL,
`city_name` varchar(55) NOT NULL,
`email` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1; |
2. database.php
<?php
$servername = 'localhost';
$username = 'root';
$password = '';
$dbname = "db_php_test";
$conn=mysqli_connect($servername,$username,$password, "$dbname");
if(!$conn)
{
die('Could not Connect My Sql:' .mysql_error());
}
?> |
3. Insert.php
<?php
include_once 'process.php';
?>
<!DOCTYPE html>
<html lang="en">
<head>
<title>PHP INSERT</title>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<!-- Library -->
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css"/>
<script src="https://code.jquery.com/jquery-3.2.1.slim.min.js" integrity="sha384-KJ3o2DKtIkvYIK3UENzmM7KCkRr/rE9/Qpg6aAZGJwFDMVNA/GpGFF93hXpG5KkN" crossorigin="anonymous"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.12.3/umd/popper.min.js" integrity="sha384-vFJXuSJphROIrBnz7yo7oB41mKfc8JzQZiCq4NCceLEaO4IHwicKwpJf9c9IpFgh" crossorigin="anonymous"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0-beta.2/js/bootstrap.min.js" integrity="sha384-alpBpkh1PFOepccYVYDB4do5UnbKysX5WZXm3XxPqe5iKTfUKjNkCk9SaVuEZflJ" crossorigin="anonymous"></script>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.1.3/css/bootstrap.min.css" />
<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery-validate/1.19.0/jquery.validate.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery-validate/1.19.0/additional-methods.min.js"></script>
</head>
<body>
<style>
.form-control, .btn,.container {
border-radius: 1px;
}
</style>
<br>
<br>
<br>
<div class="container"> <!-- Container -->
<div class="shadow p-3 mb-5 bg-white rounded"><!-- Regular shadow -->
<h3 class="mt33">PHP INSERT</h3>
<h5 class="text-center text-success" id="message"><?= $sucess ;?></h5>
<br>
<form class="mt33" action="insert.php" method = "post"><!--form -->
<!-- First name -->
<div class="form-group row">
<label for="description" class="control-label col-sm-3">First name *</label>
<div class="col-sm-9">
<input type="text" class="form-control" id="first_name" name="first_name" placeholder="Enter first name" required>
</div>
</div>
<!-- last name -->
<div class="form-group row">
<label for="description" class="control-label col-sm-3">Last name *</label>
<div class="col-sm-9">
<input type="text" class="form-control" id="last_name" name="last_name" placeholder="Enter last name" required>
</div>
</div>
<!-- City name -->
<div class="form-group row">
<label for="rootCause" class="control-label col-sm-3">City name *</label>
<div class="col-sm-9">
<select class="custom-select" id="city_name" name="city_name" aria-label="city_name" required>
<option value=""> Plase select city name</option>
<option value="Toky">Toky</option>
<option value="Phnom Penh">Phnom Penh</option>
<option value="USA">USA</option>
</select>
</div>
</div>
<!-- Email -->
<div class="form-group row">
<label for="description" class="control-label col-sm-3">Email *</label>
<div class="col-sm-9">
<input type="email" class="form-control" id="email" name="email" placeholder="Enter email" required>
</div>
</div>
<!-- Show Message -->
<div class="text-success text-center d-none" id="msg_div">
<h4 id="res_message">Insert has been successfully.</h4>
</div>
<!-- btn insert data -->
<div class="form-group row">
<div class="offset-sm-3 col-sm-9 pull-right">
<button type="submit"id="save" name="save" class="btn btn-primary">Save</button>
</div>
</div>
</form><!--end form -->
</div><!--end container -->
</div><!-- Regular shadow -->
<script>
$(document).ready(function()
{
setTimeout(function()
{
$('#message').hide();
},3000);
});
</script>
</body>
</html>
|
4. process.php
<?php
include_once 'database.php';
$sucess = "";
if(isset($_POST['save']))
{
$first_name = $_POST['first_name'];
$last_name = $_POST['last_name'];
$city_name = $_POST['city_name'];
$email = $_POST['email'];
$sql = "INSERT INTO employee (first_name,last_name,city_name,email)
VALUES ('$first_name','$last_name','$city_name','$email')";
if (mysqli_query($conn, $sql))
{
$sucess = "Insert has been successfully.!";
}
else
{
echo "Error: " . $sql . "
" . mysqli_error($conn);
}
mysqli_close($conn);
}
?>
<?php
include_once 'database.php';
$sucess = "";
if(isset($_POST['save']))
{
$first_name = $_POST['first_name'];
$last_name = $_POST['last_name'];
$city_name = $_POST['city_name'];
$email = $_POST['email'];
$sql = "INSERT INTO employee (first_name,last_name,city_name,email)
VALUES ('$first_name','$last_name','$city_name','$email')";
if (mysqli_query($conn, $sql))
{
$sucess = "Insert has been successfully.!";
}
else
{
echo "Error: " . $sql . "
" . mysqli_error($conn);
}
mysqli_close($conn);
}
?> |
5. retrieve.php
<?php
include_once 'database.php';
$result = mysqli_query($conn,"SELECT * FROM employee");
?>
<!DOCTYPE html>
<html>
<head>
<title> Retrive data</title>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<!-- library -->
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.1.3/css/bootstrap.css">
<link rel="stylesheet" href="https://cdn.datatables.net/1.10.20/css/dataTables.bootstrap4.min.css">
<!-- library bootstrap -->
<script src="https://code.jquery.com/jquery-3.3.1.js"></script>
<script src="https://cdn.datatables.net/1.10.20/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.10.20/js/dataTables.bootstrap4.min.js"></script>
</head>
<body>
<br>
<br>
<div class="container">
<div class="container-fluid ">
<!-- table user all -->
<table id="tableHorizontalWrapper" class="table table-striped table-bordered table-sm text-center" cellspacing="0"width="100%">
<thead>
<tr>
<th>First Name</th>
<th>Last Name</th>
<th>City</th>
<th>Email</th>
</tr>
</thead>
<tbody>
<?php
while($row = mysqli_fetch_array($result))
{
?>
<tr>
<td><?php echo $row["first_name"]; ?></td>
<td><?php echo $row["last_name"]; ?></td>
<td><?php echo $row["city_name"]; ?></td>
<td><?php echo $row["email"]; ?></td>
</tr>
<?php
}
?>
<?php
// close connection database
mysqli_close($conn);
?>
</tbody>
</table>
</div>
</div>
<!-- js -->
<script>
// function data table
$(document).ready(function ()
{
$('#tableHorizontalWrapper').DataTable(
{
"scrollX": true
});
$('.dataTables_length').addClass('bs-select');
});
</script>
</body>
</html> |
0 Comments
CAN FEEDBACK
Emoji