Database Connectivity

The collection of related data is called a database. XAMPP stands for cross-platform, Apache, MySQL, PHP, and Perl.

Requirements:

Start XAMPP server by starting Apache and MySQL
Write PHP script for connecting to XAMPP
Run it in the local browser
The database is successfully created which is based on the PHP code.

Connecting server-side script to database

Example:

<?php  
$host ='localhost:3306';  
$user=";  
$pass=";  
$conn= mysqli_connect($host, $user, $pass);  
if(!$conn)  
{  
die("Could not connect:" .mysqli_error());  
}  
echo 'Connected successfully';  
mysqli_close($conn);  
?>

Output:
Connected successfully

Making SQL queries

SQL stands for Structured Query Language. It is used to access and manipulate databases.

Some of the important SQL Commands

SELECT
UPDATE
DELETE
INSERT
CREATE
ALTER
DROP

SQL SELECT Statement

SELECT CustomerName, City FROM Customers;

SQL WHERE CLAUSE

SELECT * FROM Customers  
WHERE City="Kathmandu";

UPDATE

UPDATE table_name  
SET some_column= some_value  
WHERE some_column= some_value;

DELETE

DELETE FROM table_name  
WHERE some_column= some_value;

INSERT

INSERT INTO table_name(column1, column2, column3)  
VALUES (value1, value2, value3);

CREATE

CREATE TABLE table_name(  
column1 datatype,  
column2 datatype,  
column3 datatype  
);

ALTER

ALTER TABLE table_name  
ADD column_name datatype;

DROP

DROP TABLE table_name

Fetching data sets getting data about data

<?php  
include_once 'connect.php';  
$result=mysqli_query($conn,"SELECT * FROM MyUsers");  
?>  
<!DOCTYPE html>  
<html>  
<head>  
 <title>Fetch Data</title>  
</head>  
<body>  
  
<?php  
if(mysqli_num_rows($result)>0){  
?>  
<table>  
<tr>  
<td>First Name</td>  
<td>Last Name</td>  
<td>Email id</td>  
</tr>  
<?php  
$i=0;  
while($row = mysqli_fetch_array($result)){  
?>  
<tr>  
<td><?php echo $row["first_name"]; ?></td>  
<td><?php echo $row["last_name"]; ?></td>  
<td><?php echo $row["email"]; ?></td>  
</tr>  
<?php  
$i++;  
}  
?>  
</table>  
<?php  
}  
else{  
 echo"No result found";  
}  
?>  
</body>  
</html>

Creating a database with Server-side Scripting

First of all, a database named STUDENT has to be created with the following command
SELECT DATABASE student
Then database should be active to create a table
USE student
Now you can create a table with different fields
CREATE TABLE personal_details(
Roll_no AUTO PRIMARY KEY,
First_Name VARCHAR(255),
Last_Name VARCHAR(255),
Address VARCHAR(255) );

Displaying Queries in Table

<?php  
$username="username";  
$password="password";  
$database = "Student";  
$mysqli= newsqli("localhost", $username, $password, $database);  
$query= "SELECT * FROM table_name";  
echo "Database Output";  
if($result=$mysqli->query($query)){  
while ($row = $result-> fetch_assoc()){  
$field1name= $row["col 1"];  
$field2name= $row["col 2"];  
$field3name= $row["col 3"];  
echo ".$field1name.$field2name..";  
echo $field3name;  
echo $field3name;  
echo $field3name;  
} $result->free();  
}