Short overview on Unit - V

 

Unit – V: Establishing a Database Connection and Working With Database

---

1. Steps to Establish a Connection with Database

Definition:

Database connection is the process of linking a programming language (like PHP) or software to a database (like MySQL, Oracle, etc.) to perform operations such as inserting, updating, deleting, and retrieving data.

Steps:

1. Choose a Database

   • Common databases: MySQL, PostgreSQL, Oracle, SQL Server.

   • Example: For web applications, MySQL is widely used with PHP.

2. Install Database Server

   • Install MySQL Server or use hosted database services.

   • Also install phpMyAdmin for graphical management.

3. Configure Database

   • Create a Database Name (e.g., student_db).

   • Create a User with a password and grant privileges.

4. Establish Connection in Code (Example in PHP):

   <?php
   $servername = "localhost";  
   $username = "root";  
   $password = "";  
   $dbname = "student_db";  
   
   // Create connection
   $conn = new mysqli($servername, $username, $password, $dbname);
   
   // Check connection
   if ($conn->connect_error) {
       die("Connection failed: " . $conn->connect_error);
   }
   echo "Connected successfully";
   ?>
   

5. Verify Connection

   • If credentials are correct, it displays "Connected successfully".

   • Otherwise, error messages will be shown.

---

2. Steps to Create Tables and Manipulate Data using SQL

Definition:

• SQL (Structured Query Language): A language used to communicate with databases.

• It allows creating, modifying, retrieving, and managing data.

Steps:

a) Creating a Table

• Use CREATE TABLE command.

CREATE TABLE Students (
    StudentID INT PRIMARY KEY AUTO_INCREMENT,
    Name VARCHAR(100),
    Age INT,
    Course VARCHAR(50)
);

• This creates a table named Students.

b) Inserting Data

INSERT INTO Students (Name, Age, Course)
VALUES ('John Doe', 20, 'Computer Science');

c) Retrieving Data

SELECT * FROM Students;

• Displays all student records.

d) Updating Data

UPDATE Students
SET Age = 21
WHERE StudentID = 1;

e) Deleting Data

DELETE FROM Students
WHERE StudentID = 1;

f) Altering Table (Adding a column)

ALTER TABLE Students
ADD Email VARCHAR(100);

---

3. Steps for Hosting a Website using ‘cPanel’ and FileZilla

Definition:

• Hosting a Website: Making a website live on the internet by uploading it to a web server.

• cPanel: A web-based control panel provided by hosting companies to manage websites and databases.

• FileZilla: A free FTP (File Transfer Protocol) software used to transfer files from a local computer to the web server.

---

Steps using cPanel:

1. Login to cPanel (using hosting provider credentials).

2. Create Database:

   • Go to MySQL Databases → Create a database.

   • Create a user and assign it to the database with privileges.

3. Upload Website Files:

   • Open File Manager in cPanel.

   • Navigate to public_html folder (root directory).

   • Upload your HTML, CSS, JS, PHP files here.

4. Configure Database in Website Code:

   • Update database connection details (servername, username, password, dbname) inside your code.

5. Test Website by entering domain name in the browser.

---

Steps using FileZilla:

1. Download & Install FileZilla (client version).

2. Get FTP Credentials from hosting provider:

   • Host (e.g., ftp.yourdomain.com)

   • Username

   • Password

   • Port (default: 21)

3. Connect to Server:

   • Open FileZilla → Enter credentials → Click Quickconnect.

4. Upload Files:

   • Left panel = Local computer

   • Right panel = Server (public_html folder)

   • Drag and drop website files from left to right.

5. Check Website: Open browser → type your domain → website goes live.

---

Unit–V: MCQs (15 Questions)

Multiple Choice Questions (MCQs)

1. Which function is used in PHP to establish a connection with MySQL database?
a) mysql_connect()
b) mysqli_connect()
c) connect_db()
d) db_connect()
✅ Answer: b) mysqli_connect()

---

2. In cPanel, which folder is the root directory for website files?
a) /root
b) /htdocs
c) /public_html
d) /www_files
✅ Answer: c) /public_html

---

3. Which SQL command is used to create a new table?
a) INSERT
b) CREATE TABLE
c) ALTER
d) SELECT
✅ Answer: b) CREATE TABLE

---

4. In SQL, which command is used to modify existing data in a table?
a) UPDATE
b) CHANGE
c) MODIFY
d) ALTER
✅ Answer: a) UPDATE

---

5. FileZilla is used for ______.
a) Database management
b) File Transfer Protocol (FTP)
c) Coding PHP programs
d) Creating SQL tables
✅ Answer: b) File Transfer Protocol (FTP)

---

6. Which SQL command is used to delete a table permanently?
a) DROP TABLE
b) DELETE TABLE
c) REMOVE TABLE
d) TRUNCATE TABLE
✅ Answer: a) DROP TABLE

---

7. Which of the following is NOT a CRUD operation in SQL?
a) Create
b) Retrieve
c) Update
d) Display
✅ Answer: d) Display

---

8. The default port number for MySQL database is ______.
a) 3306
b) 8080
c) 21
d) 1521
✅ Answer: a) 3306

---

9. Which SQL keyword is used to remove duplicate rows from the result set?
a) UNIQUE
b) DISTINCT
c) PRIMARY
d) FILTER
✅ Answer: b) DISTINCT

---

10. In FileZilla, the right-hand side panel shows ______.
a) Local computer files
b) Web server files
c) MySQL database tables
d) Both local and server files
✅ Answer: b) Web server files

---

11. Which SQL command is used to retrieve data from a database?
a) GET
b) FETCH
c) SELECT
d) SHOW
✅ Answer: c) SELECT

---

12. Which of the following is an example of a Relational Database?
a) PHP
b) MySQL
c) HTML
d) FileZilla
✅ Answer: b) MySQL

---

13. Which SQL statement is used to add a new column to a table?
a) INSERT
b) ALTER TABLE
c) ADD COLUMN
d) MODIFY
✅ Answer: b) ALTER TABLE

---

14. In PHP, $conn->connect_error is used for ______.
a) Closing the database connection
b) Checking if connection failed
c) Running SQL queries
d) Creating tables
✅ Answer: b) Checking if connection failed

---

15. Which software is commonly used for GUI-based MySQL management in cPanel?
a) phpMyAdmin
b) MySQL Workbench
c) Oracle Developer
d) MongoDB Compass
✅ Answer: a) phpMyAdmin

---

3–4 Marks Questions with Answers

---

Q1. List the steps to establish a connection with a database in PHP.
Answer:

1. Install database server (MySQL).

2. Create a database and user in MySQL/phpMyAdmin.

3. Write connection code using mysqli_connect().

4. Check if connection is successful.

5. Use $conn object for running queries.

---

Q2. Write the SQL query to create a table Employee with fields (ID, Name, Salary).
Answer:

CREATE TABLE Employee (
    ID INT PRIMARY KEY AUTO_INCREMENT,
    Name VARCHAR(100),
    Salary DECIMAL(10,2)
);

---

Q3. What is the difference between DELETE, TRUNCATE, and DROP in SQL?
Answer:

• DELETE: Removes selected rows from a table but keeps structure.

• TRUNCATE: Removes all rows quickly, keeps structure intact.

• DROP: Deletes the entire table along with its structure.

---

Q4. Explain the use of FileZilla in website hosting.
Answer:

• FileZilla is an FTP software used to upload website files to a server.

• Left panel = local computer files, Right panel = server files.

• Users drag and drop files into the public_html folder to make the site live.

---

Q5. State steps to host a website using cPanel.
Answer:

1. Login to cPanel.

2. Create database and user.

3. Upload website files to public_html.

4. Update database connection details in code.

5. Test the website using domain name.

---

Q6. What are CRUD operations in SQL? Give example.
Answer:

• CRUD stands for Create, Read, Update, Delete.

• Examples:

  • Create → INSERT INTO Students VALUES(...)

  • Read → SELECT * FROM Students

  • Update → UPDATE Students SET Age=21 WHERE ID=1

  • Delete → DELETE FROM Students WHERE ID=1

---

Q7. Define cPanel and its advantages.
Answer:

• cPanel: A control panel provided by hosting companies to manage domains, databases, email, and website files.

• Advantages:

  1. Easy to use GUI.

  2. Database management via phpMyAdmin.

  3. File management via File Manager.

  4. One-click software installation.