Accessing
MySQL
UNIT 4
Accessing MySQL
There are two main ways to access MySQL database
• phpMyAdmin: This is a web-based interface that provides a user-
friendly way to manage your databases. It's a good option if
you prefer a graphical interface and don't need to write complex
SQL statements.
• MySQL Client: This is a command-line tool that allows to directly
interact with the MySQL server using SQL statements. It's
powerful but requires some knowledge of SQL syntax.
Using PHP
MyAdmin
• phpMyAdmin is a web application that allows you to manage MySQL
databases through a user-friendly interface accessible from a web
browser.
1. Accessing phpMyAdmin:
• Open web browser and navigate to the phpMyAdmin login URL provided by web
hosting provider (usually like http://your_domain.com/phpmyadmin/).
2. Logging In:
• Enter your MySQL username and password in the respective fields.
• Click on "Go" to log in.
3. Creating a Database:
• In the left navigation pane, we'll see a list of existing databases
• Click on the "New" button located above the list.
• In the "Create database" field, enter a name for database
• Select the character set (usually UTF-8 for most use cases).
• Click on the "Create" button to create the database.
4. Creating a Table:
• Click on the "Create table" tab in the main panel.
• In the "Name" field, enter a name for your table
• Under the "Columns" section, click on "Add field" a few times to create multiple
columns for your data.
5. Defining Table Columns:
• For each column,
• Enter a name (e.g., "id", "title", "author", "year_published").
• Choose a data type from the dropdown (e.g., "INT" for ID, "VARCHAR" for title and
author, "INT" for year).
• Specify the length/values if applicable
• Optionally, check the "AI" checkbox for the "id" column to make it auto-increment
• Once you've defined all columns, click on the "Go" button at the bottom of the page.
6. Adding Sample Data:
• Click on the "Browse" tab in the main panel.
• This will show the newly created table with its columns.
• We can click on the "Insert" link to insert new rows (data entries) into the
table.
• Fill in the values for each column of the new row and click "Go" to insert it.
Using MySQL Client
• The MySQL client (mysql) is a powerful command-line tool for interacting
with MySQL databases
1. Install and open terminal
2. Basic Command: Use command to connect: mysql -u your_username -p
your_database_name
Once connected, we can use various commands to manage your databases:
i.Show Databases:This displays a list of existing databases on the
server. Show Databases;
Ii.Use a Database:This tells the client to use a specific database for subsequent queries.
USE database_name;
Iii.Create a Database:This creates a new database with the specified name.
CREATE DATABASE database_name;
Iv.Show Tables:This displays a list of tables within the current
database. SHOW TABLES;
v.Create a Table:
Replace your_table_name with the desired table name.Specify each column with its name, data
type (e.g., INT, VARCHAR, DATE), and optional constraints (e.g., PRIMARY KEY, NOT NULL).
CREATE TABLE your_table_name (
column1_name data_type1
[constraint1], column2_name
data_type2 [constraint2],
...
);
vi. Describe a Table:
This shows the structure of the table, including column names, data types, and
constraints
DESCRIBE table_name;
vii. Insert Data:This inserts a new row of data into the table
INSERT INTO your_table_name (column1, column2, ...) VALUES (value1, value2,
...);
viii. Select Data:retrieve data from tables.
SELECT * FROM your_table_name; -- Select all columns from a table
SELECT column1, column2 FROM your_table_name; -- Select specific
columns
SELECT * FROM your_table_name WHERE condition; -- Select based on
a condition
Connecting to MySQL:
• PHP offers two main methods for connecting to MySQL:
• MySQLi: An improved and object-oriented interface for interacting with
MySQL.
• It's generally considered easier to use for beginners.
• PDO (PHP Data Objects): Provides a more generic interface that works
not only with MySQL but also with other DBMSs like PostgreSQL,
SQLite, etc.
• If there is a plan to use multiple databases in projects, PDO is a
more flexible choice
Connecting to MySQL with PHP using MySQLi
<?php
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$dbname = "your_database";
$conn = new mysqli($servername, $username, $password, $dbname); // Create connection
if ($conn->connect_error) { // Check connection
die("Connection failed: " . $conn->connect_error);
}
$sql = "SELECT id, name, email FROM users"; // Execute a query (example: selecting
data)
$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["name"]. " " . "Email: " . $row["email"].
"<br>";
}
} else {
echo "0 results";
}
$conn->close();
?>
Connecting to MySQL with PHP using MySQLi
<?php
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$dbname = "your_database";
$conn = new mysqli($servername, $username, $password, $dbname); // Create connection
if ($conn->connect_error) { // Check connection
die("Connection failed: " . $conn->connect_error);
}
$sql = "SELECT id, name, email FROM users"; // Execute a query (example: selecting data)
$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["name"]. " " . "Email: " . $row["email"]. "<br>";
}
} else {
echo "0 results";
}
$conn->close();
?>
Connecting to MySQL with using PDO
<?php
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$dbname =
"your_database"; try {
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username,
$password); // Set PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// Prepare a statement for data insertion (example)
$sql = "INSERT INTO users (name, email) VALUES (?, ?)";
$stmt = $conn->prepare($sql);
$stmt->execute([“ASHOK",
“Ashok@gmail.com"]); echo "New record created
successfully";
} catch(PDOException $e) {
echo "Error: " . $e->getMessage();
}
$conn = null;
?>
Connecting to MySQL with using PDO
<?php
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$dbname = "your_database";
try {
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password); // Set PDO error
mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// Prepare a statement for data insertion (example)
$sql = "INSERT INTO users (name, email) VALUES (?, ?)";
$stmt = $conn->prepare($sql);
$stmt->execute(["John Doe", "johndoe@example.com"]);
echo "New record created successfully";
} catch(PDOException $e) {
echo "Error: " . $e->getMessage();
}
$conn = null;
?>
PHP MySQL Functions
• There are two main sets of functions for working with MySQL
databases in PHP:
i. MySQL functions: These are the older functions, part of the
original PHP MySQL extension. They are considered
deprecated due to security vulnerabilities and are being
phased out.
• mysql_connect(): Opens a connection to the MySQL server (similar to
mysqli_connect).
• mysql_query(): Executes a MySQL query on the database (similar to
mysqli_query)
• mysql_fetch_array(): Fetches a result row as an array.
• mysql_error(): Returns the error message from the last MySQL
operation.
• mysql_close(): Closes the connection to the MySQL server
ii. MySQLi functions (MySQL Improved): This is a newer and
recommended approach for interacting with MySQL
It offers an object-oriented and a procedural interface, improved
databases.
security features like prepared statements, and better
performance.
Some of the key MySQLi functions include:
• mysqli_connect(): Establishes a connection to the MySQL server.
• mysqli_query(): Executes a MySQL query on the database.
• mysqli_fetch_assoc(): Fetches a result row as an associative array.
• mysqli_affected_rows(): Returns the number of affected rows by the
previous MySQL operation.
• mysqli_close(): Closes the connection to the MySQL server.
Retrieving Query
Results offers two main ways to retrieve and view query results:
• phpMyAdmin
✔ Direct Viewing:
• Access the phpMyAdmin interface and navigate to the desired database.
• Click on the table you want to query.
• This will display the table structure and data.
✔ Using the SQL Tab:
• In the top navigation pane, locate the "SQL" tab.
• Here, you can write your desired SQL query. This could be a simple SELECT statement to
retrieve specific data or a more complex query involving joins, filtering, and
aggregations.
• Once you've written your query, click "Go" at the bottom of the pane.
• If the query executes successfully, the results will be displayed below the query
window. This includes the number of rows affected and the retrieved data itself.
✔ Exporting Results:
• Once you have your query results displayed, you can export them for
further use:
• Locate the "Query results operations" section below the results table.
• Click the "Export" link.
• This will provide you with options to export the data in various
formats like CSV, Excel, SQL, and more.
Counting Returned
Records
There are two main ways to count the number of records returned by a
query in phpMyAdmin:
1. Using the COUNT(*) function:
• phpMyAdmin itself offers a way to count records using the SQL function
COUNT(*).
• In the SQL query window, type desired query.
• For example, to count all records from a table named users,
SELECT COUNT(*) FROM users;
The number of records will be displayed in the first column of the result
set.
2. Checking the estimated row count:
• By default, phpMyAdmin might display an estimated row count for some
tables
• This is an optimization to avoid the overhead of counting every single row
for large tables.
• Here's how to know if the displayed count is an estimate:
• Look at the bottom left corner of the table view in phpMyAdmin.
• If it says "Showing rows 0 to X (estimates)," then the displayed count is an
approximation.
• To get the exact count:
• write the query SELECT COUNT(*) FROM table_name;
• Alternatively, you can adjust a configuration setting in phpMyAdmin. This
might not be recommended for very large tables as it can impact
performance.
