PHP Simple Insert using PDO in Bootstrap Tutorial

PHP Simple Insert using PDO in Bootstrap is a tutorial in which we will learn how to insert data in database using PDO (PHP Data Objects) prepared statements while using Bootstrap CSS Framework. In Demo we will create database and then connect MySQL database using PDO (PHP Data Objects) and then use form to insert data in database and then show it using prepared select queries. Demo also contain UPDATE, DELETE and SELECT options which will be cover in next tutorials.

Source:  Click Here


We will learn

• How to make a Form for adding fruit rates.
• How to create a fruit list table for demo.
• How to make a Connection Page.
• How to get data from form.
• How to prepare query for insert
• How to bind values for query.
• How to execute insert query.
• How to show a success message.

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 have various options like INSERT, UPDATE, DELETE, SELECT buttons along with tables for showing data.
• FruitMy.php is about prepared query for data insert 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 Rate Data insert.
• Export-To-Excel.php is a file in which export to excel code available for you.
• assets folder contains necessary css files, icons, images and js files.

1. We will create a button for INSERT inside option side nav in AdminIndex.php page with id addin and set data-toggle as modal for showing reference DIV in a modal. We set button class as info button.

• <a href="#addin" data-toggle="modal" class="btn btn-info">Insert</a>
  

2. Modal will be open when Insert Button updates which will display below form for Addition of Fruit rates. Form code is in footer.php page and its a included in AdminIndex.php. This form contains fruit names in select option and rate field for adding fruit details. When we press Add Rate Button it will send values as POST method to FruitMy.php.

• <div id="addin" 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">Add Fruit Name and Rate</h3>
  </div>
  <div class="modal-body">
  <form class="form-horizontal" method="post" action="FruitMy.php">
  
  <div class="control-group">
  <label class="control-label" for="Fruitname">Fruit Name</label>
  <div class="controls">
  <select name="Fruitname" id="Fruitname"  required="required">
  <option value="">Fruit Name</option>
  <option value="Banana">Banana</option>
  <option value="Pear">Pear</option>
  <option value="Orange">Orange</option>
  <option value="Kiwi">Kiwi</option>
  <option value="Mango">Mango</option>       
  <option value="Lemon">Lemon</option>
  <option value="Peach">Peach</option>
  <option value="Cherry">Cherry</option>
  <option value="Strawberry">Strawberry</option>
  <option value="Apricot">Apricot</option>
  <option value="Apple">Apple</option>
  </select>
                
  </div>
  </div>
  <div class="control-group">
  <label class="control-label" for="Fruit Rate">Fruit Rate</label>
  <div class="controls">
  <input type="text" name="a1"  id="a1" pattern="^\d{0,2}(\.\d{0,2}){0,1}$" data-validation-pattern-message="Must be a Double Number'"   placeholder="Fruit Rate" required="required">
  </div>
  </div>
             
  
  <div class="control-group">
  <div class="controls">
  <input type="submit" name="submit" id="submit" class="btn btn-info" value="Add Rate">
  </div>
  </div>
  </form>
  
  </div>
  </div> 

3. Before explaining FruitMy.php which covers prepared insert query 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);

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();
      }
  ?>

5. Now we will disucess our FruitMy.php which is used for getting values from from and then insert these in database using PDO query.

• <?php
  // We will include connection file first
  include('configPDO.php');
  // check if variable is set and Add Rate Button pressed.
  if(isset($_POST["submit"])=="Add Rate")
  {
  // Define Variables
  $Fruitname   =  $_POST[Fruitname];     //Fruitname
  $a1     =  $_POST[a1];       //Rate
  
  // We Will prepare SQL Query
      $STM = $dbh->prepare("INSERT INTO fruitsratelist(Fruitname, Rate) VALUES (:Fruitname,:a1)");
  // bind paramenters, Named parameters always start with colon(:)
      $STM->bindParam(':Fruitname', $Fruitname);
   $STM->bindParam(':a1', $a1);
  // 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?FruitstatsAdded=77083368");             
  }
  ?>

Note: We can use 3 various methods to insert data into MySQL database using PDO Queries.
• In Demo we used named parameters key with prepared statement. Binding parameter is must before execution using this method.

• $STM = $dbh->prepare("INSERT INTO fruitsratelist(Fruitname, Rate) VALUES (:Fruitname,:a1)");
  $STM->bindParam(':Fruitname', $Fruitname);
  $STM->bindParam(':a1', $a1);
  $STM->execute();
  

OR
• We can also use unnamed parameters key for insertion of data. Binding parameter is also must before execution for this method too.

• $STM = $dbh->prepare("INSERT INTO fruitsratelist(Fruitname, Rate) VALUES (?,?)");
  $STM->bindParam(':Fruitname', $Fruitname);
  $STM->bindParam(':a1', $a1);
  $STM->execute();
  

OR
• We can set values directly without binding parameters but it is not good practice for avoiding SQL Injections.

• $STM = $dbh->prepare("INSERT INTO fruitsratelist(Fruitname, Rate) VALUES ($Fruitname,$a1)");
  $STM->execute();
  

• For Getting how many records inserted we will use rowcount() function.

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

6. After data insert it will redirected to Adminindex.php page where we will show success message using codes return from FruitMy.php

• if($_GET["FruitstatsAdded"]==77083368)  
  {
  echo "<div class='alert alert-success'>"; 
  echo "<button type='button' class='close' data-dismiss='alert'>×</button>"; 
  echo "Fruit Rate added in this Demo System."; 
  echo "</div>";
  }

We used below tools and technologies for developing PHP Simple Insert 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.

We hope you will find PHP Simple Insert using PDO in Bootstrap Tutorial very helpful and easy. PDO (PHP Data Objects) Prepared statements used in this tutorial along with Bootstrap CSS Framework for better user experience. For any query/suggestions please post a comment on our Facebook Page.