Hello, Programmers!

Basic CRUD Operations for Demand Programming Languages

Core JavaScript CRUD

getData JavaScript


  function getData(){
    ///////////////////////// 
    url = "needyamin.txt";
    fetch(url).then((response)=>{
      return response.text();
      }).then((data)=>{
        console.log(data);
        })
   ///////////////////////// 
    }
  getData()

getData JSON JavaScript


  function getData(){
    ///////////////////////// 
    url = "https://api.github.com/users";
    fetch(url).then((response)=>{
      return response.json();
      }).then((data)=>{
        console.log(data);
        })
   ///////////////////////// 
    }
  getData()

postData JSON JavaScript



  //store in variable
  var nm = document.getElementById("name").value;
  var em = document.getElementById("email").value;
  var mb = document.getElementById("mobile").value;
  
  //array variable
  var data = {myname:nm, myemail:em, myphone:mb};
  
  
  //target form
  document.getElementById('formID').addEventListener('submit', function (e) {
    
    //prevent the normal submission of the form
      e.preventDefault();
      
      /////////////// AJAX START////////////////////
      url = "insert.php";
      params = {
      method: 'post',
      headers: {'Content-Type': 'application/json'},
      body: JSON.stringify(data),
      }
    fetch(url, params).then(response=> response.json()).then(data => console.log(data))
    /////////////// AJAX END////////////////////
     
         
  });

Linux Command Line

Create Folder

 
$ mkdir foldername

Delete Folder

 
$ rmdir foldername

Create File

 
$ touch filename.extension

Delete File

 
$ rm filename.extension

Read File In Terminal

 
$ cat filename

Edit File in Text Editor

 
$ nano filename

Symlink

 
root@linux:~/target_folder_in$ sudo ln -s /home/target_folder_out/Filename_YAMiN.yml

Git Command Line

At first clone git repository

 
$ git clone http://github.com/needyamin/target-repository.git

Create an empty Git repository or reinitialize an existing one

 
$ git init

Add Your Edited Files to Git

 
$ git add .

Check Added Git Status

 
$ git status

Commit your added Git files

 
$ git commit -m "First commit"

Push Your Files to the Main Origin Git Repository

 
$ git push origin main

If want to make new Branch

 
$ git checkout -b yourbranchname

Database Connect

MySQLi Object-Oriented



$servername = "localhost";
$username = "username";
$password = "password";

// Connection Name
$conn = new mysqli($servername, $username, $password);

// Check Connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";

PHP Data Objects (PDO)


$servername = "localhost";
$username = "username";
$password = "password";

try {
  $conn = new PDO("mysql:host=$servername;dbname=myDB", $username, $password);
  
  // set the PDO error mode to exception
  $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  echo "Connected successfully";
} 

  catch(PDOException $e) {
  echo "Connection failed: " . $e->getMessage();
}

Create Statement

MYSQLi Create Statement


$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

$sql = "INSERT INTO MyGuests (firstname, lastname, email) VALUES ('John', 'Doe', 'john@example.com')"; 

if ($conn->query($sql) === TRUE) {
  echo "New record created successfully";
} else {
  echo "Error: " . $sql . "
" . $conn->error;
}

$conn->close();

PDO Create Statement


$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDBPDO";

try {
  $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
  
  // set the PDO error mode to exception
  $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  
  $sql = "INSERT INTO MyGuests (firstname, lastname, email) VALUES ('John', 'Doe', 'john@example.com')";
  
  // use exec() because no results are returned
  $conn->exec($sql);
  
  echo "New record created successfully";
} 

  catch(PDOException $e) {
  echo $sql . "" . $e->getMessage();
}

$conn = null;

Delete Statement

MYSQLi DELETE Statement


$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

// sql to delete a record
$sql = "DELETE FROM MyGuests WHERE id=3";

if ($conn->query($sql) === TRUE) {
  echo "Record deleted successfully";
} else {
  echo "Error deleting record: " . $conn->error;
}

$conn->close();

PDO DELETE Statement


$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDBPDO";

try {
  $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
  
  // set the PDO error mode to exception
  $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

  // sql to delete a record
  $sql = "DELETE FROM MyGuests WHERE id=3";

  // use exec() because no results are returned
  $conn->exec($sql);
  echo "Record deleted successfully";
} 

  catch(PDOException $e) {
  echo $sql . "" . $e->getMessage();
}

$conn = null;

Select Data Statement

MYSQLi Select Statement


$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

$sql = "SELECT id, firstname, lastname FROM MyGuests";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
  // output data of each row
  while($row = $result->fetch_assoc()) {
    echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "
";
  }
} else {
  echo "0 results";
}
$conn->close();

PDO Select Statement


$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);

// Check connection
if (!$conn) {
  die("Connection failed: " . mysqli_connect_error());
}

$sql = "SELECT id, firstname, lastname FROM MyGuests";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
	
  // output data of each row
  while($row = mysqli_fetch_assoc($result)) {
    echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "";
  }
} 

  else {
  echo "0 results";
}

mysqli_close($conn);

Update Data Statement

MYSQLi Update Statement


$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

$sql = "UPDATE MyGuests SET lastname='Doe' WHERE id=2";

if ($conn->query($sql) === TRUE) {
  echo "Record updated successfully";
} else {
  echo "Error updating record: " . $conn->error;
}

$conn->close();

PDO Update Statement


$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDBPDO";

try {
  $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
  
  // set the PDO error mode to exception
  $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

  $sql = "UPDATE MyGuests SET lastname='Doe' WHERE id=2";

  // Prepare statement
  $stmt = $conn->prepare($sql);

  // execute the query
  $stmt->execute();

  // echo a message to say the UPDATE succeeded
  echo $stmt->rowCount() . " records UPDATED successfully";
} 

  catch(PDOException $e) {
  echo $sql . "
" . $e->getMessage();
}

$conn = null;

Basic CRUD with POST Form Action

Basic Insert POST Method

  
    # Connect to mysql server 
    $connection = new mysqli ('localhost','username','password','database_name');

    # If Condition Check Error
    if ($connection->connect_error) {
        die("database not found"); #data stop;
    } 
    
    # If Condition Check True
    else {
    #print"success";
    }
    
   ############ When Clicked Action POST Start ############
    if(isset($_POST['submit_hit']))  {
    
  # Store post value data in Variables 
    $f = $_POST['fname']; 
    $l = $_POST['lname'];
    $e = $_POST['email'];
    $p = $_POST['password'];
    $p = md5($p);
    
    # Fire SQL Query
    $sql = "INSERT INTO `sign_up` (`id`, `fname`, `lname`, `email`, `password`) VALUES (NULL, '$f', '$l', '$e', '$p')";
    $connection->query($sql);
    echo "your sign up process is successfully done";
    
    }

 ############ When Clicked Action POST End ############

Basic SELECT

  
   # Connect To The Database Server 
    $connection = new mysqli ('localhost','needyamin','Yamin143','project2'); 
  
     # SELECT ALL FROM Database Table
    $f = "SELECT * FROM `sign_up`";
    $result = $connection->query($f);
    
     # While Loop Fetch All Table Rows
    while ($row = $result->fetch_assoc()){
      
   # Echo your Rows     
    echo $row['id'];
    echo $row['fname'];
    echo $row['lname'];
    echo $row['email'];
    echo $row['password'];  
    };

Database Table Query

Create a MySQL Table (DEMO DATABASE TABLE)



CREATE TABLE MyGuests (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)

SQL CRUD Operations

SQL Create/INSERT


INSERT INTO MyGuests (firstname, lastname, email) VALUES ('John', 'Doe', 'john@example.com')

SQL Read/SELECT


SELECT * FROM MyGuests

or

SELECT id, firstname, lastname FROM MyGuests

SQL Write/UPDATE


UPDATE MyGuests SET lastname='Doe' WHERE id=1

SQL Delete


DELETE FROM MyGuests WHERE id=3

Join Statement


Inner Join
SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;

LEFT JOIN
SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;

RIGHT JOIN
SELECT column_name(s) FROM table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name;

Full JOIN
SELECT column_name(s) FROM table1 FULL OUTER JOIN table2 ON table1.column_name = table2.column_name WHERE condition;

AND, OR and NOT Operators


AND 
SELECT  column1, column2, FROM table_name WHERE  condition1 AND  condition2 AND  condition3; 

OR 
SELECT  column1, column2, FROM table_name WHERE  condition1 OR  condition2 OR  condition3; 

NOT
SELECT column1, column2, FROM table_name WHERE NOT condition;

SQL LIKE %value%


SELECT column1, column2, FROM table_name WHERE columnN LIKE % pattern values %;

SQL DESC/ASC


DESC
SELECT column1, column2, FROM table_name ORDER BY column1, column2, DESC; 

ASC
SELECT column1, column2, FROM table_name ORDER BY column1, column2, ASC;

SQL ADVANCED


SQL BACKUP DATABASE  
BACKUP DATABASE testDB TO DISK = 'D:\backups\testDB.bak'; 

SQL DROP DATABASE  
DROP DATABASE databasename;

Django Statement

Update Statement

Create Statement

Total Statement

CDN JQuery


	
script src="https://code.jquery.com/jquery-3.6.0.min.js" integrity="sha256-/xUj+3OJU5yExlq6GSYGSHk7tPXikynS7ogEvDej/m4=" crossorigin="anonymous">

FORM


	
< form method="POST" action="" id="formID"> 
< input type="text" name="name" id="name" placeholder="Your Name"> < br>
< input type="email" name="email" id="email" placeholder="Email"> < br>
< input type="number" name="mobile" id="mobile" placeholder="+880"> < br>
< input type="submit" value="Send" id="JQSend">
< /form>



< div id="msg">  


< table border="1" id="table_output" style="padding:5px;">

phpJQueryAjax



	
//////// GET METHOD JQURY START //////////		
function showdata(){
	output = "";
	$.ajax({
		url: "get.php",
		method: "GET",
		dataType: 'json',
		success:function(data){
			console.log(data);
		/////////
		if(data){x = data;}
		else {x = ""}
		
		for(i=0; i < x.length; i++){
		//console.log(x[i].name);
			output +="< tr>< td>" + x[i].name + "< td>" + x[i].email + 
			"< td>" + x[i].mobile + "";
			}
			
			$("#table_output").html(output);
			
		//////////////	
		}
		
		
		})
	}
showdata()	
//////// GET METHOD JQURY END //////////	


	
//////// INSERT METHOD JQURY //////////	
	
//#JQSend is submit value ID and "e" for event object
$("#JQSend").click(function(e){
e.preventDefault(); //stop reload when click submit button
//console.log("YAMIN"); //check console log if it works of not

// store values from action (remember target ID name only)
let nm = $("#name").val();
let em = $("#email").val();
let mb = $("#mobile").val();

/*
console.log(nm);
console.log(em);
console.log(mb);
*/

mydata = {myname:nm, myemail:em, myphone:mb};

//console.log(mydata);

//////////////////////
$.ajax({
	url:"insert.php",
	method: "POST",
	data: JSON.stringify(mydata),
	
	//print success data from insert.php message start
	success: function(data){
		console.log(data);
		
	//print data in html
	msg ="< div>"+ data + "";
	$("#msg").html(msg);
	
	//clear the form
	$('#formID')[0].reset();
	
	//get data retry
	showdata()
			
	}
	//print success data from insert.php message end	

		});
//////////////////////

});

Create Database



SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `jQdata`
--

-- --------------------------------------------------------

--
-- Table structure for table `userinfo`
--

CREATE TABLE `userinfo` (
  `id` int NOT NULL,
  `name` varchar(100) NOT NULL,
  `email` varchar(100) NOT NULL,
  `mobile` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `userinfo`
--

INSERT INTO `userinfo` (`id`, `name`, `email`, `mobile`) VALUES
(15, 'Yamin', 'needyamin@ansnew.com', '+8801840538007');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `userinfo`
--
ALTER TABLE `userinfo`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `userinfo`
--
ALTER TABLE `userinfo`
  MODIFY `id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=16;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

insert.php


	
$conn = new mysqli("localhost","username","password","database_name");

// Check Connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}
//echo "Connected successfully";


$data = stripslashes(file_get_contents("php://input"));
$mydata = json_decode($data, true);

$name = $mydata['myname'];
$email = $mydata['myemail'];
$mobile = $mydata['myphone'];


$sql = "INSERT INTO `userinfo` (`id`, `name`, `email`, `mobile`) VALUES (NULL, '$name', '$email', '$mobile')";

if ($conn->query($sql) === TRUE) {
  echo "New record created successfully";
} else {
  echo "Error: " . $sql . "
" . $conn->error;
}

$conn->close();

get.php


$conn = new mysqli("localhost","username","password","database_name");

$sql = "SELECT * from userinfo";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
 $data = array();
  while($row = $result->fetch_assoc()) {
	  $data[] = $row;
	   }
}

echo json_encode($data)

Retrive Statement

Ajax Retrive Statement



//////// GET METHOD JQURY START //////////		
function showdata(){
	output = "";
	$.ajax({
		url: "get.php",
		method: "GET",
		dataType: 'json',
		success:function(data){
			console.log(data);
		/////////
		if(data){x = data;}
		else {x = ""}
		
		for(i=0; i < x.length; i++){
		//console.log(x[i].name);
			output +="" + x[i].name + "" + x[i].email + 
			"" + x[i].mobile + "";
			}
			
			$("#table_output").html(output);
			
		//////////////	
		}
		
		
		})
	}
showdata()	
//////// GET METHOD JQURY END //////////

INSERT Statement

Ajax INSERT Statement



	
//////// INSERT METHOD JQURY //////////	
	
//#JQSend is submit value ID and "e" for event object
$("#JQSend").click(function(e){
e.preventDefault(); //stop reload when click submit button
//console.log("YAMIN"); //check console log if it works of not

// store values from action (remember target ID name only)
let nm = $("#name").val();
let em = $("#email").val();
let mb = $("#mobile").val();

/*
console.log(nm);
console.log(em);
console.log(mb);
*/

mydata = {myname:nm, myemail:em, myphone:mb};

//console.log(mydata);

//////////////////////
$.ajax({
	url:"insert.php",
	method: "POST",
	data: JSON.stringify(mydata),
	
	//print success data from insert.php message start
	success: function(data){
		console.log(data);
		
	//print data in html
	msg ="< div>"+ data + "";
	$("#msg").html(msg);
	
	//clear the form
	$('#formID')[0].reset();
	
	//get data retry
	showdata()
			
	}
	//print success data from insert.php message end	

		});
//////////////////////

});

How to install virtualenv

Install pip first

$ sudo apt-get install python3-pip

Then install virtualenv using pip3

$ sudo pip3 install virtualenv

Now create a virtual environment

$ virtualenv venv

you can use any name insted of venv

You can also use a Python interpreter of your choice

$ virtualenv -p /usr/bin/python2.7 venv

Active your virtual environment:

$ source venv/bin/activate

Using fish shell:

$ source venv/bin/activate.fish

To deactivate:

$ deactivate

Create virtualenv using Python3

$ virtualenv -p python3 myenv

Instead of using virtualenv you can use this command in Python3

$ python3 -m venv myenv

Install requirments:

$ pip install -r requirments.txt 


What is CRUD?

In computer programming, create, read, update, and delete are the four basic operations of persistent storage. CRUD is also sometimes used to describe user interface conventions that facilitate viewing, searching, and changing information using computer-based forms and reports.
Copyright © 2021 by YAMiN