Hi, I have coded a PHP PDO mysqli Ajax page and am having an issue with the INSERT INTO for the DB. I do not want to do a code dump here and would like to know if any of you have some time to co through my code? I have been though various sites with trying to solve the issue but alas I have had no success. I am sure an experience coder would pick it up in a couple of minutes. Please do let me know.
Post your code here in the forum or on a site like github, and someone with time and a desire to look at it will help. Also state any errors or symptoms you are getting.
1 Like
Just post your INSERT INTO code. Just the 3 lines before and after that one line. So, post 7 lines of code here. Just something so we can see what you are talking about…
Also, welcome to the site!
//my ajax code in order.php
$("#insert").click(function(e) {
if ($("#add-form-data")[0].checkValidity) {
e.preventDefault();
$.ajax({
url: ["orderaction.php"],
type: "POST",
data: $("#add-form-data").serialize() + "&action=insert",
success: function(response) {
console.log();
Swal.fire({
title: 'Order added successfully!',
showConfirmButton: false,
type: 'success',
icon: 'success',
timer: 500,
timerProgressBar: true,
})
$("#addOrderModal").modal("hide");
$("#add-form-data")[0].reset();
ShowAllOrders();
}
});
}
});
``````````````````````````````````````````````````
//orderdb.php
<?php
class Database
{
// private $dsn = "sqlsrv:Server=localhost;Database=test"; // Conect with SQLServer
private $dsn = "mysql:host=localhost;dbname=limardfeng"; // Conect with MySQL
private $username = "root";
private $pass = "";
public $conn;
public function __construct()
{
try {
$this->conn = new PDO($this->dsn, $this->username, $this->pass);
// echo "Succesfully Conected!";
} catch (PDOException $e) {
echo $e->getMessage();
}
}
public function insert($odate, $oclient, $ocode, $oname, $otype, $oamount)
{
try {
$sql = "INSERT INTO tbl_order (`oid`, `odate`, `oclient`, `ocode`, `oname`, `otype`, `oamount`) VALUES (NULL,:amodate,:amoclient,:amoprojectcodes,:amoprojectname,:amotype,:amoamount)";
$stmt = $this->conn->prepare($sql);
$stmt->execute([`amodate` => $odate, `amoclient` => $oclient, `amoprojectcodes` => $ocode, `amoprojectname` => $oname, `amotype` => $otype, `amoamount` => $oamount]);
//success
return true;
echo "Your success message.";
} catch (PDOException $e) {
//error
echo $e->getMessage();
}
}
public function read()
{
$data = array();
$sql = "SELECT * FROM tbl_order order by oid DESC";
$stmt = $this->conn->prepare($sql);
$stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($result as $row) {
$data[] = $row;
}
return $data;
}
public function totalRowCount()
{
$sql = "SELECT count(*) FROM tbl_order";
$result = $this->conn->prepare($sql);
$result->execute();
$number_of_rows = $result->fetchColumn();
return $number_of_rows;
}
}
`````````````````````````````````````````````````````````
//orderaction.php
<?php
require('orderdb.php');
$db = new Database();
if(isset($_POST['action']) && $_POST['action']== "view"){
$output = '';
$data = $db->read();
// print_r($data);
if($db->totalRowCount()>0){
$output .= '<table id="tableOrder" class="content-table table-hover" style="width:100%">
<thead>
<tr>
<th class="text-center">PROJECT CODE</th>
<th>CREATED</th>
<th>CLIENT NAME</th>
<th>PROJECT CODE</th>
<th>PROJECT NAME</th>
<th>ORDER TYPE</th>
<th>ORDER AMOUNT</th>
<th class="text-center">ACTIONS</th>
</tr>
</thead>
<tbody>
';
foreach($data as $row){
$output .= '<tr>
<td class="text-center text-secondary">'.$row['ocode'].'</td>
<td>'.$row['odate'].'</td>
<td>'.$row['oclient'].'</td>
<td>'.$row['ocode'].'</td>
<td>'.$row['oname'].'</td>
<td>'.$row['otype'].'</td>
<td>'.$row['oamount'].'</td>
<td class="text-center">
<a href="#" title="View Details" class="text-success infoBtn" id="'.$row['oid'].'">
<i class="fas fa-info-circle fa-lg"></i></a>
<a href="#" title="Edit" class="text-primary editBtn" data-toggle="modal" data-target="#editProjectModal" id="'.$row['oid'].'">
<i class="fas fa-edit fa-lg"></i></a>
<a href="#" title="Delete" class="text-danger delBtn" id="'.$row['oid'].'">
<i class="fas fa-trash-alt fa-lg"></i></a>
</td></tr>';
}
$output .='</tbody></table>';
echo $output;
}else{
echo '<h3 class="text-center text-secondary mt-5">:( no projects listed in the database )</h3>';
}
}
if (isset($_POST['action']) && $_POST['action'] == "insert") {
$odate = $_POST['amodate'];
$oclient = $_POST['amoclient'];
$ocode = $_POST['amoprojectcodes'];
$oname = $_POST['amoprojectname'];
$otype = $_POST['amotype'];
$oamount = $_POST['amoamount'];
$db->insert($odate, $oclient, $ocode, $oname, $otype, $oamount);
}
?>The most immediate problem are the back-ticks ` in the following line of code -
Back-ticks are used in a MySql sql query context around identifiers - database, table, and column names, and only when the identifier contains characters that are not normally permitted or are reserved keywords, which should be avoided whenever possible. In the array of index/value pairs that are supplied to the ->execute([…]) call, those are associative index names, which are strings, and should be quoted, using either single ' or double " quotes.
This would probably be producing an execute error, but you don’t have any working error handling for the prepare and execute statements. The try/catch you do have around this query doesn’t do anything since you didn’t set the error mode to exceptions when you made the connection.
When you make the database connection, you should -
- Set the character set to match your database tables, so that no character conversion takes place sending/receiving data.
- Set the error mode to exceptions, so that you do use exceptions for all the prepare, execute, and query statements.
- Set emulated prepared queries to false, so that you use real prepared queries.
- Set the default fetch mode to assoc, so that you don’t need to list it in each fetch statement.
Hi, thanks for this. I have allowed for Null values in the DB so it now works. However the code below populates the given dropdown but on insert it does not insert the amoclient but it inserts the amocode instead and the amoprojectname seems to insert the id into the database. Please help. Also how do I use DISTINCT for the amoclient list and have only the amocode associated to the amoclient to be populated into the 2nd dropdown. Thanks again.
//order.php
<select onChange="getProjectCodes(this.value);" name="amoclient" id="amoclient" class="form-control">
<option value="">Select Client</option>
<?php
require_once "dbConn.php";
$result = mysqli_query($db, "SELECT * FROM tbl_project");
while ($row = mysqli_fetch_array($result)) {
?>
<option value="<?php echo $row['pcode']; ?>"><?php echo $row["pclient"]; ?></option>
<?php
}
?>
</select>
//Ajax code to get project codes and names
function getProjectCodes(val) {
$.ajax({
type: "POST",
url: "getprojectcodes.php",
data: 'amoclient=' + val,
success: function(data) {
//console.log(val);
$("#amoprojectcodes").html(data);
}
});
$('#amoprojectcodes').on('change', function() {
var projectcode = this.value;
$.ajax({
url: "getprojectname.php",
type: "POST",
data: {
projectcode: projectcode
},
cache: false,
success: function(result) {
$("#amoprojectname").html(result);
}
});
});
}As far as the insert, you read values from the posted data, but, they might not be in the correct order.
In your insert code, you accept six parms, but, insert seven parms. Also, you use an ordered insert not based on fieldnames. You might have an issue with that. If your ID number is auto-incremented, then you do not insert that table as it is done for you. You might want to just remove the id parts and make it auto-incremented.
Thank you kindly. Much appreciated.