Webslesson CRUD with PDO, Ajax and dataTable - Does not add data in Mysql after column addition in table

Question

Webslesson CRUD with PDO, Ajax and dataTable - Does not add data in Mysql after column addition in table

Navigation

0

Hello! I'm following a CRUD tutorial with PDO, Ajax and Modal Bootstrap for implementation in a project. My database has 2 tables ( users ) and ( type_ps ). The users table has the following structure:

CREATE TABLE 'users' (
 'id' int(11) NOT NULL AUTO_INCREMENT,
 'first_name' varchar(150) CHARACTER SET latin1 NOT NULL,
 'last_name' varchar(150) CHARACTER SET latin1 NOT NULL,
 'image' varchar(150) CHARACTER SET latin1 NOT NULL,
 'tipo_fk' int(11) NOT NULL,
 PRIMARY KEY ('id'),
 KEY 'tipo_fk' ('tipo_fk'),
 CONSTRAINT 'users_ibfk_1' FOREIGN KEY ('tipo_fk') REFERENCES 'tipo_ps' ('tipo_id') ON DELETE NO ACTION ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=94 DEFAULT CHARSET=utf8

And the table type_ps has the following structure:

CREATE TABLE 'tipo_ps' (
 'tipo_id' int(11) NOT NULL AUTO_INCREMENT,
 'tipo' varchar(11) NOT NULL,
 PRIMARY KEY ('tipo_id')
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COMMENT='Tabela tipo produto ou serviços'

As you can see, in the users table I created a foreing Key named f_type whose relationship was created correctly where inserting data into users via phpMyAdmin is ok.

The problem is that after I created the F_type column in the users table, I inserted this column into the PHP PDO scripts that are part of the tutorial based on structure PHP PDO that is there, but still the table does not load and does not add data in Mysql, where when the form is filled and then the submit button is clicked, an Ajax alert appears blank as below:

Afterthisactionofsubmit,thebrowsersthroughtheF12buttonNetworkmenu,informthestructureoftheactionoccurredasshownbelow:

<?php
include('db.php');
include('function.php');

if(isset($_POST["operation"]))
{
 if($_POST["operation"] == "Add")
 {
  $image = '';
  if($_FILES["user_image"]["name"] != '')
  {
   $image = upload_image();
  }
  $statement = $connection->prepare("
   INSERT INTO users (tipo_fk, first_name, last_name, image) 
   VALUES (:tipo_fk, :first_name, :last_name, :image)
  ");
  
  $result = $statement->execute(
   array(
    **********':tipo_fk' => $_POST['tipo_fk'],**********
    ':first_name' => $_POST['first_name'],
    ':last_name' => $_POST['last_name'],
    ':image'  => $image
   )
  );
  if(!empty($result))
  {
   echo 'Data Inserted';
  }
 }
 
?>

At the same time that you could not enter the data through the form shown above, you can enter the data normally by phpMyAdmin. Also, even if inserting data via phpMyAdmin normally, the dataTable table in the index.php file, does not load the data thus showing an empty table as below image:

BelowistheModalcodefoundontheindex.phppage:

<button type="button" id="add_button" data-toggle="modal" data-target="#userModal" class="btn btn-info btn-lg">Add</button>

<div id="userModal" class="modal fade">
 <div class="modal-dialog">
  <form method="post" id="user_form" enctype="multipart/form-data">
   <div class="modal-content">
    <div class="modal-header">
     <button type="button" class="close" data-dismiss="modal">&times;</button>
     <h4 class="modal-title">Add User</h4>
    </div>
    <div class="modal-body">
        
     <label for="tipo">Selecione o tipo</label>
<select name="tipo_fk">
                <?php
                include 'db.php';
                    $commandstring = "SELECT tipo FROM tipo_ps";
                    $cmd = $connection->prepare($commandstring);
                    $cmd->execute();
                    $result = $cmd->fetchAll(PDO::FETCH_ASSOC);
                    foreach($result as $row) {
                        if($selid==$row['tipo_id']) {
                            echo '<option value="'.$row['tipo_id'].'">'.$row['tipo'].'</option>';
                        }                     
                    }
                ?>            
            </select>

     <br />
     <label>Enter First Name</label>
     <input type="text" name="first_name" id="first_name" class="form-control" />
     <br />
     <label>Enter Last Name</label>
     <input type="text" name="last_name" id="last_name" class="form-control" />
     <br />
     <label>Select User Image</label>
     <input type="file" name="user_image" id="user_image" />
     <span id="user_uploaded_image"></span>
    </div>
    <div class="modal-footer">
     <input type="hidden" name="user_id" id="user_id" />
     <input type="hidden" name="operation" id="operation" />
     <input type="submit" name="action" id="action" class="btn btn-success" value="Add" />
     <button type="button" class="btn btn-default" data-dismiss="modal">Close</button>
    </div>
   </div>
  </form>
 </div>
</div>

There is also a PHP PDO script called ( fetch.php ) that is responsible for selecting the data based on a SELECT query and shows them in the table:

<?php
include('db.php');
include('function.php');
$query = '';
$output = array();
$query .= "SELECT * FROM users ";
if(isset($_POST["search"]["value"]))
{
 $query .= 'WHERE first_name, tipo_fk LIKE "%'.$_POST["search"]["value"].'%" ';
 $query .= 'OR last_name LIKE "%'.$_POST["search"]["value"].'%" ';
}
if(isset($_POST["order"]))
{
 $query .= 'ORDER BY '.$_POST['order']['0']['column'].' '.$_POST['order']['0']['dir'].' ';
}
else
{
 $query .= 'ORDER BY id DESC ';
}
if($_POST["length"] != -1)
{
 $query .= 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
}
$statement = $connection->prepare($query);
$statement->execute();
$result = $statement->fetchAll();
$data = array();
$filtered_rows = $statement->rowCount();
foreach($result as $row)
{
 $image = '';
 if($row["image"] != '')
 {
  $image = '<img src="upload/'.$row["image"].'" class="img-thumbnail" width="50" height="35" />';
 }
 else
 {
  $image = '';
 }
 $sub_array = array();
 $sub_array[] = $image;
 $sub_array[] = $row['tipo_fk'];
 $sub_array[] = $row['first_name'];
 $sub_array[] = $row['last_name'];
 $sub_array[] = '<button type="button" name="update" id="'.$row["id"].'" class="btn btn-warning btn-xs update">Update</button>';
 $sub_array[] = '<button type="button" name="delete" id="'.$row["id"].'" class="btn btn-danger btn-xs delete">Delete</button>';
 $data[] = $sub_array;
}
$output = array(
 "draw"    => intval($_POST["draw"]),
 "recordsTotal"  =>  $filtered_rows,
 "recordsFiltered" => get_total_all_records(),
 "data"    => $data
);
echo json_encode($output);
?>

Below are the Ajax scripts that are on the index.php page:

Ajax - insert.php

 $(document).on('submit', '#user_form', function(event){
  event.preventDefault();
  var tipo = $('#tipo_fk').val();
  var firstName = $('#first_name').val();
  var lastName = $('#last_name').val();
  var extension = $('#user_image').val().split('.').pop().toLowerCase();
  if(extension != '')
  {
   if(jQuery.inArray(extension, ['gif','png','jpg','jpeg']) == -1)
   {
    alert("Invalid Image File");
    $('#user_image').val('');
    return false;
   }
  } 
  if(tipo != '' && firstName != '' && lastName != '')
  {
   $.ajax({
    url:"insert.php",
    method:'POST',
    data:new FormData(this),
    contentType:false,
    processData:false,
    success:function(data)
    {
     alert(data);
     $('#user_form')[0].reset();
     $('#userModal').modal('hide');
     dataTable.ajax.reload();
    }
   });
  }
  else
  {
   alert("Both Fields are Required");
  }
 });

Ajax - fetch_single.php (update)

 $(document).on('click', '.update', function(){
  var user_id = $(this).attr("id");
  $.ajax({
   url:"fetch_single.php",
   method:"POST",
   data:{user_id:user_id},
   dataType:"json",
   success:function(data)
   {
    $('#userModal').modal('show');
    $('#tipo_fk').val(data.tipo_fk);
    $('#first_name').val(data.first_name);
    $('#last_name').val(data.last_name);
    $('.modal-title').text("Edit User");
    $('#user_id').val(user_id);
    $('#user_uploaded_image').html(data.user_image);
    $('#action').val("Edit");
    $('#operation').val("Edit");
   }
  })
 });

Ajax - delete.php

$(document).on('click', '.delete', function(){
  var user_id = $(this).attr("id");
  if(confirm("Are you sure you want to delete this?"))
  {
   $.ajax({
    url:"delete.php",
    method:"POST",
    data:{user_id:user_id},
    success:function(data)
    {
     alert(data);
     dataTable.ajax.reload();
    }
   });
  }
  else
  {
   return false; 
  }
 });
 
 
});

In this case, I am not able to identify where I may be going wrong and what may be causing the non-insertion of data in Mysql as well as not showing the data of the users table, being that before added to the f_type column in the users table, CRUD was working perfectly. The problem occurred even only when I added the fk_type column as the foreing key in the users table and PHP PDO scripts. Thank you.

php ajax pdo crud

asked by anonymous 20.07.2018 / 04:07

Webslesson CRUD with PDO, Ajax and dataTable - Does not add data in Mysql after column addition in table

0 answers