Hi. Can someone help with logic/code to insert an array to SQL using PHP.
Here is my code.
View
<div class="modal-dialog modal-lg bg-transparent" role="document">
<div class="modal-content">
<div class="modal-header">
<h5 class="modal-title"><i class="fas fa-file-alt"></i> Delegate details</h5>
<button type="button" class="close record" data-id="" data-dismiss="modal" aria-label="Close">
<span aria-hidden="true">×</span>
</button>
</div>
<div class="modal-body">
<form id="page-form" action="?" method="POST">
<div class="container">
<!-- <button class="btn btn-primary" data-toggle="modal" data-target="#page-modal">Close</button> -->
<div class="row">
<div class="col">
<fieldset class="">
<!-- <legend>Details</legend> -->
<div class="col-12">
<label for="description">Course:</label>
<select class="form-control" name="courses_attendance_list_id1"
id="courses_attendance_list_id1">
<option value="">None</option>
<% for (var i in this.courses_attendance_list){ %>
<option value="<%= this.courses_attendance_list[i].id %>" <%
if(this.courses_attendance_list[i].id==this.details.courses_attendance_list_id){
%>selected<% } %>>
<%= this.courses_attendance_list[i].course_name %>
</option>
<% } %>
</select>
</div>
<div class="form-group mb-3">
<label for="account_no">Account No:</label>
<input type="text" class="form-control" id="account_no1" name="account_no1"
placeholder=" Account No" value="<%= this.details.account_no||'' %>">
<small class="form-text text-muted">Enter the Account No</small>
</div>
<div class="col-12">
<label for="description">Municipality:</label>
<select class="form-control" name="municipality_id1" id="municipality_id1">
<option value="">None</option>
<% for (var i in this.municipalities){ %>
<option value="<%= this.municipalities[i].id %>" <%
if(this.municipalities[i].id==this.details.municipality_id){ %>
selected
<% } %>><%= this.municipalities[i].municipality_name %>
</option>
<% } %>
</select>
</div>
<div class="form-group mb-3">
<label for="Isbillable">Is billable:</label>
</div>
<div class="ps-3">
<div class="form-check border mb-1 py-1 delegate-item">
<input name="is_billable1" class="form-check-input" type="radio" value="0"
id="is_billable-01" <% if (this.details.is_billable=='0' ){ %>checked<% }
%>>
<label class="form-check-label" for="is_billable-0" role="button">
No
</label>
</div>
<div class="form-check border mb-1 py-1 delegate-item">
<input name="is_billable1" class="form-check-input" type="radio" value="1"
id="is_billable-11" <% if (this.details.is_billable=='1' ){ %>checked<% }
%>>
<label class="form-check-label" for="is_billable-1" role="button">
Yes
</label>
</div>
</div>
<div id="divAmont"
class="form-group mb-3 <% if (this.details.is_billable=='1'){ %>show<% } %>">
<label for="amount_billed">Billable Amount:</label>
<input type="text" class="form-control" id="amount_billed1" name="amount_billed1"
placeholder=" Billable Amount"
value="<%= this.details.amount_billed||'' %>">
<small class="form-text text-muted">Enter the Billable Amount</small>
</div>
</fieldset>
<div class="container">
<div class="row">
<div class="col">
<button class="btn btn-primary record" id="btnAdd" onclick="addDelegate()"
type="button">
<i class="fa fa-plus"></i>
</button>
</div>
</div>
<div id="toolbar-spacer"></div>
<div class="row">
<div class="col">
<table class="table records table-sm table-hover" name="tableDelegate[]"
id="tableDelegate">
<thead>
<th>Course</th>
<th>Account No</th>
<th>Municipality</th>
<th>Is billable</th>
<th>Billable Ammount</th>
<th></th>
</thead>
<tbody>
</tbody>
</table>
</div>
</div>
</div>
</div>
</div>
</div>
<input type="hidden" name="@@CSRF_NAME@@" value="@@CSRF_TOKEN@@">
<div class="modal-footer">
<div class="container">
<div class="row">
<div class="col">
<button class="btn btn-danger delete-btn" type="button">
<i class="fa fa-trash"></i> Delete
</button>
</div>
<div class="col text-end d-flex justify-content-between">
<button class="btn btn-light record w-50" type="button" data-id=""
data-dismiss="modal">
<i class="fas fa-ban"></i> Cancel
</button>
<button class="btn btn-primary w-50" type="submit">
<i class="far fa-save"></i> Save
</button>
</div>
</div>
</div>
</div>
</form>
</div>
</div>
</div>Javascript
var delegates = new Array();
function addDelegate(){
// var course = document.getElementById('courses_attendance_list_id').options[e.selectedIndex].text;
var course = document.getElementById("courses_attendance_list_id1");
var courseName = course.options[course.selectedIndex].text;
var courseValue = course.options[course.selectedIndex].value;
var accountNo = document.getElementById('account_no1').value;
var municipality = document.getElementById('municipality_id1');
var municipalityName = municipality.options[municipality.selectedIndex].text;
var municipalityValue = municipality.options[municipality.selectedIndex].value;
var billableAmount = document.getElementById('amount_billed1').value;
var is_billable;
var is_billableText;
if (document.getElementById('is_billable-01').checked) {
is_billable = document.getElementById('is_billable-01').value;
is_billableText = 'No'
}
if (document.getElementById('is_billable-11').checked) {
is_billable = document.getElementById('is_billable-11').value;
is_billableText = 'Yes'
}
var delegate = {
course: courseName,
courseVal: courseValue,
accountNoVal: accountNo,
municipality: municipalityName,
is_billableVal: is_billable,
is_billableTextVal: is_billableText,
billableAmountVal: billableAmount,
municipalityVal: municipalityValue,
};
//console.log('Amount billed: ' + delegate.billableAmountVal);
delegates.push(delegate);
var table = document.getElementById('tableDelegate');
var tr = document.createElement('tr');
tr.innerHTML =
'<td>' + '<input type="hidden"' + 'class="form-control"' + ' id="courses_attendance_list_id" ' + 'name="courses_attendance_list_id"' +
'value='+ delegate.courseVal + '>' + delegate.course + '</td>' +
'<td>' + '<input type="hidden"' + 'class="form-control"' + ' id="account_no" ' + 'name="account_no[]"' +
'value='+ delegate.accountNoVal + '>' + delegate.accountNoVal + '</td>' +
'<td>' + '<input type="hidden"' + 'class="form-control"' + ' id="municipality_id" ' + 'name="municipality_id[]"' +
'value='+ delegate.municipalityVal + '>' + delegate.municipality + '</td>'+
'<td>' + '<input type="hidden"' + 'class="form-control"' + ' id="is_billable" ' + 'name="is_billable[]"' +
'value='+ delegate.is_billableVal + '>' + delegate.is_billableTextVal + '</td>'+
'<td>' + '<input type="hidden"' + 'class="form-control"' + ' id="amount_billed" ' + 'name="amount_billed[]"' +
'value='+ delegate.billableAmountVal + '>' + delegate.billableAmountVal + '</td>';
table.appendChild(tr);
}Controller
<?php
namespace app\courses\controllers;
use \system\db\Query;
use \system\utilities\Strings;
use \system\utilities\System;
use \system\utilities\Arrays;
use app\courses\models\delegates\delegateModel;
use app\courses\schemas\delegates\delegatesDetailsSchema;
use app\courses\schemas\delegates\delegatesListSchema;
use app\courses\models\courses\CourseModel;
use app\courses\schemas\courses\courseDetailsSchema;
use app\courses\schemas\courses\CourseListSchema;
use app\admin\models\municipalities\municipalityModel;
use app\admin\schemas\municipalities\municipalitiesDetailsSchema;
use app\admin\schemas\municipalities\municipalitiesListSchema;
class delegateController extends AbstractController {
function page() {
$profiler = System::profiler(__CLASS__ . "::" . __FUNCTION__, __NAMESPACE__);
$data = array();
$data["id"] = $_GET['id'] ?? false;
$data["errors"] = array();
$data["search"] = $this->system->get("REQUEST.search");
switch ($this->system->get("VERB")) {
case "POST":
$data = $this->_save($data);
break;
case "DELETE":
$data = $this->_delete($data);
break;
default:
if ($data['id']) {
$data = $this->_details($data);
} else {
$data = $this->_list($data);
}
break;
}
$this->render("delegates\\page.twig", $data, true);
$profiler->stop();
}
function _list($return) {
$where = "1";
$params = array();
if ($return['search']) {
$where .= " AND (delegates.account_no LIKE :SEARCH)";
$params[':SEARCH'] = "%" . $return['search'] . "%";
}
$list_data = (new delegateModel())
->_order("delegates.account_no ASC")
->_where($where, $params)
->getAll()->toSchema(new delegatesListSchema())
;
// System::debug($list_data);
$return['list'] = $list_data;
return $return;
}
function _details($return) {
$details = (new delegateModel())->get($return['id']);
$return['details'] = $details->toSchema(new delegatesDetailsSchema());
$courses_attendance_list = (new CourseModel())
->_order("courses_attendance_list.name ASC")
->getAll()
->toSchema((new CourseListSchema())->selected($return['details']['courses_attendance_list_id']))
;
$return['courses_attendance_list'] = $courses_attendance_list;
$municipalities = (new municipalityModel())
->_order("municipalities.name ASC")
->getAll()
->toSchema((new municipalitiesListSchema())->selected($return['details']['municipality_id']))
;
//System::debug($municipalities);
$return['municipalities'] = $municipalities;
return $return;
}
function _save($return) {
// System::debug($_POST);
$values = array(
"account_no"=>(array)$this->system->get("POST.account_no"),
"municipality_id" => (array)$this->system->get("POST.municipality_id"),
"is_billable"=>(array)$this->system->get("POST.is_billable"),
"amount_billed" => (array)$this->system->get("POST.amount_billed"),
"courses_attendance_list_id"=>(array)$this->system->get("POST.courses_attendance_list_id"),
);
// if ($values['is_billable']!= '1'){
// $values['is_billable'] = '0';
// $values['amount_billed'] = '0';
// }
// System::debug('Values: ',$values);
$details = (new delegateModel())->get($return['id']);
$return['errors'] = $details->validate($values);
if (empty($return['errors'])) {
$id = $details->save($values);
$return['id'] = $id;
}
return $return;
}
function _delete($return) {
$details = (new delegateModel())->get($return['id']);
$details->delete();
return $return;
}
}
**Model**
<?php
namespace app\courses\models\delegates;
use \app\shared\models\AbstractModel;
use \app\shared\traits\DBfetchTrait;
use \system\Collection;
use \system\db\Write;
use \system\db\Query;
use \system\utilities\System;
class delegateModel extends AbstractModel {
use DBfetchTrait;
const TABLE = "delegates";
const PK = "id";
protected $ID = null;
protected $DATA = array();
function __construct($DB = null) {
parent::__construct($DB);
$this
->_select("
delegates.id,
delegates.account_no as account_no,
delegates.amount_billed as amount_billed,
municipalities.id as municipality_id,
municipalities.name as municipality_name,
courses_attendance_list.id as courses_attendance_list_id,
courses_attendance_list.name as course_name,
delegates.is_billable
")
->_from("
delegates
LEFT JOIN
municipalities ON municipalities.id = delegates.municipality_id
LEFT JOIN
courses_attendance_list ON courses_attendance_list.id = delegates.courses_attendance_list_id
");
}
function get($id = null) {
$profiler = System::profiler(__CLASS__ . "::" . __FUNCTION__, __NAMESPACE__);
$this->_where("".self::TABLE.".".self::PK." = :id", array(":id" => $id));
$this->_limit("0,1");
foreach ($this->query()->fetch() as $record) {
$this->DATA = $record;
}
$profiler->stop();
return $this;
}
function getAll() {
$profiler = System::profiler(__CLASS__ . "::" . __FUNCTION__, __NAMESPACE__);
$collection = new Collection();
foreach ($this->query()->fetch() as $record) {
$object = clone $this;
$object->DATA = $record;
$collection->add($object);
}
$profiler->stop();
return $collection;
}
function getCount() {
$profiler = System::profiler(__CLASS__ . "::" . __FUNCTION__, __NAMESPACE__);
$query = clone $this->query();
$query->setSelect("COUNT(".self::TABLE.".".self::PK.") as c");
$return = 0;
foreach ($query->fetch() as $record) {
$return = $record['c'];
}
$profiler->stop();
return $return;
}
function save($values=array()) {
$profiler = System::profiler(__CLASS__ . "::" . __FUNCTION__, __NAMESPACE__);
if (count($values)){
// System::debug($values);
$save = new Write(self::TABLE, $this->DB);
$save->setWhere("".self::TABLE.".".self::PK." = :ID", array(":ID" => $this->id()));
$save->setSaveOnDry(true);
$save->setAudit(true);
$save->setPK(self::PK);
$result = $save->save($values);
$this->id($result['id']);
}
$profiler->stop();
return $result;
}
function delete() {
$profiler = System::profiler(__CLASS__ . "::" . __FUNCTION__, __NAMESPACE__);
$result = false;
$delete = new Write(self::TABLE, $this->DB);
$delete->setWhere("".self::TABLE.".".self::PK." = :ID", array(":ID" => $this->id()));
$delete->setAudit(true);
$result = $delete->delete();
$profiler->stop();
return $result;
}
function validate($values=array()) {
$profiler = System::profiler(__CLASS__ . "::" . __FUNCTION__, __NAMESPACE__);
$errors = array();
if (isset($values['courses_attendance_list_id'])){
if (!$values['courses_attendance_list_id'] ) {
$errors['courses_attendance_list_id'][] = "Course name is required";
}
}
if (isset($values['account_no'])){
if (!$values['account_no'] ) {
$errors['account_no'][] = "Delegate account number is required";
}
}
if (isset($values['municipality_id'])){
if (!$values['municipality_id'] ) {
$errors['municipality_id'][] = "municipality name is required";
}
}
$profiler->stop();
return $errors;
}
}
**Schema**
<?php
namespace app\courses\schemas\delegates;
use \app\courses\models\delegates\delegateModel;
use \app\shared\schemas\AbstractSchema;
use \app\shared\schemas\SchemaInterface;
use \system\utilities\Strings;
class delegatesListSchema extends AbstractSchema implements SchemaInterface {
protected $types = array();
function return(){
/**
* @var delegateModel
*/
$item = $this->item;
return array(
"id"=>$item->id(),
"account_no"=>$item->data("account_no"),
"municipality_name"=>$item->data("municipality_name"),
"amount_billed"=>$item->data("amount_billed"),
"course_name"=>$item->data("course_name"),
"is_billable"=>$item->data("is_billable")
);
}
}
<?php
namespace app\courses\schemas\delegates;
use \app\courses\models\delegates\delegateModel;
use \app\shared\schemas\AbstractSchema;
use \app\shared\schemas\SchemaInterface;
use \system\utilities\Strings;
class delegatesDetailsSchema extends AbstractSchema implements SchemaInterface {
function return(){
/**
* @var delegateModel
*/
$item = $this->item;
return array(
"id"=>$item->id(),
"account_no"=>$item->data("account_no"),
"municipality_id"=>$item->data("municipality_id"),
"courses_attendance_list_id"=>$item->data("courses_attendance_list_id"),
"is_billable"=>$item->data("is_billable"),
// "is_billable"=>'1',
"amount_billed"=>$item->data("amount_billed"),
);
}
}