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:
