anantamu.com
CREATE TABLE
CREATE TABLE TABLE_NAME ( ID INT(5) UNSIGNED NOTNULL PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(50) NOTNULL, DOB DATE NOTNULL )
Syntax of MYSQL SELECT Statement:
SELECT column_list FROM table-name
[WHERE Clause]
[GROUP BY clause]
[HAVING clause]
[ORDER BY clause];
Syntax of MYSQL INSERT Statement:
INSERT INTO table-name (column_list) VALUES(column_values);
like INSERT INTO table-name (column_list1,column_list2,column_list3) VALUES ( column_values1, column_values2, column_values3);
Syntax of MYSQL UPDATE Statement:
UPDATE table-name SET column_name1 = 'column_value1', column_name2 = 'column_value2' WHERE column_name3='column_value3';
Syntax of MYSQL DELETE Statement:
DELETE TABLE table-name WHERE column_name3='column_value3';
Syntax TRUNCATE TABLE:
TRUNCATE TABLE_NAME
Syntax TRUNCATE TABLE:
TRUNCATE TABLE_NAME
Syntax DROP TABLE:
DROP TABLE TABLE_NAME
Aggregate Functions:
1.AVG(), 2.MIN(), 3.MAX, 4.SUM, 5.COUNT()
AS: IT is nothing but temporary name.
HAVING: implemented result set by using group by.
JOINS:
left join: it returns left table values if there is not exist right table values.
select * from table1 left join table2 on table1.id=table2.id;
right join: it returns right table values if there is not exist left table values.
select * from table1 right join table2 on table1.id=table2.id;
inner join: it returns matching values of left and right table.
select * from table1 inner join table2 on table1.id=table2.id;
<?php
$servername = "localhost";
$username1 = "root";
$password1 = "";
$dbname = "sample";
//server connection
$connection = mysqli_connect($servername, $username1, $password1, $dbname);
if (!$connection) {
die("Connection failed: " . mysqli_connect_error());
}
$sqlQuery = "CREATE TABLE profile
(
Id int(5) NOT NULL AUTO_INCREMENT,
Name varchar(50),
Address varchar(500)
)";
// query is excute here
mysqli_query($sqlQuery,$connection);
mysqli_close($connection);
?>
Add records into Profile table HTML file
<html> <head> </head> <body> <form name='profile' method='post' action='profileResult.php'> Name:<input type='text' name='name'> PhoneNumber:<input type='text' name='phonenumber'> <input type='submit' name='submit' value='submit'> </form> </body> </html>This is PHP file profileResult.php
<?php
$servername = "localhost";
$username1 = "root";
$password1 = "";
$dbname = "sample";
//server connection
$connection = mysqli_connect($servername, $username1, $password1, $dbname);
if (!$connection) {
die("Connection failed: " . mysqli_connect_error());
}
mysqli_query($conn, "INSERT INTO profile (id, name, phonenumber) VALUES ( NULL, '" . $_POST['name'] . "', '" . $_POST['phonenumber'] . "')");
mysqli_close($conn);
?>
Displaying the records from Profile table.
<?php
$servername = "localhost";
$username1 = "root";
$password1 = "";
$dbname = "sample";
//server connection
$connection = mysqli_connect($servername, $username1, $password1, $dbname);
if (!$connection) {
die("Connection failed: " . mysqli_connect_error());
}
$sqlQuery='select * from profile';
$resultSql=mysqli_query($conn, $sqlQuery);
echo '<table>'
echo '<tr><td>Name</td><td>phonenumber</td></tr>';
if (@mysqli_num_rows(@$resultSql) >= 1) {
// output data of each row
while ($rowValue = mysqli_fetch_assoc($resultSql)) {
echo '<tr>';
echo '<td>'.$rowValue['name'].'</td>;
echo '<td>'.$rowValue['phonenumber'].'</td>';
echo '</tr>';
}
}else{
echo '<tr><td>no records found</td><td></td></tr>';
}
echo '</table>'
mysqli_close($conn);
?>
Displaying the records and action of update & delete from Profile table
<?php
$servername = "localhost";
$username1 = "root";
$password1 = "";
$dbname = "sample";
//server connection
$connection = mysqli_connect($servername, $username1, $password1, $dbname);
if (!$connection) {
die("Connection failed: " . mysqli_connect_error());
}
$sqlQuery='select * from profile';
$resultSql=mysqli_query($conn, $sqlQuery);
echo '<table>'
echo '<tr><td>Name</td><td>phonenumber</td><td>Action</td></tr>';
if (@mysqli_num_rows(@$resultSql) >= 1) {
// output data of each row
while ($rowValue = mysqli_fetch_assoc($resultSql)) {
echo '<tr>';
echo '<td>'.$rowValue['name'].'</td>;
echo '<td>'.$rowValue['phonenumber'].'</td>';
echo '<td>'."<a href="profileUpdate.php?id=".$rowValue['id'].">Update</a>"."<a href="profileDelete.php?id=".$rowValue['id'].">Delete</a>".'</td>';
echo '</tr>';
}
}else{
echo '<tr><td>no records found</td><td></td></tr>';
}
echo '</table>'
mysqli_close($conn);
?>
When user click the update then this page will be opened.
Udpate the profile Details
<html>
<head>
</head>
<body>
<?php
$servername = "localhost";
$username1 = "root";
$password1 = "";
$dbname = "sample";
//server connection
$connection = mysqli_connect($servername, $username1, $password1, $dbname);
if (!$connection) {
die("Connection failed: " . mysqli_connect_error());
}
$resultProfile = mysqli_query($conn, "SELECT * FROM profile where id='" . $_GET['id'] . "'");
while ($rowProfile = mysqli_fetch_assoc($resultProfile)) {
?>
<form name='profile' method='post' action='updateProfileResult.php'>
Update
Name:<input type='text' name='name' value="" >
PhoneNumber:<input type='text' name='phonenumber' value="" >
<input type='submit' name='submit' value='submit' >
</form>
<?php
}
?>
</body>
</html>
When user submit the this page updateProfileResult.php then excute the profile details:
<?php
$servername = "localhost";
$username1 = "root";
$password1 = "";
$dbname = "sample";
//server connection
$connection = mysqli_connect($servername, $username1, $password1, $dbname);
if (!$connection) {
die("Connection failed: " . mysqli_connect_error());
}
$sqlQuery="update profile SET name =".$_GET['name']." , phonenumber =".$_GET['phonenumber']." WHERE id = '" . $_GET['id'] . "' ";
$resultSql=mysqli_query($conn, $sqlQuery);
mysqli_close($conn);
?>
Detele the profile details:
<?php
$servername = "localhost";
$username1 = "root";
$password1 = "";
$dbname = "sample";
//server connection
$connection = mysqli_connect($servername, $username1, $password1, $dbname);
if (!$connection) {
die("Connection failed: " . mysqli_connect_error());
}
$sqlQuery="delete from profile WHERE id = '" . $_GET['id'] . "' ";
$resultSql=mysqli_query($conn, $sqlQuery);
mysqli_close($conn);
?>
$sql = "SELECT * FROM profile LIMIT 30";
It display the 30 records, It is used at time of pagingation.
$sql = "SELECT name FROM profile ORDER BY name ASC";
names are displaying in ascending order.
$sql = "SELECT name FROM profile ORDER BY name DESC";
names are displaying in descending order.