Access MySQL with PHP in XAMPP 2


2020-05-31 PHP Web

This is a continuation of the article “Access MySQL with PHP in XAMPP“. Please check this first.

GOAL

Today’s goal is to access MariaDB(MySQL) and write data to with PHP in XAMPP.

Environment

Windows10
XAMPP 7.4.10

Method

1. Connect database with PDO

I’ll add code to getuser.php created in “Access MySQL with PHP in XAMPP“. Open the getuser.php.

<!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>

2. Add new form to input data

	
	<php?
		//omitted
	?>
	<br/>
	<div>Register new user</div>
	<form action="" method="POST">
		<div><label>name :</label><input type="text" name="name"/></div>
		<div><label>age :</label><input type="text" name="age"/></div>
		<input type="submit" value="ADD" />
	</form>
</body>

3. Prepare SQL statement

Create ‘insert’ statement. You can pass the value directly or use parameters such as :name and :age. Then execute PDO::prepare().

$sql = 'insert into user (id, name, age) values (NULL, :name, :age)';
$sql->bindParam(':name', $_POST['name'], PDO::PARAM_STR);
$sql->bindValue(':age', $_POST['age'], PDO::PARAM_INT);
$sth = $dbh->prepare($sql);

4. Execute SQL

$stmt->execute();

getuser.php

You can get input data with $_POST and display the posted data as below in PHP. Please check “How to use posted data in the same page” for details.

<!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();
	}
	if ($_POST) {
    	echo "<br />ADD data ".$_POST['name']." ".$_POST['age'];
    	$sql = "insert into user (id, name, age) values (NULL, '".$_POST['name']."', ".$_POST['age'].")";
		$sth = $dbh->prepare($sql);
		$sth->execute();
    }
	?>
	<br />
	<div>Register new user</div>
	<form action="" method="POST">
		<div><label>name :</label><input type="text" name="name"/></div>
		<div><label>age :</label><input type="text" name="age"/></div>
		<input type="submit" value="ADD" />
	</form>
</body>
</html>

Result

Access localhost/testdb/getuser.php. And input data.

The message is displayed.

Reload the page and you can see added data.
*The id is automatically set in my environment.