How to select NULL if there are no data and displa

问题:

I'd like to do a select AS NULL from MySQL db. This is the select query:

function get_new()
{
    $query = "SELECT DATE_FORMAT(curdate(),'%d/%m-%Y') AS date_created, NULL AS name, NULL AS address, NULL AS status FROM t_penomoran";
    $result = $this->db->query($query)->row();

    return $result;
}

This is the controller

public function add()
{
    $id         = 0;
    $master     = $this->MPenomoran->get_new();

    $data = array (
        'titlepage'     => APP_TITLEPAGE,
        'titleapp'      => 'Penomoran',
        'userid'        => $this->session->userdata('nip'),
        'id'            => $id,
        'record'        => $master,
        'error'         => '',
        'complete'      => 'true',
        'loadmethod'    => 'add',
        'contentpage'   => 'penomoran/detail');

    $this->load->view('shared/master_app', $data);
}

And when I use this on the view:

<?php echo $record->name; ?>

I kepp getting Trying to get property of non-object error. How to solve this?

My table structure:

CREATE TABLE `t_penomoran` (
`nomor` int(50) NOT NULL,
`date_created` date DEFAULT NULL,
`name` varchar(100) DEFAULT NULL,
`jenis_pekerjaan` text,
`nominal` bigint(20) DEFAULT NULL,
`address` varchar(50) DEFAULT NULL,
`pic` varchar(100) DEFAULT NULL,
`keterangan` varchar(255) DEFAULT NULL,
`status` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin

回答1:

If table is empty (0 rows), the query result = null is normally.

And you want to show row data when row ID x is exists, you should be select column, not select NULL AS column, because if row data is exists, all column value is null forever.

So, change code to SQL do not select null, and when query result is empty, return a default sets, like this:

function get_data( $id )
{
    $query = "SELECT
            DATE_FORMAT(curdate(),'%d/%m-%Y') AS date_created,
            `name`,
            `address`,
            `status`
        FROM `t_penomoran`
        WHERE `nomor` = '{$id}'";

    $result = $this->db->query($query)->row();

    if ( empty($result) ) {
        $result = (object)[
            'date_created' => date('d/m-Y'),
            'name' => null,
            'address' => null,
            'status' => null,
        ];
    }

    return $result;
}

回答2:

how about if you just do it like this...

if (empty($record->name)) { echo "Name"; }

回答3:

You can use mysql if-else in your select query like -

SELECT DATE_FORMAT(curdate(),'%d/%m-%Y') AS date_created, 
    IF(name IS NULL, '', name) AS name, 
    IF(address IS NULL, '', address) AS address, 
    IF(status IS NULL, '', status) AS status 
FROM t_penomoran

I executed the query and it gives the result as mentioned below without any error -

date_created | name | address | status
13/02-2017   |      |         | 1

Hope this will help you.

回答4:

Your problem is not the NULL as field, it is:

$query = "SELECT DATE_FORMAT(curdate(),'%d/%m-%Y') AS date_created, NULL AS name, NULL AS address, AS status FROM t_penomoran";

address, AS status

You have nothing between the , and the AS here.