AngularJS

========================================================================

XML

<?xml version="1.0"?> 

<userlist> --> Array Name

<user>  -----> Object Name

<name>Bimal Roy </name> ---> Tag Name
<mobile> 9876543210 ---> value </mobile>
<city> Bangalore </city>
<pincode> 560037</pincode>

</user>

<user>
<name> Robi Thakur </name>
<mobile> 9988776655 </mobile>
<city> Pune </city>
<pincode> 560032</pincode>

</user>

<user>
<name> Subhas Bose </name>
<mobile> 8877665544 </mobile>
<city> Delhi </city>
<pincode> 560030</pincode>

</user>

</userlist>


==================================================================
<?php
$xml = simplexml_load_file("one.xml"); // to read a xml file

?>

<table align="center" border=2>
<tr>
<th> Full Name </th>
<th> Mobile No. </th>
<th> City </th>
<th>Pincode </th>

</tr>

<?php

for($i =0; $i<count($xml); $i++){
echo "<tr>";

echo "<td>". $xml->user[$i]->name . "</td>";
echo "<td>". $xml->user[$i]->mobile . "</td>";
echo "<td>". $xml->user[$i]->city . "</td>";
echo "<td>". $xml->user[$i]->pincode . "</td>";

echo "</tr>";
}

?>
</table>
******************************************************************************

PHP

Global Array:-

<h1> Predefine PHP Array </h1>

<?php
session_start();
# all these are global array
echo "<pre>";
print_r($_SESSION);
echo "<pre>";
 
    echo "<br>";
    echo "********COOKIE INFO********";
echo "<pre>";
print_r($_COOKIE);
echo "<pre>";

    echo "<br>";
    echo "********SERVER INFO********";

echo "<pre>";
print_r($_SERVER); // ask interview questions
echo "<pre>";

?>

************************************************************************
How to Insert the Data:-

<?php
$name = $_POST['fname'];
$mobile = $_POST['mobile'];
$msg = $_POST ['message'];
$pin = $_POST ['pincode'];
    $item = $_POST['item'];
    $myitem = implode(",",$item);

$conn = mysqli_connect("localhost", "root", "", "myphp");

# 1. server name, 2. username, 3. password, 4. database name

$mysql = "insert into mycontact(name,mobile,message,pincode,item)
values('$name','$mobile','$msg','$pin','$myitem')";

$status = mysqli_query($conn, $mysql);

if($status == true){
//echo "success";
header("Location:contactlist.php"); # this function redirect the saved data

}else{
//echo "fail";
header("Location:contact.php");
}

/**$at = $_POST;
echo "<br>";
print_r($at);
echo "</br>";**/
?>

***********************************************************************
How to Fetch Array Object:-

<?php
     
$conn = mysqli_connect("localhost", "root", "", "myphp");
$mysql = "select * from mycontact";
$res = mysqli_query($conn, $mysql); # holding the reference of data, object

while($row = mysqli_fetch_object($res)){

/**echo "<pre>";
print_r($row);
echo"</pre>";**/

echo "<tr>";

        echo "<td> $row->name </td>";
        echo "<td> $row->mobile </td>";
        echo "<td> $row->message </td>";
        echo "<td> $row->pincode </td>";
        echo "<td>";
            $item = explode(",",$row->item);
            for($i=0; $i<count($item); $i++){
                echo $item[$i]. "<br>";
            }
        echo "</td>";
echo "</tr>";

}
# this one is BEST
/**echo "<pre>";
print_r($row);
echo"</pre>";
**/



?>

************************************************************************
How to Delete the Data:-

echo "<td>
        <a href='delete.php?id=$row->id'> Delete </a>

        </td>";

<?php
$userid = $_REQUEST['id'];

include("dbconfig.php");

$sql = "delete from user where id='$userid'";

mysqli_query($conn, $sql);

header("Location:contactlist.php");


?>

**************************************************************************
How to Edit the Data:-

<?php
include("header.php");

include("dbconfig.php");

$userid = $_REQUEST['id'];

$sql = "select * from user where id = '$userid'";
$res = mysqli_query($conn, $sql);
$row = mysqli_fetch_object($res);


?>

<form method="POST" action="updateuser.php">
<table class="table table-bordered">
<caption> Edit User </caption>
<tr>
<th> Full Name </th>
<td>
<input type="text" name="fname" value="<?php echo $row->name; ?>">
</td>

</tr>

<tr>
<th> Mobile No. </th>
<td>
<input type="text" name="mobile" value="<?php echo $row->mobile; ?>">
</td>

</tr>

<tr>
<th> Email </th>
<td>
<input type="text" name="email" value="<?php echo $row->email; ?>">
</td>

</tr>

<tr>
<th> Password </th>
<td>
<input type="text" name="pass" value="<?php echo $row->password; ?>">
</td>

</tr>

<input type="hidden" name="userid" value="<?php echo $row->id; ?>">

<tr>
<th colspan="2" align="center">
<button class="btn btn-primary">Update </button>

</th>


</tr>

</table>

</form>





<?php
include("footer.php");

?>
====================================================================
Update:-
<?php

include ("dbconfig.php");
$userid = $_POST['userid'];

$name = $_POST['fname'];
$mobile = $_POST['mobile'];
$email = $_POST['email'];
$password = $_POST['pass'];


$sql = "update user set name ='$name',
mobile= '$mobile',
email = '$email',
password = '$password'
where id = '$userid' ";

$res = mysqli_query($conn, $sql);

if ($res=true){
header("Location:contactlist.php");
}else{

echo "Update fails";
}


?>
***************************************************************************
SESSION:
<?php
session_start();
if(!isset($_SESSION['id'])){
$_SESSION['msg'] = "Invalid User! ";
header("Location:index.php");
}

echo $_SESSION['name'];


?>

**************************************************************************

COOKIE:-

<?php
session_start();
if(!isset($_COOKIE['id'])){
$_SESSION['msg'] = "your session is expired! Please login ";
header("Location:index.php");
}

setcookie("name", $_COOKIE['name'], time()+60); // 60 seconds
setcookie("id", $_COOKIE['id'], time()+40);

echo $_SESSION['name'];



?>

***************************************************************************
How to Upload File:-
<html>
 <body>
<form action="upload.php" method="POST" enctype="multipart/form-data">
<center>
Select Any Image :
<input type="file" name="myfile"/>
<br>
<br>
<button type="submit"> Upload </button>
</center>
</form>
 </body>

 </html>
==================================================================
<?php

$filename = $_FILES['myfile']['name'];

$filesize = $_FILES['myfile']['size'];

$filetype = $_FILES['myfile']['type'];

$filetmpname = $_FILES['myfile']['tmp_name'];

echo $filename . "<br>";
echo $filesize . "<br>";
echo $filetype . "<br>";
echo $filetmpname . "<br>";

move_uploaded_file($filetmpname, "myfile/$filename");


include("dbconfig.php");

$sql = "insert into photo (photoname) values ('$filename')";

mysqli_query($conn,$sql);

header("Location:photolist.php");

?>
========================================================================
<html>
<head>
<link href="assets/css/bootstrap.css" rel="stylesheet">
<style>
.margin10{margin:10px;}
</style>

<head>
<body>
<br>
<div class="container">
<div class="row">
<div class="col-sm-12 text-center">
<a href="mydata.php" class="btn btn-primary "> New Photo </a>
</div>
</div>
<br>

<div class="row">
<?php

include("dbconfig.php");
$sql = "select * from photo order by id desc";
$res = mysqli_query($conn, $sql);

while($row = mysqli_fetch_object($res)){

echo "<div class='col-sm-3'>";
echo "<img src = 'myfile/$row->photoname' class='img-responsive img-rounded margin10'>";

echo "<center> <a href='deletephoto.php?id=$row->id'> Delete </a> </center>";

echo "</div>";
}

?>

</div>

</div>
</body>
</html>
========================================================================

<?php
include("dbconfig.php");
$photoid = $_REQUEST['id']; // id is variable comming through URL

$sql1 = mysqli_query($conn,"select * from photo where id = '$photoid'");
$row = mysqli_fetch_object($sql1);

$photoname = $row->photoname;


unlink("myfile/$photoname"); // to delete a fiel from folder

$sql = "delete from photo where id='$photoid'";

mysqli_query($conn, $sql);

header("Location:photolist.php");



?>


**************************************************************************
Login Body(cookies):

<body> <br>
<div class="container">
<div class="row">

<div class="col-sm-4"> </div>
<div class="col-sm-4">
<?php
session_start();
if(isset($_SESSION['msg'])){
echo $_SESSION['msg'];
unset($_SESSION['msg']);
}
?>
<form method="POST" action="loginchek.php">
<div class="panel panel-primary">
<div class="panel-heading"> Login </div>
<div class="panel-body">
<div class="form-group">
<label> Email </label>
<input type="text" name="email" class="form-control">

</div>

<div class="form-group">
<label> Password </label>
<input type="text" name="password" class="form-control">

</div>

<div class="form-group text-center">
<button class="btn btn-primary "> Login </button>
<hr>
<a href="signup.php"> I am new user </a>

</div>

</div> <!-- panel body end-->
</div> <!-- panel end-->

</form>
</div>
<div class="col-sm-4"> </div>

</div>

</div>


</body>
===================================================================
<?php
session_start();
include("dbconfig.php");
$email = $_POST['email'];
$password = $_POST['password'];

//echo $email;
//echo $password;



$sql = "select * from user where email='$email' and password='$password'";
$res = mysqli_query($conn, $sql);
$totaluser = mysqli_num_rows($res);

//echo $totaluser;

if($totaluser>0){
$row = mysqli_fetch_object($res);

$_SESSION['name']= $row->name; // 1440 seceonds 24 hours
$_SESSION['id']= $row->id;

setcookie("name", $row->name, time()+40); // 40 seconds
setcookie("id", $row->id, time()+40);

header("Location:home.php");
}else{
$_SESSION['msg'] = 'Sorry Invalid user';
header("Location:index.php");
}
?>

***************************************************************************

FILE MANAGEMENT

How create and delete a Folder/Directory:

<h1> Directry management </h1>
<?php

/*
if( file_exists("test"))
{
echo "Already one folder is available with same name";
}else{
mkdir("test");
echo "Folder created Successfully";
}
*/

 //how to delete a folder ?

  if( file_exists("test"))
  {
 rmdir("test");
 echo"Folder deleted successfully";
  }else{

 echo "Sorry, the are no any folder to delete";
  }
?>
=======================================================================
How to delete a variable:

<h1>Directory Management </h1>

<?php 
 $a = 100;
 echo "<hr>";

 unset($a);

 echo $a;


?>
========================================================================
How to create a New File:
<h1> Create a  New File using PHP </h1>

<?php 

$obj = fopen("abc.txt", "w");
fclose($obj);

echo "file created successfully!";


?>

========================================================================

How to write in a File:

<h1> Create a  New File using PHP </h1>

<?php

$obj = fopen("abc.txt", "w");

$data = "Hello My Test";

fwrite($obj, $data);

fclose($obj);

echo "file created successfully!";
echo "<br>";

?>

========================================================================

How to read a File:

<h1> Using 2 methods we can read data from a file </h1>

<?php

$obj = fopen("abc.txt", "r");

$data1 = fread($obj, 5000);

fclose($obj);

echo "<hr>";

echo $data1;

$data2 = file_get_contents("abc.txt");
// read all contents of a file at one time and store as a string

echo "<hr>";
echo "this is 2nd method";
echo "<br>";
echo $data2;
?>

========================================================================

How to delete a File:

h1> PHP unlink() used to delete a file </h1>

<?php
if(file_exists("abc.txt")){

unlink("abc.txt");
echo "File deleted successfully";
}else{

echo "There is no any file exists under current folder";
}
?>
========================================================================
DataBase to XML:

<?php
$conn = mysqli_connect("localhost", "root", "", "myproject");
$sql = "select * from user ";

$res = mysqli_query($conn, $sql);

$userdata = "";

while($row=mysqli_fetch_object($res)){

$userdata = $userdata . "<user>";

$userdata = $userdata . "<name> $row->name </name>";
$userdata = $userdata . "<mobile> $row->mobile </mobile>";
$userdata = $userdata . "<email> $row->email </email>";
$userdata = $userdata . "<password> $row->password </password>";

$userdata = $userdata . "</user>";
}
 $xml = "<?xml version='1.0' ?>";
 $xml = $xml . "<userlist>" . $userdata. "</userlist>";

 $filename = time() . ".xml";
 $obj = fopen("$filename", "w");

 fwrite($obj ,$xml);

 fclose($obj);

 echo "New XML file created!";
 echo "<a href='$filename'> Click here to open </a>";


?>
========================================================================
XML to DataBase:

<?php
$conn = mysqli_connect("localhost", "root", "", "myproject");
$xml = simplexml_load_file("1503568755.xml");

for($i=0; $i<count($xml); $i++){

$name = $xml->user[$i]->name;
$mobile = $xml->user[$i]->mobile;
$email = $xml->user[$i]->email;
$password = $xml->user[$i]->password;

$sql = "insert into user(name,mobile,email,password) 
        values('$name','$mobile','$email','$password')";

mysqli_query($conn, $sql);

}

echo "Data Uploaded From XML file to DataBase";


?>
========================================================================
XML to JSON:

<?php
$conn = mysqli_connect("localhost", "root", "", "myproject");
$xml = simplexml_load_file("1503568755.xml");

$json = json_encode($xml);

echo $json;


?>
=======================================================================
DataBase to JSON:

<?php 
$conn = mysqli_connect("localhost", "root", "", "myproject");
$sql = "select * from user ";
$res = mysqli_query($conn, $sql);

$user = array();

while($row=mysqli_fetch_object($res)){

array_push($user, $row);
}
  echo "<pre>";
  print_r($user);
  echo "</pre>";
  
 echo "<hr color='red'>";

 $json = json_encode($user); 
 echo $json;

?>
*********************************************************************************

MySQL

How to write MySQL queries in command prompt:-

********** First start XAMPP's- Apache & MySQL***********

1st. go to this path like above pic----> C:\xampp\mysql\bin>

2nd. write this -----> mysql -u root -p -h localhost

3rd.---> there is no need to write any password

4th. you can see this prompt -- mysql>

5th. write this command---> show databases;

6th. you can see all the DataBase same as above pic.

7th. write this command --> use demo; [my created database name is demo, you should write your own DataBase name]

8th. mysql> select * from emp; 

you can see all the data from emp table :)

In Brief:-

write all these commands step by step:

1. C:\xampp\mysql\bin>mysql -u root -p -h localhost  [no need ;] 

[user name-root, password-null, host- localhost]

2. show databases;

3. use demo; [demo is database name]

4. select * from emp; [emp table name]

5. describe demo.emp; [see table structure]

https://www.mysql.com/

https://www.w3schools.com/sql/default.asp      [for tutorial]

mysql> help List of all MySQL commands: Note that all text commands must be first on line and end with ';' ? (\?) Synonym for `help'. clear (\c) Clear the current input statement. connect (\r) Reconnect to the server. Optional arguments are db and host. delimiter (\d) Set statement delimiter. edit (\e) Edit command with $EDITOR. ego (\G) Send command to mysql server, display result vertically. exit (\q) Exit mysql. Same as quit. go (\g) Send command to mysql server. help (\h) Display this help. nopager (\n) Disable pager, print to stdout. notee (\t) Don't write into outfile. pager (\P) Set PAGER [to_pager]. Print the query results via PAGER. print (\p) Print current command. prompt (\R) Change your mysql prompt. quit (\q) Quit mysql. rehash (\#) Rebuild completion hash. source (\.) Execute an SQL script file. Takes a file name as an argument. status (\s) Get status information from the server. system (\!) Execute a system shell command. tee (\T) Set outfile [to_outfile]. Append everything into given outfile. use (\u) Use another database. Takes database name as argument. charset (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets. warnings (\W) Show warnings after every statement. nowarning (\w) Don't show warnings after every statement. resetconnection(\x) Clean session context. For server side help, type 'help contents'

***************************************************************************

Set language à utf8_unicode_ci [Multilanguage]

.frm à table structure FoRMat

.ibd à Data of the Table InnoDB

RDBMS à Relational Data Base Management System

Oracle, Microsoft SQL Server, MySQL (Open Source)

SQL à Structured Query Language

SQL is language of RDBMS

Category of Statement

1.       DDL (Data Definition Language)

2.       DML (Data Manipulation Language)

DDL:

Create an Object, deals with object

i>                   CREATE table, data base.

ii>                 ALTER table, Database.

iii>                DROP table, Database.

iv>               RENAME table, Database.

DML:

Related to Data.

i>                   INSERT statement.

ii>                 UPDATE statement.

iii>                DELETE statement.

Create a Database :

                CREATE DATABASE demo;

ADDING new column in table:

                ALTER TABLE emp ADD mobile VARCHAR(10);

MODIFY:

ALTER TABLE emp MODIFY mobile VARCHAR(15), MODIFY email VARCHAR(60);

CHANGE column Name:

ALTER TABLE emp CHANGE email email_id VARCHAR(60)

DESC à Reserved Keyword

REMOVING column:

                ALTER TABLE emp DROP column salary, DROP column email;

RENAMING table:

                ALTER TABLE emp RENAME TO employee;

DROPPING a Table:

                DROP TABLE employee;

DROP DATABASE database_name;

QUERY PART:-

 INSERT INTO table_name(fname,lname,salary,email) VALUES (‘Ajay’,’Kumar’,4000,’ajay@gmail.com’), (‘Bijay,’Singh’,9000,’bijay@gmail.com’);

SELECT column_name FROM table_name;

ORDERBY ASC(Default) DESC

DISTINCT

CONDITION/ FILTER:

WHERE  state = ‘MP’

                Salary >3000

LOGICAL OPERATOR:

NOT, AND, OR

COMPARISON OPERATOR:

=

!=

< 

> 

<=

>=

Between

In

BETWEEN:

                WHERE salary BETWEEN 2000 AND 5000; [inclusive, range >=2000 <=4000]

IN:

                WHERE state IN (‘AP’,’MP’,’WB’);   [similar OR operator]

FUNCTIONS:

                AVG()

                MIN()

    MAX()

    SUM()

SELECT  AVG (salary) AS “Average Salary” [column alias]

LIMIT: [one of use Pagination]

                SELECT * FROM employee LIMIT 2;

               

Offset	Id
0	1
1	2

                By default offset 0;

                LIMIT 4(Offset),1(How many row)

DML:

UPDATE  [means transaction] Admin rollback the previous value

                UPDATE emp SET salary = 8000, email = ‘abc@gmail.com’ WHERE ID=2;

                UPDATE emp SET salary = 8000, email = ‘abc@gmail.com’ WHERE name=’Amit’;

               

DELETE:

                DELETE FROM table_name;

                DELETE FROM emp WHERE id=5;

                DELETE FROM emp WHERE salary=4000;

TRUNCATE: [not a DML statement, can’t rollback previous data]

                TRUNCATE table_name;

                TRUNCATE emp; equivalent to DELETE FROM emp;

                Delete whole Records

                Empty the Table,

                We can’t apply condition

Every DML statement has the Roll Back property.

NULL à nothing is there

UPDATE emp SET email=NULL WHERE id=3;

PRIMARY KEY:

                NOT NULL,

                NON- Repeating

Q. How to find all column having NULL values

                SELECT * FROM emp WHERE mobile ISNULL;

Q. To find RECORDS HAVING NOT-NULL values.

                SELECT * FROM emp WHERE mobile ISNOT NULL;

Q. Find the record having Highest salary?

                SELECT * FROM emp ORDERBY salary DESC LIMIT 0,1;

Q. 2^nd Highest Salary.

                SELECT * FROM emp ORDERBY salary DESC LIMIT 1,1;

               

                LIMIT 0,2 0à offset, where to start 2àno. of records

GROUPBY-Clause:

                [WHERE clause BEFORE GROUPBY ]

Q. Fetch total salary gives to each department.

Q. Fetch total salary given to HR Department?

Q. Fetch total salary given to each department for the MGR position only.

SELECT dept, SUM (salary) FROM user GROUPBY dept. HAVING SUM(salary)>10,000;

HAVINGàsame as WHERE

SELECT dept, SUM(salary) AS “Total Salary” FROM user WHERE POSITION=’MGR’ GROUPBY dept HAVING SUM(salary)>10,000;

MIN()

MAX()

AVG()

COUNT()

JOIN:

1.       INNER JOIN      2. OUTER JOIN ài)LEFT JOIN(Left Outer Join), ii) RIGHT JOIN (Left Outer Join)

INNER JOIN:

i)                    SELECT * FROM dept(left table) INNER JOIN emp(right table) ON dept.dept_id = emp.dept_id;

ii)                   SELECT * FROM dept JOIN emp ON dept.dept_id = emp.dept_id;

iii)                 SELECT * FROM dept, emp WHERE dept.dept_id = emp.dept_id; [SELF JOIN]

  2.i) LEFT JOIN :

a)      SELECT * FROM dept LEFT JOIN emp ON dept.dept_id = emp.dept_id;

b)      SELECT * emp.name, dname FROM dept LEFT JOIN emp ON dept.dept_id = emp.dept_id;

c)       SELECT  e.name, d.name FROM dept d LEFT JOIN emp e (table alias) ON d.dept_id =e.dept_id;

          2.ii)RIGHT JOIN : (not use in real time)

a)      SELECT * FROM  dept(left table) RIGHT JOIN emp(right table) ON emp.dept_id = dept.dept_id;

b)      SELECT * FROM emp LEFT JOIN dept ON dept.dept_id = emp.dept_id;

INNER TABLE:

                Returns records that have matching values in both tables.

LEFT (OUTER JOIN):

                Returns all records from the LEFT TABLE and matching records from the RIGHT TABLE.

RIGHT (OUTER JOIN):

                Returns all records from the RIGHT TABLE and matching records from the LEFT TABLE.