Can someone help with logic/code to insert an array to SQL using PHP

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">&times;</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="@@[email protected]@" value="@@[email protected]@">

                    <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"),

            

        );

    }

}

Please do not double-space your posts! Just wastes space and our time…

Now, just give us the info needed for this. HTML and JS have nothing to do with SQL…
So, you want to save a simple array into a database table’s field? To do that, you just use the serialize function. Usually, you would never do this. You would just design your database to handle multiple entries and then use SQL queries to retrieve them as needed. But, it is easy to save an array. You need to convert it from an array into a string. Then, store it and retrieve it in the reverse manner. Here is an example…

$test = array(“some”, “data”); OR $test = array(123=>“data123”, 123123=>“data123123”);
$serialized_array = serialize($test);
… Save data in database …
… Later on get it back into a $row results …
$test_array = unserialize($row[“saved_array”];

A couple notes on this… First, you do not know how large the serialized version is. Therefore you need to make sure the database field for it is larger than the input data size is. The un-serialization process should make the variable into the array, but, I have seen once in awhile you need to declare it first.

Should work…

Sponsor our Newsletter | Privacy Policy | Terms of Service