Kategori arşivi: Mysql

Mysql, Php

Unlimited level of menu through PHP and mysql

Bir yorum yapın

 

SELECT id, parent_id, title, link, position FROM menu_item ORDER BY parent_id, position;

 

$html = '';
$parent = 0;
$parent_stack = array();

// $items contains the results of the SQL query
$children = array();
foreach ( $items as $item )
    $children[$item['parent_id']][] = $item;

while ( ( $option = each( $children[$parent] ) ) || ( $parent > 0 ) )
{
    if ( !empty( $option ) )
    {
        // 1) The item contains children:
        // store current parent in the stack, and update current parent
        if ( !empty( $children[$option['value']['id']] ) )
        {
            $html .= '<li>' . $option['value']['title'] . '</li>';
            $html .= '<ul>'; 
            array_push( $parent_stack, $parent );
            $parent = $option['value']['id'];
        }
        // 2) The item does not contain children
        else
            $html .= '<li>' . $option['value']['title'] . '</li>';
    }
    // 3) Current parent has no more children:
    // jump back to the previous menu level
    else
    {
        $html .= '</ul>';
        $parent = array_pop( $parent_stack );
    }
}

// At this point, the HTML is already built
echo $html;
Mysql

MySql Partitioning Örnek

Bir yorum yapın 
ALTER TABLE `TABLOMUZ` DROP PRIMARY KEY, ADD PRIMARY KEY (`id`,`tarih`);

primary key i silip, primary keyi,tarih ile birleştiricez,

ALTER TABLE `TABLOMUZ`
      PARTITION BY RANGE(TO_DAYS(`tarih`))(      

      PARTITION `2011` VALUES LESS THAN (TO_DAYS('2012-01-01')),
      PARTITION `2012` VALUES LESS THAN (TO_DAYS('2013-01-01')),
      PARTITION `2013` VALUES LESS THAN (TO_DAYS('2014-01-01')),      

      PARTITION `201401` VALUES LESS THAN (TO_DAYS('2014-02-01')),
      PARTITION `201402` VALUES LESS THAN (TO_DAYS('2014-03-01')),
      PARTITION `201403` VALUES LESS THAN (TO_DAYS('2014-04-01')),
      PARTITION `201404` VALUES LESS THAN (TO_DAYS('2014-05-01')),
      PARTITION `201405` VALUES LESS THAN (TO_DAYS('2014-06-01')),
      PARTITION `201406` VALUES LESS THAN (TO_DAYS('2014-07-01')),
      PARTITION `201407` VALUES LESS THAN (TO_DAYS('2014-08-01')),
      PARTITION `201408` VALUES LESS THAN (TO_DAYS('2014-09-01')),
      PARTITION `201409` VALUES LESS THAN (TO_DAYS('2014-10-01')),
      PARTITION `201410` VALUES LESS THAN (TO_DAYS('2014-11-01')),
      PARTITION `201411` VALUES LESS THAN (TO_DAYS('2014-12-01')),
      PARTITION `201412` VALUES LESS THAN (TO_DAYS('2015-01-01')),       

      PARTITION `201501` VALUES LESS THAN (TO_DAYS('2015-02-01')),
      PARTITION `201502` VALUES LESS THAN (TO_DAYS('2015-03-01')),
      PARTITION `201503` VALUES LESS THAN (TO_DAYS('2015-04-01')),
      PARTITION `201504` VALUES LESS THAN (TO_DAYS('2015-05-01')),
      PARTITION `201505` VALUES LESS THAN (TO_DAYS('2015-06-01')),
      PARTITION `201506` VALUES LESS THAN (TO_DAYS('2015-07-01')),
      PARTITION `201507` VALUES LESS THAN (TO_DAYS('2015-08-01')),
      PARTITION `201508` VALUES LESS THAN (TO_DAYS('2015-09-01')),
      PARTITION `201509` VALUES LESS THAN (TO_DAYS('2015-10-01')),
      PARTITION `201510` VALUES LESS THAN (TO_DAYS('2015-11-01')),
      PARTITION `201511` VALUES LESS THAN (TO_DAYS('2015-12-01')),
      PARTITION `201512` VALUES LESS THAN (TO_DAYS('2016-01-01')),             

      PARTITION `2016` VALUES LESS THAN MAXVALUE 
);

http://pento.net/2011/04/28/partitioning-the-wordpress-comments-table/
http://www.pythian.com/blog/using-mysql-partitioning-instead-of-merge-tables/

Mysql

MySQL/Pivot table

Bir yorum yapın

“pivot table” or a “crosstab report”

(Note: this page needs to be wikified)

SQL Characteristic Functions: Do it without “if”, “case”, or “GROUP_CONCAT”. Yes, there is use for this…”if” statements sometimes cause problems when used in combination.

The simple secret, and it’s also why they work in almost all databases, is the following functions:

sign (x) returns -1,0, +1 for values x < 0, x = 0, x > 0 respectively
abs( sign( x) ) returns 0 if x = 0 else, 1 if x > 0 or x < 0
1-abs( sign( x) ) complement of the above, since this returns 1 only if x = 0

Quick example:   sign(-1) = -1,  abs( sign(-1) ) = 1,  1-abs( sign(-1) ) = 0
 CREATE TABLE exams (
        pkey int(11) NOT NULL auto_increment,
        name varchar(15),
        exam int,
        score int,
        PRIMARY KEY  (pkey)
      );

      insert into exams (name,exam,score) values ('Bob',1,75);
      insert into exams (name,exam,score) values ('Bob',2,77);
      insert into exams (name,exam,score) values ('Bob',3,78);
      insert into exams (name,exam,score) values ('Bob',4,80);

      insert into exams (name,exam,score) values ('Sue',1,90);
      insert into exams (name,exam,score) values ('Sue',2,97);
      insert into exams (name,exam,score) values ('Sue',3,98);
      insert into exams (name,exam,score) values ('Sue',4,99);

mysql> select * from exams;
+------+------+------+-------+
| pkey | name | exam | score |
+------+------+------+-------+
|    1 | Bob  |    1 |    75 |
|    2 | Bob  |    2 |    77 |
|    3 | Bob  |    3 |    78 |
|    4 | Bob  |    4 |    80 |
|    5 | Sue  |    1 |    90 |
|    6 | Sue  |    2 |    97 |
|    7 | Sue  |    3 |    98 |
|    8 | Sue  |    4 |    99 |
+------+------+------+-------+
8 rows in set (0.00 sec)

mysql> select name,
sum(score*(1-abs(sign(exam-1)))) as exam1,
sum(score*(1-abs(sign(exam-2)))) as exam2,
sum(score*(1-abs(sign(exam-3)))) as exam3,
sum(score*(1-abs(sign(exam-4)))) as exam4
from exams group by name;

+------+-------+-------+-------+-------+
| name | exam1 | exam2 | exam3 | exam4 |
+------+-------+-------+-------+-------+
| Bob  |    75 |    77 |    78 |    80 |
| Sue  |    90 |    97 |    98 |    99 |
+------+-------+-------+-------+-------+
2 rows in set (0.00 sec)

 

Note, the above pivot table was created with one select statement.

Let’s decompose to make the trick clearer, for the second exam:

mysql> select name, score, exam, exam-2, sign(exam-2), abs(sign(exam-2)), 1-abs(sign(exam-2)),
       score*(1-abs(sign(exam-2))) as exam2 from exams;
+------+-------+------+--------+--------------+-------------------+---------------------+-------+
| name | score | exam | exam-2 | sign(exam-2) | abs(sign(exam-2)) | 1-abs(sign(exam-2)) | exam2 |
+------+-------+------+--------+--------------+-------------------+---------------------+-------+
| Bob  |    75 |    1 |     -1 |           -1 |                 1 |                   0 |     0 |
| Bob  |    77 |    2 |      0 |            0 |                 0 |                   1 |    77 |
| Bob  |    78 |    3 |      1 |            1 |                 1 |                   0 |     0 |
| Bob  |    80 |    4 |      2 |            1 |                 1 |                   0 |     0 |
| Sue  |    90 |    1 |     -1 |           -1 |                 1 |                   0 |     0 |
| Sue  |    97 |    2 |      0 |            0 |                 0 |                   1 |    97 |
| Sue  |    98 |    3 |      1 |            1 |                 1 |                   0 |     0 |
| Sue  |    99 |    4 |      2 |            1 |                 1 |                   0 |     0 |
+------+-------+------+--------+--------------+-------------------+---------------------+-------+
8 rows in set (0.00 sec)

You may think IF’s would be clean but WATCH OUT! Look what the following gives (INCORRECT !!):

mysql> select name,
if(exam=1,score,null) as exam1,
if(exam=2,score,null) as exam2,
if(exam=3,score,null) as exam3,
if(exam=4,score,null) as exam4
from exams group by name;

+------+-------+-------+-------+-------+
| name | exam1 | exam2 | exam3 | exam4 |
+------+-------+-------+-------+-------+
| Bob  |    75 |  NULL |  NULL |  NULL |
| Sue  |    90 |  NULL |  NULL |  NULL |
+------+-------+-------+-------+-------+
2 rows in set (0.00 sec)

Note: the following does work – is all the maths necessary after all?

mysql> SELECT name,
       SUM(IF(exam=1,score,NULL)) AS exam1,
       SUM(IF(exam=2,score,NULL)) AS exam2,
       SUM(IF(exam=3,score,NULL)) AS exam3,
       SUM(IF(exam=4,score,0)) AS exam4
       FROM exams GROUP BY name;
+------+-------+-------+-------+-------+
| name | exam1 | exam2 | exam3 | exam4 |
+------+-------+-------+-------+-------+
| Bob  |    75 |    77 |    78 |    80 |
| Sue  |    90 |    97 |    98 |    99 |
+------+-------+-------+-------+-------+
2 rows in set (0.00 sec)

mysql> select name,
       sum(score*(1-abs(sign(exam-1)))) as exam1,
       sum(score*(1-abs(sign(exam-2)))) as exam2,
       sum(score*(1-abs(sign(exam-3)))) as exam3,
       sum(score*(1-abs(sign(exam-4)))) as exam4,
         sum(score*(1-abs(sign(exam- 2)))) -   sum(score*(1-abs(sign(exam- 1)))) as delta_1_2,
         sum(score*(1-abs(sign(exam- 3)))) -   sum(score*(1-abs(sign(exam- 2)))) as delta_2_3,
         sum(score*(1-abs(sign(exam- 4)))) -   sum(score*(1-abs(sign(exam- 3)))) as delta_3_4
       from exams group by name;
+------+-------+-------+-------+-------+-----------+-----------+-----------+
| name | exam1 | exam2 | exam3 | exam4 | delta_1_2 | delta_2_3 | delta_3_4 |
+------+-------+-------+-------+-------+-----------+-----------+-----------+
| Bob  |    75 |    77 |    78 |    80 |         2 |         1 |         2 |
| Sue  |    90 |    97 |    98 |    99 |         7 |         1 |         1 |
+------+-------+-------+-------+-------+-----------+-----------+-----------+
2 rows in set (0.00 sec)

Above delta_1_2 shows the difference between the first and second exams, with the numbers being positive because both Bob and Sue improved their score with each exam. Calculating the deltas here shows it’s possible to compare two rows, not columns which is easily done with the standard SQL statements but rows in the original table.

mysql>select name,
sum(score*(1-abs(sign(exam-1)))) as exam1,
sum(score*(1-abs(sign(exam-2)))) as exam2,
sum(score*(1-abs(sign(exam-3)))) as exam3,
sum(score*(1-abs(sign(exam-4)))) as exam4,
  sum(score*(1-abs(sign(exam- 2)))) -   sum(score*(1-abs(sign(exam- 1)))) as delta_1_2,
  sum(score*(1-abs(sign(exam- 3)))) -   sum(score*(1-abs(sign(exam- 2)))) as delta_2_3,
  sum(score*(1-abs(sign(exam- 4)))) -   sum(score*(1-abs(sign(exam- 3)))) as delta_3_4,

  sum(score*(1-abs(sign(exam- 2)))) -   sum(score*(1-abs(sign(exam- 1))))  +
  sum(score*(1-abs(sign(exam- 3)))) -   sum(score*(1-abs(sign(exam- 2))))  +
  sum(score*(1-abs(sign(exam- 4)))) -   sum(score*(1-abs(sign(exam- 3))))  as TotalIncPoints
  from exams group by name;

+------+-------+-------+-------+-------+-----------+-----------+-----------+----------------+
| name | exam1 | exam2 | exam3 | exam4 | delta_1_2 | delta_2_3 | delta_3_4 | TotalIncPoints |
+------+-------+-------+-------+-------+-----------+-----------+-----------+----------------+
| Bob  |    75 |    77 |    78 |    80 |         2 |         1 |         2 |              5 |
| Sue  |    90 |    97 |    98 |    99 |         7 |         1 |         1 |              9 |
+------+-------+-------+-------+-------+-----------+-----------+-----------+----------------+
2 rows in set (0.00 sec)

TotalIncPoints shows the sum of the deltas.

select name,
sum(score*(1-abs(sign(exam-1)))) as exam1,
sum(score*(1-abs(sign(exam-2)))) as exam2,
sum(score*(1-abs(sign(exam-3)))) as exam3,
sum(score*(1-abs(sign(exam-4)))) as exam4,
  sum(score*(1-abs(sign(exam- 2)))) -   sum(score*(1-abs(sign(exam- 1)))) as delta_1_2,
  sum(score*(1-abs(sign(exam- 3)))) -   sum(score*(1-abs(sign(exam- 2)))) as delta_2_3,
  sum(score*(1-abs(sign(exam- 4)))) -   sum(score*(1-abs(sign(exam- 3)))) as delta_3_4,

  sum(score*(1-abs(sign(exam- 2)))) -   sum(score*(1-abs(sign(exam- 1))))  +
  sum(score*(1-abs(sign(exam- 3)))) -   sum(score*(1-abs(sign(exam- 2))))  +
  sum(score*(1-abs(sign(exam- 4)))) -   sum(score*(1-abs(sign(exam- 3))))  as TotalIncPoints,

(sum(score*(1-abs(sign(exam-1)))) +
sum(score*(1-abs(sign(exam-2)))) +
sum(score*(1-abs(sign(exam-3)))) +
sum(score*(1-abs(sign(exam-4)))))/4 as AVG

from exams group by name;

+------+-------+-------+-------+-------+-----------+-----------+-----------+----------------+-------+
| name | exam1 | exam2 | exam3 | exam4 | delta_1_2 | delta_2_3 | delta_3_4 | TotalIncPoints | AVG   |
+------+-------+-------+-------+-------+-----------+-----------+-----------+----------------+-------+
| Bob  |    75 |    77 |    78 |    80 |         2 |         1 |         2 |              5 | 77.50 |
| Sue  |    90 |    97 |    98 |    99 |         7 |         1 |         1 |              9 | 96.00 |
+------+-------+-------+-------+-------+-----------+-----------+-----------+----------------+-------+
2 rows in set (0.00 sec)

It’s possible to combine Total Increasing Point TotalIncPoints with AVG. In fact, it’s possible to combine all of the example cuts of the data into one SQL statement, which provides additional options for displaying data on your page

select name,
sum(score*(1-abs(sign(exam-1)))) as exam1,
sum(score*(1-abs(sign(exam-2)))) as exam2,
sum(score*(1-abs(sign(exam-3)))) as exam3,
sum(score*(1-abs(sign(exam-4)))) as exam4,

(sum(score*(1-abs(sign(exam-1)))) +
sum(score*(1-abs(sign(exam-2)))))/2  as AVG1_2,

(sum(score*(1-abs(sign(exam-2)))) +
sum(score*(1-abs(sign(exam-3)))))/2 as AVG2_3,

(sum(score*(1-abs(sign(exam-3)))) +
sum(score*(1-abs(sign(exam-4)))))/2 as AVG3_4,

(sum(score*(1-abs(sign(exam-1)))) +
sum(score*(1-abs(sign(exam-2)))) +
sum(score*(1-abs(sign(exam-3)))) +
sum(score*(1-abs(sign(exam-4)))))/4 as AVG

from exams group by name;

+------+-------+-------+-------+-------+--------+--------+--------+-------+
| name | exam1 | exam2 | exam3 | exam4 | AVG1_2 | AVG2_3 | AVG3_4 | AVG   |
+------+-------+-------+-------+-------+--------+--------+--------+-------+
| Bob  |    75 |    77 |    78 |    80 |  76.00 |  77.50 |  79.00 | 77.50 |
| Sue  |    90 |    97 |    98 |    99 |  93.50 |  97.50 |  98.50 | 96.00 |
+------+-------+-------+-------+-------+--------+--------+--------+-------+
2 rows in set (0.00 sec)

Exam scores are listing along with moving averages…again it’s all with one select statement.

Good article on “Cross tabulations” or de-normalizing data to show stats: http://dev.mysql.com/tech-resources/articles/wizard/print_version.html

ADOdb (PHP) can generate pivot tables using PivotTableSQL().

For Perl, check DBIx-SQLCrosstab.