melo-db/Retrieve.php at master · astro4dev/melo-db · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
<!DOCTYPE html>
<html lang = "en-UK">
<!----------------------------------------------------------------------------->
  <head>

  <meta charset = "UTF-8">

  <title>Retrieve Data</title>

  <img src="IAU.jpg" alt="IAU header picture" width="1210"/img>

  <style type = "text/css">
    body{background-color: lightgray};
    table, th, td {border: 1px solid black};
    h1, h3, p, form{color: solid black};
  </style>

  <h1>OAD Projects</h1>

  </head>
<!----------------------------------------------------------------------------->
<body >

  <form action="Retrieve.php" method="POST">

  <h3> Query past funded projects </h3>

  <p align="center">Select a field and input a search value:</p>
<!----------------------------------------------------------------------------->
<div align="center">

<select name="field" >
  <option value=" "> </option>
  <option value="AllOpt">Everything</option>
  <option value="TtlOpt">Title</option>
  <option value="TFOpt">Task Force</option>
  <option value="CtryOpt">Country</option>
  <option value="YrOpt">Year</option>
  <option value="KeyOpt">Keyword</option>

</select> <br><br>
<!----------------------------------------------------------------------------->
Project Name <br>
<input type="text" name="TtlValue"> <br><br>

Task Force (1,2,3) <br>
<input type="number" min="1" max="3" name="TFValue"> <br><br>

Country <br>
<input type="text" name="CtryValue"> <br><br>

Year implemented <br>
<input type="year" name="YrValue"> <br><br>

Keywords <br>
<input type="text" name="KeyValue"> <br><br>

<input type="submit"> <br><br>
<!----------------------------------------------------------------------------->
</div>
</form>
<!----------------------------------------------------------------------------->
<?php
  //<!--Building a normal MySQL connection-->
  $con = new PDO('mysql:host=localhost;dbname=BucketList',"root","melo8946");
  //$con = new PDO('mysql:host=dbint.astro4dev.org;dbname=melosbdb',"melo","R@!nf0r3st");
  $con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

	//----------------------------------------------------------------------------

  if($_POST['field'] =='TtlOpt'){$i = 0;}
    elseif ($_POST['field']=='TFOpt') {$i = 1;}
    elseif ($_POST['field']=='CtryOpt') {$i = 2;}
    elseif ($_POST['field']=='YrOpt') {$i = 3;}
    elseif ($_POST['field']=='KeyOpt') {$i = 4;}
    elseif ($_POST['field']=='AllOpt') {$j = 5;}

	$fieldInputs = array("TtlValue","TFValue", "CtryValue", "YrValue", "KeyValue", "Everything");
	$fieldSQL = array("Title", "Task_Force", "Country","Year_Implemented", "Proposal_Summary", "NULL");

  //----------------------------------------------------------------------------
  //<!--Determine your query-->
	$init_data = $_POST[$fieldInputs[$i]];

  if ($j==5){$query = "SELECT * FROM OAD_PastProjects" ;}
    elseif ($i<5){$query = "SELECT * FROM OAD_PastProjects WHERE $fieldSQL[$i]='$init_data'";}

  //<!--Print the table tag before extracting any results-->
  print "<table> ";

 	//<!--First pass to extract column/field names-->
 	$result = $con->query($query);
 	//return only the first row (we only need field names)
 	$row = $result->fetch(PDO::FETCH_ASSOC);

 	//<!--Print the field names as table headers-->
 	print "<tr> ";
 	foreach ($row as $field => $value) {
   	print "<th>$field</th> ";
 	}
 	print "</tr> ";

 	//<!--Make a second query-->
 	//second query gets the data
 	$data = $con->query($query);
 	$data->setFetchMode(PDO::FETCH_ASSOC);

  // Number of records of result
  $total = $data->rowCount();
  print "Number of resulting projects: " . $total;

 	//<!--Use nested loops to print out data elements-->
 	foreach ($data as $row) {
   	print " <tr> ";
   	foreach ($row as $name => $value) {
   	print " <td>$value</td> ";
   	}
   	print " </tr> ";
 	}
 	print "</table> ";
?>
<!----------------------------------------------------------------------------->
  </body>
</html>