Chapter 5 PHP

1. Define MySQL.

Answer:

MySQL is used to manage stored data and is an open source Database Management Software
(DBMS) or Relational Database Management System (RDBMS).

2. POD::_construct()

Answer:

POD::_construct() in PHP
2 marks
The POD::__construct() function in PHP is used to create a new instance of the
PDO (PHP Data Objects) class. PDO provides a consistent way to interact with
different database management systems (DBMS) like MySQL, PostgreSQL, Sqlite
etc.
Here are the key parameters for the PDO::__construct() function:
1. DSN (Data Source Name) (1 mark)
o This is a required parameter that specifies the connection information for the
database. The format of the DSN string varies depending on the specific DBMS you
are connecting to. For example, a MySQL DSN might look like
mysql:host=localhost;dbname=mydatabase.

2. Username (1 mark)
o This is an optional parameter that specifies the username for the database
connection.
3. Password (1 mark)
o This is an optional parameter that specifies the password for the database
connection.
4. Options (1 mark)
o This is an optional parameter that is an associative array of driver-specific
connection options. These options can be used to configure things like persistent
connections or error handling.
Example
PHP
$dsn = 'mysql:host=localhost;dbname=mydatabase';
$username = 'username';
$password = 'password';

try {
$pdo = new PDO($dsn, $username, $password);
// PDO connection established successfully
echo 'Connected to database';
} catch (PDOException $e) {
echo 'Connection failed: ' . $e->getMessage();
}

Additional Points
 It's important to remember to escape any user input used in the DSN or when
constructing queries to prevent SQL injection attacks.
 PDO provides a more secure and object-oriented way to interact with databases
compared to older functions like mysql_connect.

3. mysqli_connect()

Answer:

mysqli_connect() in PHP
2 Marks
The mysqli_connect() function in PHP is employed to establish a connection to a
MySQL database server. It returns a connection object on success, and FALSE if the
connection fails.
Parameters (1 Mark)
The mysqli_connect() function accepts up to six parameters as shown in the
image:
1. host (Optional):
o This parameter specifies the hostname or IP address of the MySQL server. By
default, it assumes localhost, which is the server where your PHP script is running.
2. username (Optional):
o This parameter represents the username for MySQL authentication. If omitted, it
defaults to the value of the PHP_USER environment variable.
3. password (Optional):
o This parameter represents the password for MySQL authentication. If omitted, it
defaults to the value of the PHP_PASSWORD environment variable.
4. dbname (Optional):
o This parameter specifies the name of the database you want to connect to. If
omitted, the default database associated with the username is used.
5. port (Optional):
o This parameter specifies the port number on the MySQL server to connect to. The
default port for MySQL is 3306.
6. socket (Optional):
o This parameter specifies the socket or named pipe to be used for the connection.
This is typically only used on Unix-like systems for local connections.
Example:
PHP
<?php

$host = 'localhost';
$username = 'your_username';
$password = 'your_password';
$dbname = 'mydatabase';

// Connect to the MySQL server

$conn = mysqli_connect($host, $username, $password, $dbname);

// Check for connection errors

if (!$conn) {
echo "Connection failed: " . mysqli_connect_error();
exit;
}

echo "Connected to MySQL server successfully!";

// Close connection (not required here, but good practice)

mysqli_close($conn);

?>

Important Note:
 Specifying username and password directly in the script is not recommended for
security reasons. Consider using environment variables or a configuration file to
store sensitive credentials.

4. Enter a data into database.

Answer:

<?php

// Database connection settings

$servername = "localhost";

$username = "your_username";

$password = "your_password";

$dbname = "test_db";

// Create connection

$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection

if ($conn->connect_error) {

die("Connection failed: " . $conn->connect_error);

// Data to be inserted

$name = "John Doe";

$email = "john.doe@example.com";

// Prepare and bind

$stmt = $conn->prepare("INSERT INTO users (name, email) VALUES (?, ?)");

$stmt->bind_param("ss", $name, $email);

// Execute the statement

if ($stmt->execute()) {

echo "New record created successfully";

} else {

echo "Error: " . $stmt->error;

// Close the statement and connection

$stmt->close();

$conn->close();

?>

5. Retrive and present data from database.

Answer:

<?php

// Database connection settings

$servername = "localhost";

$username = "your_username";

$password = "your_password";

$dbname = "test_db";

// Create connection

$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection

if ($conn->connect_error) {

die("Connection failed: " . $conn->connect_error);

// SQL query to select data

$sql = "SELECT id, name, email, created_at FROM users";

$result = $conn->query($sql);

// Check if there are results and output data

if ($result->num_rows > 0) {

echo "<table border='1'>";

echo "<tr><th>ID</th><th>Name</th><th>Email</th><th>Created At</th></tr>";

while ($row = $result->fetch_assoc()) {

echo "<tr>";

echo "<td>" . $row["id"] . "</td>";

echo "<td>" . $row["name"] . "</td>";

echo "<td>" . $row["email"] . "</td>";

echo "<td>" . $row["created_at"] . "</td>";

echo "</tr>";

echo "</table>";

} else {

echo "0 results";

// Close the connection

$conn->close();

?>

6. Write syntax of constructing PHP webpage with MySqL

Answer:
7. Write update and delete operations on table data.

Answer: