登录 | 注册 | FAQ
Anonymous

How to retrieve data from a database using a web form

+ 发表回复

18 篇帖子 1, 2


How to retrieve data from a database using a web form

smutolo » 周六 4月 19, 2014 8:18 pm

Hello everyone here,

I have a database which stores data input from a website. I want to querry that data using a web form, and output the data in a table according to the search criteria.

The form is here http://onlinestudentsadmission.com/scho ... taform.php

I have the PHP file to query the database ready and its working.

My problem is:

What code do I use to fetch data from the database to auto populate the form fields?

How do I code the PHP page to format the data in a printable table?

Here is the code for the form:
代码: 全选
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Students Data Retrieval Interface</title>
<link rel="stylesheet" type="text/css" href="view.css" media="all">
<script type="text/javascript" src="view.js"></script>

</head>
<body id="main_body" >
   
   <img id="top" src="top.png" alt="">
   <div id="form_container">
   
      <h1><a>Students Data Retrieval Interface</a></h1>
      <form id="form_830727" class="appnitro"  method="post" action="studentsdatadisplay.php">
               <div class="form_description">
         <h2>Students Data Retrieval Interface</h2>
         <p>This form connects to students database and retrieves data according to chosen criteria then prints it in a printable table.</p>
      </div>                  
         <ul >
         
               <li id="li_3" >
      <label class="description" for="element_3">Year Of Admission </label>
      <div>
      <select class="element select medium" id="element_3" name="element_3">
         <option value="" selected="selected"></option>
<option value="3" >Options from Database</option>

      </select>
      </div><p class="guidelines" id="guide_3"><small>This field is auto populated from the database column 'year'
</small></p>
      </li>      <li id="li_5" >
      <label class="description" for="element_5">County </label>
      <div>
      <select class="element select medium" id="element_5" name="element_5">
         <option value="" selected="selected"></option>
<option value="1" >Options From Database</option>

      </select>
      </div><p class="guidelines" id="guide_5"><small>This field is auto populated by data from the database column 'County'</small></p>
      </li>      <li id="li_6" >
      <label class="description" for="element_6">School </label>
      <div>
      <select class="element select medium" id="element_6" name="element_6">
         <option value="" selected="selected"></option>
<option value="1" >Options From Database</option>

      </select>
      </div><p class="guidelines" id="guide_6"><small>This field will is auto populated with data from the database table column 'School'</small></p>
      </li>      <li id="li_4" >
      <label class="description" for="element_4">Orphaned? </label>
      <span>
         <input id="element_4_1" name="element_4_1" class="element checkbox" type="checkbox" value="1" />
<label class="choice" for="element_4_1">No</label>
<input id="element_4_2" name="element_4_2" class="element checkbox" type="checkbox" value="1" />
<label class="choice" for="element_4_2">Yes-Father Deceased</label>
<input id="element_4_3" name="element_4_3" class="element checkbox" type="checkbox" value="1" />
<label class="choice" for="element_4_3">Yes-Mother Deceased</label>
<input id="element_4_4" name="element_4_4" class="element checkbox" type="checkbox" value="1" />
<label class="choice" for="element_4_4">Yes-Both Parents Deceased</label>

      </span>
      </li>      <li id="li_7" >
      <label class="description" for="element_7">Gender </label>
      <div>
      <select class="element select medium" id="element_7" name="element_7">
         <option value="" selected="selected"></option>
<option value="1" >Male</option>
<option value="2" >Female</option>
<option value="3" >All</option>

      </select>
      </div>
      </li>
         
               <li class="buttons">
             <input type="hidden" name="form_id" value="830727" />
            
            <input id="saveForm" class="button_text" type="submit" name="submit" value="Submit" />
      </li>
         </ul>
      </form>   
      <div id="footer">
         <a href="http://www.onlinestudentsadmission.com/schooldemo">BACK HOME</a>
      </div>
   </div>
   <img id="bottom" src="bottom.png" alt="">
   </body>
</html>


I want the fields School, County and Year to pull the options from the database.

Thanks.
头像

smutolo

  • 帖子: 14
  • 注册: 周六 4月 19, 2014 7:49 pm

Re: How to retrieve data from a database using a web form

smutolo » 周一 4月 21, 2014 5:30 am

CubeSquare 写道:Lesson 20 in the PHP Tutorial: Get data from database

If your not working your way through the PHP Tutorial maybe you ought to be.

Also, you code is strange. You use an XHTML doctype and HTML4 tags. And HTML5 is coming down the pike.

http://xhtml.com/en/xhtml/reference/form/ ought to help solve that.


Thanks for your reply. I however found a php class that seems to help me achieve my objective.

Here it 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 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)?>


Its working ok so far.See http://onlinestudentsadmission.com/schooldemo/dataform.php

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);


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

2. 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.

附件

  1. advanced_search-2009-01-14.zip (2.5 KiB) 被下载 2587 次
    This are all the files of the php advanced search class.

头像

smutolo

  • 帖子: 14
  • 注册: 周六 4月 19, 2014 7:49 pm

Re: How to retrieve data from a database using a web form

smutolo » 周一 4月 21, 2014 6:44 am

CubeSquare 写道:Hello smutolo,


This level of PHP is over my head. I apologize. I will bring this matter to the attention of XainPro who will be able to help you.

CubeSquare


Thats ok, CubesSquare. Thanks. I will wait for Xains intervention.
头像

smutolo

  • 帖子: 14
  • 注册: 周六 4月 19, 2014 7:49 pm

Re: How to retrieve data from a database using a web form

XainPro » 周一 4月 21, 2014 6:57 am

Thanks CubeSquare for bringing me here and smutolo the solution to your first issue would be changing that sql to something like this

function fieldSelect(){
$query = $this->queryRow('SHOW FULL COLUMNS FROM '.$this->table);

function fieldSelect(){
$query = $this->queryRow('SHOW FULL COLUMNS FROM '.$this->table. ' WHERE Field NOT IN ("id","friendly_url") ');

you can add all your fields you want to remove in the bold section of my query by comma separated.



about the 2nd issue you must populate a select box in search from all available years in database by making a query like this

select distinct(year) from table

then save the selected year into session after that use it in where clause with (AND)
头像

XainPro

  • 帖子: 3933
  • 注册: 周五 2月 17, 2012 8:10 pm

Re: How to retrieve data from a database using a web form

smutolo » 周一 4月 21, 2014 7:31 am

XainPro 写道:Thanks CubeSquare for bringing me here and smutolo the solution to your first issue would be changing that sql to something like this

function fieldSelect(){
$query = $this->queryRow('SHOW FULL COLUMNS FROM '.$this->table);

function fieldSelect(){
$query = $this->queryRow('SHOW FULL COLUMNS FROM '.$this->table. ' WHERE Field NOT IN ("id","friendly_url") ');

you can add all your fields you want to remove in the bold section of my query by comma separated.



about the 2nd issue you must populate a select box in search from all available years in database by making a query like this

select distinct(year) from table

then save the selected year into session after that use it in where clause with (AND)



Thanks alot XainPro and Easter greetings from Kenya. I have successfully ommitted the columns that I dont want in the first search box. I however have a problem understanding how to add another search box from where, a user will select the columns Gender, County, District and Orphan Status, then an extra search box where they will define the conditions of the preceding selection.

As in, a user querries the db in the following criteria.

year (selectt)>>( enters year manually) >> Select either Gender, County, District,Orphan >> States the condition (e.g. Gender-Male or Female, Orphan ( Yes or No)>> Submit.


Hope am clear enough Xain. I wait fro your help.
头像

smutolo

  • 帖子: 14
  • 注册: 周六 4月 19, 2014 7:49 pm

Re: How to retrieve data from a database using a web form

smutolo » 周一 4月 21, 2014 8:31 am

Basically, I want a form that looks like this http://onlinestudentsadmission.com/schooldemo/dataform.php

I just added new search boxes like:
代码: 全选
<?=$search->fieldSelect()?>
IS
<br>
<br>
<?=$search->whereSelect()?>
TO
<br>
<br>
<?=$search->fieldText(10,20)?>
AND
<br>
<br>
<?=$search->field1Select()?>
IS
<br>
<br>
<?=$search->whereSelect()?>
TO
<br>
<br>
<?=$search->field2Text(10,20)?>
<br><br>
<input type="submit" name="submit" value="submit" />
</form>   


Where in the code should I change to make such search combinations work?

Thanks.
头像

smutolo

  • 帖子: 14
  • 注册: 周六 4月 19, 2014 7:49 pm

Re: How to retrieve data from a database using a web form

XainPro » 周一 4月 21, 2014 12:55 pm

i am afraid you can not do this with just changing a line of code in this class.
you need to develop a custom function and or class for this or extend your query in single search form.
头像

XainPro

  • 帖子: 3933
  • 注册: 周五 2月 17, 2012 8:10 pm

Re: How to retrieve data from a database using a web form

smutolo » 周一 4月 21, 2014 7:14 pm

XainPro 写道:i am afraid you can not do this with just changing a line of code in this class.
you need to develop a custom function and or class for this or extend your query in single search form.



Thanks XainPro. For now, I will be content with searching only one column ( year) then displaying the associated data as I look for a better way of accomplishing this task.

One thing though, I want to display the data output in a clean table. How do I achieve this?

The output data contains the fields: Admission No., First Name, Last Name, Gender, County, District and Orphan Status. ( i.e. 7 columns) The rows will be dynamically determined after the querry. What code can I use and where could I place it in the php class?

Thanks once more for your advice.
头像

smutolo

  • 帖子: 14
  • 注册: 周六 4月 19, 2014 7:49 pm

Re: How to retrieve data from a database using a web form

smutolo » 周一 4月 21, 2014 8:05 pm

I have this code to print the table:
代码: 全选
print "
        <table border=\"5\" cellpadding=\"5\" cellspacing=\"0\" style=\"border-collapse: collapse\" bordercolor=\"#808080\" width=\"100&#37;\" id=\"AutoNumber2\" bgcolor=\"#C0C0C0\"><tr>
        <td width=100>Admission:</td>
        <td width=100>Last Name:</td>
        <td width=100>First Name:</td>
        <td width=100>Gender:</td>
        <td width=100>County:</td>
        <td width=100>District:</td>
        <td width=100>Orphan Status:</td> 
        </tr>";

       while($row = mysql_fetch_array($result, MYSQL_ASSOC))
        {
        print "<tr>";
        print "<td>" . $row['Admission'] . "</td>";
        print "<td>" . $row['last_name'] . "</td>";
        print "<td>" . $row['first_name'] . . "</td>";
        print "<td>" . $row['Gender'] . "</td>";
        print "<td>" . $row['County'] . "</td>";
        print "<td>" . $row['District'] . "</td>";
        print "<td>" . $row['Orpan'] . "</td>";
        print "</tr>";
        }
        print "</table>";
        ?>


I however fail to know where I could place it ( or even whether its compatible with the class!) to get the results in a nice table as the one given by the code above.

In the search.php, the data is printed after the code:
代码: 全选
<?=$search->result($result)?>


Thanks.
头像

smutolo

  • 帖子: 14
  • 注册: 周六 4月 19, 2014 7:49 pm

Re: How to retrieve data from a database using a web form

XainPro » 周二 4月 22, 2014 9:13 am

Use loop statement to show your record in table

<table border="1">
<tr>
<th>Sr.</th>
<th>Name</th>
<th>Field 1</th>
<th>Field 2</th>
<th>Field 3</th>
<th>Field 4</th>
<th>Field 5</th>
<th>Field 6</th>
</tr>

<tr>
<?php
while($row = mysql_fetch_array($result)):
?>
<td><?=$row['fieldname'];?></td>
<?php
endwhile;
?>
</tr>

</table>

Replace fieldname with your field names is database this is just an idea there are lots of way for doing this simple task.
头像

XainPro

  • 帖子: 3933
  • 注册: 周五 2月 17, 2012 8:10 pm


+ 发表回复

1, 2