Processing SQL Results

[eluser]harpster[/eluser]
Yet another newbe question... I'm working on my first CI App and it's been a tough learning process for me but I'm slowly getting the hang of it especially with the help of this forum. I have a few questions but will post them separately.

Now that I've done the MVC and have several db queries I need to process some of that query data... things like stripping leading zero's from time fields and changing a 0 or 1 value to the actual name to be displayed in a view. (like 1=Group Ride and 0= Solo Ride).

So right now I'm doing this processing in the view prior to displaying in a table. It works fine and I'm used to doing it that way from my procedural php apps. But CI give a lot of choices for things and this is where I usually get a bit frustrated as I don't know the best or preferred way to handle seemingly simple things like this. So just wondering what most of you do.... process the query results in the Model, Controller, or View? Or does it even matter? Thanks for your valued opinions!

[eluser]InsiteFX[/eluser]
Database stuff should be done in the Model.

InsiteFX

[eluser]oldmatt[/eluser]
I try to format as much as I can in the Query itself.

For example:

Code:

SELECT
   CONCAT(user_name_first,' ',user_name_last) user,
   TIME_FORMAT(clockin,%l:%i %p) AS clockin,
   IF(active=0,'Inactive','Active') AS status
FROM
   users
WHERE
   user_id = 125;

The above query might return results looking like:

John Smith 1:43 PM Inactive
Mary Jones 12:56 AM Active
Ben Roads 3:12 PM Active

So when the data gets returned the PHP, it is already formatted correctly. That is, if I understood you correctly.

[eluser]harpster[/eluser]
Thanks that was very helpful especially the use of the IF statement in the query. I changed my query from this

Code:

$query = $this->db->query("SELECT * FROM cycling WHERE user = $user_id ORDER BY ridedate");

to this, but I did have to add single quotes around the time & date formatting '%c %d' to get it error free.

Code:

$query = $this->db->query("SELECT location, id, distance, ridetime, avespeed,
                                    maxspeed, avehr, maxhr, avecad, notes, zone,
                                    rating, location, ridename,
                                    IF(`group`=0,'','Y') AS `group`,
                                    IF(org=0,'','Y') AS org,
                                    IF(weight=0.0,'',weight) AS weight,
                                    IF(avecad=0,'','') AS avecad,
                                    IF(maxhr=0,'',maxhr) AS maxhr,
                                    IF(avehr=0,'',avehr) AS avehr,
                                    DATE_FORMAT(ridedate, '%c/%d') AS ridedate,
                                    DATE_FORMAT(ridetime, '%l:%i:%s') AS ridetime
                                    FROM cycling
                                    WHERE user = $user_id
                                    ORDER BY ridedate");