Add a discord user to your database (Login with Discord OAuth) in PHP
This is a follow up to how to make a login with discord button in PHP tutorial I made not too long ago.
Here, the objective is simple. Take that authenticated user and add them to our database. If they are already in there, just log them in.
Finally, we will want to visualize our database, and output all of our joined users.
Video tutorial:
Setting up your database
You need a way to access a mysql database. In my tutorial I have a locally hosted php server on WAMP, but you can use an external database, a locally hosted mysql server, WSL, etc.
In conjunction with WAMP, I'm using phpmyadmin to access my database. Again, you can use whatever you want to access your database, phpmyadmin is just very common for beginners using php+mysql.
Step 1: Create the database
On the left side of the screen, hit new, and then navigate to the "Database Name" field.
Enter in your database name. I will be naming it "new_application"
Step 2: Create a table
In your new database, on the first tab, create your table, enter your name, and select 4 for your number of columns
Step 3: Create your columns
In your four columns, create an
id (INT, autoincrement, primary key);
discord_id (varchar(255)),
discord_avatar (varchar(255)),
and created_at (timestamp(CURRENT_TIME).
Your first column is your primary key. It will be automatically incremented by mySQL each time you create a new column. We don't have to worry about setting it.
You will supply the discord id given by our OAuth scheme, as seen in the previous tutorial.
Likewise, discord_avatar will be entered in.
Finally, created_at is just a timestamp marker of when we first inserted the user. Good for knowing when your users joined.
Connecting to your database in PHP
Now, head over to your project folder and create db.php
Add the this basic connection code to connect to your database.
Add connection code (PDO)
<?php
$host = "localhost";
$user = "";
$pass = "";
$db='new_application';
try {
$pdo = new PDO("mysql:host=$host;dbname=$db", $user, $pass);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "Connected successfully";
}
catch(PDOException $e){
echo "Connection failed: " . $e->getMessage();
}
We need a username and a password to connect to our database
Let's create one in phpmyadmin.
Go to your database, and hit the privilege's tab
At the bottom of that screen, hit Add user account
Create a username, hostname (% or any), password, and select grant all priviledges
Hit Go, and add those credentials to your db.php file.
<?php
$host = "localhost";
$user = "devuser";
$pass = "mysqlpassword";
$db='new_application';
try {
$pdo = new PDO("mysql:host=$host;dbname=$db", $user, $pass);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "Connected successfully";
}
catch(PDOException $e){
echo "Connection failed: " . $e->getMessage();
}
Head over to your project, and see if your database connection is successful:
http://localhost/login-with-discord/src/db.php
Creating functions to add a user to our database
In your db.php file, test a basic mySQL insert statement:
<?php
$host = "localhost";
$user = "devuser";
$pass = "mysqlpassword";
$db='new_application';
try {
$pdo = new PDO("mysql:host=$host;dbname=$db", $user, $pass);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "Connected successfully";
}
catch(PDOException $e){
echo "Connection failed: " . $e->getMessage();
}
function addUserToDatabase($pdo,$discord_id,$discord_avatar){
$sql = "INSERT INTO users (discord_id,discord_avatar) VALUES (:discord_id,:discord_avatar)";
try {
$stmt = $pdo->prepare($sql);
$stmt->execute([
'discord_id'=>$discord_id,
'discord_avatar'=>$discord_avatar,
]);
echo 'inserted successfully';
} catch (Exception $e) {
echo $e;
}
}
addUserToDatabase($pdo,'test','test');Execute db.php, and check your database to see if it worked:
Great, now let's retrieve a user by a discord id (to see if one exists)
Add this function to the same file:
function getUserFromDatabase($pdo,$discord_id){
$sql = "SELECT * FROM users WHERE discord_id=:discord_id";
try {
$stmt = $pdo->prepare($sql);
$stmt->execute([
'discord_id'=>$discord_id,
]);
$data = $stmt->fetch(PDO::FETCH_ASSOC);
return $data;
} catch (Exception $e) {
echo $e;
}
}
$discord_id = 'test';
$user_data = getUserFromDatabase($pdo,$discord_id);
if($user_data){
echo 'user exists:';
print_r($user_data);
}else{
echo 'no user exists';
}We just added discord_id test, and so we will retrieve it to.
Finally, let's make a function that gets all discord users from our database:
function getAllUsersFromDatabase($pdo){
$sql = "SELECT * FROM users";
try {
$stmt = $pdo->prepare($sql);
$stmt->execute();
$data = $stmt->fetchAll(PDO::FETCH_ASSOC);
return $data;
} catch (Exception $e) {
echo $e;
}
}Let's output it on our users.php page
<!doctype html>
<html>
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<link href="../dist/output.css" rel="stylesheet">
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.1.1/css/all.min.css" integrity="sha512-KfkfwYDsLkIlwQp6LFnl8zNdLGxu9YAA1QvwINks4PhcElQSvqcyVLLD9aMhXd13uQjoXtEKNosOWaZqXgel0g==" crossorigin="anonymous" referrerpolicy="no-referrer" />
</head>
<body>
<div class="flex items-center justify-center h-screen bg-discord-gray text-white flex-col">
<ul class="w-96 text-sm font-medium text-gray-900 bg-white rounded-lg border border-gray-200 dark:bg-gray-700 dark:border-gray-600 dark:text-white mt-6">
<h3 class="text-xl font-bold ml-3 text-gray-300 uppercase py-2">Users:</h3>
</ul>
</div>
</body>
</html>Now, add your php to the header to include our database functions file, as well as our get all users function.
Loop through it to add to html markup, and then print it in the html:
<?php
include('db.php');
$all_users = getAllUsersFromDatabase($pdo);
$usersMarkup='';
foreach ($all_users as $key => $userData) {
$usersMarkup.='<li class="py-2 px-4 w-full rounded-t-lg border-b border-gray-200 dark:border-gray-600">'.$userData['discord_id'].'</li>';
}
?>
<!doctype html>
<html>
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<link href="../dist/output.css" rel="stylesheet">
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.1.1/css/all.min.css" integrity="sha512-KfkfwYDsLkIlwQp6LFnl8zNdLGxu9YAA1QvwINks4PhcElQSvqcyVLLD9aMhXd13uQjoXtEKNosOWaZqXgel0g==" crossorigin="anonymous" referrerpolicy="no-referrer" />
</head>
<body>
<div class="flex items-center justify-center h-screen bg-discord-gray text-white flex-col">
<ul class="w-96 text-sm font-medium text-gray-900 bg-white rounded-lg border border-gray-200 dark:bg-gray-700 dark:border-gray-600 dark:text-white mt-6">
<h3 class="text-xl font-bold ml-3 text-gray-300 uppercase py-2">Users:</h3>
<?php echo $usersMarkup;?>
</ul>
</div>
</body>
</html>We are now outputting users:
Finally, add the user via discord authentication, checking if the user exists in the database, and if not inserting them.
Good news is that we have the two functions for this, so let's head over to process-oauth
//Add this to the top of process-oauth.php
include('db.php');$userData = json_decode($result,true);
$user_data = getUserFromDatabase($pdo,$userData['id']);
if(!$user_data){
addUserToDatabase($pdo,$userData['id'],$userData['avatar']);
}
$_SESSION['logged_in'] = true;
$_SESSION['userData'] = [
'discord_id'=>$userData['id'],
'name'=>$userData['username'],
'avatar'=>$userData['avatar'],
];
header("location: dashboard.php");
exit();Get the image to our user list in our markup of users.php:
$usersMarkup.='
<li class="py-2 px-4 w-full rounded-t-lg border-b border-gray-200 dark:border-gray-600 flex items-center">
<img class="h-12 w-12 rounded-full mr-6" src="https://cdn.discordapp.com/avatars/'.$userData['discord_id'].'/'.$userData['discord_avatar'].'.jpg"/>
'.$userData['discord_id'].'
</li>';