Login

10-01-2014, 03:49 PM

[eluser]webmarkcompt[/eluser]
Hello everyone,
I am dealing with a sintax problem
I have this code working

Code:
SELECT

p.id, p.name,

p.code, 

clA.code as claCode, 

sclA.code as sclaCode, 

clB.code as clbCode, 

sclB.code as sclbCode, 

clC.code as clcCode, 

sclC.code as sclcCode

FROM 

             products p

                 INNER JOIN catleathers clA

                    ON p.catleather_id_a = clA.id

                 INNER JOIN subcatleathers sclA

                    ON p.subcatleather_id_a = sclA.id

                 INNER JOIN catleathers clB

                    ON p.catleather_id_b = clB.id

                 INNER JOIN subcatleathers sclB

                    ON p.subcatleather_id_b = sclB.id

                 INNER JOIN catleathers clC

                    ON p.catleather_id_c = clC.id

                 INNER JOIN subcatleathers sclC

                   ON p.subcatleather_id_c = sclC.id

But I need something like this, and is not working

Code:
function getdatatableajaxcost()

   {

    $this->load->library('datatables');

    $this->datatables

  ->select("

   products.id as productid, 

   products.image as image, 

   products.code as code, 

   products.name as name, 

   categories.name as cname, 

   subcategories.name as subcategory, 

   products.cost, products.price,  

   products.lining as lining, 

   COALESCE(quantity, 0) as quantity, 

   alert_quantity, 

    clA.code as claCode,

                sclA.code as sclaCode,

                clB.code as clbCode,

                sclB.code as sclbCode,

                clC.code as clcCode,

                sclC.code as sclcCode,

                catSoleA.code as catSoleACode,

                subcatSoleA.code as subcatSoleACode,

                catSoleB.code as catSoleBCode,

                subcatSoleB.code as subcatSoleBCode,

                catSoleC.code as catSoleCCode,

                subcatSoleC.code as subcatSoleCCode,

   products.details", FALSE)

   ->from('products')

   ->join('categories', 'products.category_id=categories.id', 'left')

   ->join('subcategories', 'products.subcategory_id=subcategories.id', 'left')

   ->join('catleathers clA', 'products.catleather_id_a = clA.id', 'INNER')

            ->join('subcatleathers sclA', 'products.subcatleather_id_a = sclA.id', 'INNER')

            ->join('catleathers clB', 'products.catleather_id_a = clB.id', 'INNER')

            ->join('subcatleathers sclB', 'products.subcatleather_id_a = sclB.id', 'INNER')

            ->join('catleathers clC', 'products.catleather_id_a = clC.id', 'INNER')

            ->join('subcatleathers sclC', 'products.subcatleather_id_a = sclC.id', 'INNER')

            ->join('catsoles catSoleA', 'products.catsole_id_a = catSoleA.id', 'INNER')

            ->join('subcatsoles subcatSoleA', 'products.subcatsole_id_a = subcatSoleA.id', 'INNER')

            ->join('catsoles catSoleB', 'products.catsole_id_b = catSoleB.id', 'INNER')

            ->join('subcatsoles subcatSoleB', 'products.subcatsole_id_b = subcatSoleB.id', 'INNER')

            ->join('catsoles catSoleC', 'products.catsole_id_c = catSoleC.id', 'INNER')

            ->join('subcatsoles subcatSoleC', 'products.subcatsole_id_c = subcatSoleC.id', 'INNER')

   ->join('tbl_lining', 'products.lining=tbl_lining.id', 'left')

   ->group_by("products.id");

   $this->datatables->add_column("image", 

   "<center>

   <a >config->base_url()."assets/uploads/$2' data-lightbox='image-1'><img >config->base_url()."assets/uploads/$2' width='100px' height='80px' title='Clique para aumentar'></a>

            </center>", "productid, image");

   $this->datatables->add_column("Actions", 

   "<center><a id='$4 - $3' href='#'>lang->line("view_barcode")."' class='barcode tip'><i class='icon-barcode'></i></a> 

   <a href='#' class='tip'>lang->line("product_details")."'><i class='icon-fullscreen'></i></a> 

   <a href='index.php?module=products&view=edit&id;=$1' class='tip'>lang->line("edit_product")."'><i class='icon-edit'></i></a> <a href='index.php?module=products&view=delete&id;=$1'>lang->line('alert_x_product') ."')\" class='tip' title='".$this-&gt;lang-&gt;line("delete_product")."'><i class='icon-trash'></i></a></center>", "productid, image, code, name");

  $this->datatables->unset_column('productid');

  $this->datatables->unset_column('image');

     echo $this->datatables->generate();

   }

Heres the original code working

Code:
function getdatatableajaxcost()

   {

    $this->load->library('datatables');

    $this->datatables

   ->select("products.id as productid, products.image as image, products.code as code, products.name as name, categories.name as cname, subcategories.name as subcategory, products.cost, products.price,  products.leather as leather, products.lining as lining, products.sole as sole, COALESCE(quantity, 0) as quantity, alert_quantity, products.details", FALSE)

   ->from('products')

   ->join('categories', 'products.category_id=categories.id', 'left')

   ->join('subcategories', 'products.subcategory_id=subcategories.id', 'left')

   ->join('tbl_leather', 'products.leather=tbl_leather.id', 'left')

   ->join('tbl_lining', 'products.lining=tbl_lining.id', 'left')

   ->join('tbl_sole', 'products.sole=tbl_sole.id', 'left')

   $this->datatables->add_column("image", 

   "<center>

   <a >config->base_url()."assets/upload

10-01-2014, 04:00 PM

[eluser]syrys[/eluser]
im unsure if this is correct, but worth a try anyway. I dont really know what this is:

Code:
$this->load->library('datatables')

Assuming its a database object?

Anyway, if you just create a new model, and within the model do something like:

Code:
function someFunction(){

$this->db->select('....')->from('...');

$this->db->join('....');

$this->db->join('....');

$this->db->join('....');

$this->db->join('....');

$res = $this->db->get()->result();

var_dump($res); //see if this is what you want

//if its not, do this

$q = $this->db->last_query(); //this should give you the last query, print this out and see if it built the correct sql

var_dump($q); 

}

10-01-2014, 04:08 PM

[eluser]webmarkcompt[/eluser]
I updated the whole code above. Datatables is a libray http://www.datatables.net/

The difficulty here for me is to make the joins correctly...

I'm not sure I undestood your solution. This code is in controller.

10-01-2014, 05:31 PM

[eluser]syrys[/eluser]
[quote author="webmarkcompt" date="1412204929"]I updated the whole code above. Datatables is a libray http://www.datatables.net/

The difficulty here for me is to make the joins correctly...

I'm not sure I undestood your solution. This code is in controller.[/quote]
Sorry i was mistaken. I suppose this depends on the datatables library, which im not familiar with. I assumed it was a normal CI database object due to syntax similarity (maybe it is).

My code above is how you would manually get the data from the DB using a CI database object (or a model). The most useful command was the last couple of lines where it actually gets you the last run db query so you can look for the mistakes if you know your sql.

10-02-2014, 07:10 AM

[eluser]webmarkcompt[/eluser]
Thanks syrys

I am not very familiar with the codeigniter... This function is called on a index page and shows all records in database according to this query.

The query works fine when I run it on mysql.. But here I need to put the joins with this similar syntax. Such as

Code:
->join('subcatleathers sclA',

with the inner join.

The code I've tried not working, I believe the problem is in the syntax... But I do not have sufficient knowledge solve this.

10-02-2014, 09:41 AM

[eluser]webmarkcompt[/eluser]
Solved!

Thanks!

I'll be back in soon! Smile

Code:
->select("

   products.id as productid, 

   products.image as image, 

   products.code as code, 

   products.name as name, 

   categories.name as cname, 

   subcategories.name as subcategory, 

   products.cost, 

   products.price,  

   clA.code as claCode,

            sclA.code as sclaCode,

            clB.code as clbCode,

            sclB.code as sclbCode,

            clC.code as clcCode,

            sclC.code as sclcCode,

   catSoleA.code as catSoleACode,

            subcatSoleA.code as subcatSoleACode,

            catSoleB.code as catSoleBCode,

            subcatSoleB.code as subcatSoleBCode,

            catSoleC.code as catSoleCCode,

            subcatSoleC.code as subcatSoleCCode,

   products.details", FALSE)

   ->from('products')

   ->join('categories', 'products.category_id=categories.id', 'left')

   ->join('subcategories', 'products.subcategory_id=subcategories.id', 'left')

   ->join('catleathers clA', 'products.catleather_id_a = clA.id', 'INNER')

            ->join('subcatleathers sclA', 'products.subcatleather_id_a = sclA.id', 'INNER')

            ->join('catleathers clB', 'products.catleather_id_b = clB.id', 'INNER')

            ->join('subcatleathers sclB', 'products.subcatleather_id_b = sclB.id', 'INNER')

            ->join('catleathers clC', 'products.catleather_id_c = clC.id', 'INNER')

            ->join('subcatleathers sclC', 'products.subcatleather_id_c = sclC.id', 'INNER')

            ->join('catsoles catSoleA', 'products.catsole_id_a = catSoleA.id', 'INNER')

            ->join('subcatsoles subcatSoleA', 'products.subcatsole_id_a = subcatSoleA.id', 'INNER')

            ->join('catsoles catSoleB', 'products.catsole_id_b = catSoleB.id', 'INNER')

            ->join('subcatsoles subcatSoleB', 'products.subcatsole_id_b = subcatSoleB.id', 'INNER')

            ->join('catsoles catSoleC', 'products.catsole_id_c = catSoleC.id', 'INNER')

            ->join('subcatsoles subcatSoleC', 'products.subcatsole_id_c = subcatSoleC.id', 'INNER')

 ->group_by("products.id");