แสดงบทความที่มีป้ายกำกับ mysql แสดงบทความทั้งหมด
แสดงบทความที่มีป้ายกำกับ mysql แสดงบทความทั้งหมด

วันศุกร์ที่ 14 มิถุนายน พ.ศ. 2556

การค้นหาแบบช่วงวันที่

สมมติว่า $date_start_search และ $date_end_search เป็นข้อมูลจากช่องกรอกข้อมูลวันที่
ในรูปแบบ yyyy-mm-dd ตัวอย่างเช่น 2013-01-01

กรณี 1
ช่วงวันที่ค้นหา(ทั้งหมด) อยู่ข้างในระหว่างช่วงวันที่ในฐานข้อมูล
.............|---------------|.................ช่วงวันที่ค้นหา
........||==============||.............ช่วงวันที่ในฐานข้อมูล
.............(---------------).................ช่วงข้อมูลที่ได้รับจากฐานข้อมูล
อยู่ในรูปของ sql query:
SELECT * FROM date_test WHERE
'$date_start_search' >= date_start AND '$date_end_search' <= date_end

กรณี 2
ช่วงช่วงวันที่ในฐานข้อมูล(บางส่วน) อยู่ข้างในช่วงวันที่ค้นหา
....|---------------|
.........||============||
.........(-----------)
หรือ
..........................|---------------|
.........||============||
........................(--------)
อยู่ในรูปของ sql query:
SELECT * FROM date_test WHERE date_start BETWEEN '$date_start_search' AND '$date_end_search'
SELECT * FROM date_test WHERE date_end BETWEEN '$date_start_search' AND '$date_end_search'
ตามลำดับ

กรณี 3
ช่วงวันที่ในฐานข้อมูล(ทั้งหมด) อยู่ข้างในระหว่างช่วงวันที่ที่ค้นหา
.....|------------------------------|
.........||============||
.........(-------------------)
อยู่ในรูปของ sql query:
SELECT * FROM date_test WHERE
date_start >= '$date_start_search' AND date_end <= '$date_end_search'


- เพียงแค่ query ของกรณีที่ 2 ก็ครอบคลุมกรณีที่ 3 แล้ว
- ส่วนกรณีที่ 2 ยังไม่ครอบคลุมกรณีที่ 1 เนื่องจากในกรณีที่ 1
ทั้ง date_start และ date_end ไม่ได้อยู่ข้างใน $date_start_search, $date_end_search
ทำให้ query ของกรณีที่ 2 ไม่สามารถค้นพบหากเกิดกรณีที่ 1 ขึ้น

ดังนั้น query ที่สมบูรณ์จะอยู่ในรูปแบบ

SELECT * FROM date_test WHERE
( '$date_start_search' >= date_start AND '$date_end_search' <= date_end )
OR ( date_start BETWEEN '$date_start_search' AND '$date_end_search' )
OR ( date_end BETWEEN '$date_start_search' AND '$date_end_search' )

ก็จะเป็นคำสั่งค้นหาข้อมูลในฐานข้อมูลที่อยู่ในช่วงวันที่ๆ ต้องการครับ :)
เขียนโดย หนุ่ม ที่ 22:07 1 ความคิดเห็น
ป้ายกำกับ: ,

วันอาทิตย์ที่ 9 กันยายน พ.ศ. 2555

php pdo

วิธีการใช้งาน php pdo แบบง่ายๆ ครับ php pdo class จะมีคำสั่งช่วยในการป้องกัน sql injection และคำสั่งการดึงข้อมูลที่หลากหลาย ช่วยให้สะดวกในการเขียนโปรแกรมติดต่อกับ database ครับ 
/*
CREATE TABLE `tbcontact` (
        `id` INT(4) NOT NULL AUTO_INCREMENT,
        `name` VARCHAR(50) NULL DEFAULT NULL,
        PRIMARY KEY (`id`)
)
ENGINE=MyISAM
*/

//configuration
$dbname = 'test';
$user = 'root';
$pass = '';

try{
    //connect    
    $db = new PDO("mysql:host=localhost;dbname=$dbname;", $user, $pass, array(
        PDO::MYSQL_ATTR_INIT_COMMAND=>'SET NAMES UTF8'
    ));
}catch (Exception $e){
    echo 'error:'.$e->getMessage();
}

//insert
$stmt = $db->prepare('insert into tbcontact(name) values(:name)');    
$stmt->bindValue(':name','nobita');
$stmt->execute();

$info = $stmt->errorInfo();
if ($info[0]=='0000')
    echo 'no error';
else
    echo 'error: '.$info[2];

//select
echo '<hr /><h1>FETCH TO OBJECT</h1>';
$stmt = $db->query('SELECT id,name from tbcontact',PDO::FETCH_OBJ);
foreach($stmt as $row) {
    echo $row->id,'...',$row->name,'<br','>';
}

echo '<hr /><h1>FETCH TO ARRAY</h1>';
$stmt = $db->query('SELECT id,name from tbcontact',PDO::FETCH_ASSOC);
while($row = $stmt->fetch()) {
    echo $row['id'],'...',$row['name'],'<br','>';
}

echo '<hr /><h1>FETCH TO VARIABLE</h1>';
$stmt = $db->query('SELECT id,name from tbcontact',PDO::FETCH_BOUND);
$stmt->bindColumn('id',$id);
$stmt->bindColumn('name',$name);
while($row = $stmt->fetch()) {
    echo $id,'...',$name,'<br','>';
}

echo "column count = {$stmt->columnCount()}, row count= {$stmt->rowCount()}";
เขียนโดย หนุ่ม ที่ 07:03 0 ความคิดเห็น
ป้ายกำกับ: , ,

วันพุธที่ 3 พฤศจิกายน พ.ศ. 2553

increase mysql connection limit

http://rackerhacker.com/2007/01/24/increase-mysql-connection-limit/
เขียนโดย หนุ่ม ที่ 21:34 0 ความคิดเห็น
ป้ายกำกับ:

วันอังคารที่ 2 พฤศจิกายน พ.ศ. 2553

mysql complex query

1. GROUP BY Query Pagination



SELECT * FROM box;
id    box1    box2
4    1    abc
3    1    def
5    2    ghi
6    3    jkl
7    2    mno

SELECT COUNT(*) FROM box GROUP BY( box1 );
box1    COUNT(*)
1    2
2    2
3    1
จะเห็นได้ว่า count(*) จะเป็นค่าที่เก็บจำนวนของ box1 ที่ซ้ำกัน อย่างเช่น box=1 มี 2 แถว จึงมีค่า count(*)=2

โดยปกติเมื่อเราจะทำการแบ่งหน้าใน PHP (Pagination) เราต้องการจำนวนแถวของผลลัพธ์ทั้งหมด
จะเห็นได้ว่าเมื่อใช้คำสั่ง GROUP BY เราจะไม่สามารถใช้ query count(*) อย่างข้างบนในการหาจำนวนแถวผลลัพธ์ทั้งหมด
เนื่องจาก query count(*) จะถูกนำไปใช้เป็นจำนวนที่ซ้ำกันของข้อมูลที่ถูก GROUP BY ไว้

ซึ่งปัญหานี้จะสามารถแก้ไขได้ง่ายๆ ด้วยการใช้ sub query อย่างข้างล่างนี้ครับ

SELECT COUNT(*) FROM (SELECT COUNT(*) FROM box GROUP BY box1) AS t1;
COUNT(*)
3

2. FIND THE STUDENTS GOT MAXIMUM SCORE IN EACH SUBJECT


ปัญหานี้ค่อนข้างซับซ้อนกว่าที่คิดครับ

ถ้าต้องการจะทดสอบ query สามารถเอา sql statement นี้ไปสร้างตารางเพื่อใช้ในการทดสอบได้ครับ

CREATE TABLE `students` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sname` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

insert  into `students`(`id`,`sname`) values (1,'A'),(2,'B'),(3,'C'),(4,'D');

CREATE TABLE `subjects` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sjname` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

insert  into `subjects`(`id`,`sjname`) values (1,'CT211'),(2,'CT212'),(3,'CT316'),(4,'CT317'),(5,'CT488');

CREATE TABLE `tests` (
  `student_id` int(11) NOT NULL,
  `subject_id` int(11) NOT NULL,
  `score` int(11) NOT NULL,
  PRIMARY KEY (`student_id`,`subject_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

insert  into `tests`(`student_id`,`subject_id`,`score`) values (1,1,80),(1,2,70),(1,3,25),(1,4,61),(1,5,99),(2,1,50),(2,2,72),(2,3,85),(2,4,11),(3,1,83),(3,2,39),(3,3,95),(3,4,96),(3,5,99),(4,1,98),(4,2,21),(4,3,60);


มาดู query ที่ใช้ในการหาคนที่ได้คะแนนสูงสุดในแต่ละวิชากันครับ

SELECT sname,sjname,score
FROM students,subjects,tests
WHERE tests.student_id = students.id AND
tests.subject_id = subjects.id AND
CONCAT(tests.student_id,'-',tests.subject_id) IN
(
  SELECT CONCAT(student_id,'-',subject_id) FROM
  (
  SELECT tests.student_id,tests.subject_id FROM tests ORDER BY tests.subject_id, tests.score DESC
  )
  AS temp_table
  GROUP BY subject_id
);

ซึ่งจะได้ผลลัพธ์
sname    sjname    score
A    CT488    99
B    CT212    72
C    CT316    95
C    CT317    96
D    CT211    98

query ดังกล่าวดูเผินๆ ก็ทำงานปกติดีแต่ มีข้อเสียคือทำงานช้าครับ เนื่องจากใช้ฟังค์ชั่น concat()
ตอนนั้นผมยังไม่รู้จักการใช้ column หลายๆ column มาเปรียบเทียบพร้อมๆ กัน ซึ่งจะทำให้ query
SELECT * FROM tb1 WHERE c1=1 AND c2=2;
ทำให้เขียนได้สั้นๆ ดังนี้ครับ
SELECT * FROM tb1 WHERE (c1,c2)=(1,2);

จากความรู้นี้ทำให้สามารถปรับปรุง query ก่อนให้เร็วขึ้นและเขียนสั้นลงอีกด้วยครับ

SELECT sname,sjname,score
FROM students,subjects,tests
WHERE tests.student_id = students.id AND
tests.subject_id = subjects.id AND
(tests.student_id,tests.subject_id) IN
(
  SELECT student_id,subject_id FROM
  (
  SELECT tests.student_id,tests.subject_id FROM tests ORDER BY tests.subject_id, tests.score DESC
  )
  AS temp_table
  GROUP BY subject_id
);

ในตอนหลังผมพบว่าสามารถเขียน query ให้ได้ง่ายๆ ขึ้นด้วยคำสั่งนี้

SELECT sname,sjname,score
FROM students,subjects,tests
WHERE tests.student_id = students.id AND
tests.subject_id = subjects.id
AND tests.score = (SELECT MAX(score) FROM tests AS t1 WHERE t1.subject_id=tests.subject_id);


ปัญหาใหม่ก็คือในบางครั้งจะมีคนที่ได้คะแนนสูงสุดเหมือนกัน แต่ query ข้างบนนี้จะแสดงแยกแถวกันซึ่งไม่สะดวกในการใช้งาน
ซึ่งเราสามารถแก้ไขโดยใช้คำสั่ง GROUP BY ร่วมกับ GROUP_CONCAT

SELECT GROUP_CONCAT(sname) AS snames,sjname,score
FROM students,subjects,tests
WHERE tests.student_id = students.id AND
tests.subject_id = subjects.id
AND tests.score = (SELECT MAX(score) FROM tests AS t1 WHERE t1.subject_id=tests.subject_id)
GROUP BY sjname;

ซึ่งจะได้ผลลัพธ์เป็นไปตามความต้องการดังนี้ครับ
snames    sjname    score
D    CT211    98
B    CT212    72
C    CT316    95
C    CT317    96
C,A    CT488    99 /* C และ A ได้คะแนนสูงสุดเท่ากัน */


3. FIND RANDOM NAME OF EACH GROUP ONLY ONCE


s    name
1    ddd
2    eee
3    fff
1    mmm
2    ggg
3    zzz
1    hhh
2    bbb
3    ttt

SELECT CONCAT('set',s) AS `set`,`name` FROM (SELECT * FROM t ORDER BY RAND()) AS t1 GROUP BY s;

set    name
set1    hhh
set2    eee
set3    fff



4. JOIN TABLE


แบบที่ 1) SELECT b1.id FROM b1,b2 WHERE b1.id = b2.b1_id;
แบบที่ 2) SELECT b1.id FROM b1 INNER JOIN  b2 ON b1.id = b2.b1_id;
ทั้ง 2 แบบให้ผลลัพธ์เหมือนกัน แต่แบบที่ 2 จะเร็วกกว่าครับ


5. DELETE DUPLICATE RECORD



DROP TABLE IF EXISTS id_temp;
CREATE TEMPORARY TABLE id_temp  (
  `id` SMALLINT(5) UNSIGNED NOT NULL
) SELECT `id` FROM b3 GROUP BY `code` HAVING COUNT(*)>1;

DELETE FROM b3 WHERE b3.`id` IN(SELECT id_temp.`id` FROM id_temp);

6. GET MAX ORDER_ID OF CURRENT YEAR



$r = mysql_query("
SELECT
  MAX(CAST(MID(pr_order,5,3) AS UNSIGNED)) AS now_id,
  RIGHT(pr_order,4) AS year_id
FROM pr_description
GROUP BY year_id
HAVING year_id = YEAR(CURRENT_DATE)+543
");
if (mysql_num_rows($r)){
   $now_id = mysql_result($r,0,'now_id');
} else {
   $now_id = 1;
}
$now_id = sprintf("%02d",$now_id);
echo $now_id;


SELECT
  MAX(CAST(MID(pr_order,5,3) AS UNSIGNED)) AS now_id,
  RIGHT(pr_order,4) AS year_id
FROM
(
SELECT 'CPMK001/2552' AS pr_order
UNION
SELECT 'CPMK003/2552' AS pr_order
UNION
SELECT 'CPMK004/2551' AS pr_order
UNION 
SELECT 'CPMK002/2552' AS pr_order
) AS t1
GROUP BY year_id
HAVING year_id = YEAR(CURRENT_DATE)+543

7. TOP 5 BEST SELLER



SELECT products.id,products.name,SUM(order_details.qty) AS sum_qty
FROM orders
INNER JOIN order_details ON orders.id=order_details.order_id
INNER JOIN products ON products.id=order_details.product_id
WHERE YEAR(orders.create_at)='2009'
GROUP BY products.id
ORDER BY sum_qty DESC
LIMIT 5;


7. FIXING THAI CHARACTER SET SORT


character set utf8 จะมีปัญหากับการเรียงลำดับภาษาไทย
เช่น กางเกง และ เกม จะเรียงลำดับห่างกัน เพราะ collation ของ utf8 จะนับเอาตัวอักษรตัวแรกเป็นตัวเริ่มต้นเรียงลำดับ
แต่สามารถแก้ไขโดยการ convert character set ให้เป็น tis620 ก่อน
SELECT * FROM tb ORDER BY CONVERT (NAME USING tis620);

8. Find Related Post




SELECT COUNT(posts_tags.tag_id) ct FROM posts_tags
WHERE posts_tags.tag_id IN
(
  SELECT posts_tags.tag_id
  FROM posts_tags
  WHERE posts_tags.post_id='10'
)
GROUP BY post_id
ORDER BY ct DESC LIMIT 10
;


9. SELECT between date or datetime



SELECT CAST(created AS DATETIME)
FROM posts
WHERE created BETWEEN CAST('2009-02-01' AS DATETIME) AND CAST('2009-06-01' AS DATETIME);


10. SELECT ORDER BY ID ASC LIMIT 5 OF SELECT ORDER BY ID DESC



$rs = mysql_query('SET @lim:=0;');
$rs = mysql_query('SELECT * FROM (SELECT id FROM posts ORDER BY id DESC) AS t1 WHERE (@lim:=(@lim+1)) < 5 ORDER BY id ASC');
echo mysql_error();
while($r = mysql_fetch_assoc($rs)){
echo $r['id'],'<br','>';
}


11. SEARCH MULTI TABLE



$sql = "
select column_1 as c1 from tb1 where column_1 like '%notebook%'
union
select column_2 as c1 from tb2 where column_2 like '%notebook%'
union
select column_3 as c1 from tb3 where column_3 like '%notebook%'
";
$rs = mysql_query($sql);
while($r = mysql_fetch_assoc($rs)){
    echo '<br','>',$r['c1'];
}
เขียนโดย หนุ่ม ที่ 22:13 0 ความคิดเห็น
ป้ายกำกับ:
สมัครสมาชิก: บทความ (Atom)