Access MySQL with PHP in XAMPP


2020-05-27 PHP Web

GOAL

Apply to access MariaDB(MySQL) with PHP in XAMPP

Environment

Windows10
XAMPP 7.4.10

At first, install XAMPP (“Installation of XAMPP“) and If you’ll use MariaDB in a production environment, make it secure (“How To Set MySQL Password in XAMPP“). And I create database “photo_sharing” according to “How to Create Database of MariaDB in XAMPP“.

About PDO

PDO, PHP Data Objects, is an extension to access database in PHP.

Reference: PHP Data Objects in PHP manual

pdo_mysql is specified in xampp\php\php.ini. So you can use PDO extension to access MariaDB(MySQL).

extension=mysqli
// omited
extension=pdo_mysql

Method

1. Create new PHP file “getuser.php”.

getuser.php

<!DOCTYPE html>
<html>
<head>
	<meta charset="utf-8">
	<title>Users</title>
</head>
<body>
	<?php
		//input php here
	?>
</body>
</html>

2. Create PDO instance

See the example in PDO_MYSQL DSN. I don’t use any option here. 

<?php
$dsn = 'mysql:host=localhost;dbname=photo_sharing';
$username = 'root';
$password = '********';

$dbh = new PDO($dsn, $username, $password, $options);
?>

You can create the instance of PDO by new PDO(<DSN>, <UserName>, <Password>);

<DSN> is the strings ‘mysql:host=<host of database server>;dbname=<database name>’
<UserName> and <host of database server> can be referred in phpMyAdmin.

<Password> is the password you defined.

3. Check if PDO could connect successfully

Use try and catch to check if the connection is succeed or not.

<?php
$dsn = 'mysql:host=localhost;dbname=photo_sharing';
$username = 'root';
$password = '********';

try {
    $dbh = new PDO($dsn, $username, $password);
    echo "<p>Succeed!</p>";
} catch (PDOException $e) {
    echo "<p>Failed : " . $e->getMessage()."</p>";
    exit();
}
?>

Put the PHP file in the document root directory, that is “xampp\htdocs” by default. I created new directory xampp\htdocs\testdb.

Access the directory where getuser.php exits. In my case, I accessed localhost/testdb/getuser.php.

If there are some problems, the connection failed and error message is displayed.

example of failure1
example of failure2

4. Prepare SQL statement

In this tutorial, I’ll get data from the database. So I use ‘select’ statement. The following is an example of SQL statement to get all the data of database “user” with PDO::prepare() function.

$sql = 'select * from user';
$sth = $dbh->prepare($sql);

5. Execute prepared statement and get result

You can execute with PDO::execute() function and fetches the result set with PDO::fetch() function.

$sth->execute();
$result = $sth->fetchAll(); //get all data at this time

6. Display the result

foreach ($result as $row) {
        echo $row['id']." ";
        echo $row['name']." ";
        echo $row['age']." ";
        echo "<br />";
}

getuser.php

The complete source code of getuser.php is as below.

<!DOCTYPE html>
<html>
<head>
	<meta charset="utf-8">
	<title>Users</title>
</head>
<body>
<?php
$dsn = 'mysql:host=localhost;dbname=photo_sharing';
$username = 'root';
$password = '********';

try {
	$dbh = new PDO($dsn, $username, $password);
	echo "<p>Succeed!</p>";
		
	$sql = 'select * from user';
	$sth = $dbh->prepare($sql);
	$sth->execute();
	$result = $sth->fetchAll();
	
	foreach ($result as $row) {
		echo $row['id']." ";
		echo $row['name']." ";
		echo $row['age']." ";
		echo "<br />";
	}
} catch (PDOException $e) {
	echo  "<p>Failed : " . $e->getMessage()."</p>";
	exit();
}
?>
</body>
</html>

Result

Access localhost/testdb/getuser.php again and you can see the list of users stored in database.