CODEWITHSUNDEEP
phpmysqljsoncodeigniter
ashura91
ashura91

Reputation: 441

How to select NULL if there are no data and display the data if it exist?

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

Upvotes: 0

Views: 3431

Answers (4)

Calos
Calos

Reputation: 1942

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;
}

Upvotes: 1

Afshan Shujat
Afshan Shujat

Reputation: 551

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.

Upvotes: 1

kaizoku himura
kaizoku himura

Reputation: 85

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

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

Upvotes: 1

Matthew Asia
Matthew Asia

Reputation: 16

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.

Upvotes: 0

Related Questions