The article consists of the programs which are given in the assignments of the colleges. It is the list of solved assignment - PHP & Mysql. It also contains some common FAQs and list of questions that might help you to perform better at interview or viva.

This article has 3 sections

1) Solved Assignment

2) Frequently Asked Questions (FAQs)

3) Interview / Viva Questions

1) Solved Assignment

For below scripts following database and tables are required. Go ahead and copy and paste the code in phpMyAdmin or type the code at MySql command prompt.

-- Database: `emp_mgmt`

CREATE DATABASE `emp_mgmt`;

-- --------------------------------------------------------

-- Table structure for table `employee`

CREATE TABLE IF NOT EXISTS `employee` (

`empno` int(3) NOT NULL,

`ename` varchar(40) NOT NULL,

`post` varchar(40) NOT NULL,

`salary` bigint(10) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Dumping data for table `employee`

INSERT INTO `employee` (`empno`, `ename`, `post`, `salary`) VALUES

(1, 'George', 'Manager', 100000),

(2, 'John', 'President', 200000),

(3, 'Kevin', 'Manager', 210000),

(4, 'Tom Hanks', 'CEO', 200000),

(5, 'Rachel', 'Accounts', 100000);

PHP Script 1) Displaying employee information in the tabular format and provided the form to insert the employee details into the database (use of Insert Query)

Code:

<html>

<head>

<title> Displaying Info in table and Insert Query</title>

</head>

<body>

<tr>

<th>Emp Salary</th>

</tr>

<?php

$conn = mysql_connect("localhost","root");

mysql_select_db("emp_mgmt",$conn);

$sql = "select * from employee";

$result = mysql_query($sql, $conn);

while ($row = mysql_fetch_array($result,MYSQL_ASSOC))

{

$eno = $row['empno'];

$name = $row['ename'];

$epo = $row['post'];

$esa = $row['salary'];

<tr>

</tr>

<?php

}

mysql_close($conn);

<table>

<br/>

<form action="<?php $PHP_SELF ?>" method = "POST">

Employee No: <br/> <input type="text" name = "txtEno" size="50"><br/>

Employee Name: <br/> <input type="text" name = "txtEname" size="50"><br/>

Employee Post: <br/> <input type="text" name = "txtPost" size="50"><br/>

Employee Salary: <br/> <input type="text" name = "txtSalary" size="50"><br/>

</form>

<?php

if(isset($_POST['btnInsert']))

{

$conn = mysql_connect("localhost","root");

if ($conn)

{

mysql_select_db("emp_mgmt",$conn);

$eno = intval($_POST['txtEno']);

$ename = strval($_POST['txtEname']);

$epost = strval($_POST['txtPost']);

$esal = intval($_POST['txtSalary']);

$insert = "insert into employee values($eno, '$ename', '$epost', $esal)";

if (mysql_query($insert, $conn))

{

echo "Employee Information saved successfully";

header("location:Display-Info-in-Table-Insert.php");

}

mysql_close($conn);

}

</center>

</body>

</html>

PHP Script 2) Fill the combo box (dropdown box) from the database with employee numbers and remove that employee information from the table (use of Delete Query)

Code:

<?php

$conn=mysql_connect("localhost","root") or die(mysql_error());

mysql_select_db("emp_mgmt",$conn) or die(mysql_error());

$sql = "SELECT empno from employee";

$result = mysql_query($sql);

<html>

<body>

<h3> Select the Employee To Remove From The Table</h3>

Select Employee No. : <br/><br/>

<option value="Choose">Choose</option>

<?php

while($row = mysql_fetch_array($result))

{

<?php echo $row['empno'] ?>

</option>

<?php

}

</select> <br/> <br/>

</form>

<?php

if (isset($_POST['btnSubmit']))

{

$eno = $_POST['dempno'];

$del = "delete from employee where empno = $eno";

$result = mysql_query($del,$conn);

if ($result)

{

echo "Emp No. $eno successfully deleted";

}

else

{

echo "Emp No. $eno Not successfully deleted";

}

</center>

</body>

</html>

PHP Script 3) Display employee information in a tabular format and provide a hyperlink to update that employee's information (use of Querystring & Update Query)

Code: There are 2 types -

1) Main PHP script

2) Containing Update Query code (Querystring-Update.php)

Main PHP Script

Code:

<html>

<head>

<title> Displaying Info in table. Use of Querystring and Update Query</title>

</head>

<body>

<h3> Click on the "Edit Info" link To Edit Employee's Information </h3>

<tr>

<th>Update Details </th>

</tr>

<?php

$conn = mysql_connect("localhost","root");

mysql_select_db("emp_mgmt",$conn);

$sql = "select empno, ename from employee";

$result = mysql_query($sql, $conn);

while ($row = mysql_fetch_array($result,MYSQL_ASSOC))

{

$eno = $row['empno'];

$name = $row['ename'];

<tr>

Edit Info

</a>

</td>

</tr>

<?php

}

mysql_close($conn);

</center>

<table>

</body>

</html>

Querystring-Update.php

Code:

<html>

<head><title> Update Employee information</title></head>

<body>

<?php

$qeno = $_GET['enno'];

$conn = mysql_connect("localhost","root");

mysql_select_db("emp_mgmt",$conn);

$sql = "select * from employee where empno = $qeno";

$result = mysql_query($sql, $conn);

while ($row = mysql_fetch_array($result,MYSQL_ASSOC))

{

$eno = $row['empno'];

$name = $row['ename'];

$epo = $row['post'];

$esa = $row['salary'];

}

<form action="<?php $_PHP_SELF ?>" method="POST" >

<table>

<tr>

<td>

<input type="text" size="50" name="txtEno"

value="<?php echo $eno;?>" readonly />

</td>

</tr>

<tr>

<td>

<input type="text" size="50" name ="txtEname"

value="<?php echo $name;?>"/>

</td>

</tr>

<tr>

<td>

<input type="text" size="50" name="txtPost"

value="<?php echo $epo;?>"/>

</td>

</tr>

<tr>

<td >Emp Salary</td>

<td>

<input type="text" size="50" name="txtSal"

value="<?php echo $esa; ?>" /></td>

</td>

</tr>

<tr>

</td>

</tr>

</table>

</form>

<?php

if (isset($_POST['btnSubmit']))

{

$en = $_POST['txtEname'];

$po = $_POST['txtPost'];

$sal = $_POST['txtSal'];

$upd = "update employee set ename = '$en' , post = '$po', salary = $sal

where empno = $qeno";

$result = mysql_query($upd,$conn);

if ($result)

{

echo "Emp No. $eno successfully updated";

}

else

{

echo "Emp No. $eno Not successfully updated";

}

</center>

</body>

</html>

PHP Script 4) Display the entire list of database that exists on MySql Server(Use of Show Database Query)

Code:

<html>

<head> <title> List of Database on MySQL Server </title> </head>

<body>

<h3> The List of Database Consists Of: </h3>

<?php

$conn = mysql_connect("localhost","root");

$result = mysql_query("show databases");

while ($row = mysql_fetch_array($result))

{

echo $row[0]."<br/><br/>";

}

2) Frequently Asked Questions (FAQs)

Q) What is PHP, and what is its role in web development?

A) PHP stands for Hypertext Preprocessor. PHP is a server-side scripting language that enables the creation of dynamic and interactive web pages by embedding code within HTML.

Q) How does PHP handle databases, and which databases are commonly used with PHP?

A) PHP interacts with database in very smooth manner via database extensions and APIs. The most commonly used databases with PHP include MySQL, PostgreSQL, SQLite, and MongoDB. Developers can use PHP to connect to these databases, execute queries, and retrieve or manipulate data, making it a robust choice for building database-driven web applications.

Q) What are PHP frameworks, and how do they simplify the development process?

PHP frameworks, such as Laravel, Symfony, and Zend, are pre-built sets of tools and libraries designed to streamline the web development process. They provide a structured environment, reusable code components, and follow best practices, significantly reducing development time.

Q) How do I create a backup of my MySQL database, and what are the recommended backup strategies?

A) Creating backups in MySQL is very important in order to protect the data. You can use tools like mysqldump or implement hot backup solutions. It's recommended to perform regular backups and store backups in separate locations to prevent data loss in case of hardware failures, accidental deletions, or other unforeseen issues.

Q) Can MySQL be used for large-scale and high-traffic applications?

A) Yes, MySQL can be used for large-scale and high-traffic applications. Many well-known websites and applications, including those with millions of users, rely on MySQL for their data storage needs.

3-a) Interview Questions - MYSQL

Basic Concepts:

What is MySQL, and how does it differ from other relational database management systems?
Can you explain the difference between a database and a table in MySQL?
What is normalization, and why is it important in database design?

SQL Queries:

How do you retrieve all the records from a table in MySQL?
Can you explain the difference between the WHERE and HAVING clauses in SQL?
What is a subquery, and when might you use one in a MySQL query?

Database Design:

How do you approach designing a database schema for a new project?
What are the advantages and disadvantages of denormalization in database design?
Can you explain the concept of foreign keys and their role in maintaining data integrity?

Indexes and Optimization:

Why is indexing important in MySQL, and how does it impact query performance?
What types of indexes are available in MySQL, and when would you use each type?
How do you optimize a slow-performing query in MySQL?

Security:

What are some best practices for securing a MySQL database?
How would you manage user privileges in MySQL to follow the principle of least privilege?
Can you explain the role of SSL/TLS in MySQL security?

Backup and Recovery:

How do you perform a backup and restore in MySQL?
What is the significance of transaction logs in MySQL, especially in terms of recovery?
Can you explain the difference between logical and physical backups?

Scaling and Replication:

How would you scale a MySQL database to handle increased traffic and data volume?
What is database replication, and how does it contribute to high availability?
Can you describe the process of setting up and monitoring MySQL replication?

Performance Tuning:

What tools or techniques do you use to identify and resolve performance issues in MySQL?
How would you optimize a database for read-heavy workloads versus write-heavy workloads?
Can you explain the concept of query caching in MySQL and its benefits?

Error Handling and Troubleshooting:

How do you handle errors in MySQL, both in terms of application-level and database-level errors?
What steps would you take to troubleshoot a connectivity issue between a web application and a MySQL database?
Can you discuss common issues and solutions related to deadlocks in MySQL?

3-b) Interview Questions - PHP

Basic Concepts:

What is PHP, and how does it differ from other programming languages?
Can you explain the difference between single quotes and double quotes in PHP?
What is the significance of the dollar sign ($) in PHP?

Variables and Data Types:

How do you declare a variable in PHP?
Can you describe the differences between local and global variables in PHP?
What are the basic data types in PHP?

Control Structures:

Explain the difference between "include" and "require" in PHP.
How do "if," "else if," and "else" statements work in PHP?
What is a "switch" statement, and when would you use it?

Functions:

How do you define a function in PHP?
Can you explain the difference between "return" and "echo" in the context of functions?
What are anonymous functions, and when might you use them?

Arrays:

How do you create an associative array in PHP?
What is the difference between "array_merge" and the "+" operator when combining arrays?
How would you loop through an array in PHP?

Object-Oriented Programming (OOP):

What are the principles of object-oriented programming, and how are they applied in PHP?
Explain the concepts of encapsulation, inheritance, and polymorphism in PHP.
How do you create an object in PHP, and what is the constructor method?

Database Interaction:

How do you connect to a MySQL database using PHP?
Explain the process of executing a SQL query in PHP.
What are prepared statements, and why are they important for database security?

Error Handling:

How does error handling work in PHP, and what are the differences between "die," "exit," and "trigger_error"?
What is the purpose of the "@" symbol in front of a function call, and why is it sometimes discouraged?
Can you explain the concept of exceptions and how they are caught in PHP?

Security Best Practices:

What measures can be taken to prevent SQL injection in PHP?
How would you securely handle user authentication and passwords in PHP?
Explain the importance of input validation and sanitization in web applications.

Frameworks and Libraries:

Are you familiar with any PHP frameworks, and what are their advantages?
How does autoloading work in PHP, and why is it useful?
Can you discuss the role of Composer in PHP development?

Hot Posts

Professor For You

Solved Assignment - PHP - MySQL