Outputting the name of the month instead of the number

Question

I have a query which returns 3 fields, one of which is the month as a two digit number.

I want to basically have it so if month == 1 output january, if month == 02 output febuary etc

This is what I am trying, but this does not work at all, and prevent the entire column from being displayed in PHP.

            while ($row = mysql_fetch_array($sqlstr)) {
               if ($row['theMonth']=="6") {
                  echo "<td>{June}</td>";}
                  echo "<td>{$row['sumSales']}</td>";
                  echo "<td>{$row['sumPurchases']}</td>";
                  echo "</tr>";
               }
            }

What is the correct way to do what I want, and why is what I am doing wrong?

The SQL query I am using is:

SELECT theMonth, sum(Sales) as sumSales, sum(Purchases) as sumPurchases
FROM
 ( SELECT date_format(saledate, '%Y-%m') AS theMonth, sales.cost as Sales, 0 AS Purchases
   FROM sales, products
   WHERE sales.product=products.name AND category='Food' OR category='Bocas' 
                OR category='Bebidas' OR category='Flor de cana por botellas' 
                OR category='Vino por botella' OR category='hora feliz'
  UNION ALL
   SELECT date_format(purchasedate, '%Y-%m') AS theMonth, 0 as Sales, purchases.cost as Purchases
   FROM purchases
 ) AS all_costs
group by theMonth

I don´t think I could just replace

SELECT date_format(purchasedate, '%Y-%m') AS theMonth

with

SELECT MONTHNAME(purchasedate) AS theMonth

So what would be the best way to return the month name as text in SQL?

Answer 1

There is probably a php builtin for that, but not using anything like that

$monthNames = array(1 => "January", 2 => "Febuary", 3 => "March", .... 12 => "December");
while ($row = mysql_fetch_array($sqlstr)) {
    echo "<td>{$monthNames[$row['theMonth']]}</td>";
    echo "<td>{$row['sumSales']}</td>";
    echo "<td>{$row['sumPurchases']}</td>";
    echo "</tr>";

}

Answer 2

First option would be to modify your SQL query to return the month as a name rather than (or as well as) a numeric. See MONTHNAME

Second option would be to use PHP's date functions to generate the name for you

$monthName = date('F',mktime(1,1,1,$row['theMonth'],1,2010));

Third would be to use a monthnames array, similar to zebediah's suggestion

Answer 3

.

function month_name($int) {
  return date( 'F' , mktime(1, 1, 1, (int)$int, 1) );
}
echo month_name(2); // February

Answer 4

In your SQL you can use DATE_FORMAT to convert a date to a month name:

SELECT DATE_FORMAT(NOW(), '%M')

July

The list of allowable specifiers can be found in the MySQL documentation.

(MONTHNAME should also work too.)

The reason why your current method doesn't work is because you are currently outputting both the year and the month (e.g. "2010-06") and this string doesn't compare equal to the string "6".

Answer 5

You've got a closing curly brace at the end of the line on your first echo statement. That's causing PHP to prematurely terminate the conditional block, then it has a parse error when it stumbles across your last closing curly brace since it doesn't have an opening match.

You can get the name of the month from a timestamp (if you have one) with the date function. date('F', $timestamp); see the php date function reference

Doing it in the SQL statement is probably your easiest and most performance-friendly way of handling this particular situation.