Table of Contents
Create and Check Connection
$servername = "localhost";
$username = "saurabh";
$password = "123456";
$dbname = "bapugraphics";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully
";
Delete
DELETE Customer
WHERE Id = 17
DELETE Product
WHERE Price > 100
FETCH Result
Run Show Query
if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
echo "<h2>".$row["username"]. "</h2>".
$row["email"]. "<br>".
$row["mobile"]. "<br>---------------------------<br>";
}
} else {
echo "0 results";
}
INSERT
INSERT INTO table-name (column-names)
VALUES (values)
INSERT INTO Customer (FirstName, LastName, City, Country, Phone)
VALUES ('Saurabh', 'Chugh', 'Delhi', 'India', 9898000000)
Run Query
$sql = "INSERT INTO regform (username, password, email, mobile, address, dateofbirth)
VALUES ('$username', '$password', '$email', '$mobile', '$address', '$dateofbirth')"
;
$result = $conn->query($sql);
OFFSET FETCH
Problem: Get all but the 10 most expensive products sorted by price
SELECT Id, ProductName, UnitPrice, Package
FROM Product
ORDER BY UnitPrice DESC
OFFSET 10 ROWS
Problem: Get the 10th to 15th most expensive products sorted by price
SELECT Id, ProductName, UnitPrice, Package
FROM Product
ORDER BY UnitPrice DESC
OFFSET 10 ROWS
FETCH NEXT 5 ROWS ONLY
ORDER BY
SELECT CompanyName, ContactName, City, Country
FROM Supplier
ORDER BY CompanyName
SELECT CompanyName, ContactName, City, Country
FROM Supplier
ORDER BY CompanyName DESC
SELECT FirstName, LastName, City, Country
FROM Customer
ORDER BY Country, City
SELECT Id, CompanyName, City, Country
FROM Supplier
WHERE Country IN ('USA', 'Japan', 'Germany')
ORDER BY Country ASC, CompanyName DESC
SELECT Id, OrderDate, CustomerId, TotalAmount
FROM [Order]
ORDER BY YEAR(OrderDate) ASC, TotalAmount DESC
SELECT and WHERE
SELECT *
FROM table-name
SELECT *
FROM Customer
SELECT FirstName, LastName, City
FROM Customer
SELECT Id, FirstName, LastName, City, Country, Phone
FROM Customer
WHERE Country = 'India'
Run Select Query
$sql = "SELECT * FROM registrationform where gender='$gender'";
$result = $conn->query($sql);
Select Database from Form
// select database
$username=$_POST["username"];
$password=$_POST["password"];
$email=$_POST["email"];
$mobile=$_POST["mobile"];
$address=$_POST["address"];
$dateofbirth=$_POST["dateofbirth"];
SELECT TOP
Problem: List top 10 most expensive products
SELECT TOP 10 Id, ProductName, UnitPrice, Package
FROM Product
ORDER BY UnitPrice DESC
UPDATE
UPDATE regform
SET City = 'Pune', Phone = '9876543210', email = 'abc@gmail.com'
WHERE Id = 15
UPDATE regform
SET City = 'Noida'
WHERE Name = 'Saurabh