mysql - Advanced search form to query a database in any criteria using PHP -
i found php class seems me advanced search of database.
here is:
<?php class search { var $table; var $field1; var $field2; function queryrow($query){ //define database settings define("host", "xxxxxxxx"); define("login", "xxxxxx"); define("senha", "xxxxxxx"); //define database name define("data", "xxxxx"); //conection routine try{ $host = host; $data = data; $connection = new pdo("mysql:host=$host;dbname=$data", login, senha); //$connection->setattribute(pdo::attr_errmode, pdo::errmode_exception); $result = $connection->prepare($query); $result->execute(); return $result; $this->connection = $connection; }catch(pdoexception $e){ echo $e->getmessage(); } } function close($connection){ $connection = null; } function query($query){ $host = host; $result = $this->queryrow($query); $row = $result->fetch(pdo::fetch_assoc); $this->close($this->connection); $this->query = $query; return $row; } //finish connection //method list fields function fieldselect(){ $query = $this->queryrow('select gender,orphan,county,district '.$this->table); $retorno = "<select name=\"fieldselect\">\n"; foreach ($query $collums){ if ($_post['fieldselect'] == $collums['field']){ $selected = " selected=\"selected\" "; }else{ $selected = ""; } $retorno .= "<option value=\"$collums[field]\"$selected>$collums[field]</option>\n"; } $retorno .= "</select>\n"; return $retorno; } //method select functions condictions function whereselect(){ $wheres = array(); $wheres[] = 'equal'; $wheres[] = 'diferent'; $wheres[] = 'minor'; $wheres[] = 'more'; $wheres[] = 'minororequal'; $wheres[] = 'moreorequal'; $wheres[] = 'content'; $wheres[] = 'notcontent'; $wheres[] = 'between'; $wheres[] = 'notbetween'; $label[] = 'equal'; $label[] = 'diferent'; $label[] = 'minor'; $label[] = 'more'; $label[] = 'minor or equal'; $label[] = 'more or equal'; $label[] = 'content'; $label[] = 'not content'; $label[] = 'between'; $label[] = 'not between'; $retorno = "<select name=\"select\">\n"; $i=0; do{ if ($_post['select'] == $wheres[$i]){ $selected = " selected=\"selected\" "; }else{ $selected = ""; } $retorno .= "<option value=\"$wheres[$i]\"$selected>$label[$i]</option>\n"; $i++; }while($i < count($wheres)); $retorno .= "</select>\n"; return $retorno; } function fieldtext($size, $max){ $retorno .= "<input type=\"text\" name=\"fieldtext\" size=\"$size\" maxlength=\"$max\" value=\"$_post[fieldtext]\" />\n"; return $retorno; } //method implement condictions , variables function wheres($value){ $retorno = ""; //parei aqui $this->field2 = explode(' or ',$this->field2); //var_dump($this->field2); $i = 0; switch($value){ case 'equal': foreach ($this->field2 $field2){ $retorno .= "$this->field1 = '$field2' "; $i = ++$i; if ($i != 0 && $i != count($this->field2)){ $retorno .= " or "; } } break; case 'diferent': foreach ($this->field2 $field2){ $retorno .= "$this->field1 != '$field2'"; $i = ++$i; if ($i != 0 && $i != count($this->field2)){ $retorno .= " or "; } } break; case 'minor': foreach ($this->field2 $field2){ $retorno .= "$this->field1 < '$field2'"; $i = ++$i; if ($i != 0 && $i != count($this->field2)){ $retorno .= " or "; } } break; case 'more': foreach ($this->field2 $field2){ $retorno .= "$this->field1 > '$field2'"; $i = ++$i; if ($i != 0 && $i != count($this->field2)){ $retorno .= " or "; } } break; case 'minororequal': foreach ($this->field2 $field2){ $retorno .= "$this->field1 <= '$field2'"; $i = ++$i; if ($i != 0 && $i != count($this->field2)){ $retorno .= " or "; } } break; case 'moreorequal': foreach ($this->field2 $field2){ $retorno .= "$this->field1 >= '$field2'"; $i = ++$i; if ($i != 0 && $i != count($this->field2)){ $retorno .= " or "; } } break; case 'content': foreach ($this->field2 $field2){ $retorno .= "$this->field1 '%$field2%'"; $i = ++$i; if ($i != 0 && $i != count($this->field2)){ $retorno .= " or "; } } break; case 'notcontent': foreach ($this->field2 $field2){ $retorno .= "$this->field1 not '%$field2%'"; $i = ++$i; if ($i != 0 && $i != count($this->field2)){ $retorno .= " or "; } } break; case 'between': foreach ($this->field2 $field2){ $retorno .= "$this->field1 between $field2"; $i = ++$i; if ($i != 0 && $i != count($this->field2)){ $retorno .= " or "; } } break; case 'notbetween': foreach ($this->field2 $field2){ $retorno .= "$this->field1 not between $field2"; $i = ++$i; if ($i != 0 && $i != count($this->field2)){ $retorno .= " or "; } } break; } return $retorno; } //method list results of sql consult function result($fields){ if (isset($_post['submit'])){ $this->field1 = $_post['fieldselect']; $this->field2 = $_post['fieldtext']; $resultfields = ""; if(is_array($fields)){ $i = 0; foreach($fields $collums){ if($i< count($fields)-1){ $resultfields .= $collums.', '; }else{ $resultfields .= $collums; } $i = ++$i; } }else{ $resultfields = $fields; } $query = $this->queryrow("select $resultfields $this->table ".$this->wheres($_post['select'])); $retorno = "<table>\n"; foreach($query $querycollum){ $retorno .= "<tr>"; if(is_array($fields)){ foreach($fields $collumstable){ $retorno .= "<td>$querycollum[$collumstable]</td>"; } $retorno .= "</tr>\n"; } } $retorno .= "</table>\n"; return $retorno; } } } ?> its working ok far.see http://onlinestudentsadmission.com/schooldemo/dataform.php
however, wish changes thus: 1. show columns in fieldselect, not columns in table. code seems pull columns in search.class.php is:
//method list fields function fieldselect(){ $query = $this->queryrow('show full columns '.$this->table); i have tried different variations of sql select no avail.
- i wish expand search criteria. want students data sorted year ( year of admission) 'search criteria.
that is, 1 should first choose year, other conditions gender, county, district , orphan status of student.
it seems should able achieve using php class, dont know change code.
any appreciated.
Comments
Post a Comment