Access MySQL with PHP in XAMPP
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.


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.
