3 Replies Latest reply on Dec 4, 2006 8:45 AM by pixelfused

    I can do it in PHP but how with CF?

      I have a sizable database that keeps track of profit per job that goes back several years. What I need to do is create a table layout of:

      1) Customer's ID
      2) Total $ from 2004
      3) Total $ from 2005
      3) Total $ from 2006

      In PHP I do a fair amount of query sorting with arrays, but since CF doesn't support testing an empty array element that seems to be out.

      The process I'm using is basically this: (in PHP)

      SELECT SysID, ProfitAmount, Year
      FROM Table
      WHERE year = '2004' or year = '2005' or year = '2006'

      while($row = mysql_fetch_assoc($query){
      $job[$row[SysID]][$row['Year']] += $row['ProfitAmount'];

      It's possibIe that a client didn't do business one year, so potentially this can cause empty elements:

      $job[1234][2004] = 100.00
      $job[1234][2005] = NULL
      $job[1234][2006] = 300.00

      I've tried using GROUP BY in SQL as well as QofQ but I'm not having much luck. I really want to give CF a fair shot, it seems like it can do some impressive things, but something so simple to do in PHP seems almost impossible to do here outside of running 4 queries, 4 QofQ (to join results) and a bunch of cfcatches so an empty array elements won't crash the whole thing. What am I missing here?

        • 1. Re: I can do it in PHP but how with CF?
          Are you just trying to get a each clients profit for each of the three years? Couldn't you do this all with SQL?

          SELECT SysID, [Year], SUM(ProfitAmount) AS YearProfit
          FROM Table
          WHERE [Year] = '2004' OR [Year] = '2005' OR [Year] = '2006'
          GROUP BY SysID, [Year]
          • 2. Re: I can do it in PHP but how with CF?
            Dan Bracuk Level 5
            Quotes around the year? That's scary. In any event, if this query:
            SELECT SysID, ProfitAmount, Year as TheYear
            FROM Table
            WHERE year = '2004' or year = '2005' or year = '2006'

            returned rows for 2004 and 2006 but not 2005, but you want to display all 3 years, here is one way to do it. Let's call that query q1.

            YearsIWant = "2004,2005,2006";
            for (i = 1; i lte ListLen(YearsIWant); i = 1 + 1) {
            ThisYear = ListGetAt(YearsIWant, i);
            // if ThisYear is not in the query, add it.
            if (ListFind(ValueList, q1.TheYear) is 0) {
            x = QueryAddRow(q1, 1);
            x = QuerySetCell(q1, "TheYear", ThisYear);
            x= QuerySetCell(q1, "ProfitAmount", 0);
            x = QuerySetCell(q1, "SysId", "something");
            } // end if
            } // end loop

            <cfquery name ="q2" dbtype = "Query">
            select SysId, ProfitAmount, TheYear
            from q1
            order by TheYear

            Then output q2 as you see fit.
            • 3. Re: I can do it in PHP but how with CF?
              pixelfused Level 1
              Thanks Dan, that's what I was looking for. I don't know if the last guy just didn't understand what datatypes are for or what, but needless to say, I'm getting very familiar with Convert().