Welcome Guest, Not a member yet? Register   Sign In
Solved : Newbie question - Working with models
#1

[eluser]ywftdg[/eluser]
I been bombarding the board lately, sorry I'm hitting a lot of walls lately, still very new with CI. All morning, trying to understand working in a model with a massive query that jumps around a lot. I am trying to rewrite some old php code, into a CI model now. But it seems, anytime I do a if statement on row results, it kills the model, page breaks. I was trying this:

Code:
function get_bundlestats($id,$datepost)    {
    
        $this->db->select('pSku, pName, pCost, pPrice, pSkulist');    //     grab products with skulists
        $this->db->where("pSkulist <> ''");
        $query = $this->db->from('products');
        
        
        if ($query->num_rows() > 0)    {
        
            $skulist = $query->row('pSkulist');
            
            $this->db->select('sum(pPrice)');
            $this->db->where('psku in $skulist');
            $this->db->from('products');
        }
        
       }

It breaks right when I start the if statement to check row results. Is this the wrong way to approach queries in a model? If not, why would this code be breaking? I have basically ben trying to move over the old code into this new format, sort of a translation if you may, and its quite a task. I thought it would have been easy, since the queries and function is before me in the old php code.
#2

[eluser]xwero[/eluser]
The sql statements are executed with 4 methods : get (get_where), insert, update and select. if one of those methods isn't added the only thing you have is some data that is stored in the database library. So your code has to look like this to work
Code:
$this->db->select('pSku, pName, pCost, pPrice, pSkulist');    //     grab products with skulists
        $this->db->where("pSkulist <> ''");
        $query = $this->db->get('products');
#3

[eluser]ywftdg[/eluser]
Thanks xwero, I rewrote the item to this like, which returned correctly, and is much cleaner:

Code:
function get_bundlestats($id,$datepost)    {
    
        $this->db->select('pSku, pName, pCost, pPrice, pSkulist');    //     grab products with skulists
        $this->db->where("pSkulist <> ''");
        $this->db->from('products');

        $this->db->group_by('pSku,pSku'); //group the items, using same reference for both

        $this->db->select('sum(pPrice) as BaseBundlePrice');
        $this->db->from('products');
    
    
        $querybundle = $this->db->get();
        return $querybundle->result();
        
       }

I am assuming, there is no need (at least in my case) to be doing the if statement, right? Because, the full query is necessary to return any value what so ever. Basically, there is no else case in my query.
#4

[eluser]xwero[/eluser]
Your code looks a bit odd because it looks like you are doing two sql statements but the AR library cleans it up for you. But you better write it like this
Code:
$this->db->select('pSku, pName, pCost, pPrice, pSkulist,sum(pPrice) as BaseBundlePrice');    //     grab products with skulists
        $this->db->where("pSkulist <> ''");
        $this->db->from('products');

        $this->db->group_by('pSku,pSku'); //group the items, using same reference for both
$querybundle = $this->db->get();
        return $querybundle->result();
#5

[eluser]ywftdg[/eluser]
What sort of confuses me here is why is the group necessary then, if we are only doing one select? For exmaple, if I remove the group it brings up the error:

Code:
Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

SELECT `pSku`, `pName`, `pCost`, `pPrice`, `pSkulist`, sum(`pPrice`) as BaseBundlePrice FROM (`products`) WHERE `pSkulist` <> ''
#6

[eluser]xwero[/eluser]
The group is necessary for the sum function
#7

[eluser]ywftdg[/eluser]
Ahh ok, the group is basically saying for each result, group the reslts by sku, then totall em up ?

Now when I get this doing the first phase, I then have the second part. The skulist would be a list of items, which would contain things, that needs to be referenced in the next query. I guess now, my other big misunderstanding with models, is now how do I pass that skulist result, into the next query? Would I want to do the second query like this? I am still trying to wrap my head around how the best process/order is for all this.


Code:
//     grab products with skulists
        $this->db->select('pSku, pName, pCost, pPrice, pSkulist as skulist, sum(pPrice) as BaseBundlePrice, pPrice/sum(pPrice) as discount');
        $this->db->where("pSkulist <> ''");
        $this->db->from('products');
        $this->db->group_by('pSku');
        
// start the second query
        $this->db->select('pSku, pName, pCost, pPrice');
        $this->db->where("pSku in ($skulist)");
        $this->db->where('products.pDesignerId', $id);
#8

[eluser]xwero[/eluser]
If you are busy wrapping your head around the queries you have to do try to write them first to see if they have the desired result before you cut them up in AR library pieces. At the moment your are doing two things at the same time which can be confusing because you don't really know where it goes wrong.
As an alternative you can write the whole statement in the db->query method like you would do in the mysql_query php function.

By grouping you only get the first record of the pSku so you will not have a complete list of the pSkus. And i don't know where that $id variable is coming from? So there are a few holes in your information which doesn't allow me to get you on the right track.
#9

[eluser]ywftdg[/eluser]
The $id comes from function get_bundlestats($id,$datepost) {

My code works as: The first part is looking for products that have a value in pSkulist. If values, grab those items as skulist, as well grab the sum of what they all are worth. Then my second part, looks to see if there are 'designers' of those products, but it has to check the skulist and see if there is a sku (for that designer) in the list (array). So where my gap is, how to reference that first result of skulists, into the next query. I got an understanding of what needs to be done and what tables link, etc. What I dont understand, is how can I call a value named in a other select, into the next select.

Right now, when I use tat skulist, it just returns Message: Undefined variable: skulist
#10

[eluser]ywftdg[/eluser]
Maybe it will all make more sense if I posted the old php code. This code is a little wild, but it should help show how I am trying to say ok do this first thing. Now I got these values, go on to the next query. I just don't get how in CI, you go from one query to the next. Where below makes total sense to me, because I can call my rows from previous queries, etc.

Code:
$query = "select dpercentages from designer where ddesignerid = $DesignerId";
$result = mysql_query($query);
if($row = mysql_fetch_row($result)) {
    $BasePercentage = $row[0];
} else {
    $BasePercentage = 0;
}

$query = "select psku, pname, pcost, pprice, pskulist from products where pskulist <> ''";
$result = mysql_query($query);
$GrandTotal = 0;
$GrandQty = 0;
$GrandSales = 0;

// !!!!!!!!!!!!!!!!!!!!



while($row = mysql_fetch_row($result)) {

    $BundleTotal = 0;
    $BundleProfit = 0;


    // Get the non-discounted total for this bundle
    $query2 = "select sum(pprice) from products where psku in ($row[4])";
    $result2 = mysql_query($query2);
    
    if($row2 = mysql_fetch_row($result2)) {
        $BaseBundlePrice = $row2[0];
    } else {
        $BaseBundlePrice = 0;
    }
    
    $Discount = $row[3] / $BaseBundlePrice;



    $query2 = "select psku, pname, pcost, pprice from products where psku in ($row[4]) and pdesignerid = $DesignerId";
    $result2 = mysql_query($query2);
    if(mysql_num_rows($result2) > 0) {
    while($row2 = mysql_fetch_row($result2)) {

        $query3 = "select sum(oquantity) from orderdetail inner join receipt on oOrderNum = rOrderNum where osku = '$row[0]' and " . $query_date;
        $result3 = mysql_query($query3);
        if($row3 = mysql_fetch_row($result3)) {
            $BundleProfit += ($row2[3] * $Discount) * ($BasePercentage / 100);
            $BundleTotal += ((($row2[3] * $Discount) * $row3[0]) * ($BasePercentage / 100));
            }

    }

            $GrandQty += $row3[0];
    $GrandTotal += $BundleTotal;
    $GrandSales += $row[3] * $row3[0];




Theme © iAndrew 2016 - Forum software by © MyBB