CRUD PHP Ajax, Bootstrap e MySQL

CRUD PHP simples com Ajax, Bootstrap e MySQL

Voltar

Pré-requisitos

Necessário XAMPP executando. Primeiramente, execute o banco.sql no Phpmyadmin. Mantenha idêntica a árvore de diretórios do projeto e o nome dos arquivos, conforme abaixo.

Diretório 'ajax'

addRecord.php


<?php
if(!empty($_POST['first_name'])) {
    include("db_connection.php");
    $r = $db->prepare("INSERT INTO users(first_name) VALUES (?)");
    $r->execute(array($_POST['first_name']));
}

db_connection.php


<?php
$db = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'root', '');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

deleteUser.php


<?php
if(!empty($_POST['id'])) {
    include("db_connection.php");
    $r = $db->prepare("DELETE FROM users WHERE id=?");
    $r->execute(array($_POST['id']));
}

readRecords.php


<?php
include("db_connection.php");
$r = $db->query("SELECT * FROM users");
$linhas = $r->fetchAll(PDO::FETCH_ASSOC);
foreach($linhas as $l) {echo $l['id']." ".$l['first_name']." <a onclick='GetUserDetails(".$l['id'].")'>Editar</a> <a onclick='DeleteUser(".$l['id'].")'>Excluir</a><br>";}

readUserDetails.php


<?php
include("db_connection.php");
if(!empty($_POST['id'])) {
    $r = $db->prepare("SELECT * FROM users WHERE id=?");
    $r->execute(array($_POST['id']));
    $response = array();
    $linhas = $r->fetchAll(PDO::FETCH_ASSOC);
    if($r->rowCount()>0) {foreach($linhas as $l) {$response = $l['id'];}}
    else {
        $response['status'] = 200;
        $response['message'] = "Data not found!";
    }
    echo json_encode($response);
} else {
    $response['status'] = 200;
    $response['message'] = "Invalid Request!";
}

updateUserDetails.php


<?php
include("db_connection.php");
if(!empty($_POST)) {
    $r = $db->prepare("UPDATE users SET first_name=? WHERE id=?");
    $r->execute(array($_POST['first_name'],$_POST['id']));
}

Diretório 'js'

script.js


function addRecord() {
    var first_name = $("#first_name").val();
    $.post("ajax/addRecord.php", {first_name: first_name}, function(data,status){$("#addModal").modal("hide"); readRecords(); $("#first_name").val("");});
}

function readRecords() {$.get("ajax/readRecords.php", {}, function(data,status){$(".records_content").html(data);});}

function DeleteUser(id) {$.post("ajax/deleteUser.php", {id:id}, function(data,status){readRecords();});}

function GetUserDetails(id) {
    $("#hidden_user_id").val(id);
    $.post("ajax/readUserDetails.php", {id:id}, function(data,status){var user = JSON.parse(data); $("#upModal").val(user.first_name);});
    $("#upModal").modal("show");
}

function UpdateUserDetails() {
    var first_name = $("#update_first_name").val();
    var id = $("#hidden_user_id").val();
    $.post("ajax/updateUserDetails.php", {id:id,first_name:first_name}, function(data,status) {$("#upModal").modal("hide"); readRecords();});
}

$(document).ready(function(){readRecords();});

jquery-1.11.3.min.js

Este arquivo é pertencente ao jQuery, do qual fará a importação das funcionalidades js. Ele deverá ser baixado juntamente com os demais arquivos, no botão de download acima.

Diretório principal

banco.sql


CREATE DATABASE test CHARSET=utf8;
USE test;
CREATE TABLE users(
    id INTEGER PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(100) NOT NULL
)CHARSET=utf8;
INSERT INTO users(first_name) VALUES ("ubsocial1"),("ubsocial2");

index.php


<script type="text/javascript" src="js/jquery-1.11.3.min.js"></script>
<script type="text/javascript" src="js/script.js"></script>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" integrity="sha384-BVYiiSIFeK1dGmJRAkycuHAHRg32OmUcww7on3RYdg4Va+PmSTsz/K68vbdEjh4u" crossorigin="anonymous">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap-theme.min.css" integrity="sha384-rHyoN1iRsVXV4nD0JutlnGaslCJuC7uwjduW9SVrLvRYooPp2bWYgmgJQIXwl/Sp" crossorigin="anonymous">
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js" integrity="sha384-Tc5IQib027qvyjSMfHjOMaLkfuWVxZxUPnCJA7l2mCWNIpG9mGCD8wGNIcPD7Txa" crossorigin="anonymous"></script>

<h1>CRUD PHP Mysql e Ajax</h1>
<a data-toggle="modal" data-target="#addModal">Adicionar</a>
<div class="records_content"></div>

<div class="modal fade" id="addModal" role="dialog">
    <div class="modal-dialog" role="document">
        <div class="modal-content">
            <h4>Adicionar</h4>
            <input type="text" id="first_name" placeholder="Nome">
            <a data-dismiss="modal">Cancelar</a>
            <a onclick="addRecord()">Adicionar</a>
        </div>
    </div>
</div>

<div class="modal fade" id="upModal" role="dialog">
    <div class="modal-dialog" role="document">
        <div class="modal-content">
            <h4>Atualizar</h4>
            <input type="text" id="update_first_name" placeholder="Nome">
            <a data-dismiss="modal">Cancelar</a>
            <a onclick="UpdateUserDetails()">Atualizar</a>
            <input type="hidden" id="hidden_user_id">
        </div>
    </div>
</div>