Introduction
To retreive data from any database, you have to connect to that database, then send a query that binds the form you are working on with the database in order to extract data and display it. And, finally close the connection.
To avoid unexpected possible attacks to your database, you have to use a secure query and that's the subject of our current tutorial.
We will use the prepared statements as they are truly secure method to avoid possible attacks.
Content
- Screen Shots
- 1- Database Connection.
- 2- Database content.
- 3- HTML code.
- 4- Retreiving data securely
-
Screenshots
1- Database Connection
To handle the connection to our database, add the following code each time you need to retreive data from database. You can also put it in an independant file and just include that file each time you need connection to database :<?php
//Database Connection file. Update with your Database information .
define ("DB_HOST", "localhost"); //Databse Host.
define ("DB_USER", "root"); //Databse User.
define ("DB_PASS", ""); //database password.
define ("DB_NAME", "tutos"); //database Name.
$db = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);
//
//$db->set_charset('utf8');
mysqli_set_charset($db,"utf8");
if($db->connect_errno > 0){
die('Unable to connect to database ['.$db->connect_error.']');
}
Update the file with your database information
2- Database Content
Create a table named
products in your database and add the following SQL code :
--
-- Table structure for table `products`
--
CREATE TABLE `products` (
`productId` int(11) NOT NULL,
`productBarcode` varchar(20) NOT NULL,
`productName` varchar(100) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Dumping data for table `products`
--
INSERT INTO `products` (`productId`, `productBarcode`, `productName`) VALUES
(1, '1254785496325', 'Fruit Juice Tropical CAN 250 ml'),
(2, '1254236552148', 'Fruit Juice Mojito CAN 250 ml'),
(3, '1254785665923', 'Carbonated Drink Cola 300 ml'),
(4, '1254785696523', 'Carbonated Juice 250 ml'),
(5, '1236521545879', 'Fried Chicken Frozen 500 g'),
(6, '1254785696325', 'Fresh Fish 1 Kg');
--
-- Indexes for dumped tables
--
--
-- Indexes for table `products`
--
ALTER TABLE `products`
ADD PRIMARY KEY (`productId`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `products`
--
ALTER TABLE `products`
MODIFY `productId` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;COMMIT;3- HTML Code
In this tutorial, we need to retreive data of a product which code is entered by the user. For that, we added an input text to type the product code and a "Check" button that will handle the action of connecting to the database and retreiving the data.
Add the following code to your index page :
<?php
<form method="POST" action ="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]);?>">
<div class="form-group ">
<label>Enter Product ID</label>
<input type="text" name="productId" class="form-control" value="<?php echo $param_id; ?>">
</div>
<div class="form-group ">
<button type="submit" class="btn btn-primary">Search </button>
</div>
</form>
<hr>
<?php
//dispaly product details only after receiving success response from the database.
// which means that the product Id is not empty and already exists in the database
if (isset($param_id) && $param_id !='') {
?>
<div class="form-group">
<label>Barcode</label>
<p class="form-control-static"><?php echo $barcode; ?></p>
</div>
<div class="form-group">
<label>Description</label>
<p class="form-control-static"><?php echo $name; ?></p>
</div>
<div class="form-group">
<label>Unit Price</label>
<p class="form-control-static"><?php echo $price; ?></p>
</div>
<?php } ?>
As you can see, the product information retreived from the database will not appear unless after receiving a successfull response from the database that the product exists.
4- Retreiving data securely
To retreive data, we have to send the SQL query to the databse with the product Id so as it search if it really exists. If so, it will answer to the query with the details of that product. Otherwise, a "NOT FOUND" message will appear.
We chose to use a prepared statement as it's a truly secure way to avoid attacks.
Insert the following code just before the begining of the form :
<?php
$param_id =$barcode =$name =$price ='';
// Processing form data when form is submitted
if($_SERVER["REQUEST_METHOD"] == "POST"){
// Prepare a select statement
$sql = "SELECT * FROM products WHERE productId = ?";
if($stmt = mysqli_prepare($db, $sql)){
// Bind variables to the prepared statement as parameters
mysqli_stmt_bind_param($stmt, "i", $param_id);
// Set parameters
$param_id = trim($_POST["productId"]);
// Attempt to execute the prepared statement
if(mysqli_stmt_execute($stmt)){
$result = mysqli_stmt_get_result($stmt);
if(mysqli_num_rows($result) == 1){
/* Fetch result row as an associative array. Since the result set contains only one row, we don't need to use while loop */
$row = mysqli_fetch_array($result, MYSQLI_ASSOC);
// Retrieve individual field value
$barcode = $row["productBarcode"];
$name = $row["productName"];
$price = $row["UnitPrice"];
} else{
// Non valid id parameter.
echo '<div class="alert alert-danger"> No product found for the entered ID </div>';
}
} else{
echo '<div class="alert alert-danger">Oops! Something went wrong. Please try again later.</div>';
}
}
// Close statement
mysqli_stmt_close($stmt);
}
?>
The code is well commented so as you can understand how things work.