Read 1026 times | Created 2013-10-29 14:09:19 | Updated 2013-10-29 14:09:19 | | |
# FILENAME : docsearch.r1.php ======================== <?php /********************************* FILENAME : docsearch.r1.php CREATE BY : cahya dsn PURPOSE : search data by specific field CREATE DATE : 2013-10-29 UPDATE DATE : 2013-10-29 ********************************** 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); ?> <!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 name="frm"> <fieldset> <legend>Search Document</legend> title <input type="text" id="title" name="title" /> status <select id="id_status" name="id_status" onChange="loadAjax();"> <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" onChange="loadAjax();"> <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" onChange="loadAjax();"> <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" onChange="loadAjax();"> <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> </fieldset> </form> </div> <div id="grid_box"> </div> </div> <script language="javascript"> function loadAjax() { var xmlhttp; if (window.XMLHttpRequest) { xmlhttp=new XMLHttpRequest(); } else { xmlhttp=new ActiveXObject("Microsoft.XMLHTTP"); } xmlhttp.onreadystatechange=function() { if (xmlhttp.readyState==4 && xmlhttp.status==200) { document.getElementById("grid_box").innerHTML=xmlhttp.responseText; } } var title=document.getElementById('title'); var id_status=document.getElementById('id_status'); var id_category=document.getElementById('id_category'); var id_min=document.getElementById('id_min'); var id_max=document.getElementById('id_max'); xmlhttp.open("POST","docsearch.r1.ajax.php",true); xmlhttp.setRequestHeader("Content-type","application/x-www-form-urlencoded"); xmlhttp.send("title="+title.value+ "&id_status="+id_status.value+ "&id_category="+id_category.value+ "&id_min="+id_min.value+ "&id_max="+id_max.value); } </script> </body> </html> <?php $db->close();?> # FILENAME : docsearch.r1.ajax.php ========================== <?php /********************************* FILENAME : docsearch.r1.ajax.php CREATE BY : cahya dsn PURPOSE : get data from db with specific parameter (excecute with ajax call) CREATE DATE : 2013-10-29 UPDATE DATE : 2013-10-29 **********************************/ //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']:''; $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; ?> <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 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."</td\n>" ."<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"; } $db->close(); ?> </table> </fieldset>