difference-truncate-delete-mysql with example

DELETE and TRUNCATE differ in some more important and subtle ways than the auto increment counter.
The most important difference is DELETE operations are transaction-safe and logged, which means DELETEs can be rolled back. TRUNCATE cannot be done inside a transaction and can’t be rolled back. Because TRUNCATE is not logged recovering a mistakenly TRUNCATEd table is a much bigger problem than recovering from a DELETE.
DELETE will fail if foreign key constraints are broken; TRUNCATE may not honor foreign key constraints (it does for InnoDB tables). DELETE will fire any ON DELETE triggers; TRUNCATE will not.
TRUNCATE is probably better thought of as a shortcut for DROP TABLE/CREATE TABLE rather than a quirky kind of DELETE.
These differences are not specific to mySQL — PostgreSQL, MS SQL Server, and Oracle behave more or less the same way.
Well this is very common but much confused discussion. This question is often asked from freshers in interviews as well. The difference is pretty simple but yet people confuse it because they see the result to be the same of both the queries.
Truncate and Delete are both SQL commands which result in removing the table records. So lets list the differences one by one :
Type of Command – Truncate is a DDL command and Delete is a Dml command
RollBack - As mentioned above Truncate is DDL command, so the changes made by it are committed automatically hence there is nothing called rollback when you use truncate, while Delete commands can be rolled back
Table Structure – When you use Truncate command, all the rows in the table are delete and the structure of the table is recreated and so does the indexes. On the contrary if you use Delete command only the desired rows or all the rows are deleted and the structure remains unchanged.
Syntax - The syntax for both the commands is :

Truncate table <tablename> #command to truncate a table. Delete from <tablename> #command to delete all the records from table.

Practical example -

#creates a table with 2 columns, 1st column is auto incremented Create table mysqlDemo (id integer not null auto_increment,name varchar(100),PRIMARY KEY(id));   #now insert two records in the table insert into mysqlDemo(name)values ('sachin'); insert into mysqlDemo(name)values ('digimantra');   #check the records and note their auto_increment values select * from mysqlDemo;   #Let us try delete it using Delete command delete from mysqlDemo;   #Now the table is empty, lets insert values from the first row. insert into mysqlDemo(name)values ('new_sachin'); insert into mysqlDemo(name)values ('new_digimantra');   #check the records and note their auto_increment values select * from mysqlDemo; #the aut_increment values will continue from the last records, as the table structure is preserved.   #Now let us Truncate the table and re-insert the values. Truncate table mysqlDemo; insert into mysqlDemo(name)values ('sachin'); insert into mysqlDemo(name)values ('digimantra');   #check the records and note their auto_increment values select * from mysqlDemo;   #this time the auto_increment value will start from one, as the table structure is recreated because we used Truncate instead of Delete.

So this is what the difference is, in short always remember Truncate command recreates the structure of the table and deletes all the records of the table. However Delete command does not recreates the structure and deletes the complete or partial records (as desired) from the table.

OOP Pattern - Factory: Simple Factory Pattern

The factory method pattern is an object-oriented creational design pattern to implement the concept of factories and deals with the problem of creating objects (products) without specifying the exact class of object that will be created. The essence of this pattern is to "Define an interface for creating an object, but let the classes that implement the interface decide which class to instantiate. The Factory method lets a class defer instantiation to subclasses."^[1]
Creating an object often requires complex processes not appropriate to include within a composing object. The object's creation may lead to a significant duplication of code, may require information not accessible to the composing object, may not provide a sufficient level of abstraction, or may otherwise not be part of the composing object's concerns. The factory method design pattern handles these problems by defining a separate method for creating the objects, which subclasses can then override to specify the derived type of product that will be created.
Some of the processes required in the creation of an object include determining which object to create, managing the lifetime of the object, and managing specialized build-up and tear-down concerns of the object. Outside the scope of design patterns, the term factory method can also refer to a method of a factory whose main purpose is creation of objects.

PHP Factory Pattern Step By Step Tutorial - Part 1: In Object Oriented Programming, we always create new object with new operator. And in some cases, we need call same class for different steps. We can create a factory to avoid always call same class. The factory pattern encapsulates the creation of objects.
This is simple example that we always create new object every function that need it.

<?php
class Vehicle{

  function category($wheel = 0){
   if($wheel == 2){
  return "Motor";
 }elseif($wheel == 4){
  return "Car";
 }
  }
}

class Spec{
 
  var $wheel = '';
 
  function Spec($wheel){
 $this->wheel = $wheel; 
  }
 
  function name(){
 $wheel = $this->wheel;
   return Vehicle::category($wheel);
  }

  function brand(){
 $wheel = $this->wheel;  
   $kind =& Vehicle::category($wheel);
 if($kind == "Motor"){
  return array('Harley','Honda');
 }elseif($kind = "Car"){
  return array('Nisan','Opel'); 
 } 
  }
 
}

$kind = new Spec(2);
echo "Kind: ".$kind->name();
echo "<br>";
echo "Brand: ".implode(",",$kind->brand());
?>

This picture show how to implement factory pattern:

This is complete code after changing (implement factory pattern)

<?php
class Vehicle{

  function category($wheel = 0){
   if($wheel == 2){
  return "Motor";
 }elseif($wheel == 4){
  return "Car";
 }
  }
}

class Spec{
 
  var $wheel = '';
  var $vc    = '';
   
  function Spec($wheel){
 $this->wheel = $wheel;
 $this->_getVehicle();
  }
  
  function &_getVehicle(){
 $wheel = $this->wheel;
   $this->vc =& Vehicle::category($wheel);
  }
 
  function name(){
   return $this->vc;
  }

  function brand(){
 if($this->vc == "Motor"){
  return array('Harley','Honda');
 }elseif($this->vc = "Car"){
  return array('Nisan','Opel'); 
 } 
  }
 
}

$kind = new Spec(2);
echo "Kind: ".$kind->name();
echo "<br>";
echo "Brand: ".implode(",",$kind->brand());
?>

In this practice, we create a factory within the object itself.