Click here to Skip to main content
15,886,518 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
ERROR
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ORDER BY tblmas_trip_tracking ASC' at line 21


MY CODE

*MODEL
<?php

namespace App\Models;


use CodeIgniter\Model;

class TipModel extends Model
{
    protected $table = 'tblmas_trip_tracking';
    protected $key = 'id';
    
    public function getviewdata()
    {
        $query ="SELECT tblmas_trip_tracking.*,
                    GROUP_CONCAT(Distinct tblmas_customer.customername) AS customerName
                        FROM tblmas_trip_tracking
                    
                            JOIN tbltrn_do1 ON FIND_IN_SET(tbltrn_do1.dono, tblmas_trip_tracking.do_no)
                            JOIN tblmas_customer ON tbltrn_do1.idcustomer = tblmas_customer.idcustomer
                            
                            GROUP BY 
                            tblmas_trip_tracking.id, 
                            tblmas_trip_tracking.start_time,
                            tblmas_trip_tracking.end_time,
                            tblmas_trip_tracking.customerName,
                            tblmas_trip_tracking.value,
                            tblmas_trip_tracking.do_no,
                            tblmas_trip_tracking.driver,
                            tblmas_trip_tracking.bp_lorry,
                            tblmas_trip_tracking.trip,
                            tblmas_trip_tracking.remarks
                            
                            ORDER BY tblmas_trip_tracking.driver ASC";

        $data = $this->db->query($query)->getResultArray();
        return $data;
    }

    public function getdataID($idtrip)
    {
        $query = "SELECT tblmas_trip_tracking.*,
                    GROUP_CONCAT(Distinct tblmas_customer.customername) AS customerName
                        FROM tblmas_trip_tracking
        
                            JOIN tbltrn_do1 ON FIND_IN_SET(tbltrn_do1.dono, tblmas_trip_tracking.do_no)
                            JOIN tblmas_customer ON tbltrn_do1.idcustomer = tblmas_customer.idcustomer
                            WHERE tblmas_trip_tracking.id = $idtrip
                
                            GROUP BY 
                            tblmas_trip_tracking.id, 
                            tblmas_trip_tracking.start_time,
                            tblmas_trip_tracking.end_time,
                            tblmas_trip_tracking.customerName,
                            tblmas_trip_tracking.value,
                            tblmas_trip_tracking.do_no,
                            tblmas_trip_tracking.driver,
                            tblmas_trip_tracking.bp_lorry,
                            tblmas_trip_tracking.trip,
                            tblmas_trip_tracking.remarks
                
                            ORDER BY tblmas_trip_tracking.driver ASC";
        $data = $this->db->query($query)->getResultArray();
        return $data;
    }

    public function getCustomer()
    {
        return $this->db->table('tblmas_customer')->get()->getResultArray();
    }

    public function Addformtip($data)
    {
        $this->db->table('tblmas_trip_tracking')->insert($data);
        $pesan = [
            'stts' => true,
            'msg' => "data telah ditambahkan",
            ];
        return $pesan;
    }

    public function Updateformtip($data)
    {
        $this->db->table('tblmas_trip_tracking')->where(['id' => $data['id']])->update($data);
        $pesan = [
            'stts' => true,
            'msg' => "data telah di update",
        ];
        return $pesan;
    }

    public function Deletetip($data)
    {
        $this->db->table('tblmas_trip_tracking')->where(['id' => $data['id']])->delete($data);
        $pesan['stts'] = true;
        $pesan['msg'] = "data telah dihapus!";
        return $pesan;
    }

    public function getDriver()
    {
        return $this->db->table('tblmas_driver')->get()->getResultArray();
    }

    public function getValue()
    {
        return $this->db->table('tblmas_value')->get()->getResultArray();
    }

    public function getLorry()
    {
        return $this->db->table('tblmas_lorry')->get()->getResultArray();
    }

    public function getdono()
    {
        $query = "SELECT tbltrn_do1.dono FROM tbltrn_do1";
        return $this->db->query($query)->getResultArray();
    }

    public function getdonobycustomername()
    {
        $query ="SELECT tblmas_customer.*, tbltrn_do1.dono FROM tblmas_customer
                    JOIN tbltrn_do1 ON tbltrn_do1.idcustomer = tblmas_customer.idcustomer
                        WHERE tbltrn_do1.stsdosent = 1 limit 2000";
        return $this->db->query($query)->getResultArray();
    }

    public function getdataDono($do_no)
    {
        // $do = explode(',', $do_no);
        // dd($do);
        $query = "SELECT tblmas_trip_tracking.*  FROM tblmas_trip_tracking 
                    JOIN tbltrn_do1 ON tbltrn_do1.dono = tblmas_trip_tracking.do_no 
                    WHERE tblmas_trip_tracking.do_no";
        return $this->db->query($query)->getResultArray();
    }
    
    public function getambildatadono($csr)
    {
        $query = "SELECT tblmas_customer.*, tbltrn_do1.dono FROM tblmas_customer
        JOIN tbltrn_do1 ON tbltrn_do1.idcustomer = tblmas_customer.idcustomer
            WHERE tblmas_customer.customername = '$csr' AND tbltrn_do1.stsdosent = 1";
        return $this->db->query($query)->getResultArray();
    }
}


*CONTROLLER

<?php

namespace App\Controllers;

use App\Models\TipModel;
use CodeIgniter\HTTP\Request;

class Tip extends BaseController
{

    public function __construct()
    {
        $this->tip  = new TipModel();
    }

    public function viewListTip()
    {
        $data = [
            'title' => 'dashboard',
            'list_dono' => $this->tip->getdonobycustomername(),
            'list_customer' => $this->tip->getCustomer(),
            'list_value' => $this->tip->getValue(),
            'list_driver' => $this->tip->getDriver(),
            'list_lorry' => $this->tip->getLorry(),
            'data_list' => $this->tip->getviewdata(),
        ];
        return view('Tip_tracking/list_tip', $data);
    }

    public function addListTip()
    {
        if ($this->request->isAJAX()) {

        };
        $data = [
            'title' => 'Add Trip Tracking',
            'list_dono' => $this->tip->getdonobycustomername(),
            'list_customer' => $this->tip->getCustomer(),
            'list_value' => $this->tip->getValue(),
            'list_driver' => $this->tip->getDriver(),
            'list_lorry' => $this->tip->getLorry(),
            'data_list' => $this->tip->getviewdata(),
        ];
        return view('Tip_tracking/add_list_trip', $data);
    }

    public function viewlistupdate()
    {
        $idtrip = $this->request->getVar('id');
        $do_no  = $this->request->getVar('Dono');
        // $do = explode(',', $this->request->getPost('Dono'));

        $data =[
            'title' => 'Edit Trip Tracking',
            'data_list' => $this->tip->getdataID($idtrip),
            'list_driver' => $this->tip->getDriver(),
            'list_lorry' => $this->tip->getLorry(),
            'list_value' => $this->tip->getValue(),
            'dono_edit' => $this->tip->getdataDono($do_no),
        ];
        return view('Tip_tracking/update_list_trip', $data);
    }

    public function formtambahdata()
    {
        if  ($this->request->isAJAX()) {
            $msg = [
                'data_list' => $this->tip->getviewdata(),
                'data' => view('Tip_tracking/add_list_trip')
            ];
            echo json_encode($msg);
        };
    }

    public function tambahlistTip()
    {
        $start_time =  date("d-m-Y h:i:s", strtotime($this->request->getPost('start_time')));
        $end_time =  date("d-m-Y h:i:s", strtotime($this->request->getPost('end_time')));
        $data_do = implode(',', $this->request->getPost('Dono'));
        $data = [
            'start_time' => $start_time,
            'end_time' => $end_time,
            'do_no' => $data_do,
            // 'customer_name' => $data_cos, 
            'value' => $this->request->getVar('value'),
            'trip' => $this->request->getVar('trip'),
            'driver' => $this->request->getVar('driver'),
            'bp_lorry' => $this->request->getVar('bp_lorry'),
            'remarks' => $this->request->getVar('remarks'),
        ];

        $pesan = $this->tip->Addformtip($data);
        session()->setFlashdata('pesan', $pesan);
        return redirect()->to('Tip/viewListTip');
    }
    
    public function editlistTip()
    {
        $do = explode(',', $this->request->getPost('Dono'));
        $data = [
            'id' => $this->request->getVar('id'),
            // 'customerName' => $this->request->getVar('customer'),
            'value' => $this->request->getVar('value'),
            'do_no' => $do,
            'trip' => $this->request->getVar('trip'),
            'driver' => $this->request->getVar('driver'),
            'bp_lorry' => $this->request->getVar('bp_lorry'),
            'remarks' => $this->request->getVar('remarks'),
        ];
        $pesan = $this->tip->Updateformtip($data);
        session()->setFlashdata('pesan', $pesan);
        return redirect()->to('Tip/viewlistupdate');
    }

    public function deleteListTip()
    {
        $data = array(
            'id' => $this->request->getVar('id'),
        );
        $pesan = $this->tip->Deletetip($data);
        session()->setFlashdata('pesan', $pesan);
        return redirect()->to('Tip/viewListTip');
    }

    public function AjaxDono()
    {
        $csr = $this->request->getVar('customer');
        $data = $this->tip->getambildatadono($csr);

        $do_no = "";
        $do_no .= '<option>-- Select Do No --</option>';
        foreach ($data as $list) {
            $do_no .= '<option value=' .$list['do_no'] . '>' . $list["do_no"] . '</option>';
        }
        echo $do_no;
    }


}


What I have tried:

I've tried a lot of tutorials and help online, but I just can't figure out what's wrong.
Posted
Updated 27-Dec-22 22:27pm
Comments
Member 15627495 28-Dec-22 3:08am    
does your ALIAS need quotes ? --> ... as 'CustomerName'
the space between lines maybe break your string query.
select *
---------------------
from
----------
// go by :
select *
from ....

1 solution

You are missing the semi-colon at the end of the statement, it should be:
SQL
ORDER BY tblmas_trip_tracking.driver ASC;";
                                        ^ semi-colon required here

See ORDER BY - MariaDB Knowledge Base[^].
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900