การดึงข้อมูล จากฐานข้อมูล สร้าง xml ไฟล์ ด้วย php

-- --------------------------------------------------------

--
-- Table structure for table `tbl_provinces_map`
--

CREATE TABLE `tbl_provinces_map` (
  `province_id` int(11) NOT NULL,
  `province_name` varchar(255) NOT NULL,
  `province_lat` varchar(255) NOT NULL,
  `province_lon` varchar(255) NOT NULL,
  `province_zoom` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Dumping data for table `tbl_provinces_map`
--

INSERT INTO `tbl_provinces_map` (`province_id`, `province_name`, `province_lat`, `province_lon`, `province_zoom`) VALUES
(1, 'กรุงเทพมหานคร', '13.7278956', '100.52412349999997', 13),
(2, 'กระบี่', '8.0862997', '98.90628349999997', 13),
(3, 'กาญจนบุรี', '14.0227797', '99.53281149999998', 13),
(4, 'กาฬสินธุ์', '16.4314078', '103.5058755', 13),
(5, 'กำแพงเพชร', '16.4827798', '99.52266179999992', 13),
(6, 'ขอนแก่น', '16.4419355', '102.8359921', 13),
(7, 'จันทบุรี', '12.61134', '102.10385459999998', 13),
(8, 'ฉะเชิงเทรา', '13.6904194', '101.07795959999999', 13),
(9, 'ชลบุรี', '13.3611431', '100.98467170000004', 13),
(10, 'ชัยนาท', '15.1851971', '100.12512500000003', 13),
(11, 'ชัยภูมิ', '15.8068173', '102.03150270000003', 13),
(12, 'ชุมพร', '10.4930496', '99.18001989999993', 13),
(13, 'เชียงราย', '19.9071656', '99.83095500000002', 13),
(14, 'เชียงใหม่', '18.7877477', '98.99313110000003', 13),
(15, 'ตรัง', '7.5593851', '99.61100650000003', 13),
(16, 'ตราด', '12.2427563', '102.51747339999997', 13),
(17, 'ตาก', '16.8839901', '99.12584979999997', 13),
(18, 'นครนายก', '14.2069466', '101.21305110000003', 13),
(19, 'นครปฐม', '13.8199206', '100.06216760000007', 13),
(20, 'นครพนม', '17.392039', '104.76955079999993', 13),
(21, 'นคราชสีมา', '14.9798997', '102.09776929999998', 13),
(22, 'นครศรีธรรมราช', '8.4303975', '99.96312190000003', 13),
(23, 'นครสวรรค์', '15.6930072', '100.12255949999997', 13),
(24, 'นนทบุรี', '13.8621125', '100.51435279999998', 13),
(25, 'นราธิวาส', '6.4254607', '101.82531429999995', 13),
(26, 'น่าน', '18.7756318', '100.77304170000002', 13),
(27, 'บุรีรัมย์', '14.9930017', '103.10291910000001', 13),
(28, 'ปทุมธานี', '14.0208391', '100.52502759999993', 13),
(29, 'ประจวบคีรีขันธ์', '11.812367', '99.79732709999996', 13),
(30, 'ปราจีนบุรี', '14.0509704', '101.37274389999993', 13),
(31, 'ปัตตานี', '6.869484399999999', '101.25048259999994', 13),
(32, 'พระนครศรีอยุธยา', '14.3532128', '100.56895989999998', 13),
(33, 'พะเยา', '19.1664789', '99.9019419', 13),
(34, 'พังงา', '8.4407456', '98.51930319999997', 13),
(35, 'พัทลุง', '7.6166823', '100.07402309999998', 13),
(36, 'พิจิตร', '16.4429516', '100.34823289999997', 13),
(37, 'พิษณุโลก', '16.8298048', '100.26149150000003', 13),
(38, 'เพชรบุรี', '13.1111601', '99.93913069999996', 13),
(39, 'เพชรบูรณ์', '16.4189807', '101.15509259999999', 13),
(40, 'แพร่', '18.1445774', '100.14028310000003', 13),
(41, 'ภูเก็ต', '7.9810496', '98.36388239999997', 13),
(42, 'มหาสารคาม', '16.1850896', '103.30264609999995', 13),
(43, 'มุกดาหาร', '16.542443', '104.72091509999996', 13),
(44, 'แม่ฮ่องสอน', '19.2990643', '97.96562259999996', 13),
(45, 'ยโสธร', '15.792641', '104.14528270000005', 13),
(46, 'ยะลา', '6.541147', '101.28039469999999', 13),
(47, 'ร้อยเอ็ด', '16.0538196', '103.65200359999994', 13),
(48, 'ระนอง', '9.9528702', '98.60846409999999', 13),
(49, 'ระยอง', '12.6833115', '101.23742949999996', 13),
(50, 'ราชบุรี', '13.5282893', '99.81342110000003', 13),
(51, 'ลพบุรี', '14.7995081', '100.65337060000002', 13),
(52, 'ลำปาง', '18.2888404', '99.49087399999996', 13),
(53, 'ลำพูน', '18.5744606', '99.0087221', 13),
(54, 'เลย', '17.4860232', '101.72230020000006', 13),
(55, 'ศรีสะเกษ', '15.1186009', '104.32200949999992', 13),
(56, 'สกลนคร', '17.1545995', '104.1348365', 13),
(57, 'สงขลา', '7.1756004', '100.61434699999995', 13),
(58, 'สตูล', '6.6238158', '100.06737440000006', 13),
(59, 'สมุทรปราการ', '13.5990961', '100.59983190000003', 13),
(60, 'สมุทรสงคราม', '13.4098217', '100.00226450000002', 13),
(61, 'สมุทรสาคร', '13.5475216', '100.27439559999993', 13),
(62, 'สระแก้ว', '13.824038', '102.0645839', 13),
(63, 'สระบุรี', '14.5289154', '100.91014210000003', 13),
(64, 'สิงห์บุรี', '14.8936253', '100.39673140000002', 13),
(65, 'สุโขทัย', '17.0055573', '99.82637120000004', 13),
(66, 'สุพรรณบุรี', '14.4744892', '100.11771279999994', 13),
(67, 'สุราษฎร์ธานี', '9.1382389', '99.32174829999997', 13),
(68, 'สุรินทร์', '14.882905', '103.49371070000007', 13),
(69, 'หนองคาย', '17.8782803', '102.74126380000007', 13),
(70, 'หนองบัวลำภู', '17.2218247', '102.42603680000002', 13),
(71, 'อ่างทอง', '14.5896054', '100.45505200000002', 13),
(72, 'อำนาจเจริญ', '15.8656783', '104.62577740000006', 13),
(73, 'อุดรธานี', '17.4138413', '102.78723250000007', 13),
(74, 'อุตรดิตถ์', '17.6200886', '100.09929420000003', 13),
(75, 'อุทัยธานี', '15.3835001', '100.02455269999996', 13),
(76, 'อุบลราชธานี', '15.2286861', '104.85642170000006', 13),
(77, 'บึงกาฬ', '18.3609104', '103.64644629999998', 13);

--
-- Indexes for dumped tables
--

--
-- Indexes for table `tbl_provinces_map`
--
ALTER TABLE `tbl_provinces_map`
  ADD PRIMARY KEY (`province_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `tbl_provinces_map`
--
ALTER TABLE `tbl_provinces_map`
  MODIFY `province_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=78;

<?php  
$mysqli = new mysqli("localhost", "root","","test");  
/* check connection */ 
if ($mysqli->connect_errno) {  
    printf("Connect failed: %s\n", $mysqli->connect_error);  
    exit();  
}  
if(!$mysqli->set_charset("utf8")) {  
    printf("Error loading character set utf8: %s\n", $mysqli->error);  
    exit();  
}

<?php
header("Content-type:application/json; charset=UTF-8");    
header("Cache-Control: no-store, no-cache, must-revalidate");         
header("Cache-Control: post-check=0, pre-check=0", false); 
require_once("dbconnect.php");
$json_data = array();
$sql = "
SELECT * FROM tbl_provinces_map WHERE 1 ORDER BY province_id
";
$result = $mysqli->query($sql);
if($result && $result->num_rows > 0){
    while($row = $result->fetch_assoc()){
        $json_data[] = array(
            "province_id" => $row['province_id'],
            "province_name" => $row['province_name'],
            "province_lat" => $row['province_lat'],
            "province_lon" => $row['province_lon']						
        );
    }
}
// แปลง array เป็นรูปแบบ json string  
if(isset($json_data)){  
    $json= json_encode($json_data);    
    if(isset($_GET['callback']) && $_GET['callback']!=""){    
    echo $_GET['callback']."(".$json.");";        
    }else{    
    echo $json;    
    }    
}
?>