วันศุกร์ที่ 25 พฤศจิกายน พ.ศ. 2554

การสร้าง Query นับจำนวนกิจกรรมแต่ละเดือน/ปี

กิจกรรม

Code (PHP)
ผลที่ได้
SQL



แต่ถ้าต้องการให้แสดงผลเเบบนี้จะได้ไหมครับ

year

CODE
SELECT DISTINCT DATE_FORMAT( ACTIVITY_DATE, '%Y-%m' ) AS DISDATE, COUNT( ACTIVITY_ID ) AS Count
FROM activity
GROUP BY DATE_FORMAT( ACTIVITY_DATE, '%Y-%m' )

select
`Year`,
sum(`Jan`) as `Jan`,
sum(`Feb`) as `Feb`,
sum(`Mar`) as `Mar`,
sum(`Apr`) as `Apr`,
sum(`May`) as `May`,
sum(`Jun`) as `Jun`,
sum(`Jul`) as `Jul`,
sum(`Aug`) as `Aug`,
sum(`Sep`) as `Sep`,
sum(`Oct`) as `Oct`,
sum(`Nov`) as `Nov`,
sum(`Dec`) as `Dec`,
sum(`Total`) as `Total`
from
(select year(`ACTIVITY_DATE`) as `Year`,
case month(`ACTIVITY_DATE`) when 1 then count(`ACTIVITY_DATE`) else null end as `Jan`,
case month(`ACTIVITY_DATE`) when 2 then count(`ACTIVITY_DATE`) else null end as `Feb`,
case month(`ACTIVITY_DATE`) when 3 then count(`ACTIVITY_DATE`) else null end as `Mar`,
case month(`ACTIVITY_DATE`) when 4 then count(`ACTIVITY_DATE`) else null end as `Apr`,
case month(`ACTIVITY_DATE`) when 5 then count(`ACTIVITY_DATE`) else null end as `May`,
case month(`ACTIVITY_DATE`) when 6 then count(`ACTIVITY_DATE`) else null end as `Jun`,
case month(`ACTIVITY_DATE`) when 7 then count(`ACTIVITY_DATE`) else null end as `Jul`,
case month(`ACTIVITY_DATE`) when 8 then count(`ACTIVITY_DATE`) else null end as `Aug`,
case month(`ACTIVITY_DATE`) when 9 then count(`ACTIVITY_DATE`) else null end as `Sep`,
case month(`ACTIVITY_DATE`) when 10 then count(`ACTIVITY_DATE`) else null end as `Oct`,
case month(`ACTIVITY_DATE`) when 11 then count(`ACTIVITY_DATE`) else null end as `Nov`,
case month(`ACTIVITY_DATE`) when 12 then count(`ACTIVITY_DATE`) else null end as `Dec`,
count(`ACTIVITY_DATE`) as Total
from activity group by year(`activity_date`), month(`activity_date`))
as temp group by `Year`
 
ผลล้พธ์
sum2

วันจันทร์ที่ 7 พฤศจิกายน พ.ศ. 2554

เพิ่ม Code ใน Blogspot

After posting any source code, First we make sure that the code is correct and same as the original. For HTML or script source code, we require HTML Encoding so that blogger doesn't execute this code.The Source code format should be different from content and there should be option for copy code or print code so that user don't need to select entire code and press ctrl+c. I use "Syntaxhighligher" to display source code in blogger. It fullfills all requirement. First Layout is very pretty with line numbers and it provides "View Plain" (To display only source code in another window),"Copy to clipboard" and "Print?" options. We will see How can we use syntaxhighlighter in blogger without any hosting.

1.In the blogger,Click on Layout tab ->Edit HTML and put following things Before </head>

<link href='http://syntaxhighlighter.googlecode.com/svn/trunk/Styles/SyntaxHighlighter.css' rel='stylesheet' type='text/css'/>
<script language='javascript' src='http://syntaxhighlighter.googlecode.com/svn/trunk/Scripts/shCore.js'/>
<script language='javascript' src='http://syntaxhighlighter.googlecode.com/svn/trunk/Scripts/shBrushCpp.js'/>


2. put following things Before </body>

<script language="javascript">
dp.SyntaxHighlighter.BloggerMode();
dp.SyntaxHighlighter.HighlightAll('code');
</script>


3. encode (HTML Encode) your source code. for this, you can use this.
OR

Copy your code in notepad and replace all < in &lt; etc.

4. Put your updated code between:
<pre name="code" class="Cpp">
….My code here…
</pre>