Welcome Guest, Not a member yet? Register   Sign In
Session variable needs to change all DB queries.. any ideas ?

Hello guys, hope you're all fine ! Smile

well, I'm sitting here now trying to figure out a good way to do as i said in the title. Let me explain it:

This project i'm working on has many companies, which have a category, many subcategories and they can be from different cities.

When a user starts navigating in this site, he will first see a <select> component in which he will choose his city and then he will see only companies from that city he chose. He will also be able to change the city inside the pages as well, on the fly.

I made a controller which will handle the city changes, but it's not clear to me how should I proceed on the model to get that. Tried some conditions checking if the session var "city" is present, then changing the query to match only with that city, something like that, but got no positive results yet.. :/

Any ideas on how I can do this on a good way ?

Im not sure how you managed to get your controller working without talking to your model (I assume all your cities and companies are stored in a relational database?)...

Anyway, you could try adding the city name to the end of your URL. Then you can access that in your controller and pass it to your model, thus being able to access companies associated to that city. Check out the video tutorials 'build a blog in 20 minutes', it shows you the logic behind it.

Or yes, you could store the city as a session, but that runs into the trouble of if your user has cookies disabled (CI by default stores its session data inside a cookie, but you can set it up to store it in a DB instead, but that's another story.)

Or you could use hidden form fields as well passing it from controller to views, but that can get tedious, especially in anything other than a small application.

Perhaps post your code so we can see what you have, just dont forget to put it inside the code [] tags.

The simple way i can think right now is :
1. In your form, which send the category/sub-category, include one hidden fields (named selected_city or something like that).
2. Then everytime user change the city, create a callback function in javascript, to modify the value of the hidden input. This will completely easy, if you use some popular js framework (like jQuery, dojo or extjs).
3. Now, everytime user submitting the form, its should contain the selected city also.

thanks for your fast reply guys ! Smile

@toopay your idea is good and quite simple, I agree, really thinking bout how should I use it in my case, but as boltsabre said, it's not going to be really a small application so that could create some mess in the long run...


I'll try to explain better and post something of what I have so you can get the general idea... I think my problem is more with the logic thing than with the code.. let's put it this way

first, the tables structures (simplified so you understand):


-cat_itg_id (foreign key)

tbl_companies (simplified for the situation)

-cmp_itg_id (foreign key)
-sub_itg_id (foreign key)

what i have ready now is the following: When the user enters the page, before he hits the company itself, he has to navigate through the categories (all shown on the front view with the number of companies belonging to it eg. "Animals & Pets(25)"), then the subcategories inside the category (which is shown the same way as categories) and then the companies, filtered by the choices the user made. Do you guys get that? These 4 tables I made are related and working together all the time. Plus, I have to filter the city he chose also, and include that in the query. This is what i'm having trouble with. So many "joins" are making me go nuts. XD

Now I'll post the code i made in my model, a main model with kind of a function where i pass an array with what i want in a query and it will return me the query ready to be used by the controller. I made this way so it could be easier to deal with all those joins (donou if it's a good practice tho) :

class Main_model extends CI_Model {

    function getTblFields($configs=""){
        $default = array(
            'table' => '',
            'fields' => '*', //(array)
            'join' => '', //(array('join1'=>'table','ON statement','join type(left, right..)', 'join2'...)
            'where' => '',
            'limit' => '',
            'group_by' => '',
            'order_by' => ''
        if($configs != "") $options = array_merge($default, $configs);

        $opts = (object) $options;

            foreach($opts->join as $key => $joinTables) :
        if($opts->where) $this->db->where($opts->where);
        if($opts->order_by) {
        if($opts->group_by) $this->db->group_by($opts->group_by);
        if($opts->limit) $this->db->limit($opts->limit);

        $query = $this->db->get();

        return $query;

Here goes an example of what i have in the home-page using this model
Front Controller:

class Home extends CI_Controller {
    function __construct() {
    function index(){
        $options_categories = array(
            'table' => 'tbl_categories',
            'order_by' => array('tbl_categories.cat_vch_nome','asc')
        $data['categories'] = $categories = $this->main->getTblFields($options_categories);

        $options_categories['fields'] = array('tbl_categories.*', 'tbl_subcateg.sub_vch_nome', 'tbl_companies_subcateg.emp_itg_id', 'tbl_companies.emp_vch_cidade');
        $options_categories['join'] = array(
                'join1' => array('tbl_subcateg','tbl_categories.cat_itg_id = tbl_subcateg.cat_itg_id','left'),
                'join2' => array('tbl_companies_subcateg','tbl_subcateg.sub_itg_id = tbl_companies_subcateg.sub_itg_id','left'),
                'join3' => array('tbl_companies','tbl_companies_subcateg.emp_itg_id = tbl_companies.emp_itg_id','left')
        $options_categories['group_by'] = 'tbl_companies_subcateg.emp_itg_id';

        foreach ($categories->result() as $category) :
        $options_categories['where'] = "tbl_categories.cat_itg_id = $category->cat_itg_id AND tbl_companies_subcateg.emp_itg_id IS NOT NULL";
        $total = $this->main->getTblFields($options_categories);
        $data['total'][$category->cat_itg_id] = $total->num_rows();


and then the Home view:
<div id="categories">
        &lt;?php foreach($categories->result() as $category): ?&gt;
        &lt;? if($total[$category->cat_itg_id] >= 1) { ?&gt;
        <li><a >cat_vch_url;?&gt;" title="&lt;?=$category->cat_vch_name;?&gt;">&lt;?=$category->cat_vch_name." <strong>(".$total[$category->cat_itg_id].")</strong>";?&gt;</a></li>
        &lt;? } ?&gt;
        &lt;? endforeach; ?&gt;
        <li class="clear" />

Based on the code you can see ( feel free to alert me of anything wrong you see there or bad practices, please, don't forget i'm a newbie here Tongue ) where u guys suggest me to make the city filter? On the model ? On the controller calls ? No matter where i have to store the selected city, if in a hidden field or a session variable, what i really wanna know is where to put that city filter and how... feel free to suggest changes in my code. If you need any better explanation, just ask me, English isn't my native lang, so i might have said something weird in this huge post(i probably did, i know haha.. /facepalm)

thanks in advance ! cheers

Well, that is a doozie of a pickle. I'm not great with CI/coding in general (well, lets just say I'm not a natural like some, but I can get stuff done with some playing around), so other people may have a better suggestion?

I'd consider re-organising your model and controller, seems like your trying to do too much in one function - make it more modular. First in your model I'd make one function for your first view (which lists all the categories (with the count of associated companies -> Animal and Pets (24)).

public function getCategories(){
   // sql statement here to get categories and their company count
   return $data

And a controller function to get this model data and pass it to a view
public function index(){  //because it's your main view, you can just use index, or name it something else if you want
   // load model, actually you have this in the constructor, so that's fine there.
   // call model function
   $data[categories] = $this->model->getCategories;
   $this->load->view('mainView', $data);

And in your view, just loop thru your $categories variable to generate your 'select' menu, using the category id as the value of each option. This way when your form gets posted to another controller you have the category id. Okay, so user selects a category, and they get another 'select' menu yes? with all the sub categories of the main category? Am I following this correctly????

Well, user selects main category, you need another controller to handle this
pucblic function processMainCategorySelection(){
   // do form validation here, basically check that you have a value from your main category select menu, and assign post value to a variable, $mainCatId
   // now pass this value to another model function
   $data['getSubCat'] = $this->modelName->getSubCategories($mainCatId);

   // pass sub cats to view and load
   $this->load->view('subCatView', $data);

And the model function to get the sub categories
public function getSubCategories($catId){
   // sql here, ie. $data "SELECT * FROM subCatTable WHERE mainCatId = ".$catId;
   return $data;

So you get the idea, breaking it down into many smaller functions. Not sure at what part the user must select the city, but where ever it is, just pass it from the form to your controller, validate it in your controller, and then pass it to your model to build your sql statement.

Look, its really late here, maybe I missed the point completely, and my code above is crap, it's all just pseudo so you get an idea of the logic of breaking it down into smaller chunks/functions, and it also helps to keep all your db logic in the model, rather than half in the controller like you have above. Hope it's of some help. I'll check back tomorrow after a good night sleep.

man, thanks soo much for your will to help out, even with all this code posted... I think on a late time like when you posted it's not easy to read all that and figure out all my logic from scratch hehe Smile

Now man, no, you didn't missed the point, you got it pretty good ... the system flow you described is correct and that is what i have ready here.
Now that you understand better what i have i can ask my question clearer. What I wanted to know is how could I add the "city" condition to my SQL statements, without having to do it controller by controller, in every function that i call the companies, which are all related to the other tables?

I wished to do that one time only, would even make conditions in the model if needed to check if we're accessing companies table and then add the city WHERE statement to those queries so if i happened to change that, I would only change one line, not having to go thru all the controllers to do so.

on the "breaking down the model" part, yeah, I understand that and I think i'm probably doing a bad practice on mine, but I tried before using something quite like that, but when I had many tables and various situations to get data from them, ended up with 'n' get functions, with 'n' parameters each and that became quite a mess too haha ! Tongue

The reason i did it like that is cause I like things the most generic they can be. If one function can serve another 2, 3 or 4, that's fine for me. This helps not having to copy/paste similar code just cause , let's say, page 1 needs one thing and page 2 needs the same thing but using another table. Instead of pasting the same code in the other get function, better use one who does the same thing for both while allowing you to change the table you want to get the data from. At least for me, it works to help staying organized

BTW i'm still open for more suggestions, I figured a way through that problem, but it's not quite like i wanted, changed it controller by controller from what was ready here.

@boltsabre thank you very much for your time, patience and your suggestions, I'll think about a way of using some of em surely. Smile

Theme © iAndrew 2016 - Forum software by © MyBB