CRUD FETCH FULL CON SELECT OPTION JS PHP MYSQL API JSON

 index.php

<!DOCTYPE html>

<html lang="en">

<head>

  <meta charset="UTF-8">

  <meta name="viewport" content="width=device-width, initial-scale=1.0">

  <meta http-equiv="X-UA-Compatible" content="ie=edge">

  <title>PHP & JavaScript Fetch CRUD</title>

  <link rel="stylesheet" href="css/style.css">

</head>

<body>

  <div id="main">

    <div id="header">

      <h2>PHP & JavaScript Fetch CRUD</h2>

      <div id="search-bar">

        <label>Search :</label>

        <input type="text" id="search" onkeyup="load_search()" autocomplete="off">

      </div>

    </div>

    

    <div id="table-data">

      <h3>All Records</h3>

      <button class="add_new" onclick="addNewModal()">Add New</button>

      <table border="1" width="100%" cellspacing="0" cellpadding="10px">

        <thead>

          <tr>

            <th width="60px">Id</th>

            <th>Name</th>

            <th>Class</th>

            <th>City</th>

            <th width="90px">Edit</th>

            <th width="90px">Delete</th>

          </tr>

        </thead>

        <tbody id="tbody"></tbody>

      </table>

    </div>


    <div id="error-message"></div>

    <div id="success-message"></div>

  </div>



  <!-- modal for show add new -->

  <div id="addModal">

    <div id="modal-form" >

      <h2>Add New Form</h2>

      <form method="POST" id="addModal-form">

        <table cellpadding="10px" width="100%" id="add-form">

          <tr>

            <td width='90px'>First Name</td>

            <td><input type='text' id='fname'></td>

          </tr>

          <tr>

            <td width='90px'>Last Name</td>

            <td><input type='text' id='lname' ></td>

          </tr>

          <tr>

            <td width='90px'>Class</td>

            <td>

              <select id="classlist"></select>

            </td>

          </tr>

          <tr>

            <td width='90px'>City</td>

            <td><input type='text' id='city' ></td>

          </tr>

          <tr>

            <td></td>

            <td><button type="button" onclick='submit_data()' id='new-submit'>Save</button></td>

          </tr>

        </table>

      </form>

      <div id="close-btn" onclick="hide_modal()">X</div>

    </div>

  </div>


  <!-- modal for show edit -->

  <div id="modal">

    <div id="modal-form">

      <h2>Edit Form</h2>

      <form method="POST">

        <table cellpadding="10px" width="100%" id="edit-form">

          <tr>

            <td width='90px'>First Name</td>

            <td><input type='text' id='edit-fname' autocomplete="off">

                <input type='text' id='edit-id' hidden>

            </td>

          </tr>

          <tr>

            <td width='90px'>Last Name</td>

            <td><input type='text' id='edit-lname' autocomplete="off"></td>

          </tr>

          <tr>

            <td width='90px'>Class</td>

            <td>

              <select id='edit-class'></select>

            </td>

          </tr>

          <tr>

            <td width='90px'>City</td>

            <td><input type='text' id='edit-city' autocomplete="off"></td>

          </tr>

          <tr>

            <td></td>

            <td><button type="button" onclick='modify_data()' id='edit-submit'>Save</button></td>

          </tr>

        </table>

      </form>

      <div id="close-btn" onclick="hide_modal()">X</div>

    </div>

  </div>

  

<script type="text/javascript" src="js/fetch.js"></script>

</body>

</html>

///////////////////
config.php

<?php

$conn = mysqli_connect("localhost","root","","test") or die("Connection Failed.");
?>
//////////////////////////////////////

fetch-class-field.php
<?php

include 'config.php';

$sql = "SELECT * FROM class";

$result = mysqli_query($conn, $sql) or die("SQL Failed");
$output = [];

if(mysqli_num_rows($result) > 0){
  while($row = mysqli_fetch_assoc($result)){
    $output[] = $row;
  }
}else{
    return false;
}

mysqli_close($conn);

echo json_encode($output);

?>
/////////////////////////////////////////
fetch-delete.php

<?php

include 'config.php';

$sid = $_GET['delId'];

$sql = "DELETE FROM students WHERE id={$sid}";

if(mysqli_query($conn,$sql)){
echo json_encode(array('delete' => 'success'));
}else{
echo json_encode(array('delete' => 'failed'));
}

?>

//////////
fetch-edit.php
<?php

include 'config.php';

$sid = $_GET['editId'];

$sql = "SELECT * FROM students WHERE id = {$sid}";

$result = mysqli_query($conn, $sql) or die("SQL Failed");
$output = [];

if(mysqli_num_rows($result) > 0){
  while($row = mysqli_fetch_assoc($result)){
    $output['response'][] = $row;
  }
}

$sql1 = "SELECT * FROM class";

$result1 = mysqli_query($conn, $sql1) or die("SQL Failed");

if(mysqli_num_rows($result1) > 0){
  while($row1 = mysqli_fetch_assoc($result1)){
    $output['class'][] = $row1;
  }
}

mysqli_close($conn);

echo json_encode($output);

?>

/////////////

fetch-insert.php
<?php

include 'config.php';

$input = file_get_contents('php://input');
$decode = json_decode($input, true);

$firstName = $decode["fname"];
$lastName = $decode["lname"];
$class = $decode["class"];
$city = $decode["city"];

$sql = "INSERT INTO students(first_name, last_name,class, city) VALUES ('{$firstName}',
'{$lastName}','{$class}','{$city}')";

if(mysqli_query($conn,$sql)){
echo json_encode(array('insert' => 'success'));
}else{
echo json_encode(array('insert' => 'failed'));
}

?>

/////////////

fetch-search.php

<?php

include 'config.php';

$search = $_GET['search'];

$sql = "SELECT students.id,students.first_name,students.last_name,
students.city,class.class_name 
FROM students LEFT JOIN class ON class.cid = students.class 
WHERE concat(first_name,last_name) LIKE '%{$search}%'";

$result = mysqli_query($conn, $sql) or die("SQL Failed");
$output = [];

if(mysqli_num_rows($result) > 0){
  while($row = mysqli_fetch_assoc($result)){
    $output[] = $row;
  }
}else{
    $output['empty'] = ['empty'];
}

mysqli_close($conn);

echo json_encode($output);

?>

//////////////////////////////

fetch-update.php

<?php

include 'config.php';

$input = file_get_contents('php://input');
$decode = json_decode($input, true);

$student_id = $decode["s_id"];
$firstName = $decode["fname"];
$lastName = $decode["lname"];
$class = $decode["class"];
$city = $decode["city"];

$sql = "UPDATE students SET first_name = '{$firstName}', 
last_name = '{$lastName}',class = '{$class}', city= '{$city}' WHERE id = {$student_id}";

if(mysqli_query($conn,$sql)){
echo json_encode(array('update' => 'success'));
}else{
echo json_encode(array('update' => 'failed'));
}

?>

///////////

load-table.php

<?php

include 'config.php';

$sql = "SELECT students.id,students.first_name,students.last_name,students.city,class.class_name FROM students LEFT JOIN class ON class.cid = students.class";

$result = mysqli_query($conn, $sql) or die("SQL Failed");
$output = [];

if(mysqli_num_rows($result) > 0){
  while($row = mysqli_fetch_assoc($result)){
    $output[] = $row;
  }
}else{
    $output['empty'] = ['empty'];
}

mysqli_close($conn);

echo json_encode($output);

?>

//////////

fetch.js

// function for load student record in table on page load
function loadTable(){ 
fetch('php/load-table.php')
.then((response) => response.json())
.then((data)=>{
var tbody = document.getElementById('tbody');
if(data['empty']){
tbody.innerHTML = '<tr><td colspan="6" align="center"><h3>No Record Found.</h3></td></tr>'
}else{
var tr = '';
for(var i in data){
tr += `<tr>
            <td align="center">${data[i].id}</td>
            <td>${data[i].first_name} ${data[i].last_name}</td>
            <td>${data[i].class_name}</td>
            <td>${data[i].city}</td>
            <td align="center"><button class="edit-btn" onclick="editRecord(${data[i].id})">Edit</button></td>
            <td align="center"><button class="delete-btn" onclick="deleteRecord(${data[i].id})">Delete</button></td>
          </tr>`;
}
tbody.innerHTML = tr;
}
})
.catch((error) => {
show_message('error',"Can't Fetch Data");
});
}

loadTable(); // Load Student Record on Page opening

// Open Add new student Modal Box 
function addNewModal(){
var addModal = document.getElementById("addModal");
addModal.style.display = 'block';

fetch('php/fetch-class-field.php')
.then((response) => response.json())
.then((data)=>{
var select = document.getElementById('classlist');
var option = '<option value="0" disabled selected>Select Class</option>';
for(var i=0; i < data.length; i++){
option += `<option value="${data[i].cid}">${data[i].class_name}</option>`;
}
select.innerHTML = option;
})
.catch((error) => {
show_message('error',"Can't Fetch Class List");
});
}

// Hide Modal Box / Popup Box
function hide_modal(){
var addModal = document.getElementById("addModal");
addModal.style.display = 'none';

var editModal = document.getElementById("modal");
editModal.style.display = 'none';
}

// Add Student Record
function submit_data(){
var fname = document.getElementById('fname').value;
var lname = document.getElementById('lname').value;
var sClass = document.getElementById('classlist').value;
var city = document.getElementById('city').value;

if(fname === '' || lname === '' || sClass === '0' || city === ''){
alert('Please Fill All The Fields');
return false;
}else{
var formdata = {
'fname' : fname,
'lname' : lname,
'class' : sClass,
'city' : city
}

jsondata = JSON.stringify(formdata);

fetch('php/fetch-insert.php',{
method : 'POST',
body : jsondata,
headers: {
'Content-type' : 'application/json',
}
})
.then((response) => response.json())
.then((result)=>{
if(result.insert == 'success'){
show_message('success','Data Inserted Successfully.');
loadTable();
hide_modal();
document.getElementById('addModal-form').reset();
}else{
show_message('error',"Data Can't Inserted.");
hide_modal();
}
})
.catch((error) => {
show_message('error',"Data not Inserted.");
});
}
}

// Open Update Modal Box and show Student record in it.
function editRecord(id){
var editModal = document.getElementById("modal");
editModal.style.display = 'block';

fetch('php/fetch-edit.php?editId=' + id)
.then((response) => response.json())
.then((data)=>{
var option = '';
for(var i in data['response']){
document.getElementById('edit-id').value = data['response'][i].id;
document.getElementById('edit-fname').value = data['response'][i].first_name;
document.getElementById('edit-lname').value = data['response'][i].last_name;
document.getElementById('edit-city').value = data['response'][i].city;

var selected = '';
for(var j in data['class']){
if(data['class'][j].cid === data['response'][i].id){
selected = 'selected';
}else{
selected = '';
}
option += `<option ${selected} value="${data['class'][j].cid}">${data['class'][j].class_name}</option>`;
}

document.getElementById('edit-class').innerHTML = option;
}
})
.catch((error) => {
show_message('error',"Can't Fetch Data");
});
}

// Update student record
function modify_data(){
var id = document.getElementById('edit-id').value;
var fname = document.getElementById('edit-fname').value;
var lname = document.getElementById('edit-lname').value;
var sClass = document.getElementById('edit-class').value;
var city = document.getElementById('edit-city').value;

if(fname === '' || lname === '' || sClass === '0' || city === ''){
alert('Please Fill All The Fields');
return false;
}else{
var formdata = {
's_id' : id,
'fname' : fname,
'lname' : lname,
'class' : sClass,
'city' : city
}

jsondata = JSON.stringify(formdata);

fetch('php/fetch-update.php',{
method : 'PUT',
body : jsondata,
headers: {
'Content-type' : 'application/json',
}
})
.then((response) => response.json())
.then((result)=>{
if(result.update == 'success'){
show_message('success','Data Updated Successfully.');
loadTable();
hide_modal();
}else{
show_message('error',"Data Can't Updated.");
hide_modal();
}
})
.catch((error) => {
show_message('error',"Data Can't Updated : Server Problem.");
});
}
}

// Delete student record
function deleteRecord(id){
if(confirm("Are you sure want to Delete this record ?")){
fetch('php/fetch-delete.php?delId=' + id,{
method : 'DELETE'
})
.then((response) => response.json())
.then((result)=>{
if(result.delete == 'success'){
show_message('success','Deleted Successfully.');
loadTable();
}else{
show_message('error',"Can't Delete Data.");
}
})
.catch((error) => {
show_message('error',"Data not Deleted.");
});
}
}

// Search student record
function load_search(){
var search = document.getElementById('search').value;
if(search === ''){
loadTable();
return false;
}else{
fetch('php/fetch-search.php?search=' + search)
.then((response) => response.json())
.then((data)=>{
var tbody = document.getElementById('tbody');
if(data['empty']){
tbody.innerHTML = '<tr><td colspan="6" align="center"><h3>No Record Found.</h3></td></tr>'
}else{
var tr = '';
for(var i in data){
tr += `<tr>
            <td align="center">${data[i].id}</td>
            <td>${data[i].first_name} ${data[i].last_name}</td>
            <td>${data[i].class_name}</td>
            <td>${data[i].city}</td>
            <td align="center"><button class="edit-btn" onclick="editRecord(${data[i].id})">Edit</button></td>
            <td align="center"><button class="delete-btn" onclick="deleteRecord(${data[i].id})">Delete</button></td>
          </tr>`;
}
tbody.innerHTML = tr;
}
})
.catch((error) => {
show_message('error',"Can't Fetch Data");
});
}
}

//show error / success message
function show_message(type,text){
if(type=='error'){
var message_box = document.getElementById('error-message');
}else{
var message_box = document.getElementById('success-message');
}
message_box.innerHTML = text;
message_box.style.display = "block";
setTimeout(function(){
message_box.style.display = "none";
},3000);
}

////////////

style.css

body{
  font-family: arial;
  background: #b2bec3;
  padding:0;
  margin: 0;
}
#main{
  width: 800px;
  margin: 0 auto;
  background: white;
  font-size: 19px;
  position: relative;
}
#header{
  background: #f7d794;
  overflow: auto;
  padding: 5px 0;
}
#header h2{
  float: left;
  margin: 15px;
}
h3{
  margin: 0;
  display: inline-block;
}
.add_new{
  color: #fff;
  background-color: #222;
  font-size: 15px;
  font-weight: 600;
  text-transform: uppercase;
  border: none;
  padding: 7px 10px;
  margin: 0 0 10px;
  vertical-align: top;
  display: inline-block;
  cursor: pointer;
  float: right;
  transition: all 0.3s ease 0s;
}
.add_new:hover,
.add_new:focus{
  box-shadow: 0 0 5px rgba(0,0,0,0.5);
}
#search-bar{
  padding: 10px 20px 0;
  float: right;
}
#search-bar label{
  font-size: 16px;
  font-weight: bold;
  display: block;
}
#search-bar input{
  width: 250px;
  height: 25px;
  font-size: 18px;
  letter-spacing: 0.8px;
  padding: 3px 10px;
  border-radius: 4px;
  border: 1px solid #000;
}
#search-bar input:focus{
  outline: 0;
}

#table-form input[type="text"]{
  width: 180px;
  height: 25px;
  font-size: 18px;
  padding: 3px 10px;
  border-radius: 4px;
  border: 1px solid green;
}
#save-button{
  background:#2c3e50;
  color: #fff;
  border:0;
  padding: 8px 30px;
  margin-left: 7px;
  border-radius: 3px;
  cursor: pointer;
}
#table-data{
  padding: 15px;
  min-height: 500px;
  clear: both;
}
#table-data th{
  background: #74b9ff;
}
#table-data tr:nth-child(odd){
  background: #ecf0f1;
}

#table-form{
  background: #55efc4;
  padding: 12px 10px;
}
#menu{
  background: #55efc4;
  padding: 12px 10px;
}

#main tr ul{
  padding: 0;
  margin: 0;
  list-style: none;
}

#main tr ul li{
  display: inline-block; 
  margin: 0 5px 0 0; 
}

#main tr ul li a{
  color: #fff;
  background-color: #000;
  font-size: 15px;
  font-weight: 600;
  text-transform: uppercase;
  text-decoration: none;
  padding: 7px 15px;
  transition: all 0.3s ease;
}

#main tr ul li.active a,
#main tr ul li a:hover{
  color: #fff;
}

#success-message{
  background: #DEF1D8;
  color: green;
  padding: 10px;
  margin: 10px;
  display: none;
  position: absolute;
  right: 150px;
  top: 70px;
  z-index: 100;
}
#error-message{
  background: #EFDCDD;
  color: red;
  padding: 10px;
  margin: 10px;
  display: none;
  position:absolute;
  right: 150px;
  top: 70px;
  z-index: 100;
}
.delete-btn{
  background:#e74c3c;
  color: #fff;
  border:0;
  padding: 4px 10px;
  border-radius: 3px;
  cursor: pointer;
}
.edit-btn{
  background: #27ae60;
  color: white;
  border: 0;
  padding: 4px 10px;
  border-radius: 3px;
  cursor: pointer;
}
#modal,
#addModal{
  background: rgba(0,0,0,0.7);
  position: fixed;
  left: 0;
  top:0;
  width: 100%;
  height: 100%;
  z-index: 100;
  display: none;
}
#modal-form{
  background: #fff;
  width: 30%;
  position: relative;
  top: 20%;
  left: calc(50% - 15%);
  padding: 15px;
  border-radius: 4px;
}
#modal-form h2{
  margin: 0 0 15px;
  padding-bottom: 10px;
  border-bottom: 1px solid #000;
}
#modal-form input[type = "text"]{
  width: 100%;
}
#close-btn{
  background: red;
  color: white;
  width: 30px;
  height: 30px;
  line-height: 30px;
  text-align: center;
  border-radius: 50%;
  position: absolute;
  top: -15px;
  right: -15px;
  cursor: pointer;
}

//////////////////////////

DATABASE

--
-- Database: `test`
--

-- --------------------------------------------------------

--
-- Table structure for table `class`
--

CREATE TABLE `class` (
  `cid` int(11) NOT NULL,
  `class_name` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `class`
--

INSERT INTO `class` (`cid`, `class_name`) VALUES
(1, 'B.COM'),
(2, 'B.Sc');

-- --------------------------------------------------------

--
-- Table structure for table `students`
--

CREATE TABLE `students` (
  `id` int(11) NOT NULL,
  `first_name` varchar(30) NOT NULL,
  `last_name` varchar(30) NOT NULL,
  `class` int(10) NOT NULL,
  `city` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `students`
--

INSERT INTO `students` (`id`, `first_name`, `last_name`, `class`, `city`) VALUES
(1, 'Yahoo', 'Baba', 1, 'Mumbai'),
(2, 'Salim', 'Khan', 2, 'Delhi'),
(3, 'Shahid', 'Kapoor', 1, 'Indore'),
(4, 'Anil', 'Kapoor', 2, 'Chandigarh'),
(5, 'Katrina', 'kaif', 1, 'Mumbai'),
(6, 'Kishor', 'Kumar', 2, 'Chennai'),
(7, 'Kumar', 'Sanu', 1, 'Mumbai');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `class`
--
ALTER TABLE `class`
  ADD PRIMARY KEY (`cid`);

--
-- Indexes for table `students`
--
ALTER TABLE `students`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `class`
--
ALTER TABLE `class`
  MODIFY `cid` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

--
-- AUTO_INCREMENT for table `students`
--
ALTER TABLE `students`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=16;




Comentarios

Publicar un comentario

Entradas populares de este blog

Como proteger una api json con php al pasar el parametro POST[id] de los ataques sql injection y xss

 Como proteger una api json con php al pasar el parametro POST_[id] de los ataques sql injection y xss ChatGPT Para proteger una API JSON en PHP contra ataques de inyección SQL y XSS al pasar parámetros como POST[id], puedes seguir algunas prácticas de seguridad: Validación de entrada: Antes de usar cualquier dato recibido del usuario, valida y filtra la entrada para asegurarte de que cumple con los requisitos esperados. Puedes utilizar funciones como filter_input() o filter_var() para validar y sanitizar los datos. Preparación de consultas SQL: Si estás realizando consultas SQL directamente en tu base de datos, utiliza sentencias preparadas (prepared statements) y parámetros vinculados (bound parameters) en lugar de construir consultas SQL concatenando cadenas de texto. Las sentencias preparadas ayudan a prevenir la inyección SQL al separar los datos de las instrucciones SQL. Escape de salida: Cuando devuelvas datos a través de la API JSON, asegúrate de escapar apropiadamente los ...
Leer más

Copia de Seguridad de Archivo MS Access (.mdb) a Pendrive con VB6

  Copia de Seguridad de Archivo MS Access (.mdb) a Pendrive con VB6   Para crear una copia de seguridad de un archivo MS Access (.mdb) a un pendrive cuando se hace clic en un botón en un formulario VB6, puedes usar el siguiente código: vb Private Sub cmdBackup_Click ()     On Error GoTo ErrorHandler         Dim ArchivoOrigen As String     Dim ArchivoDestino As String     Dim UnidadPendrive As String     Dim FSO As Object     Dim Drv As Object     Dim Existe As Boolean         ' Ruta del archivo de origen (base de datos Access)     ArchivoOrigen = "C:\Ruta\A\Tu\BaseDeDatos.mdb" ' Cambiar por tu ruta real         ' Verificar si el archivo de origen existe     If Dir(ArchivoOrigen) = "" Then         Ms...
Leer más

NUMEROS SERIE DE DISCO RIGIDO Y PENDRIVER USB CON PHP

 Para listar todos los dispositivos USB conectados y obtener su número de serie en PHP usando wmic, necesitas ajustar el comando wmic. El comando que tienes está intentando filtrar las unidades basadas en una letra de unidad, lo cual no funcionará para todos los dispositivos USB. En su lugar, puedes usar el siguiente enfoque:     Usa wmic diskdrive para obtener todos los dispositivos conectados, y luego puedes filtrarlos por MediaType o DeviceID para identificar cuáles son dispositivos USB. Aquí tienes el código corregido: php // Ejecutar el comando para listar todas las unidades de disco, incluyendo USB $output2 = shell_exec('wmic diskdrive where "MediaType=\'Removable Media\'" get DeviceID,SerialNumber,Model'); // Ver la salida completa (para depuración) echo "<pre>$output2</pre>"; Explicación:     wmic diskdrive where "MediaType='Removable Media'" get DeviceID,SerialNumber,Model: Este comando obtiene todas las unidade...
Leer más