SQL Sample

<?php

class Db_query extends CI_Model {

    function __construct() {

        // Call the Model constructor
        parent::__construct();
    }

    // **************************************
    // Dashboard Tab
    // **************************************

    // Get counts for admin panel dashboard
    function get_counts_for_admin_dashboard() {

        $sql = "SELECT
                (SELECT COUNT(*) FROM admin_table) AS count_total_users,
                (SELECT COUNT(*) FROM admin_table WHERE user_status = 'active') AS count_active_users,
                (SELECT COUNT(*) FROM admin_table WHERE user_status = 'inactive') AS count_inactive_users,

                (SELECT COUNT(*) FROM company_profile) AS count_total_companies,
                (SELECT COUNT(*) FROM company_profile WHERE status = 'active') AS count_total_active_companies,
                (SELECT COUNT(*) FROM company_profile WHERE status = 'holding') AS count_comps_held,

                (SELECT COUNT(*) FROM product_profile) AS count_total_products,
                (SELECT COUNT(*) FROM product_profile WHERE prod_status = 'active') AS count_total_active_products,
                (SELECT COUNT(*) FROM product_profile WHERE prod_status = 'holding') AS count_prods_held"
        ;
        $query = $this->db->query($sql);

        if ($query->num_rows() > 0) { $output = $query->result(); $query->free_result(); return $output; } else { return false; }
    }

    // Query companies with revisions for admin panel dashboard
    function comps_awaiting_revisions() {

        $sql = "SELECT * FROM revisions WHERE prod_or_comp = 'comp' AND approved = 'N' GROUP BY prod_comp_id";
        $query = $this->db->query($sql);

        if ($query->num_rows() > 0) { $output = $query->result(); $query->free_result(); return $output; } else { return false; }
    }

    // Query products with revisions for admin panel dashboard
    function prods_awaiting_revisions() {

        $sql = "SELECT * FROM revisions WHERE prod_or_comp = 'prod' AND approved = 'N' GROUP BY prod_comp_id";
        $query = $this->db->query($sql);

        if ($query->num_rows() > 0) { $output = $query->result(); $query->free_result(); return $output; } else { return false; }
    }




    // Query all user profiles for awaiting gisr approval
    function users_awaiting_approval() {

        $sql = "SELECT * FROM admin_table WHERE approved = 'N' OR user_status = 'inactive'";
        $query = $this->db->query($sql);

        if ($query->num_rows() > 0) { $output = $query->result(); $query->free_result(); return $output; } else { return false; }
    }



    // Query all companies
    function query_all_comps() {

        $sql = "SELECT * FROM company_profile WHERE status = 'active' ORDER BY firm_name ASC";
        $query = $this->db->query($sql);

        if ($query->num_rows() > 0) { $output = $query->result(); $query->free_result(); return $output; } else { return false; }
    }

    // Query all products by company id
    function query_all_prods_by_comp_id($comp_id = 0) {

        $sql = "SELECT * FROM product_profile WHERE company_id = '$comp_id' AND prod_status = 'active' ORDER BY product_name ASC";
        $query = $this->db->query($sql);

        if ($query->num_rows() > 0) { $output = $query->result(); $query->free_result(); return $output; } else { return false; }
    }

    // Query single company by company id
    function query_single_comp_by_comp_id($comp_id = 0) {

        $sql = "SELECT * FROM company_profile WHERE company_id = '$comp_id' ORDER BY company_id desc LIMIT 0, 5000";
        $query = $this->db->query($sql);

        if ($query->num_rows() > 0) { $output = $query->result(); $query->free_result(); return $output; } else { return false; }
    }





    // Query all company profiles for awaiting gisr approval
    function comps_awaiting_approval() {

        $sql = "SELECT company_id, firm_name FROM company_profile WHERE status = 'awaiting_approval'";
        $query = $this->db->query($sql);

        if ($query->num_rows() > 0) { $output = $query->result(); $query->free_result(); return $output; } else { return false; }
    }

    // Query all companies held by user
    function comps_held_by_user() {

        $sql = "SELECT * FROM company_profile_held";
        $query = $this->db->query($sql);

        if ($query->num_rows() > 0) { $output = $query->result(); $query->free_result(); return $output; } else { return false; }
    }

    // Query deactivated companies
    function deactivated_companies() {

        $sql = "SELECT * FROM company_profile WHERE status = 'inactive' ORDER BY firm_name";
        $query = $this->db->query($sql);

        if ($query->num_rows() > 0) { $output = $query->result(); $query->free_result(); return $output; } else { return false; }
    }




    // Query all product profiles for awaiting gisr approval
    function prods_awaiting_approval() {

        $sql = "SELECT product_id, company_id, product_name FROM product_profile WHERE prod_status = 'awaiting_approval'";
        $query = $this->db->query($sql);

        if ($query->num_rows() > 0) { $output = $query->result(); $query->free_result(); return $output; } else { return false; }
    }

    // Query all products held by user
    function prods_held_by_user() {

        $sql = "SELECT * FROM product_profile_held";
        $query = $this->db->query($sql);

        if ($query->num_rows() > 0) { $output = $query->result(); $query->free_result(); return $output; } else { return false; }
    }

    // Query deactivated products
    function deactivated_products() {

        $sql = "SELECT pp.product_name AS product_name, pp.product_id AS product_id, pp.company_id AS company_id, pp.prod_status_reason AS status_reason, cp.firm_name AS firm_name
                FROM product_profile pp
                LEFT JOIN company_profile cp ON pp.company_id = cp.company_id
                WHERE pp.prod_status = 'inactive' ORDER BY pp.product_name"
        ;
        $query = $this->db->query($sql);

        if ($query->num_rows() > 0) { $output = $query->result(); $query->free_result(); return $output; } else { return false; }
    }



    // Query bookmarked companies
    function bm_comps() {

        $sql = "SELECT * FROM admin_table";
        $query = $this->Comman_crud->custom_query($sql);

        $count = 0;
        foreach($query as $key => $value) {

            $array = isset($value->bm_comps) ? @unserialize($value->bm_comps) : array();
            $count = !empty($array) ? $count + count($array) : $count;
        }

        return $count;
    }
    // Query bookmarked products
    function bm_prods() {

        $sql = "SELECT * FROM admin_table";
        $query = $this->Comman_crud->custom_query($sql);

        $count = 0;
        foreach($query as $key => $value) {

            $array = isset($value->bm_prods) ? @unserialize($value->bm_prods) : array();
            $count = !empty($array) ? $count + count($array) : $count;
        }

        return $count;
    }
    // Query bookmarked company comparisons
    function bm_comps_compare() {

        $sql = "SELECT * FROM admin_table";
        $query = $this->Comman_crud->custom_query($sql);

        $count = 0;
        foreach($query as $key => $value) {

            $array = isset($value->bm_comps_compare) ? @unserialize($value->bm_comps_compare) : array();
            $count = !empty($array) ? $count + count($array) : $count;
        }

        return $count;
    }
    // Query bookmarked product comparisons
    function bm_prods_compare() {

        $sql = "SELECT * FROM admin_table";
        $query = $this->Comman_crud->custom_query($sql);

        $count = 0;
        foreach($query as $key => $value) {

            $array = isset($value->bm_prods_compare) ? @unserialize($value->bm_prods_compare) : array();
            $count = !empty($array) ? $count + count($array) : $count;
        }

        return $count;
    }
    // Query bookmarked advanced searches
    function bm_adv_search() {

        $sql = "SELECT * FROM admin_table";
        $query = $this->Comman_crud->custom_query($sql);

        $count = 0;
        foreach($query as $key => $value) {

            $array = isset($value->bm_adv_search) ? @unserialize($value->bm_adv_search) : array();
            $count = !empty($array) ? $count + count($array) : $count;
        }

        return $count;
    }




    // Query all company profiles for add/edit comps prods dropdown selector (if member)
    function list_survey_user_assigned_comp_profile() {

        // Get user id
        $user = $this->session->userdata('user');
        $user_id = isset($user['id']) ? $user['id'] : '';

        $sql = "SELECT cp.* FROM company_profile cp LEFT JOIN admin_table at on cp.company_id = at.assigned_comp WHERE at.user_id = $user_id AND cp.status <> 'inactive' GROUP BY cp.firm_name";
        $query = $this->db->query($sql);

        if ($query->num_rows() > 0) { $output = $query->result(); $query->free_result(); return $output; } else { return false; }
    }

    // Query all company profiles for add/edit comps prods dropdown selector
    function list_survey_admin_comp_profiles() {

        $sql = "SELECT * FROM company_profile WHERE 1 = 1 AND status <> 'inactive' ORDER BY firm_name asc, company_id desc LIMIT 0, 5000";
        $query = $this->db->query($sql);

        if ($query->num_rows() > 0) { $output = $query->result(); $query->free_result(); return $output; } else { return false; }
    }

    // Query all product profiles for add/edit comps prods dropdown selector
    function list_survey_get_prod_profiles($company_id = 0) {

        $sql = "SELECT company_id, product_id, product_name FROM product_profile WHERE company_id = $company_id AND prod_status <> 'inactive' ORDER BY product_name asc, product_id desc LIMIT 0, 5000";
        $query = $this->db->query($sql);

        // If no product found (or product is held); check product_profile_held db table
        if($query->num_rows() == 0) {
            $sql = "SELECT company_id, product_id, product_name FROM product_profile_held WHERE company_id = $company_id AND prod_status <> 'inactive' ORDER BY product_name asc, product_id desc LIMIT 0, 5000";
            $query = $this->db->query($sql);
        }

        if ($query->num_rows() > 0) { $output = $query->result(); $query->free_result(); return $output; } else { return false; }
    }

    // Query all products by company for product at_a_glance summary page
    function search_at_a_glace_product_summary($company_id = '', $product_family_id = '') {

        // STATEMENT REVISED: This was revised to remove the produt_family_prod_lookup table reference
        $sql = "SELECT cp.*, prod.*, reseach_cp.firm_name AS hq_name, reseach_cp.headquarters AS hq_loc"
             . " FROM product_profile prod"
             . " LEFT JOIN company_profile cp ON prod.company_id = cp.company_id"
             . " LEFT JOIN product_family_list pfl ON pfl.product_family_id = prod.product_family_id"
             . " LEFT JOIN company_profile reseach_cp ON reseach_cp.company_id = pfl.research_company_id"
             . " WHERE prod.prod_status <> 'inactive'"
             . " AND cp.company_id = " . $company_id
             . " AND prod.product_family_id = ". $product_family_id
             . " ORDER BY prod.product_name ASC"
        ;
        $query = $this->db->query($sql);

        if ($query->num_rows() > 0) { $output = $query->result(); $query->free_result(); return $output; } else { return false; }
    }
}