NEP : BCA VISEM

PHP & MySQL (DSC17)

It can be done in two ways:

MySQLi Object-Oriented MySQLi Procedural
<?php <?php
$servername = "localhost";
$username = "username"; $servername = "localhost";
$password = "password"; $username = "username";
$password = "password";
// Connection
$conn = new mysqli($servername, // Connection
$username, $password); $conn = mysqli_connect($servername,
$username,
// For checking if connection is // $password);
successful or not
if ($conn->connect_error) // Check if connection is
{ // Successful or not
die("Connection failed: ". $conn- if (!$conn) {
>connect_error); die("Connection failed: ".
} mysqli_connect_error());
echo "Connected successfully"; }
?> echo "Connected successfully";
?>

Connection to MySQL using PDO

<?php
$servername = "localhost";
$username = "username";
$password = "password";
try {$conn = new PDO("mysql:host=$servername;dbname=myDB", $username, $password);
// Set the PDO error mode // to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "Connected successfully";
} catch(PDOException $e) {
echo "Connection failed: ". $e->getMessage();
}
?>

MySQLi Object-Oriented: MySQLi Procedural: PDO:

$conn->close(); mysqli_close($conn); $conn = null;

2
NEP : BCA VISEM
PHP & MySQL (DSC17)

Using MySQL Client

1. Connecting to MySQL Server :- Command: `mysql -u username -p`

Example: Mysql -u root –p

<?php
$servername = "localhost"; $username = "username"; $password = "password";
// Create connection
$conn = new mysqli($servername, $username, $password);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";
?>
2. Creating a Database :- Command: `CREATE DATABASE database_name;`
Example: CREATE DATABASE mydatabase;

<?php
$servername = "localhost"; $username = "username"; $password = "password";
// Create connection
$conn = new mysqli($servername, $username, $password);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Create database
$sql = "CREATE DATABASE myDB";
if ($conn->query($sql) === TRUE) {
echo "Database created successfully";
} else {
echo "Error creating database: " . $conn->error;
}
$conn->close();
?>

3. Selecting a Database:- - Command: ` SELECT column_name(s) FROM table_name`

Example: SELECT id, firstname, lastname FROM MyGuests;

<?php
$servername = "localhost"; $username = "username"; $password = "password"; $dbname
= "myDB";
3
NEP : BCA VISEM
PHP & MySQL (DSC17)

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = "SELECT id, firstname, lastname FROM MyGuests";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";
}
} else {
echo "0 results";
}
$conn->close();
?>

4. Creating a Table :- Command: `CREATE TABLE table_name (column1 datatype,

column2 datatype, ...);`

Example:

CREATE TABLE users ( Id INT AUTO_INCREMENT PRIMARY KEY,Username

VARCHAR(50) NOT NULL,

Email VARCHAR(100) NOT NULL );

<?php
$servername = "localhost"; $username = "username"; $password = "password";
$dbname = "myDB";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// sql to create table
$sql = "CREATE TABLE MyGuests ( id INT(6) UNSIGNED AUTO_INCREMENT
PRIMARY KEY, firstname VARCHAR(30) NOT NULL, lastname VARCHAR(30) NOT
NULL,email VARCHAR(50), reg_date TIMESTAMP DEFAULT

4
NEP : BCA VISEM
PHP & MySQL (DSC17)

CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

)";

if ($conn->query($sql) === TRUE) {

echo "Table MyGuests created successfully";
} else {
echo "Error creating table: " . $conn->error;
}
$conn->close();
?>

5. Inserting Data into a Table:- Command: `INSERT INTO table_name (column1,

column2, ...) VALUES (value1, value2, ...);`

Example: INSERT INTO users (username, email) VALUES (‘john_doe’,

‘[email protected]’);

<?php
$servername = "localhost";$username = "username"; $password = "password";
$dbname = "myDB";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}

$sql = "INSERT INTO MyGuests (firstname, lastname, email) VALUES ('John', 'Doe',
'[email protected]')";
if ($conn->query($sql) === TRUE) {
echo "New record created successfully";
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}
$conn->close();
?>

5
NEP : BCA VISEM
PHP & MySQL (DSC17)

Using php MyAdmin

PHPMYADMIN is an open-source software tool introduced on September 9, 1998, which is

written in PHP. Basically, it is a third-party tool to manage the tables and data inside the
database. phpMyAdmin supports various type of operations on MariaDB and MySQL. The
main purpose of phpMyAdmin is to handle the administration of MySQL over the web.

It is the most popular application for MySQL database management. We can create, update,
drop, alter, delete, import, and export MySQL database tables by using this software.
phpMyAdmin also supports a wide range of operation like managing databases, relations,
tables, columns, indexes, permissions, and users, etc., on MySQL and MariaDB. These
operations can be performed via user interface, while we still have the ability to execute any
SQL statement.

phpMyAdmin is a GUI-based application which is used to manage MySQL database. We

can manually create database and table and execute the query on them. It provides a web-
based interface and can run on any server. Since it is web-based, so we can access it from any
computer.
Features of phpMyAdmin
phpMyAdmin supports several features that are given below:
o phpMyAdmin can create, alter, browse, and drop databases, views, tables, columns,
and indexes.
o It can display multiple results sets through queries and stored procedures.
o phpMyAdmin use stored procedure and queries to display multiple results sets.
o It supports foreign keys and InnoDB tables.
o phpMyAdmin can track the changes done on databases, views, and tables.
o We can also create PDF graphics of our database layout.
o phpMyAdmin can be exported into various formats such as XML, CSV, PDF,
ISO/IEC 26300 - OpenDocument Text and Spreadsheet.
o It supports mysqli, which is the improved MySQL extension.
o phpMyAdmin can interact with 80 different languages.

Advantage of phpMyAdmin
o phpMyAdmin can run on any server or any OS as it has a web browser.
o We can easily create, delete, and edit the database and can manage all elements using
the graphical interface of phpMyAdmin, which is much easier than MySQL
command-line editor.
o phpMyAdmin helps us to control the user's permission and operate several servers at
the same time.

6
NEP : BCA VISEM
PHP & MySQL (DSC17)

o We can also backup our database and export the data into different formats like XML,
CSV, SQL, PDF, OpenDocument Text, Excel, Word, and Spreadsheet, etc.
o We can execute complex SQL statements and queries, create and edit functions,
triggers, and events using the graphical interface of phpMyAdmin.
Disadvantage of phpMyAdmin
o phpMyAdmin is a simple interface, but quite tough for a beginner to learn.
o phpMyAdmin is difficult to install as it needs three more software tools before
installation, which is- Apache server, PHP, and MySQL.
o We have to install all these software tools individually, whereas XAMPP already
contains them in a single package. XAMPP is the easiest way to get phpMyAdmin.
o It has no schema visualization.
o phpMyAdmin is a web-based software tool which runs only on the browser, so It
completely depends on browsers.
o It does not have auto-compilation capability.
Using phpMyAdmin :
1. Logging into phpMyAdmin : Open web browser and navigate to
`http://localhost/phpmyadmin` Log in with MySQL username and password.

Now open the browser and type http://localhost/phpmyadmin/. phpMyAdmin will start
running in the browser.

7
NEP : BCA VISEM
PHP & MySQL (DSC17)

2. Creating a Database : Click on “New” on the left sidebar. Enter the database name and
click “Create.”

Click on New (1) to create a database and enter the database name in Create database
(2) field and then click on Create (3) button. We can create any number of databases.

3. Creating a Table : Select the database from the left sidebar.Click on “Create Table.”
Enter table details and click “Save.”

Enter the table name, number of columns, and click on Go. A message will show that the
table is created successfully.

Now enter the field name, type, their size, and any constraint here and save it.

8
NEP : BCA VISEM
PHP & MySQL (DSC17)

The table is created successfully. We can make changes in the table from here.

9
NEP : BCA VISEM
PHP & MySQL (DSC17)

4. Inserting Data into a Table : Select the table.Click on “Insert” tab.Enter data into the
fields and click “Go.”

5. Executing SQL Queries : Click on “SQL” tab. Enter SQL query and click “Go.”

10
 NEP : BCA VISEM
PHP & MySQL (DSC17)

6. Exporting Database : Select the database to export.Click on “Export” tab.

- Choose export method and options, then click “Go.”

Export a MySQL database using phpMyAdmin

1. Once inside, you will see a list of your databases on the left sidebar.

Tip: You may expand the list by clicking the + button on the left.
2. Select the name of the database you would like to export.
3. Click Export at the top.
4. Select the Export method you wish to use. If you aren't sure, leave things as they are.
5. Choose the file format in the drop-down box.
6. Click Go. This will download a .sql file to your computer.

7. Importing Database :Click on “Import” tab.Choose file to import and click “Go.”
Export a MySQL database using phpMyAdmin
1. Once inside, you will see a list of your databases on the left sidebar.
Tip: You may expand the list by clicking the + button on the left.
2. Select the name of the database you would like to export.
3. Click Export at the top.
4. Select the Export method you wish to use. If you aren't sure, leave things as they are.
5. Choose the file format in the drop-down box.
6. Click Go. This will download a .sql file to your computer.

11
NEP : BCA VISEM
PHP & MySQL (DSC17)

This breakdown covers various operations using both the MySQL client and phpMyAdmin,
from basic tasks like creating databases and tables to more advanced operations like
executing SQL queries and exporting/importing databases.
compare using the MySQL Client and phpMyAdmin for managing MySQL databases,
highlighting the differences between the two:

Using MySQL Client:

1. Interface:
- Command-line interface.
- Operated via terminal or command prompt.
- Suitable for users comfortable with command-line operations.
2. Connectivity:
- Directly connects to the MySQL server using specified credentials.
- Requires knowledge of MySQL server location, username, and password.

3. Flexibility:
- Offers more flexibility and control over database operations through direct SQL
commands.
- Suitable for advanced users and developers who prefer command-line interfaces.

4. Scripting:
- Convenient for scripting database operations.
- Allows for automation of tasks through shell scripts or batch files.

5. Portability:
- Available on various platforms (Windows, Linux, macOS).
- Provides consistent experience across different operating systems.

12
NEP : BCA VISEM
PHP & MySQL (DSC17)

Using phpMyAdmin:

1. Interface:
- Web-based graphical user interface (GUI).
- Accessed through a web browser.
- Suitable for users who prefer visual interfaces.

2. Connectivity:
- Connects to MySQL server via web browser.
- Authentication managed by the web server.

3. Ease of Use:
- Provides a user-friendly interface with menus and forms for database management tasks.
- Suitable for beginners and non-technical users.

4. Functionality:
- Offers a wide range of features for database management, including database creation,
table creation, data insertion, querying, and more.
- Provides tools for database administration, such as user management and privileges.

5. Visual Representation:
- Presents database structures visually, making it easy to understand table relationships and
database schemas.
- Offers graphical representation of query results.

6. Accessibility:
- Requires a web server with PHP and MySQL installed.
- Can be accessed from any device with a web browser and network access.

Differences:
1. Interface: MySQL Client uses a command-line interface, while phpMyAdmin provides
a web-based graphical interface.

2. Connectivity: MySQL Client connects directly to the MySQL server, while

phpMyAdmin connects through a web server.

3. Flexibility: MySQL Client offers more flexibility and control over database operations
with direct SQL commands, while phpMyAdmin provides a user-friendly interface for
managing databases.

4. Access: MySQL Client can be used on any system with MySQL installed, while
phpMyAdmin requires a web server with PHP and MySQL.

13
NEP : BCA VISEM
PHP & MySQL (DSC17)

5. User Base: MySQL Client is typically used by advanced users and developers, while
phpMyAdmin is more accessible to beginners and non-technical users.

In summary, MySQL Client and phpMyAdmin offer different approaches to managing

MySQL databases, catering to different user preferences and skill levels. While MySQL
Client provides a more hands-on and flexible approach through the command line,
phpMyAdmin offers a user-friendly and visual interface accessible via web browser.

MySQL commands

MySQL provides a wide range of commands for managing databases, tables, users, and more.
Here’s an overview of some common MySQL commands

Database Management:

1. Creating a Database: CREATE DATABASE database_name;

2. Selecting a Database:USE database_name;
3. Showing Databases:SHOW DATABASES;
4. Describing a Database:DESCRIBE database_name;
5. Altering a Database: ALTER DATABASE database_name [OPTIONS];
6. Copying a Database:CREATE DATABASE new_database_name AS SELECT *
FROM existing_database_name;
7. Renaming a Database:RENAME DATABASE old_database_name TO
new_database_name;
8. Deleting a Database:DROP DATABASE database_name;

Table Management:

1. Creating a Table: CREATE TABLE table_name (Column1 datatype,Column2

datatype,..);
2. Showing Tables: SHOW TABLES;
3. Describing a Table:DESCRIBE table_name;
4. Altering Table Structure: ALTER TABLE table_name ADD COLUMN
column_name datatype;
ALTER TABLE table_name MODIFY column_name
datatype;
ALTER TABLE table_name DROP column_name ;
5. Deleting a Table: DROP TABLE table_name;
6. Renaming a Table: RENAME TABLE old_table_name TO new_table_name;
7. Truncating a Table: TRUNCATE TABLE table_name;// DELETE RECORD
ONLY
8. Copying a Table: CREATE TABLE new_table_name AS SELECT * FROM
existing_table_name;

14
NEP : BCA VISEM
PHP & MySQL (DSC17)

Data Manipulation:

1. Inserting Data:INSERT INTO table_name (column1, column2, ...) VALUES

(value1, value2, ...);
2. Selecting Data:SELECT column1, column2, ... FROM table_name;
3. Updating Data:UPDATE table_name SET column1 = value1, column2 = value2
WHERE condition;
4. Deleting Data: DELETE FROM table_name WHERE condition;
5. Grouping Data: SELECT column1, SUM(column2) FROM table_name GROUP BY
column1;
6. Joining Tables: SELECT * FROM table1 INNER JOIN table2 ON table1.column =
table2.column;

User Management:

1. Creating a User:CREATE USER ‘username’@’localhost’ IDENTIFIED BY

‘password’;
2. Granting Privileges:GRANT ALL PRIVILEGES ON database_name.* TO
‘username’@’localhost’;
3. Revoking Privileges:REVOKE ALL PRIVILEGES ON database_name.* FROM
‘username’@’localhost’;
4. Deleting a User:DROP USER ‘username’@’localhost’;

Other Commands/ Miscellaneous:

1. Showing / Displaying MySQL Version: SELECT VERSION();

2. Displaying Server Variables: SHOW VARIABLES;
3. Showing Process List: SHOW PROCESSLIST;
4. Exiting MySQL Client: EXIT;
These MySQL commands cover database management, table operations, data manipulation,
user management, and other administrative tasks, providing a comprehensive toolkit for
interacting with MySQL databases.

Using PHP with MySQL

Using PHP with MySQL typically involves establishing a connection between a PHP script
and a MySQL database, executing SQL queries to retrieve or manipulate data, and processing
the results within the PHP script. This integration allows developers to build dynamic and
interactive web applications that can store, retrieve, and manage data efficiently.

15
NEP : BCA VISEM
PHP & MySQL (DSC17)

PHP MySQL functions

Here's a basic example demonstrating how to use PHP MySQL functions to connect to a
MySQL database, execute a query, fetch data, and close the connection:

some commonly used PHP MySQL functions along with examples:

1. Connecting to MySQL Database

`mysqli_connect()`: Establishes a connection to a MySQL database.
$servername = "localhost";$username = "username";$password = "password";
$database = "dbname";
$conn = mysqli_connect($servername, $username, $password, $database);
if (!$conn) { die("Connection failed: " . mysqli_connect_error()) }
2. Executing Queries
mysqli_query(): Executes a MySQL query. Performs a query against the database
$sql = "SELECT * FROM table";
$result = mysqli_query($conn, $sql);
if (!$result) {die("Query failed: " . mysqli_error($conn));}
3. Fetching Data
mysqli_fetch_assoc(): Fetches a result row as an associative array.
while ($row = mysqli_fetch_assoc($result))
{echo $row["column1"] . " " . $row["column2"]; }
4. Fetching Number of Rows
mysqli_num_rows(): Returns the number of rows in a result set.
$row_count = mysqli_num_rows($result);
echo "Number of rows: " . $row_count;
5. mysqli_affected_rows(): Returns the number of rows affected by the last INSERT,
UPDATE, or DELETE query.
$affected_rows = mysqli_affected_rows($conn);
echo "Number of affected rows: " . $affected_rows;
`mysqli_affected_rows()` is a PHP function used to retrieve the number of rows affected by
the most recent INSERT, UPDATE, DELETE, or REPLACE query executed on a MySQL
database connection.

$conn = mysqli_connect("localhost", "username", "password", "database");

// Example UPDATE query
$sql = "UPDATE users SET name='John' WHERE id=1";
// Execute the query
mysqli_query($conn, $sql);
// Get the number of affected rows
$affected_rows = mysqli_affected_rows($conn);
echo "Number of affected rows: " . $affected_rows;

16
NEP : BCA VISEM
PHP & MySQL (DSC17)

In this example, `$affected_rows` will contain the number of rows that were updated by the
UPDATE query. This value can be useful for checking if any changes were made to the
database and for error handling purposes.

Keep in mind that `mysqli_affected_rows()` should only be used after executing an INSERT,
UPDATE, DELETE, or REPLACE query. If you use it after executing a SELECT query, it
will return -1.

6. mysqli_real_escape_string(): Escapes special characters in a string for use in an SQL

statement.

$name = mysqli_real_escape_string($conn, $name);

$email = mysqli_real_escape_string($conn, $email);
7. mysqli_error(): Returns a string description of the last error.
if (!$result) {die("Query failed: " . mysqli_error($conn));}

8. mysqli_close(): Closes a previously opened database connection.

mysqli_close($conn);

9. Transaction Management :
- `mysqli_begin_transaction()`: Starts a transaction.
- `mysqli_commit()`: Commits the current transaction.
- `mysqli_rollback()`: Rolls back the current transaction.

Example:
mysqli_begin_transaction($conn);
mysqli_query($conn, "INSERT INTO table_name (column1, column2) VALUES ('value1',
'value2')");
mysqli_commit($conn);

10. Prepared Statements:

- `mysqli_prepare()`: Prepares an SQL statement for execution.
- `mysqli_stmt_bind_param()`: Binds variables to a prepared statement as parameters.
- `mysqli_stmt_execute()`: Executes a prepared Query.

Example:
$stmt = mysqli_prepare($conn, "INSERT INTO table_name (column1, column2) VALUES
(?, ?)");
mysqli_stmt_bind_param($stmt, "ss", $value1, $value2);
mysqli_stmt_execute($stmt);

17
NEP : BCA VISEM
PHP & MySQL (DSC17)

11. Database Metadata Retrieval :

- `mysqli_get_server_info()`: Returns the MySQL server version.
- `mysqli_get_client_info()`: Returns the MySQL client library version.
Example:
echo "Server version: " . mysqli_get_server_info($conn);
echo "Client version: " . mysqli_get_client_info();

Example
These functions are commonly used for interacting with MySQL databases in PHP
applications, enabling tasks such as querying data, inserting records, updating data, and more.

<?php
// Database credentials
$hostname = 'localhost'; // MySQL server hostname
$username = 'your_username'; // MySQL username
$password = 'your_password'; // MySQL password
$database = 'your_database'; // MySQL database name
// Connect to MySQL database
$conn = mysqli_connect($hostname, $username, $password, $database);

// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
// SQL query
$sql = "SELECT id, name, email FROM users";
// Execute query
$result = mysqli_query($conn, $sql);
// Check if query executed successfully
if (!$result) {
die("Query failed: " . mysqli_error($conn));
}
// Fetch data and display
echo "<h2>Users</h2>";
echo "<table border='1'>
<tr><th>ID</th><th>Name</th><th>Email</th></tr>";

while ($row = mysqli_fetch_assoc($result)) {

echo "<tr>";
echo "<td>" . $row['id'] . "</td>";
echo "<td>" . $row['name'] . "</td>";
echo "<td>" . $row['email'] . "</td>";
echo "</tr>";
}
echo "</table>";
// Free result set

18
NEP : BCA VISEM
PHP & MySQL (DSC17)

mysqli_free_result($result);
// Close connection
mysqli_close($conn);
?>

In this example:

- Replace `'localhost'`, `'your_username'`, `'your_password'`, and `'your_database'` with your

actual MySQL server hostname, username, password, and database name.
- The script connects to the MySQL database using `mysqli_connect()` function.
- It then executes a SELECT query to retrieve data from the `users` table.
- The fetched data is displayed in an HTML table.
- Finally, the script frees the result set using `mysqli_free_result()` and closes the database
connection using `mysqli_close()`.

List of Functions

PHP − indicates the earliest version of PHP that supports the function.

Sr.No Function & Description PHP

1 mysqli_affected_rows 4
It returns the number of affected rows in the previous SELECT, INSERT,
UPDATE, REPLACE, or DELETE query.
2 mysqli_autocommit 4.3.0
It is used to turn off or turn of auto-committing database modifications.
3 mysqli_begin_transaction 4.3.0
It is used to start a MySQL transaction.
4 mysqli_change_user 4.3.0
It is used to change the user of the current/given database connection.
5 mysqli_character_set_name 4.3.0
It is used to retrieve the default character set of the current database.
6 mysqli_close 4
It is used to close MySQLi connection
7 mysqli_commit 5
It is used to save the database modifications.
8 mysqli_connect 4
It opens a connection to a MySQLi Server
9 mysqli_connect_errno 5
It returns the error code from the last connection
10 mysqli_connect_error 5
It returns the description of the error from the last connection
11 mysqli_debug 5
It is used to performs debugging operations
12 mysqli_dump_debug_info 5
It is used dumps debugging info into the log
13 mysqli_errno 4

19
NEP : BCA VISEM
PHP & MySQL (DSC17)

It returns the last error code for the most recent statement
14 mysqli_error 4
It returns the last error description for the most recent statement
15 mysqli_error_list 4
It returns a list of errors from the last statement
16 mysqli_field_count 5
It returns the number of columns/field for the most recent query.
17 mysqli_get_charset 4
It returns a character set object.
18 mysqli_get_client_info 5
It returns the MySQL client library version.
19 mysqli_get client_stats 5
It returns statistics about client per-process.
20 mysqli_get_client_version 5.1.0
It returns the MySQLi client library version.
21 mysqli_get_connection_stats 5
It returns statistics about the client connection.
22 mysqli_get_host_info 5
It returns the MySQLi server hostname and the connection type.
23 mysqli_get_proto_info 5
It returns the MySQLi protocol version information
24 mysqli_get_server_info 5
It returns the MySQLi server information.
25 mysqli_get_server_version 5
It returns the MySQLi server version.
26 mysqli_get_warnings 5
It returns the errors generated by the last executed query.
27 mysqli_info 5
It returns information about the most recently executed query.
28 mysqli_init 5
It returns an object to use with the mysqli_real_connect() function.
29 mysqli_insert_id 5
It returns an id of last query.
30 mysqli_kill 5
This function asks to the server to kill MySQLi thread specified by the
process-id parameter.
31 mysqli_more_results 5
This function checks if there are more results from a multi query.
32 mysqli_multi_query 5
It used to separate the queries with a semicolon against the database.
33 mysqli_next_result 5
It prepares the next result set from mysqli_multi_query().
34 mysqli_options 5
It is used to sets connect options and change connection settings.
35 mysqli_ping 5
It is used to pings a server connection and reconnect to server if

20
NEP : BCA VISEM
PHP & MySQL (DSC17)

connection is lost.
36 mysqli_prepare 5
It performs a MySQL prepared query (with parameter markers) against
the database.
37 mysqli_query 5
It performs a query against the database.
38 mysqli_real_connect 5
This function opens a new connection to the MySQLi
39 mysqli_real_escape_string 5
This function escapes special characters in a string for an SQL statement.
40 mysqli_real_query 5
This function executes an SQL query.
41 mysqli_refresh 5
This function refreshes tables or caches, or resets the replication server
information.
42 mysqli_rollback 5
This function rolls back the current transaction for the specified database
connection.
43 mysqli_select_db 5
This function changes the default database.
44 mysqli_set_charset 5
It function sets the default character set.
45 mysqli_sqlstate 5
This function returns the SQLSTATE error code for the last error.
46 mysqli_ssl_set 5
This function creates an SSL connection.
47 mysqli_stat 5
This function returns the current system status.
48 mysqli_stmt_init 5
This function initializes a statement and returns an object suitable for
mysqli_stmt_prepare().
49 mysqli_thread _id 5
This function returns the current connection thread ID.
50 mysqli_thread_safe 5
This function verifies whether the client library is compiled as thread-
safe.

21
NEP : BCA VISEM
PHP & MySQL (DSC17)

Connecting to MySQL

To connect to a MySQL database using PHP, you can use the `mysqli_connect()` function.
Here's how you can do it:

<?php
$servername = "localhost"; // Change to your MySQL server name
$username = "username"; // Change to your MySQL username
$password = "password"; // Change to your MySQL password
$database = "dbname"; // Change to your MySQL database name
// Create connection
$conn = mysqli_connect($servername, $username, $password, $database);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";
?>
```
Replace `"localhost"`, `"username"`, `"password"`, and `"dbname"` with your actual MySQL
server name, username, password, and database name respectively.

Explanation:
- `$servername`: This variable holds the hostname of the MySQL server. In most cases, when
the MySQL server is running on the same machine as your PHP code, you can use
`"localhost"`.
- `$username`: This variable holds your MySQL username.
- `$password`: This variable holds your MySQL password.
- `$database`: This variable holds the name of the MySQL database you want to connect to.
- `mysqli_connect()`: This function establishes a connection to the MySQL server.
- `mysqli_connect_error()`: This function returns a string description of the last connection
error, if any.
- `die()`: This function terminates the script and prints an error message if the connection
fails.

If the connection is successful, you will see the message "Connected successfully".
Otherwise, you will see an error message indicating the reason for the connection failure.

Selecting the Database

When selecting a database in MySQL, you can use different methods depending on your
environment and requirements. Here are the main methods:

1. Using SQL `USE` Statement: Syntax: USE database_name;

Example: USE mydatabase; This method is used within SQL scripts or when interacting
with the database directly using a client like MySQL Workbench.

22
NEP : BCA VISEM
PHP & MySQL (DSC17)

2. Specifying Database in Connection String: Syntax: Depends on the programming

language or tool you are using.
For example,
PHP with mysqli extension:
$conn = mysqli_connect($servername, $username, $password, $database);
Example:
$conn = mysqli_connect(“localhost”, “username”, “password”, “mydatabase”);
- This method specifies the default database to use when establishing the
connection.
-
3. Using Configuration Files: Syntax: Depends on the configuration file format.
For example, in PHP with PDO:
$dsn = ‘mysql:host=localhost;dbname=mydatabase’;
$pdo = new PDO($dsn, $username, $password);
Example:
$dsn = ‘mysql:host=localhost;dbname=mydatabase’;
$pdo = new PDO($dsn, ‘root’, ‘password’);
- This method sets the default database in configuration files, which is useful for
applications that connect to the database frequently.

4. Command-line Option: Syntax: Mysql -u username -p database_name

Example:Mysql -u root -p mydatabase

This method is used when connecting to the MySQL server from the command line and
specifying the default database to use.

Each method has its use case and can be chosen based on factors such as convenience,
security, and the specific requirements of your application or environment.

Different types of SELECT queries in MySQL

allow for various ways to retrieve data from one or multiple tables. Here are the main types:

1. Basic SELECT:Retrieve data from one or more columns of a table.

SELECT column1, column2 FROM table_name;

2. SELECT with WHERE Clause: Retrieve data based on specified conditions.

SELECT column1, column2 FROM table_name WHERE condition;

3. SELECT DISTINCT:Retrieve unique values from a column.

SELECT DISTINCT column_name FROM table_name;

23
NEP : BCA VISEM
PHP & MySQL (DSC17)

4. SELECT with LIMIT:Retrieve a limited number of rows from the result set.

SELECT column1, column2 FROM table_name LIMIT 10;

5. SELECT with ORDER BY:Retrieve data sorted in ascending or descending order.

SELECT column1, column2 FROM table_name ORDER BY column1 ASC;

6. SELECT with GROUP BY:Group rows that have the same values into summary rows.

SELECT column1, SUM(column2) FROM table_name GROUP BY column1;

7. SELECT with JOIN:Retrieve data from multiple tables using a join condition.

SELECT t1.column1, t2.column2 FROM table1 AS t1 JOIN table2 AS t2 ON t1.id =

t2.id;

8. Aggregate Functions:Perform calculations on the data.

SELECT COUNT(*), AVG(column_name), MAX(column_name), MIN(column_name)

FROM table_name;

9. Subqueries: Use the result of one query as the input for another query.

SELECT column1 FROM table1 WHERE column2 IN (SELECT column2 FROM table2);

10. Conditional SELECT:Use conditional statements within the SELECT query.

SELECT column1, CASE WHEN condition THEN result ELSE result END FROM
table_name;

These are some of the main types of SELECT queries in MySQL. They provide a powerful
set of tools for retrieving and manipulating data from databases.

DIFFERENT TYPES OF SELECT QUERIES IN PHP AND MYSQL

In PHP, you use the mysqli or PDO extension to execute MySQL queries. Here are different
types of SELECT queries in PHP and MySQL:

1. Basic SELECT Query: Retrieve data from one or more columns of a table.

Using mysqli:
$query = “SELECT column1, column2 FROM table_name”;
$result = mysqli_query($conn, $query);
Using PDO:
$query = “SELECT column1, column2 FROM table_name”;

24
NEP : BCA VISEM
PHP & MySQL (DSC17)

$stmt = $pdo->query($query);
2. SELECT Query with WHERE Clause: Retrieve data based on specified conditions.
Using mysqli:
$query = “SELECT column1, column2 FROM table_name WHERE condition”;
$result = mysqli_query($conn, $query);
Using PDO:
$query = “SELECT column1, column2 FROM table_name WHERE condition”;
$stmt = $pdo->query($query);
3. SELECT Query with ORDER BY:
Retrieve data sorted in ascending or descending order.
Using mysqli:
$query = “SELECT column1, column2 FROM table_name ORDER BY column1 ASC”;
$result = mysqli_query($conn, $query);
Using PDO:
$query = “SELECT column1, column2 FROM table_name ORDER BY column1 ASC”;
$stmt = $pdo->query($query);
4. SELECT Query with LIMIT:
Retrieve a limited number of rows from the result set.
Using mysqli:
$query = “SELECT column1, column2 FROM table_name LIMIT 10”;
$result = mysqli_query($conn, $query);
Using PDO:
$query = “SELECT column1, column2 FROM table_name LIMIT 10”;
$stmt = $pdo->query($query);
5. SELECT Query with JOIN:
Retrieve data from multiple tables using a join condition.
Using mysqli:
$query = “SELECT t1.column1, t2.column2 FROM table1 AS t1 JOIN table2 AS t2 ON t1.id
= t2.id”;
$result = mysqli_query($conn, $query);
Using PDO:
$query = “SELECT t1.column1, t2.column2 FROM table1 AS t1 JOIN table2 AS t2 ON t1.id
= t2.id”;
$stmt = $pdo->query($query);
These are some examples of different types of SELECT queries in PHP using both mysqli
and PDO extensions to interact with MySQL databases.
Example
After connecting to the MySQL server using `mysqli_connect()`, you can select a specific
database using the `mysqli_select_db()` function. Here's how you can do it:
<?php
$servername = "localhost"; // Change to your MySQL server name
$username = "username"; // Change to your MySQL username

25
NEP : BCA VISEM
PHP & MySQL (DSC17)

$password = "password"; // Change to your MySQL password

$database = "dbname"; // Change to your MySQL database name

// Create connection
$conn = mysqli_connect($servername, $username, $password);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
// Select the database
if (!mysqli_select_db($conn, $database)) {
die("Database selection failed: " . mysqli_error($conn));
}
echo "Connected to database successfully";
?>
```
Replace `"localhost"`, `"username"`, `"password"`, and `"dbname"` with your actual MySQL
server name, username, password, and database name respectively.

Explanation:
- `$servername`, `$username`, `$password`: These variables hold the hostname, username,
and password of the MySQL server, as explained in the previous response.
- `$database`: This variable holds the name of the MySQL database you want to connect to.
- `mysqli_select_db()`: This function selects the specified database on the given MySQL
connection.
- `mysqli_error()`: This function returns a string description of the last error that occurred on
the MySQL connection.

If the database selection is successful, you will see the message "Connected to database
successfully". Otherwise, you will see an error message indicating the reason for the database
selection failure.
Counting Returned Records
To count the number of records returned by a query in MySQL, you can use the `COUNT()`
function along with the `SELECT` statement. Here’s how you can do it:

Syntax: SELECT COUNT(*) FROM table_name WHERE condition;

- `COUNT(*)`: Counts all rows in the specified table that meet the specified condition.
- `table_name`: The name of the table from which you want to count records.
- `condition`: Optional. Specifies the condition that must be met for a row to be included in
the count. If omitted, all rows are counted.
Example: Let’s say you want to count the number of users in a table named `users`:
SELECT COUNT(*) FROM users;
This query will return the total number of rows in the `users` table.

26
NEP : BCA VISEM
PHP & MySQL (DSC17)

If you want to count the number of users with a specific condition, such as where the age is
greater than 18:
SELECT COUNT(*) FROM users WHERE age > 18;
This query will return the number of users in the `users` table where the `age` column is
greater than 18.
You can also count the number of distinct values in a specific column by replacing `*` with
the column name:
SELECT COUNT(DISTINCT column_name) FROM table_name;
This will count the number of unique values in the specified column.
NUMBER OF RECORDS
To count the number of records returned by a query in PHP and MySQL, you can execute the
query and then use PHP to fetch the count. Here’s how you can do it:

Using PHP with mysqli extension:

// Establish connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
If (!$conn) {
Die(“Connection failed: “ . mysqli_connect_error());
}
// SQL query
$sql = “SELECT COUNT(*) AS total FROM table_name WHERE condition”;
// Execute query
$result = mysqli_query($conn, $sql);
// Fetch count
If ($result) {
$row = mysqli_fetch_assoc($result);
$count = $row[‘total’];
Echo “Total records: “ . $count;
} else {
Echo “Error: “ . mysqli_error($conn);
}
// Close connection
Mysqli_close($conn);
Replace `$servername`, `$username`, `$password`, `$dbname`, `table_name`, and `condition`
with your MySQL server details, table name, and condition for counting records.

This PHP code connects to the MySQL database, executes the query to count the number of
records that meet the specified condition, fetches the count, and then prints it. Make sure to
replace placeholders with your actual values.

27
NEP : BCA VISEM
PHP & MySQL (DSC17)

Updating Records with PHP

To update records in MySQL, you use the `UPDATE` statement. Here’s how you can update
records in MySQL:

Syntax:

UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;

- `table_name`: The name of the table where you want to update records.

- `column1`, `column2`, ...: The columns you want to update.

- `value1`, `value2`, ...: The new values you want to set for the columns.

- `condition`: Optional. Specifies the condition that must be met for the update to occur. If
omitted, all rows in the table are updated.

Example: Let’s say you have a table named `users` and you want to update the email of a
user with ID 1:

UPDATE users

SET email = ‘[email protected]’

WHERE id = 1;

This query will update the `email` column of the `users` table to
`’[email protected]’` for the user with ID 1.

You can also update multiple columns at once:

UPDATE users SET email = ‘[email protected]’, username = ‘new_username’

WHERE id = 1;

This query will update both the `email` and `username` columns for the user with ID 1.

Make sure to replace `table_name`, `column1`, `column2`, `value1`, `value2`, and `condition`
with your actual table and column names, values, and conditions.

To update records in a MySQL database using PHP, you can use the mysqli extension. Here's
a step-by-step guide:

28
NEP : BCA VISEM
PHP & MySQL (DSC17)

1. Connect to the MySQL database.

2. Prepare and execute an SQL UPDATE statement.
3. Handle any errors that may occur during the update process.
Below is a basic example:
<?php
// MySQL server configuration
$servername = "localhost"; $username = "your_username";
$password = "your_password"; $dbname = "your_database";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// New values for the record to be updated
$new_value1 = "new_value1";
$new_value2 = "new_value2";
// SQL query to update records
$sql = "UPDATE your_table SET column1='$new_value1', column2='$new_value2'
WHERE condition";
// Execute the update query
if ($conn->query($sql) === TRUE) {
echo "Record updated successfully";
} else {
echo "Error updating record: " . $conn->error;
}
// Close connection
$conn->close();
?>
Replace `your_username`, `your_password`, `your_database`, `your_table`, `column1`,
`column2`, and `condition` with your actual values and conditions.
In this example, we're updating records in `your_table` with new values specified in
`$new_value1` and `$new_value2`, based on the specified condition. Make sure to sanitize
user inputs to prevent SQL injection attacks.

29