• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Printing only NOT NULL datas from MySql query

#1
[eluser]Référencement Google[/eluser]
Hi.

I bug on a simple problem.
I have fields in the DB that have a default value to NULL.
I want to print on the user screen only the datas wich are in the DB fields. About the SQL query it's not that problem for me, a simple IS NOT NULL do that.

My problem is about PHP printing data, I can of course test each fields one by one to know if the field is null or not, but on a 30 fields DB table row this will make a lot of shitty code.

So I would like to know if somebody can help me on that to come to a nice coded solution ?

Actually, the code is that, but it was not finished yet:

Code:
function get_profileByID($id)
    {
        $query = $this->db->getwhere('nip_profile', array('profile_ID' => $id));

        // Is there any results ?
        if(!$query->num_rows() > 0)
        {
            return false;
        }


        // @TODO: Tester tous les champs NULL et ne renvoyer que ceux-ci pour n'afficher que ceux-ci


        foreach($query->result() as $row)
        {
            // Check if the profile is active
            if($row->active == 0)
            {
                return false;
            }

            $data = array(  'is_human'      => $row->is_human,
                            'active'        => $row->active,
                            'created'       => $row->created,
                            'modified'      => $row->modified,          // Possible NULL
                            'name'          => $row->name,
                            'website'       => $row->website,           // Possible NULL
                            'origin'        => $row->origin,            // Possible NULL
                            'based_at'      => $row->based_at,          // Possible NULL
                            'infrastructure'=> $row->infrastructure,    // Possible NULL
                            'height'        => $row->height,            // Possible NULL
                            'weight'        => $row->weight,            // Possible NULL
                            'measurements'  => $row->measurements,      // Possible NULL
                            'description'   => $row->description);      // Possible NULL

            // If birthdate is not Null, we convert it to Age
            if($row->date_of_birth)
            {
                $data['age'] = mysqldate_to_age($row->date_of_birth);
            }

        }

        return $data;
    }

And then I have this function in controller wich will set the View file:

Code:
function _view_profileDetail($profile_ID)
    {
        // Get the profile by it's ID.
        if(!$query = $this->profile->get_profileByID($profile_ID)) // If return false, we show a Deactivated message
        {
            $data['content'] = "<h3 class=\"warning\">Désolé, ce profil a été désactivé pour le moment</h3>";
        }
        else
        {
            // Query returned true, we show the profile
            $data['content'] = $this->load->view('profile_details', $query, TRUE);
        }

        $this->load->view('espace_vipx', $data);
    }

And then I didn't do yet the view file, where I will print all my datas and exactly in the view file it's important for me to print only existing datas, I don't want to show on screen some fields wich are not filled.

How can we make all that stuff simplier ?

#2
[eluser]Phil Sturgeon[/eluser]
Surely just:

Code:
function get_profileByID($id)
    {
        $query = $this->db->getwhere('nip_profile', array('profile_ID' => $id, 'active' => 1));

        // Is there any results ?
        if(!$query->num_rows() > 0)
        {
            return false;
        }


        // @TODO: Tester tous les champs NULL et ne renvoyer que ceux-ci pour n'afficher que ceux-ci


        foreach($query->result() as $row)
        {
            $data = array(  'is_human'      => $row->is_human,
                            'active'        => $row->active,
                            'created'       => $row->created,
                            'modified'      => (isset($row->modified) ? $row->modified : '',          // Possible NULL
                            'name'          => $row->name,
                            'website'       => (isset($row->website) ? $row->website : '',           // Possible NULL
                            'origin'        => (isset($row->origin) ? $row->origin : '',            // Possible NULL
                            'based_at'      => (isset($row->based) ? $row->based_at : '',          // Possible NULL
                            //etc
            );

            // If birthdate is not Null, we convert it to Age
            if($row->date_of_birth)
            {
                $data['age'] = mysqldate_to_age($row->date_of_birth);
            }

        }

        return $data;
    }

#3
[eluser]Référencement Google[/eluser]
Yes 'Thepyromaniac' you are right on this point to check active profile or not.
And what about returning only not null fields in my array ? I should I manage that the best way ?

#4
[eluser]Phil Sturgeon[/eluser]
You can use ternary/trinary equations to accomplish this, if I have understood you correctly. I updated my post with a few examples.

Depending on how you want it to work, use either isset() or !empty() (isst makes sure it has been defined, !empty means its not 0, NULL, array(), FALSE, etc).

#5
[eluser]Référencement Google[/eluser]
Ok, that's what I was planing to do, but what refracted me to do it is that I must do almost 95 tests like that, for this I posted my question to know if there was maybe a better and nicer way to do that.

#6
[eluser]Phil Sturgeon[/eluser]
Code:
foreach($row as $field => $value):
$row[$field]  = (!isset($value) || is_null($value)) ? '' : $value;
endforeach;

#7
[eluser]Référencement Google[/eluser]
Thank you, you just let me to not have 95 fields to test 1 by 1 !

That's what I was looking for and trying for, but I was trying to get a working solution from '$query->result()' to dynamically construct my test.


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2019 MyBB Group.