我正在尝试使用PHP创建一个应用程序（基本上只是一个表单），以帮助用户查询用于存储测试结果的现有大型MySQL数据库。计划是要有一个功能强大的服务器来查询和处理现有数据库中的数据，并将有关该数据的报告发送到要显示的应用程序。
现有数据库可能用于新类型的测试数据，因此表格必须能够基于新数据类型进行更改。能够为应用程序提供某种管理面板，该面板可以根据用户请求配置表单上可用的选项供用户选择，这也很棒。我真的不想管理这个应用程序的用户帐户，我希望它是通用的。
对于我来说，创建MySQL表来管理表单似乎是合乎逻辑的。错了吗在大型数据库本身中是否应该存在管理用于访问大型数据库的应用程序的表，而不是专门为接口PHP应用程序创建数据库？
表格生成：
<?php
$dbhost = "";
$dbuser = "";
$dbpassword = "";
$dbname = "";
$dbport = "";
//*/
//Data types lookup table: Array of short names each with 2nd dimension array
//containing table name in database, Human Name
$url = $_SERVER['QUERY_STRING'];
$data = array
(//$type $name[0] = table name $name[1] = Readable name
//key, short name?
//Lookup table (parameter/form enable/disable? database?)
'tbl1'=>array('table1_data_tbl1', ''
),
'tbl2'=>array('table2_data_tbl2', ''
),
'tbl3'=>array('table3_data_tbl3', ''
),
'tbl4'=>array('table4_data_tbl4', ''
),
'tbl5'=>array('table5_data_tbl5', ''
),
'tbl6'=>array('table6_data__tbl6', ''
)
);
error_reporting(E_ALL);
//I COPIED THIS CODE
//-copied-Creates two dimensional array ($params) containing $_GET data, creating an
//-copied-array for each unique $_GET parameter containing their values.
if(isset($_GET["run"])||isset($_GET["data"])){
$query = explode('&', $_SERVER['QUERY_STRING']);
$params = array();
foreach( $query as $param )
{
list($name, $value) = explode('=', $param);
$params[urldecode($name)][] = urldecode($value);
}
//If the run has been selected: create region query, update url variable.
if(isset($_GET["run"])){
$id_run_rgn_query = implode(" OR id_run_rgn = ",$params["run"]);
$id_run_PARAM1_query = implode(" OR id_run_PARAM1 = ",$params["run"]);
$id_run_url = implode("&run=",$params["run"]);
}
//If data type has been selected: create run query?, update url variable.
if(isset($_GET["data"])){
$id_run_tbl4_query = implode(" OR id_run_tbl4 = ",$params["data"]);
$id_run_tbl5_query = implode(" OR id_run_tbl5 = ",$params["data"]);
$id_run_tbl3_query = implode(" OR id_run_tbl3 = ",$params["data"]);
$id_run_tbl6_query = implode(" OR id_run_tbl4 = ",$params["data"]);
$id_run_tbl2_query = implode(" OR id_run_tbl4 = ",$params["data"]);
$id_data_url = implode("&data=",$params["data"]);
}
if(isset($_GET["data"])){
}
}
?>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title></title>
<link rel="stylesheet" type="text/css" href="view.css" media="all">
<script type="text/javascript" src="view.js"></script>
<script src="jquery.js"></script>
<script src="addInput.js"></script>
<script src="jquery.dform.js"></script>
</head>
<body id="main_body" >
<?php
$db=mysqli_connect($dbhost,$dbuser,$dbpassword,$dbname,$dbport);
if (!$db)
{
echo "Failed to connect to MySQL: " . mysql_error();
}
?>
<img id="top" src="form/top.png" alt=""/>
<div id="form_container">
<h1><a></a></h1>
<div class="form_description">
<h2></h2>
<p>Retrieve testing data from database.</p>
</div>
<form id="myform">
<script type="text/javascript">
var phpurl = "<?php echo $url; ?>";
$(function() {
// Generate a form
$("#myform").dform({
"action" : "createquery.php",
"method" : "get",
"target" : "blank",
"html" :
[
{
"name" : "data",
"id" : "data",
"caption" : "Select category of data:<br>",
"type" : "select",
"options" : {
<?php
//$data = ['tbl3','tbl4','tbl5','table1_cw','dist','tbl6','tbl2'];
$i=0;//increment for selected datatypes
$j=1;//increment for number of datatypes, used to omit final comma
foreach ($data as $type => $name){
echo '"'.$type.'" : ';
if(isset($params["data"][$i]) && ($params["data"][$i] == $type)){
echo '{';
echo '"selected" : "selected",';
echo '"html" : "'.$name[1].'"';
echo '}';
$i++;
}
else{
echo '"'.$name[1].'"';
}
if(sizeof($data)!=$j++){
echo ',';
//Formatting, PHP does not allow escape chars in single quotes
echo "\n ";
}
}
echo "\n";
?>
},
"size" : <?php echo sizeof($data);?>,
"multiple" : "multiple"
},
{
"type" : "br"
}
<?php
//List experiment runs available if data is selected
if(isset($params["data"][0])){?>
,{
"name" : "run",
"id" : "run",
"caption" : "Select experiments:<br>",
"type" : "select",
"options" : {
<?php
// $totalruns is value returned from query:
// SELECT COUNT(id_run) FROM run_run;
$totalruns=291;
$i=0;
$j=0;
$distinct = array();
$numdata = sizeof($params["data"]);
foreach ($data as $type => $name){
if(in_array($type,$params["data"]) && sizeof($distinct) < $totalruns){
$j++;
$sql = "SELECT DISTINCT id_run_".$type.
" FROM ".$name[0];
$rs_result = mysqli_query ($db,$sql);
$temp_distinct = array();
$num_run = mysqli_num_rows($rs_result);
//Only get results if less than total runs
if($num_run <= $totalruns){
while( $row = mysqli_fetch_array( $rs_result)){
$temp_distinct[] = $row['id_run_'.$type]; // Inside while loop
}
$distinct = array_unique(array_merge($temp_distinct,$distinct));
}
//If the data type is involved in EVERY run, nothing is distinct
else {$distinct = "";break;}
$i++;
if($i==$numdata){break;}
//if(sizeof($distinct) >= $totalruns){
// break;
//}
}
}
if($distinct!=""){
$distinctquery = implode(" OR id_run = ",$distinct);
}
else{
$distinctquery = "";
}
//*Verify it's working:
echo '"777" : "Queries Performed: '.$i.'",';
echo '"778" : "Loops before break: '.$j.'",';
echo '"779" : "dataparam: '.$params["data"][0].'",';
echo '"780" : "Size: '.sizeof($distinct).'",';
//foreach($params["data"] as $)
$sql = "SELECT id_run,experiment_run FROM run_run";
//"FROM run_run" .
if(!empty($distinctquery)){$sql=$sql." WHERE id_run = ".$distinctquery;}
" ORDER BY id_run ASC";
$rs_result1 = mysqli_query ($db,$sql);
$num_run = mysqli_num_rows($rs_result1);
//echo '"num_run" : "'.$num_run.'",';
$i=0;
$j=1;
while ($row = mysqli_fetch_array($rs_result1)) {
echo '"'.$row["id_run"].'" : ';
if(isset($params["run"][$i]) && ($params["run"][$i] == $row["id_run"])){
echo '{';
echo '"selected" : "selected",';
echo '"html" : "'.$row["experiment_run"].'"';
echo '}';
$i++;
}
else{
echo '"'.$row["experiment_run"].'"';
}
if($num_run!=$j++){
echo ',';
}
}
mysqli_close($db);
?>
},
"size" : 7,
"multiple" : "multiple"
}
<?php
}//endbracket for data isset check
echo "]});";
if(isset($_GET["run"])){?>
$("#myform").append('<table id="myTable"><tr><th>Parameter</th><th>Filter</th><th>Usage</th></tr>');//\n\
$("#myTable tr:last").after("<tr><td>Param 1 Name</td><td>");
$("#myTable td:last").dform({
"html" :
[
{
"type" : "checkbox",
"name" : "pePARAM1",
"id" : "pePARAM1",
"value" : "0",
"caption" : ""
<?php if(isset($_GET['pePARAM1'])){echo ',"checked" : ""';}?>
}
]
});
$("#myTable td:last").after("</td><td>");
$("#myTable td:last").dform({
//"html" :
//[
//{
"name" : "poPARAM1",
"id" : "poPARAM1",
"caption" : "",//</td><td>",
"type" : "select",
"options" :
{
"xprm" : "X axis",
"yprm" : "Y axis",
"avg" : "AVERAGE",
"grp" : "GROUP BY"
},
"size" : "1"
//"disabled" : ""
});
//Parameter Entry 2
$("#myTable tr:last").after("<tr><td>Param 2 Name</td><td>");
$("#myTable td:last").dform({
"html" :
[
{
"type" : "checkbox",
"name" : "pePARAM2",
"id" : "pePARAM2",
"value" : "0",
"caption" : ""
<?php if(isset($_GET['pePARAM2'])){echo ',"checked" : ""';}?>
}
]
});
$("#myTable td:last").after("</td><td>");
$("#myTable td:last").dform({
//"html" :
//[
//{
"name" : "poPARAM2",
"id" : "poPARAM2",
"caption" : "",//</td><td>",
"type" : "select",
"options" :
{
"xprm" : "X axis",
"yprm" : "Y axis",
"avg" : "AVERAGE",
"grp" : "GROUP BY"
},
"size" : "1"
//"disabled" : ""
});
//End of Parameter Select List
$("#myTable td:last").after("</td></tr></table>");
$("#myform").dform({
"html" :
[
{"type":"br"
//"caption":"</td></tr></table>"
},
<?php
//Parameter 1 Form
if(isset($_GET["pePARAM1"])){
?>
{
"type" : "div",
"class" : "pePARAM1",
"html" :
[
{
"type" : "select",
"name" : "PARAM1",
"id" : "PARAM1",
//"class" : "PARAM1"
"caption" : "Select Param 1 Name<br>",
"options" : {
<?php
$db=mysqli_connect($dbhost,$dbuser,$dbpassword,$dbname,$dbport);
if (!$db)
{
echo "Failed to connect to MySQL: " . mysql_error();
}
$sql = "SELECT id_PARAM1,subparam1_PARAM1,subparam2_PARAM1
FROM PARAM1_PARAM1
WHERE id_run_PARAM1 = ".$id_run_PARAM1_query
." ORDER BY subparam1_PARAM1 ASC";
$rs_result = mysqli_query ($db,$sql);
$num_PARAM1 = mysqli_num_rows($rs_result);
$i=1;
while ($row = mysqli_fetch_array($rs_result)) {
echo '"'.$row["id_PARAM1"].'" : ';
echo '"Param 1 Name '.$row["id_PARAM1"].', Parameter of Param '.$row["subparam_PARAM1"].', Another Sub Parameter'.$row["subparam_PARAM1"].'"';
if($num_PARAM1!=$i++){
echo ',';
}
}
mysqli_close($db);
?>
},
"size" : 7,
"multiple" : "multiple",
"disabled" : "false"
}
]
},
<?php }
//Parameter 2 Form
if(isset($_GET["pePARAM2"])){
?>
{
"type" : "div",
"class" : "pePARAM2",
"html" :
[
{
"type" : "checkbox",
"id" : "element0",
"value" : "element0",
"caption" : "0"
},
{"type":"br"},
{
"type" : "select",
"name" : "region",
"id" : "region",
"caption" : "Select Region<br>",
"options" : {
<?php
$db=mysqli_connect($dbhost,$dbuser,$dbpassword,$dbname,$dbport);
if (!$db)
{
echo "Failed to connect to MySQL: " . mysql_error();
}
$sql = "SELECT id_rgn,id_param1_rgn,param2_rgn
FROM region_rgn
WHERE id_run_rgn = ".$id_run_rgn_query."
ORDER BY id_rgn ASC";
$rs_result1 = mysqli_query ($db,$sql);
$num_rgn = mysqli_num_rows($rs_result1);
$i=1;
while ($row = mysqli_fetch_array($rs_result1)) {
echo '"'.$row["id_rgn"].'" : ';
echo '"Param 1 Name '.$row["id_param1_rgn"].', Param 2 Name '.$row["param2_rgn"].'"';
if($num_rgn!=$i++){
echo ',';
}
}
mysqli_close($db);
?>
},
"size" : 7,
"multiple" : "multiple"
}
]
},
<?php }?>
{
"type" : "submit",
"id" : "submit",
"value" : "Get Data"
}
]
});
<?php }?>
});
</script>
<script>
var url = '';
$( document ).ready(function() {
$( "#data").click(function( event ) {
var selectedValues = [];
$("#data :selected").each(function(){
selectedValues.push($(this).val());
});
selectedValuesUrl = selectedValues.join('&data=');
window.location.href = "createqueryform.php?data=" + selectedValuesUrl;//"data=tbl4&run=7";//<?php //echo "data=tbl4&run=7"; ?>;
event.preventDefault();
});
$( "#run").click(function( event ) {
var selectedValues = [];
$("#run :selected").each(function(){
selectedValues.push($(this).val());
});
selectedValuesUrl = "&run=" + selectedValues.join('&run=');
window.location.href = "createqueryform.php?" + phpurl + selectedValuesUrl;//"data=tbl4&run=7";//<?php //echo "data=tbl4&run=7"; ?>;
event.preventDefault();
});
$( "#pePARAM1, #pePARAM2").click(function( event ) {
//var phpurl = "<?php //echo $url; ?>";
var param = $(this).attr('id');
var flag = "&" + param + "=1";
if ($(this).is(":checked")){
window.location.href = "createqueryform.php?" + phpurl + flag;
}
else
{
phpurl = phpurl.replace(flag,'');
var div = '.' + param;
$(div).remove();
}
});
});
</script>
_注意：为了掩盖我正在使用的测试数据的类型，我已经用通用名称替换了表名称，只是代码太恐怖了。此代码未完成。我仍在决定要用于人们生成查询的表单对象。最初的问题是指我是否应该拥有一个数据库（与使用此表单查询的数据库分开），该数据库可以管理用户可用的表单对象，并帮助扩展可用数据的类型。
最佳答案
在现有数据库中，为每个表单元素创建一个表格：类别，实验，参数，区域等。除非完全不相关的数据，否则不需要来自完全不相关的应用程序的另一个数据库。
或者在同一个数据库中仅创建一个名为“ testing_options”的表，表单元素共享一个名为“ key”的列，该列将保存其类型的元素（类别，实验，参数，区域）及其值保存在名为column的列中'值'。因此，键/值表可服务于表单选项。
关于php - 我是否需要第二个数据库来管理我的PHP表单，该表单可为用户提供从大型现有数据库访问数据的权限？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/21174571/