I am following this CRUD tutorial with PDO, Ajax and Modal Bootstrap for implementation purposes in a project. My database has 2 tables (users) and (tipo_ps).
The users table has the following structure:
CREATE TABLE `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`tipo_fk` int(11) NOT NULL,
`first_name` varchar(150) NOT NULL,
`last_name` varchar(150) NOT NULL,
`image` varchar(150) 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=5 DEFAULT CHARSET=utf8Below, users table example with register:
+-------+-------------+--------------+-------------+-------------+
| id | tipo_fk | first_name | last_name | image |
+-------+-------------+--------------+-------------+--------------
| 1 | student | John | Jackson | 001.jpg |
| 2 | Professinal | Stephany | Roberts | 002.jpg |
| 3 | Professinal | Bruce | Wayne | 003.jpg |
| 4 | Professinal | Jimmy | Forbes | 004.jpg |
| 5 | student | Lisa | Fooman | 005.jpg |
+-------+-------------+--------------+-------------+-------------+And the tipo_ps table 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'Below, tipo_ps table with example register:
+---------+--------------+
| tipo_id | tipo |
+---------+--------------+
| 1 | Student |
| 2 | Professional |
+---------+--------------+As you can see, in users table i created a foreing Key that call tipo_fk whose relationship was created correctly and the insertion into users table is ok when i use phpMyAdmin for it.
After i created the column tipo_fk in users table, i just added tipo_fk inside the PHP PDO code scripts that are part of functionalities that make crud works, but even then, the dataTable does not display any data in index.php page and the PDO does not insert into data in users table. When the form is filled and then the user click on submit button, an Ajax alert show blank response as image below:
After this submit action, the browsers, through the F12 button in Network menu, inform the response of the action occurred as image below:
Answer of browsers after click submit button form
Below, i post the PHP PDO insert script (insert.php) where show the code line (':tipo_fk' => $_POST['tipo_fk'],) that i inserted in PDO format:
<?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(
/*here*/':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 i can't insert data through the form shown above, i can insert data normally by phpMyAdmin. Also, even though inserting data via phpMyAdmin normally, the dataTable in the index.php file, does not load the inserted data in phpMyAdmin, displaying an empty table as image below:
Empty table even insert data by phpMyAdmin
Below is the Bootstrap Modal code that i'm using to display form. That code is in index.php page:
<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">×</button>
<h4 class="modal-title">Add User</h4>
</div>
<div class="modal-body">
<label for="tipo">Select</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) which 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);
?>And finally, Ajax scripts that are in index.php page:
Ajax - url: 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 - url: 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 - url: 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 by dataTable in index.php. The point is, before i created the column tipo_fk in the users table, the CRUD was working perfectly.
The problem occurred just when I added the tipo_fk column as the foreing key in the users table and in the PHP PDO scripts.
</div>