codeswitcher

As per previously, I've changed the structure of the msgs table to support the reality that contacts' phone numbers change, and that the relationship between the msgs and contacts TABLES can't be dynamic or future changes to phone numbers orphan msgs in the log.

Now that I have FIELDs fromcontactname and tocontactname in TABLE msgs, I don't need VIEW v_msgs_2 to hit TABLE contacts, as per below.

I am about to eviscerate it, so I'd like to store this wonder for posterity:

CREATE VIEW `v_msgs_2` AS
SELECT
       `msgs`.`id` AS `id`,
       `msgs`.`body` AS `body`,
       `msgs`.`created_on` AS `created_on`,
       (CASE
             WHEN (`msgs`.`tonum` = '+1617XXXXXXX') THEN 'from'
             WHEN (`msgs`.`fromnum` = '+1617XXXXXXX') THEN 'to'
             ELSE NULL
             END
             ) AS `tf`,
       (CASE

                WHEN (`msgs`.`tonum` = '+1617XXXXXXX') THEN `msgs`.`fromnum`
                WHEN (`msgs`.`fromnum` = '+1617XXXXXXX') THEN `msgs`.`tonum`
                ELSE NULL
                END
                ) AS `num`,
       (CASE
                WHEN (`msgs`.`tonum` = '+1617XXXXXXX') THEN `cf`.`name`
                WHEN (`msgs`.`fromnum` = '+1617XXXXXXX') THEN `ct`.`name`
                ELSE NULL
                END
                ) AS `name`
FROM (
        (`msgs` left join `contacts` `cf` on
                (
                     (`msgs`.`fromnum` = `cf`.`number`)
                )
        ) left join `contacts` `ct` on
               (
                        (`msgs`.`tonum` = `ct`.`number`)
                )
        )
ORDER BY
      `msgs`.`created_on`,
      `msgs`.`id`

The new improved view is:

CREATE VIEW `v_msgs_2` AS
SELECT
       `msgs`.`id` AS `id`,
       `msgs`.`body` AS `body`,
       `msgs`.`created_on` AS `created_on`,
       (CASE
             WHEN (`msgs`.`tonum` = '+1617XXXXXXX') THEN 'from'
             WHEN (`msgs`.`fromnum` = '+1617XXXXXXX') THEN 'to'
             ELSE NULL
             END
             ) AS `tf`,
       (CASE

                WHEN (`msgs`.`tonum` = '+1617XXXXXXX') THEN `msgs`.`fromnum`
                WHEN (`msgs`.`fromnum` = '+1617XXXXXXX') THEN `msgs`.`tonum`
                ELSE NULL
                END
                ) AS `num`,
       (CASE
                WHEN (`msgs`.`tonum` = '+1617XXXXXXX') THEN `msgs`.`fromcontactname`
                WHEN (`msgs`.`fromnum` = '+1617XXXXXXX') THEN `msgs`.`tocontactname`
                ELSE NULL
                END
                ) AS `name`
FROM msgs
ORDER BY
      `msgs`.`created_on`,
      `msgs`.`id`

Works great.

Next: update the view name and corresponding PHP call. ETA: done!