BCA PHP & MYSQL

UNIT 5
Using MySQL Client
What is a MySQL client?
MySQL client is a common name for tools that are designed to connect to MySQL
Server. Client programs are used to send commands or queries to the server and allow
managing data in the databases stored on the server.
How to use MySQL Command Line Client
To access MySQL Server from the command-line client, open the program and
enter the password. After that, you will be able to use the client.

You can also access MySQL Command Line Client from Command Prompt.
For this:
• Open Command Prompt.
• Navigate to the bin folder. For example: cd C:\Program
Files\MySQL\MySQL Server 8.0\bin Run the mysql -u root -p command.
• Enter the password.

How to create a user from the command line CREATE

USER 'username' IDENTIFIED BY 'password';

LAKSHMIPATHY S V 1
 BCA PHP & MYSQL

You need to grant certain privileges to this user.

GRANT SELECT ON *.* TO 'username';
GRANT ALL PRIVILEGES ON *.* TO 'username';
How to create a database from the command line
CREATE DATABASE dbname;
To start working with the newly created database, execute the query:
USE dbname;
How to delete a MySQL database from the command line.
DROP DATABASE dbname;
How to delete a MySQL user account
DROP USER 'username';

MySQL Client options and query syntax

To get a full list of MySQL Client commands, enter help or \h at the prompt.
Using phpMyAdmin
phpMyAdmin is a GUI-based application which is used to manage MySQL
database.
Features of phpMyAdmin
• phpMyAdmin can create, alter, browse, and drop databases, views, tables,
columns, and indexes.
LAKSHMIPATHY S V 2
 BCA PHP & MYSQL

• It can display multiple results sets through queries and stored procedures.
• phpMyAdmin use stored procedure and queries to display multiple results
sets.
• It supports foreign keys and InnoDB tables.
• phpMyAdmin can track the changes done on databases, views, and tables.
• We can also create PDF graphics of our database layout.
• phpMyAdmin can be exported into various formats such as XML, CSV,
PDF, ISO/IEC 26300 - OpenDocument Text and Spreadsheet.
• It supports mysqli, which is the improved MySQL extension.
• phpMyAdmin can interact with 80 different languages.
• phpMyAdmin can edit, execute, and bookmark any SQL-statements and
even batch-queries.
• By using a set of pre-defined functions, it can transform stored data into any
format. For example - BLOB-data as image or download-link.
• It provides the facility to backup the database into different forms.
How to work with phpMyAdmin?
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.

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

LAKSHMIPATHY S V 3
 BCA PHP & MYSQL

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

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

LAKSHMIPATHY S V 4
 BCA PHP & MYSQL

Using PHP with MySQL:

PHP 5 and later can work with a MySQL database using:
• MySQLi extension (the "i" stands for improved)
• PDO (PHP Data Objects)
Connecting to MySQL:
The function used to connect PHP to MYSQL :
Procedure oriented mysqli.connect(servername,username,password,databasename)
• Database name is optional
eg.,
<?php
$servername = "localhost";

LAKSHMIPATHY S V 5
 BCA PHP & MYSQL

$username = "username";
$password = "password";
// Create connection
$conn = mysqli_connect($servername, $username, $password);
// Check connection if (!$conn) { die("Connection
failed: " . mysqli_connect_error());
} echo "Connected successfully";
?> Object oriented
mysqli(servername,username,password,dbname)
Dbname is optional
eg.,
<?php
$servername = "localhost";
$username = "username";
$password = "password";
// Create connection
$conn = mysqli($servername, $username, $password);
// Check connection if (!$conn) { die("Connection
failed: " . mysqli_connect_error());
} echo "Connected successfully";
?>

PDO: This command requires valid database to connect to. If no database is

specified, an exception is thrown.
Eg.,
<?php
$servername = "localhost";
$username = "username";
$password = "password"; try {
LAKSHMIPATHY S V 6
 BCA PHP & MYSQL

$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();
}
?>
Close the Connection
The connection will be closed automatically when the script ends. To close the
connection before, use the following:

MySQLi Object-Oriented:
$conn->close();

MySQLi Procedural: mysqli_close($conn);

PDO:
$conn = null;

PHP MySQL Create Database

Example (MySQLi Object-oriented)

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

// Create connection
$conn = new mysqli($servername, $username, $password);

LAKSHMIPATHY S V 7
 BCA PHP & MYSQL

// 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();
?>
Example (MySQLi Procedural)
<?php
$servername = "localhost";
$username = "username";
$password = "password";
// Create connection
$conn = mysqli_connect($servername, $username, $password);
// Check connection if (!$conn) { die("Connection
failed: " . mysqli_connect_error());
}
// Create database
$sql = "CREATE DATABASE myDB";
if (mysqli_query($conn, $sql)) { echo
"Database created successfully";
} else { echo "Error creating database: " .
mysqli_error($conn);
}
mysqli_close($conn); ?>
LAKSHMIPATHY S V 8
 BCA PHP & MYSQL

Example (PDO)
<?php
$servername = "localhost";
$username = "username";
$password = "password";

try {
$conn = new PDO("mysql:host=$servername", $username, $password);
// set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE,
PDO::ERRMODE_EXCEPTION);
$sql = "CREATE DATABASE myDBPDO";
// use exec() because no results are returned
$conn->exec($sql); echo "Database created
successfully<br>";
} catch(PDOException $e) { echo $sql .
"<br>" . $e->getMessage();
}

$conn = null;
?>
PHP MySQL Create Table
Example
<?php
$host = 'localhost:3306';
$user = '';
$pass = '';
$dbname = 'test';

LAKSHMIPATHY S V 9
 BCA PHP & MYSQL

$conn = mysqli_connect($host, $user, $pass,$dbname);

if(!$conn){ die('Could not connect:
'.mysqli_connect_error());
}
echo 'Connected successfully<br/>';

$sql = "create table emp5(id INT AUTO_INCREMENT,name VARCHAR(20)

NOT NULL, emp_salary INT NOT NULL,primary key (id))";
if(mysqli_query($conn, $sql)){ echo "Table emp5 created successfully";
}else{ echo "Could not create table: ".
mysqli_error($conn);
}
mysqli_close($conn);
?>
PHP MySQL Insert Data
Example
<?php
$host = 'localhost:3306';
$user = '';
$pass = '';
$dbname = 'test';
$conn = mysqli_connect($host, $user, $pass,$dbname);
if(!$conn){ die('Could not connect:
'.mysqli_connect_error());
}
echo 'Connected successfully<br/>';
$sql = 'INSERT INTO emp4(name,salary) VALUES ("sonoo", 9000)';
if(mysqli_query($conn, $sql)){ echo "Record inserted successfully";
}else{ echo "Could not insert record: ".
mysqli_error($conn);
}

LAKSHMIPATHY S V 10
 BCA PHP & MYSQL

mysqli_close($conn);
?>
PHP MySQL Update Record
Example
<?php
$host = 'localhost:3306';
$user = '';
$pass = '';
$dbname = 'test';

$conn = mysqli_connect($host, $user, $pass,$dbname);

if(!$conn){ die('Could not connect:
'.mysqli_connect_error());
}
echo 'Connected successfully<br/>';

$id=2;
$name="Rahul";
$salary=80000;
$sql = "update emp4 set name=\"$name\", salary=$salary where id=$id";
if(mysqli_query($conn, $sql)){ echo "Record updated successfully";
}else{ echo "Could not update record: ".
mysqli_error($conn);
}
mysqli_close($conn);
?>

PHP MySQL Delete Record

Example
<?php

LAKSHMIPATHY S V 11
 BCA PHP & MYSQL

$host = 'localhost:3306';
$user = '';
$pass = '';
$dbname = 'test';

$conn = mysqli_connect($host, $user, $pass,$dbname);

if(!$conn){ die('Could not connect:
'.mysqli_connect_error());
}
echo 'Connected successfully<br/>';

$id=2;
$sql = "delete from emp4 where id=$id";
if(mysqli_query($conn, $sql)){ echo
"Record deleted successfully"; }else{
echo "Could not deleted record: ".
mysqli_error($conn);
}
mysqli_close($conn);
?>
PHP MySQL Select Query
<?php
$host = 'localhost:3306';
$user = '';
$pass = '';
$dbname = 'test';
$conn = mysqli_connect($host, $user, $pass,$dbname);
if(!$conn){ die('Could not connect:
'.mysqli_connect_error());
}
echo 'Connected successfully<br/>';
LAKSHMIPATHY S V 12
 BCA PHP & MYSQL

$sql = 'SELECT * FROM emp4';

$retval=mysqli_query($conn, $sql);

if(mysqli_num_rows($retval) > 0){ while($row =

mysqli_fetch_assoc($retval)){ echo "EMP ID
:{$row['id']} <br> ".
"EMP NAME : {$row['name']} <br> ".
"EMP SALARY : {$row['salary']} <br> ".
"--------------------------------<br>";
} //end of while
}else{ echo "0
results";
}
mysqli_close($conn);
?>
Output:

Connected successfully
EMP ID :1
EMP NAME : ratan
EMP SALARY : 9000
--------------------------------
EMP ID :2
EMP NAME : karan
EMP SALARY : 40000
--------------------------------
EMP ID :3
EMP NAME : jai
EMP SALARY : 90000
LAKSHMIPATHY S V 13
 BCA PHP & MYSQL

--------------------------------
mysqli_query($conn, $sql): The mysqli_query() function executes/performs
the given query on the database. For SELECT query this function returns the
rows retrieved as an object if the execution is successful. Syntax
mysqli_query($con, query)
con(Mandatory)
1
This is an object representing a connection to MySQL Server.

query(Mandatory)
2
This is a string value representing the query to be executed.

mode(Optional)
This is a integer value representing the result mode. You can
3
pass MYSQLI_USE_RESULT or MYSQLI_STORE_RESULT as values to this
parameter.
mysqli_num_rows($retval): returns number of tuples stored in the object retrieval
or num of rows in the result set if a query.
<?php
$host = 'localhost:3306';
$user = '';
$pass = '';
$dbname = 'test';
$conn = mysqli_connect($host, $user, $pass,$dbname);
if(!$conn){ die('Could not connect:
'.mysqli_connect_error());
}
echo 'Connected successfully<br/>';

$sql = 'SELECT * FROM emp4';

$retval=mysqli_query($conn, $sql);
LAKSHMIPATHY S V 14
 BCA PHP & MYSQL

$num= mysqli_num_rows($retval);
Echo $num; mysqli_close($conn);
?> mysqli_fetch_assoc($retval)
Fetch a result row as an associative array:
Eg.,
<?php
$con = mysqli_connect("localhost","my_user","my_password","my_db"); if
(mysqli_connect_errno()) {
echo "Failed to connect to MySQL: " . mysqli_connect_error(); exit();
}

$sql = "SELECT Lastname, Age FROM Persons ORDER BY Lastname";

$result = mysqli_query($con, $sql);

// Associative array
$row = mysqli_fetch_assoc($result); printf ("%s (%s)\n",
$row["Lastname"], $row["Age"]);

// Free result set

mysqli_free_result($result);

mysqli_close($con);
?>

LAKSHMIPATHY S V 15