Programming Tutorial #4: Manipulating DB (Signup / Login in PHP)

What Will I Learn?

• You will learn how to properly query a database
• How to create a basic login system
• How to use PHP

Requirements

• WAMP / XAMPP / LAMP
• Notepad++ or PHPStorm
• Previous Tutorial Code

Difficulty

• Basic

Tutorial Contents

So, today we will make the basis of the interaction of the database

Firstly, create a file called DatabaseConnect.php in the scripts folder and start the file and import the previously created configuration:

<?php
$config = include 'config.php';

Next, we will create the function to create a new user:

function newRecord($username,$password,$email,$displayName) {
    $config = include 'config.php';
    $conn = new PDO("mysql:host=$config->host;dbname=$config->database", $config->username,$config->pass);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $stmt = $conn->prepare("INSERT INTO users (username, password, email, display) VALUES (:username, :password, :email, :display)");
    $stmt->bindParam(':username', $username);
    $stmt->bindParam(':password', $password);
    $stmt->bindParam(':email', $email);
    $stmt->bindParam(':display', $displayName);
    $stmt->execute();
}

This code creates a premade query, assigns values to it, and then sends it to the database

function updatepass($user,$pass) {
    $config = include 'config.php';
    $conn = new PDO("mysql:host=$config->host;dbname=$config->database", $config->username,$config->pass);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $stmt = $conn->prepare("UPDATE `users` SET `password`= :password WHERE `username` = :username");
    $stmt->bindValue(':username',$user);
    $stmt->bindValue(':password',$pass);
    $stmt->execute();
}

This allows a user to change their password, please note, these have no validation, we will add that later.

function usernameExists($username) {
    //Checks Whether A Username Exists (Returns 0 or 1 / true or false)
    $config = include 'config.php';
    $conn = new PDO("mysql:host=$config->host;dbname=$config->database", $config->username,$config->pass);
    $stmt = $conn->prepare('select * from users where `username` = :userx');
    $stmt->bindValue(':userx',$username);
    $stmt->execute();
    $isvalidtest = $stmt->fetchAll(PDO::FETCH_ASSOC);
    if (sizeof($isvalidtest)>0) {
        return true;
    } else {
        return false;
    }
}

Checks if a username already exists, so that users can't take an existing account.

function emailExists($email) {
    //Checks Whether An Email Exists (Returns 0 or 1 / true or false)
    $config = include 'config.php';
    $conn = new PDO("mysql:host=$config->host;dbname=$config->database", $config->username,$config->pass);
    $stmt = $conn->prepare('select * from users where `email` = :emailx');
    $stmt->bindValue(':emailx',$email);
    $stmt->execute();
    $isvalidtest = $stmt->fetchAll(PDO::FETCH_ASSOC);
    if (sizeof($isvalidtest)>0) {
        return true;
    } else {
        return false;
    }
}

Same as above, but for emails.

function getrelatedinfo($username) {
    //Please  use isusernameinexistance first, then use this.
    //Returns Array With Details
    $config = include 'config.php';
    $conn = new PDO("mysql:host=$config->host;dbname=$config->database", $config->username,$config->pass);
    $stmt = $conn->prepare('select * from users where `username` = :userx');
    $stmt->bindValue(':userx',$username);
    $stmt->execute();
    return ($stmt->fetchAll(PDO::FETCH_ASSOC));
};

Gets all info related to a certain username

function getrelatedinfoe($email) {
    //Please  use isusernameinexistance first, then use this.
    //Returns Array With Details
    $config = include 'config.php';
    $conn = new PDO("mysql:host=$config->host;dbname=$config->database", $config->username,$config->pass);
    $stmt = $conn->prepare('select * from users where `email` = :emailx');
    $stmt->bindValue(':emailx',$email);
    $stmt->execute();
    return ($stmt->fetchAll(PDO::FETCH_ASSOC));
}

Gets related details to a certain email address.

function splitVar($atestx) {
foreach ($atestx as $row => $g) {
            $pass=$g['password'];
            $ema=$g['email'];
            $usr=$g['username'];
}
return array($pass,$ema,$usr);};

Splits info from getrelatedinfo & getrelatedinfoe

Thanks for reading, next week we will create filters for the users and passwords, and the actual pages.

This lesson's code is on github

Curriculum

• Introduction
• Hello, World!
• Prerequisites
• Database Setup

---

Posted on Utopian.io - Rewarding Open Source Contributors

---