PHP MySQL Simple Select using PDO in Bootstrap Tutorial

 Source: Click here

Introduction

This tutorial is about PDO (PHP Data Objects) select statement for showing data from MySQL table on a PHP Page. We will use three various methods to select data from database using Prepared queries. We will learn

• How to make a Connection Page.
• How to prepare query for Select
• How to bind values for query.
• How to execute select query.

Connect to Database

First we will make configPDO.php file which use a PDO (PHP Data Objects) for connecting MySQL Database.

<?php
$hostname = 'localhost';
$username = 'root';
$password = '';
try {
$dbh = new PDO("mysql:host=$hostname;dbname=YourDatabaseName", $username, $password);
    }
catch(PDOException $e)
    {
    echo $e->getMessage();
    }
?>

Prepare and Execute Select Queries

Now we will discuss how to prepare and execute PDO queries. We will use

fetchAll();

for getting all results.

<?php
include('configPDO.php');
$Fruitname    =  "Apple";     //Fruit name
$STM = $dbh->prepare("SELECT  `Fruitname`, FORMAT(AVG(`Rate`),2) FROM fruitsratelist WHERE Fruitname=:Fruitname");
$STM->bindParam(':Fruitname', $Fruitname);
$STM->execute();   
$STMrecords = $STM->fetchAll();
foreach($STMrecords as $row)
{
echo $row[0];
echo $row[1]; 
}
?> 

OR

We can also use just fetch

fetch();

Only gets one row. So no foreach loop needed.

<?php
include('configPDO.php');
$Fruitname    =  "Apple";     //Fruit name
$STM = $dbh->prepare("SELECT  `Fruitname`, FORMAT(AVG(`Rate`),2) FROM fruitsratelist WHERE Fruitname=:Fruitname");
$STM->bindParam(':Fruitname', $Fruitname);
$STM->execute();   
$STMrecords = $STM->fetch();
echo $row[0];
echo $row[1]; 
?> 

Prepared Queries Methods

There are three various methods to write PDO queries.

In this Demo we used named parameters key with prepared statement. Binding parameter is must before execution using this method.

$STM = $dbh->prepare("SELECT  `Fruitname`, FORMAT(AVG(`Rate`),2) FROM fruitsratelist  WHERE Fruitname=:Fruitname");
$STM->bindParam(':Fruitname', $Fruitname);
$STM->execute();

OR

Other method is to use unnamed parameters with question mark (?)

$STM = $dbh->prepare("SELECT  `Fruitname`, FORMAT(AVG(`Rate`),2) FROM fruitsratelist  WHERE Fruitname=?");
$STM->bindParam(1, $Fruitname);
$STM->execute();

OR

Below method is without binding and it can make code ripe for SQL Injections.

$STM = $dbh->prepare("SELECT  `Fruitname`, FORMAT(AVG(`Rate`),2) FROM fruitsratelist  WHERE Fruitname=$Fruitname1");
$STM->execute();

Tools Required

We used following tools and technologies for developing PHP MySQL Simple Select using PDO in Bootstrap Tutorial.

• PHP as Server Side Scripting Language
• MySQL as a Database.
• Bootstrap as CSS Framework.
• JQuery as a service.
• phpMyAdmin for creating database and tables.
• Sublime Text 2+ as a code editor.
• XAMPP as a package for Apache web server, PHP and MySQL.
• Google Chrome as a browser for testing.

Thanks for reading. Enjoy and share with friends.

Conclusion

We hope you will like PHP MySQL Simple Select using PDO in Bootstrap Tutorial and find it easy. We used latest PDO (PHP Data Objects) Prepared statements along with Bootstrap CSS Framework 2.3.2 for making this tutorial quickly. For any query/suggestions please send message on our Facebook Page.

PHP MySQL Simple Delete using PDO in Bootstrap Tutorial

 Furqan Aziz |  28 Dec 2013 |  Tutorials

Introduction

Delete Statement is very common and used to Delete existing records in database table.PHP MySQL Simple Delete using PDO in Bootstrap is a tutorial in which we will learn how to delete data in database table using PDO (PHP Data Objects) prepared statements while using Bootstrap CSS Framework. We will create table fist and then insert some records in it, then we will create a form for delete. Then we will use Post method in form to send values to another page and then we will get values, bind them and use them in prepare statements. Finally we will show the success message if data Deleted successfully. This tutorial contains demo and source in which you can test INSERT, UPDATE and SELECT options at one place. We will learn

• How to create a fruit list table for demo.
• How to make a Connection Page.
• How to prepare query for delete
• How to bind values for query.
• How to execute delete query.
• How to show a success message for Delete.

Source Files and Folders Explanation

Source Code Folder contains many files and folders, below is the explanation of each files and folders.

• sql.txt contains full database structure along with sample data.
• index.php is a startup page which contains try it button.
• AdminIndex.php is a page which has various options like INSERT, UPDATE, DELETE, SELECT buttons along with tables for showing data.
• FruitDMy.php is about prepared query for Delete functionality.
• configPDO.php is a file which will create connection with MySQL database.
• footer.php is a file which contains footer bar and form for Fruit name delete.
• assets folder contains necessary css files, icons, images and js files.

Create Table and Sample Data

1. We will create sample table in our database using sql.txt file which contains following table create queries and sample data for demo and test.
• fruitsratelist table contains fields like Fruitname and its Rate.

• CREATE TABLE IF NOT EXISTS `fruitsratelist` (
    `Fruitname` varchar(15) NOT NULL,
    `Rate` double NOT NULL
  ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

• INSERT INTO `fruitsratelist` (`Fruitname`, `Rate`) VALUES
  ('Apple', 70),
  ('Apple', 90),
  ('Mango', 60),
  ('Banana', 70),
  ('Strawberry', 80),
  ('Apple', 40),
  ('Lemon', 70),
  ('Kiwi', 80);

Create Delete Button

2. We will create a button for DELETE inside option side nav in AdminIndex.php page with id deleteit and set data-toggle as modal for showing reference DIV in a modal. We set button class as danger button.

• <a href="#deleteit" data-toggle="modal" class="btn btn-danger">Delete</a>
  

Create Form in Modal

3. Modal will be open when Delete Button pressed which will display below form for Delete of Fruit Names. Form code is in footer.php page which is included in AdminIndex.php. This form contains fruit names in drop down select option for delete. When we press Delete Fruit Records Button it will send values as POST method to FruitDMy.php.

• <div id="deleteit" class="modal hide fade" tabindex="-1" role="dialog" aria-labelledby="myModalLabel" aria-hidden="true">
  <div class="modal-header">
  <button type="button" class="close" data-dismiss="modal" aria-hidden="true">×</button>
  <h3 id="myModalLabel">Delete Fruit Name and Rates</h3>
  </div>
  <div class="modal-body">
  <form class="form-horizontal" method="post" action="FruitDMy.php">
  
  <div class="control-group">
  <label class="control-label" for="Fruitname1">Fruit Name</label>
  <div class="controls">
  <select name="Fruitname1" id="Fruitname1"  required="required">
  <option value="">Fruit Name</option>
  <?php
  include("configPDO.php");
  // We Will prepare SQL Query
  $STM = $dbh->prepare("SELECT DISTINCT  Fruitname FROM fruitsratelist");
  // bind parameters, Named parameters always start with colon(:)
  $STM->execute();
  // we will fetch records like this and use foreach loop to show multiple Results
  $STMrecords = $STM->fetchAll();
  foreach($STMrecords as $row)
  {
  echo"<option value='$row[0]'>$row[0]</option>"; 
  }      
  ?> 
  </select>
  
  </div>
  </div>
          
  
  <div class="control-group">
  <div class="controls">
  <input type="submit" name="submit" id="submit" class="btn btn-danger" value="Delete Fruit Records">
  </div>
  </div>
  </form>
  
  </div>
  </div>  
  

Connect to Database

4. configPDO.php file use a PDO (PHP Data Objects) for connecting MySQL Database.

• <?php
  // mysql hostname
  $hostname = 'localhost';
  // mysql username
  $username = 'root';
  // mysql password
  $password = '';
  // Database Connection using PDO
  try {
  $dbh = new PDO("mysql:host=$hostname;dbname=YourDatabaseName", $username, $password);
      }
  catch(PDOException $e)
      {
      echo $e->getMessage();
      }
  ?>

Prepare and Execute Delete Queries

5. Now we will discuss our FruitDMy.php which is used for getting values from the form and then Delete these in database using PDO query.

• <?php
  include('configPDO.php');
  if(isset($_POST["submit"])=="Delete Fruit Records")
  
  {
  // Define Variable
  $Fruitname1    =  $_POST[Fruitname1];     //Fruitname1
  
  // We Will prepare SQL Query
   $STM = $dbh->prepare("DELETE FROM fruitsratelist  WHERE Fruitname=:Fruitname1");
  // bind paramenters, Named paramenters alaways start with colon(:)
      $STM->bindParam(':Fruitname1', $Fruitname1);
  // For Executing prepared statement we will use below function
      $STM->execute(); 
  // We use header here for redirecting it to other page where we will show success message.    
  header( "location:AdminIndex.php?Fruitstatsdeleted=77083368");             
  }
  ?> 

Prepared Queries Methods

There are three various methods to write PDO queries.
• In this Demo we used named parameters key with prepared statement. Binding parameter is must before execution using this method.

• $STM = $dbh->prepare("DELETE FROM fruitsratelist  WHERE Fruitname=:Fruitname1");
  $STM->bindParam(':Fruitname1', $Fruitname1);
  $STM->execute();
  

OR
• Other method is to use unnamed parameters with question mark (?)

• $STM = $dbh->prepare("DELETE FROM fruitsratelist  WHERE Fruitname=?");
  $STM->bindParam(1, $Fruitname1);
  $STM->execute();
  

OR
• Below method is without binding and it can make code ripe for SQL Injections.

• $STM = $dbh->prepare("DELETE FROM fruitsratelist  WHERE Fruitname=$Fruitname1");
  $STM->execute();
  

• For Getting how many records Deleted we can use rowcount() function.

• $Records_Deleted= $STM->rowCount();
  

Show Success Message

6. After data Deleted We will redirected to Adminindex.php page where we will show success message using codes return from FruitDMy.php

• if($_GET["Fruitstatsdeleted"]==77083368)  
  {
  echo "<div class='alert alert-error'>"; 
  echo "<button type='button' class='close' data-dismiss='alert'>×</button>"; 
  echo "<h4>Updated.</h4>"; 
  echo "Fruit Name and all it rates deleted from this Demo System."; 
  echo "</div>";
  }

Tools Required

We used following tools and technologies for developing PHP Simple Delete using PDO in Bootstrap Tutorial.
• PHP as Server Side Scripting Language
• MySQL as a Database.
• Bootstrap as CSS Framework.
• JQuery as a service.
• jqBootstrapValidation for validating sign-in form.
• phpMyAdmin for creating database and tables.
• Sublime Text 2+ as a code editor.
• XAMPP as a package for Apache web server, PHP and MySQL.
• Google Chrome as a browser for testing.
Thanks for reading. Enjoy and share with friends.

Conclusion

We hope you will like PHP Simple Delete using PDO in Bootstrap Tutorial and find it easy. We used latest PDO (PHP Data Objects) Prepared statements in this tutorial with Bootstrap CSS Framework for making this quickly. For any query/suggestions please send message on ourFacebook Page.