I found a php class that seems to help me achieve my objective of doing an advanced search of my database

    <?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 to list the fields
    function fieldSelect(){   
       $query = $this->queryRow('SELECT Gender,Orphan,County,District FROM '.$this->table);
       $retorno  = "<select name=\"fieldselect\">\n";
       foreach ($query as $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 to select the functions to 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 to implement condictions and your 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 as $field2){
          $retorno .= "$this->field1 = '$field2' ";
          $i = ++$i;
          if ($i != 0 && $i != count($this->field2)){
          $retorno .= " OR ";
          }
          }
          break;
          case 'diferent':
          foreach ($this->field2 as $field2){
          $retorno .= "$this->field1 != '$field2'";
          $i = ++$i;
          if ($i != 0 && $i != count($this->field2)){
          $retorno .= " OR ";
          }
          }
          break;
          case 'minor':
          foreach ($this->field2 as $field2){
          $retorno .= "$this->field1 < '$field2'";
          $i = ++$i;
          if ($i != 0 && $i != count($this->field2)){
          $retorno .= " OR ";
          }
          }
          break;
          case 'more':
          foreach ($this->field2 as $field2){
          $retorno .= "$this->field1 > '$field2'";
          $i = ++$i;
          if ($i != 0 && $i != count($this->field2)){
          $retorno .= " OR ";
          }
          }
          break;
          case 'minororequal':
          foreach ($this->field2 as $field2){
          $retorno .= "$this->field1 <= '$field2'";
          $i = ++$i;
          if ($i != 0 && $i != count($this->field2)){
          $retorno .= " OR ";
          }
          }
          break;
          case 'moreorequal':
          foreach ($this->field2 as $field2){
          $retorno .= "$this->field1 >= '$field2'";
          $i = ++$i;
          if ($i != 0 && $i != count($this->field2)){
          $retorno .= " OR ";
          }
          }
          break;
          case 'content':
          foreach ($this->field2 as $field2){
          $retorno .= "$this->field1 LIKE '%$field2%'";
          $i = ++$i;
          if ($i != 0 && $i != count($this->field2)){
          $retorno .= " OR ";
          }
          }
          break;
          case 'notcontent':
          foreach ($this->field2 as $field2){
          $retorno .= "$this->field1 NOT LIKE '%$field2%'";
          $i = ++$i;
          if ($i != 0 && $i != count($this->field2)){
          $retorno .= " OR ";
          }
          }
          break;
          case 'between':
          foreach ($this->field2 as $field2){
          $retorno .= "$this->field1 BETWEEN $field2";
          $i = ++$i;
          if ($i != 0 && $i != count($this->field2)){
          $retorno .= " OR ";
          }
          }
          break;
          case 'notbetween':
          foreach ($this->field2 as $field2){
          $retorno .= "$this->field1 NOT BETWEEN $field2";
          $i = ++$i;
          if ($i != 0 && $i != count($this->field2)){
          $retorno .= " OR ";
          }
          }
          break;
       }
       return $retorno;
       }
    //method to 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 as $collums){
             if($i< count($fields)-1){
             $resultfields .= $collums.', ';
          }else{
             $resultfields .= $collums;
          }
          $i = ++$i;

       }
       }else{
          $resultfields = $fields;
       }
       $query = $this->queryRow("SELECT $resultfields FROM $this->table WHERE ".$this->wheres($_POST['select']));   
       $retorno = "<table>\n";
       foreach($query as $querycollum){
       $retorno .= "<tr>";
       if(is_array($fields)){
       foreach($fields as $collumstable){
          $retorno .= "<td>$querycollum[$collumstable]</td>";
             }
       $retorno .= "</tr>\n";
       }
       }   
       $retorno .= "</table>\n";
       return $retorno;
       }
    }
    }
    ?>

The form that fetches the data looks like this:

    <?php
    include('search.class.php');
    $search = new search;
    //table to search
    $search->table = 'xxxxxxxxxx';
    //array to show results
    $result = array('column1', 'column2');
    ?>
    <p> Insert a field to search</p>
    <p> For user between, an not between, use the boollean operator AND </p>
    <p> For search with more words, use the boollean operator OR</p>
    <form action="<?=$_SERVER['PHP_SELF']?>" method="post">
    <?=$search->fieldSelect()?>
    <?=$search->whereSelect()?>
    <?=$search->fieldText(10,20)?>
    <input type="submit" name="submit" value="submit" />
    </form>
    <?=$search->result($result)?>

However, I wish to do some changes thus:
1. Show only some columns in the fieldSelect, not all the columns in the table. The code that seems to pull the columns in search.class.php is:

    //method to list the fields
    function fieldSelect(){   
       $query = $this->queryRow('SHOW FULL COLUMNS FROM '.$this->table);

Here it is:Click Here

I have tried different variations of sql select to no avail.

  1. I wish to expand the search criteria. I actually want the students data sorted by year ( this is year of admission) then 'search criteria.

That is, one should first choose the year, then other conditions like Gender, County, District and Orphan status of the student.

It seems I should be able to achieve this using this php class, I only dont know where to change the code.

Any help is greatly appreciated.

Member Avatar for diafol

I found a php class

This class is pretty bad.

Any ideas for a better solution for my problem?

Member Avatar for diafol

Yes, but it's gonna take a while. I will say that you should never expose the fieldnames/tablenames etc in your forms. Always use aliases if possible. Also PDO should use prepared queries with bound parameters - not place POST vars directly into the SQL - that's just asking for SQL injection.

I'll see if I can bang a couple of functions together. Maybe a class.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.