PHP Simple Update using PDO in Bootstrap Tutorial

For Source Click Here

Update Statement is very common and used to update or edit existing records in database.PHP Simple Update using PDO in Bootstrap is a tutorial in which we will learn how to update data in database 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 update. 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 everything updated successfully. This tutorial contains demo and source in which you can test INSERT, DELETE 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 update
• How to bind values for query.
• How to execute update query.
• How to show a success message for update.

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.
• FruitUMy.php is about prepared query for data update 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 update.
• assets folder contains necessary css files, icons, images and js files.

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);

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

• <a href="#updateit" data-toggle="modal" class="btn btn-warning">Update</a>
  

3. Modal will be open when Update Button pressed which will display below form for Update of Fruit rates. Form code is in footer.php page which is included in AdminIndex.php. This form contains current fruit names all other fruit names in drop down select option for update. When we press Change Druit Name Button it will send values as POST method to FruitUMy.php.

• <div id="updateit" 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">Update Fruit Name</h3>
  </div>
  <div class="modal-body">
  <form class="form-horizontal" method="post" action="FruitUMy.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="">Current 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">
  <label class="control-label" for="Fruitname2">Fruit Name</label>
   <div class="controls">
            <select name="Fruitname2" id="Fruitname2"  required="required">
                <option value="">New 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">
   <div class="controls">
                  <input type="submit" name="submit" id="submit" class="btn btn-warning" value="Change Fruit Name">
   </div>
  </div>
  </form>
  
   </div>
  </div> 

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 discuss our FruitUMy.php which is used for getting values from form and then Update these in database using PDO query.

• <?php
  // We will include connection file first
  include('configPDO.php');
  // check if variable is set and Change Fruit Name Button pressed.
  if(isset($_POST["submit"])=="Change Fruit Name")
  {
  // Define Variables and getting values.
  $Fruitname1   =  $_POST[Fruitname1];     //Fruitname1
  $Fruitname2   =  $_POST[Fruitname2];     //Fruitname2
  
  // We Will prepare SQL Query
      $STM = $dbh->prepare("UPDATE fruitsratelist SET Fruitname=:Fruitname2 WHERE Fruitname=:Fruitname1");
  // bind parameters, Named parameters always start with colon(:)
      $STM->bindParam(':Fruitname1', $Fruitname1);
   $STM->bindParam(':Fruitname2', $Fruitname2);
  // 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?FruitstatsUpdated=77083368");             
  }
  ?>

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("UPDATE fruitsratelist SET Fruitname=:Fruitname2 WHERE Fruitname=:Fruitname1");
  $STM->bindParam(':Fruitname1', $Fruitname1);
  $STM->bindParam(':Fruitname2', $Fruitname2);
  $STM->execute();
  

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

• $STM = $dbh->prepare("UPDATE fruitsratelist SET Fruitname=? WHERE Fruitname=?");
  $STM->bindParam(':Fruitname1', $Fruitname1);
  $STM->bindParam(':Fruitname2', $Fruitname2);
  $STM->execute();
  

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

• $STM = $dbh->prepare("UPDATE fruitsratelist SET Fruitname=$Fruitname1 WHERE Fruitname=$Fruitname2");
  $STM->execute();
  

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

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

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

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

We used following tools and technologies for developing PHP Simple Update 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 like PHP Simple Update 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 our Facebook Page.

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.

PHP MySQL Easy CSV Data Insertion in Bootstrap using PDO Tutorial

PHP MySQL Easy CSV Data Insertion in Bootstrap using PDO is a tutorial in which you will learn how to import data in MySQL Database via CSV File while using PDO (PHP Data Objects) Prepared statements. We are using Bootstrap as CSS Framework in this tutorial for creating CSV File Upload Form and other functionality. for Demo we will make excel file in the format given in sample excel file.

For Source Code Please Click Here

 Then we will save it as csv (comma separated values) file and press CSV Upload Button and choose test.csv file and Press Save CSV Button. Data will be moved in the temporary table and after upload we will redirected to index.php page where message will be shown for success or failure. In index.php it will also show your uploaded data in a table. We will learn

• How to make a Form for CSV File Upload.
• How to create a Temporary Table.
• How to make a Connection Page
• How to make an Insertion Page using PDO Queries
• How to show a success and Warning message.

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

• sql.txt contains full database structure along with sample data.
• index.php is file from where you can upload CSV File and view results.
• Uploadmypdo.php is file in which code of getting CSV File and Upload using PDO queries written.
• configPDO.php is a file which will create connection with database.
• Footer.php is a file which contains footer bar and form for CSV Upload.
• assets folder contains necessary css, icon, images and js files.

We use following for developing PHP MySQL Easy CSV Data Insertion in Bootstrap using PDO 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.

1. First we will make a upload CSV button in index.php page and use data-toggle as modal and give href value as #fileupload

• <a href="#fileupload" data-toggle="modal" class"btn btn-primary btn-large">Upload CSV</a>
  

2. When we press upload CSV button, modal will be open which will display below form for Upload CSV. Form code is in footer.php page and its a included in index.php page.

• <div id="fileupload" 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">Upload Data via CSV File</h3>
              </div>
              <div class="modal-body">
  <form class="form-horizontal" enctype="multipart/form-data" method="post" action="Uploadmypdo.php">
              <div class="control-group">
                <label class="control-label" for="inputName">Save CSV File</label>
                <div class="controls">
                <input type="hidden" name="MAX_FILE_SIZE" value="9999999" />
                <input class="btn btn-info" name="file" type="file" id="file" onchange="showCode()" onblur="showCode()" onclick="showCode" required="required"  />
                </div>
              </div>
              <div class="control-group">
                <div class="controls">
                  <button type="submit" class="btn">Save CSV</button>
                </div>
              </div>
            </form>
  
      </div>
  </div>

3. Now its time for creating sample database csvsignindb using sql.txt file which contains following table create queries and sample data for demo and test.
• statstrackertemp table contains fields like ServerName, HiMemUti, AvgMemUti, HiCpuUti, AvgCpuUti, HiIOPerSec, AvgIOPerSec, HiDiskUsage, AvgDsikUsage.

4. configPDO.php file use a PDO (PHP Data Objects) for connection to MySQL Databaseto avoid SQL injections.

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

5. For inserting CSV Data in MySQL Database using PDO (PHP Data Objects) Prepared queries we will write code like below with explanation using comments.

• <?php 
   session_start();
  // mysql hostname
   $hostname = 'localhost';
  // mysql username
   $username = 'root';
  // mysql password
   $password = '';
  // Get Extension of the File.
   $extension= end(explode(".", basename($_FILES['file']['name'])));
  // isset Determines if a variable is set and is not NULL. Set Size Limit less then 10 MB=10485760 bytes. Extension must be CSV.
   if (isset($_FILES['file']) && $_FILES['file']['size'] < 10485760 && $extension== 'csv')
   {  
  // We will get csv file and save it in a $file
      $file = $_FILES['file']['tmp_name']; 
  //$handle is a valid file pointer to a file successfully opened by fopen(), popen(), or fsockopen(). fopen() used to open file.
      $handle = fopen($file, "r"); 
  // We will use try{} Catch() statements here.
       try { 
  // Database Connection using PDO
    $dbh = new PDO("mysql:host=$hostname;dbname=csvsignindb", $username, $password);
  // Truncate Table before insertion
    $STMR = $dbh->prepare("TRUNCATE TABLE statstrackertemp");
    $STMR->execute();
  // Prepare the statement for the insertion in the table
          $STM = $dbh->prepare('INSERT INTO statstrackertemp (ServerName, HiMemUti, AvgMemUti, HiCpuUti, AvgCpuUti, HiIOPerSec, AvgIOPerSec, HiDiskUsage, AvgDsikUsage) VALUES (?, ?, ?, ?, ?,?, ?, ?, ? )');
  //Check handel is True or False
     if ($handle !== FALSE) 
     {
  // fgets() Gets a line from file pointer and read the first line from $handle and ignore it.   
            fgets($handle);
  // While loop used here and  fgetcsv() parses the line it reads for fields in CSV format and returns an array containing the fields read.
            while (($data = fgetcsv($handle, 1000, ',')) !== FALSE)
      {
  // For Executing prepared statement we will use below function
               $STM->execute($data);
            }       
  //The file pointed to by handle will be closed.
            fclose($handle);
      
  // Closing MySQL database connection
         $dbh = null; 
  // If data inserted successfully we will redirect this page to index.php and show success message there with code 77083368
      header( "location:index.php?ServerStatsAdded=77083368"); 
      
        
     }
  
       }
  // Exceptions error will be thrown if Database not connected. 
    catch(PDOException $e)
    {
          die($e->getMessage());
       }
  
  
   }
   else 
   {
  // Error mesage id File type is not CSV or File Size is greater then 10MB.
      header( "location:index.php?ServerStatsAdded=37767");
   }
  ?>

6. After processing of CSV File it will redirected to index.php page where we will show success of failure message using codes return from Uploadmypdo.php

• if($_GET["ServerStatsAdded"]==77083368)  
  {
  echo "<div class='alert alert-success'>"; 
  echo "<button type='button' class='close' data-dismiss='alert'>×</button>"; 
  echo "Server Stats added in System."; 
  echo "</div>";
  }
  if($_GET["ServerStatsAdded"]==37767)  
  {
  echo "<div class='alert alert-block'>"; 
  echo "<button type='button' class='close' data-dismiss='alert'>×</button>";
  echo "<h4>Warning!</h4>"; 
  echo "Many Reasons for not adding stats. it can be wrong file format, file size greater then 10 MB, data already in system."; 
  echo "</div>"; 
  }

Thanks for reading. Enjoy and share with friends.

We hope you will find PHP MySQL Easy CSV Data Insertion in Bootstrap using PDO 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.