Read 448 times | Created 2013-04-16 09:49:37 | Updated 2013-09-16 10:40:46 | | |

 

<?php 
/*********************************   
FILENAME    : docsearch.php   
CREATE BY   : cahya dsn   
PURPOSE     : search data by specific field 
CREATE DATE : 2013-04-11 
UPDATE DATE : 2013-09-16
********************************** 

use test; 

-- -------------------------------------------------------- 
-- Table structure for table `tbl_category` 
-- 
DROP TABLE IF EXISTS `tbl_category`; 
CREATE TABLE IF NOT EXISTS `tbl_category` ( 
  `id_category` smallint(4) unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary key for table tbl_catgeory', 
  `category` varchar(50) NOT NULL COMMENT 'category', 
  PRIMARY KEY (`id_category`) 
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COMMENT='table to store category datas'; 
-- 
-- Dumping data for table `tbl_category` 
-- 
INSERT INTO `tbl_category` (`id_category`, `category`) VALUES 
(1, 'internal'), 
(2, 'external'); 
-- -------------------------------------------------------- 
-- Table structure for table `tbl_status` 
-- 
DROP TABLE IF EXISTS `tbl_status`; 
CREATE TABLE IF NOT EXISTS `tbl_status` ( 
  `id_status` tinyint(2) unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary key for table tbl_status', 
  `status` varchar(50) NOT NULL COMMENT 'status', 
  PRIMARY KEY (`id_status`) 
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COMMENT='table to store status datas'; 
-- 
-- Dumping data for table `tbl_status` 
-- 
INSERT INTO `tbl_status` (`id_status`, `status`) VALUES 
(1, 'draft'), 
(2, 'publish'); 
-- -------------------------------------------------------- 
-- Table structure for table `tbl_document` 
-- 
DROP TABLE IF EXISTS `tbl_document`; 
CREATE TABLE IF NOT EXISTS `tbl_document` ( 
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary key for table tbl_document', 
  `title` varchar(50) NOT NULL COMMENT 'document title', 
  `number` int(11) NOT NULL,
  `id_status` tinyint(2) unsigned NOT NULL DEFAULT '0' COMMENT 'document status', 
  `id_category` smallint(4) unsigned NOT NULL DEFAULT '0' COMMENT 'document category', 
  PRIMARY KEY (`id`) 
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COMMENT='table to store document datas'; 
-- 
-- Dumping data for table `tbl_document` 
-- 
INSERT INTO `tbl_document` (`id`, `title`,`number`,`id_status`, `id_category`) VALUES 
(1, 'Modul Pembelajaran PHP 1',3, 1, 1), 
(2, 'Ajax Unleashed',10, 1, 2), 
(3, 'Modul Pembelajaran MySQL',2, 2, 1), 
(4, 'PHP for Web Developer',15, 2, 2), 
(5, 'Modul Pembelajaran Javascript',8, 2, 1); 

*/ 
//database configuration   
$dbhost='localhost';   
$dbuser='root';   
$dbpass='';   
$dbname='test';   
//database connection   
$db=new mysqli($dbhost,$dbuser,$dbpass,$dbname);   
//get input data 
$title=isset($_POST['title'])?$_POST['title']:''; 
$id_status=isset($_POST['id_status'])?$_POST['id_status']:''; 
$id_category=isset($_POST['id_category'])?$_POST['id_category']:''; 
$id_min=isset($_POST['id_min'])?$_POST['id_min']:''; 
$id_max=isset($_POST['id_max'])?$_POST['id_max']:''; 
?> 
<!DOCTYPE html> 
<html> 
  <head> 
    <meta charset="utf-8"> 
    <meta name="viewport" content="width=device-width, initial-scale=1"> 
    <title>Search Document</title> 
    <style type="text/css"> 
      *{font-family:calibri,arial,san-serif;font-size:12px;} 
      .right {text-align:right; }
      table {padding:2px;} 
      input,select { 
          font-family:calibri,arial,sans-serif;font-size:12px;padding:3px; 
          -webkit-border-radius: 4px;-moz-border-radius: 4px;border-radius: 4px; 
          border: solid 1 px #999;color:#333;background-color:#ff9;} 
      legend {color:#900;padding:3px 10px; border:1 solid #eee;background-color:#eee; 
         -webkit-border-radius: 3px;-moz-border-radius: 3px;border-radius: 3px; 
         -moz-box-shadow:2px 2px 8px rgba(0,0,0,0.5); 
         -webkit-box-shadow:2px 2px 8px rgba(0,0,0,0.5);} 
      fieldset{-webkit-border-radius: 4px;-moz-border-radius: 4px; 
         border-radius: 4px;padding:3px;-moz-box-shadow:2px 2px 8px rgba(0,0,0,0.5); 
         -webkit-box-shadow:2px 2px 8px rgba(0,0,0,0.5);margin-bottom:15px;border:none;}       
    </style> 
  </head> 
  <body> 
    <div class="container"> 
      <div id="search_box"> 
        <form method="post"> 
          <fieldset> 
            <legend>Search Document</legend> 
            title 
            <input type="text" id="title" name="title" /> 
            status 
            <select id="id_status" name="id_status"> 
              <option value=''>-- all --</option> 
              <?php 
              $sql="SELECT * FROM tbl_status"; 
              $result=$db->query($sql); 
              while($row=$result->fetch_object()){ 
                echo "<option value='".$row->id_status."'" 
                    .($id_status==$row->id_status?" selected='selected'":"").">" 
                    .$row->status."</option>n"; 
              } 
              $result->free; 
              ?> 
            </select> 
            category 
            <select id="id_category" name="id_category"> 
              <option value=''>-- all --</option> 
              <?php 
              $sql="SELECT * FROM tbl_category"; 
              $result=$db->query($sql); 
              while($row=$result->fetch_object()){ 
                echo "<option value='".$row->id_category."'" 
                    .($id_category==$row->id_category?" selected='selected'":"").">" 
                    .$row->category."</option>n"; 
              } 
              $result->free; 
              ?> 
            </select> 
            Number Min 
            <select id="id_min" name="id_min">
              <option value=''>-- all --</option> 
              <?php
              for($i=0;$i<=15;$i=$i+5)
              {
                echo "<option value='$i'".($id_min==$i?" selected='selected'":"").">$i</option>n";
              }
              ?>
            </select>
            Max
            <select id="id_max" name="id_max">
              <option value=''>-- all --</option> 
              <?php
              for($i=5;$i<=20;$i=$i+5)
              {
                echo "<option value='$i'".($id_max==$i?" selected='selected'":"").">$i</option>n";
              }
              ?>
            </select>

            <input type="submit" name="search" id="search" value="search" /> 
          </fieldset> 
        </form> 
      </div>  
      <div id="grid_box"> 
        <fieldset> 
          <legend>Result</legend> 
          <table> 
            <tr> 
              <th>No</th> 
              <th>Document</th> 
              <th>Number</th> 
              <th>Status</th> 
              <th>Category</th> 
              <th>Action</th> 
            </tr> 
            <?php 
            $sql="SELECT a.id,a.title,a.number,b.status,c.category " 
                ."FROM tbl_document a " 
                ."JOIN tbl_status b USING(id_status) " 
                ."JOIN tbl_category c USING(id_category) " 
                ."WHERE 1" 
                .(!empty($title)?" AND a.title LIKE '%$title%'":'') 
                .(!empty($id_status)?" AND a.id_status='$id_status'":'') 
                .(!empty($id_category)?" AND a.id_category='$id_category'":'')
                .(!empty($id_min)?" AND a.number>='$id_min'":'')
                .(!empty($id_max)?" AND a.number<='$id_max'":''); 
            $result=$db->query($sql); 
            $i=0; 
            if($result){ 
              while($row=$result->fetch_object()){ 
                echo "<tr>n" 
                    ."<td>".++$i."</td>n" 
                    ."<td>".$row->title."</td>n" 
                    ."<td class='right'>".$row->number."</td>n" 
                    ."<td>".$row->status."</tdn>" 
                    ."<td>".$row->category."</td>n" 
                    ."<td>n" 
                    ."<a href='editdoc.php?id=".$row->id."'>edit</a> | n" 
                    ."<a href='deldoc.php?id=".$row->id."'>delete</a>n" 
                    ."</td>n" 
                    ."</tr>n"; 
              } 
              $result->free(); 
            }else{ 
              echo "<tr><td>No data found</td></tr>n"; 
            } 
            ?> 
          </table> 
        </fieldset> 
      </div> 
    </div> 
  </body> 
</html> 
<?php $db->close();?>