创建一个站点以查询表数据库 [英] Creating a site to query a database of tables
问题描述
我有一个小问题。我正在与一些未经编程/数据库设计培训的手动测试人员一起工作。我们当前的流程意味着这些手动测试人员需要在某些时候将数据插入数据库中，同时我们还要构建一个GUI以便将来使用。
I have a small problem. I am working with some manual testers who are untrained in programming/database design. Our current process means that these manual testers need to insert data into our database at certain times whilst we build a GUI to facilitate this in the future.
想要创建一个简单的网站。我想对该站点进行的操作就是简单地连接到我们的数据库，允许手动测试人员输入一些关键字，并返回表中与提供的关键字接近/相关的所有列。这将为测试人员在我们的（相当大的）数据库中搜索列节省大量时间。
In the interim, I would like to create a simple site. What I would like to do with the site is, simply, connect to our database, allow the manual tester to enter some keywords, and return any columns within tables that are close/related to the keywords provided. This would save a lot of time for our testers searching for colums in our (rather large) database.
我如何创建这样的网站？我认为这可能对很多人有用，所以我决定在此处发布问题，以收集StackOverflow的想法。
How could I create a site like this? I think it could be useful for a lot of people, so I have decided to post the question up here to gather the thoughts of StackOverflow.
目前，我是考虑一个带有文本框的简单PHP页面，该文本框允许用户输入一些数据，以逗号分隔。根据逗号展开数据，并将其保存在数组中。连接到我的数据库，然后使用信息架构视图检索列信息。
我的主要问题是-使用信息架构视图检索与用户输入的关键字相关的列的最有效方法是什么？我如何确保返回的列最合适？
At the moment, I am thinking a simple PHP page with a textbox, which allows the user to enter some data, separated by commas. Explode the data based on commas, hold it in an array. Connect to my database, then use the Information Schema View to retrieve column information. My main problem is - what is the most effective way to use the Information Schema View to retrieve columns related to the keywords entered by the users ? How can I ensure the columns returned are the most suitable?
在此输入任何内容，将不胜感激。非常感谢。
Any input here would be greatly appreciated. Thanks a lot.
Tl; dr是加粗的部分，对于忙碌的人来说：）
Tl;dr is the bolded part, for busy people :)
推荐答案
我认为您可以使用简单的形式并使用on key up进行一些ajax调用来实现此目的。
这是一个简单的示例，其中，每当用户在要搜索的列名称中输入字母时，列表就会更新。
I think you could achieve this with a simple form and some ajax calls using on key up. Here is a simple example in which the list will update each time the user enters a letter in the column name they are searching for.
Index.html
Index.html
<!DOCTYPE html>
<html lang="en">
<head>
<script type="text/javascript">
$(document).ready(function() {
$("#faq_search_input").keyup(function()
{
var faq_search_input = $(this).val();
var dataString = 'keyword='+ faq_search_input;
if(faq_search_input.length>1)
{
$.ajax({
type: "GET",
url: "ajax-search.php",
data: dataString,
success: function(server_response)
{
document.getElementById("searchresultdata").style.display = "block";
$('#searchresultdata').html(server_response).show();
}
});
}return false;
});
});
</script>
</head>
<body>
<div class="searchholder">
<input name="query" class="quicksearch" type="text" id="faq_search_input" />
<div id="searchresultdata" class="searchresults" style="display:none;"> </div>
</div>
</body>
</html>
接下来，我们需要一个脚本来执行搜索
next we need a script to carry out our search
ajax-search.php
ajax-search.php
//you must define your database settings
define("DB_HOST", "FOO");
define("DB_USERNAME", "BAR");
define("DB_PASSWORD", "YOUR PASSWORD");
define("DB_NAME", "DATABASE NAME");
if(isset($_GET['keyword']))
{
$search = new mysqli(DB_HOST, DB_USERNAME, DB_PASSWORD, DB_NAME);
if ($search->connect_errno)
{
echo "Failed to connect to MySQL: (" . $search->connect_errno . ") " . $search->connect_error;
$search->close();
}
$keyword = trim($_GET['keyword']) ;
$query ="SELECT COLUMN_NAME FROM ".DB_NAME.".INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE '%".$keyword."%'";
$values = $search->query($query);
if($values->num_rows != 0)
{
while($row = $values->fetch_assoc())
{
echo $row['COLUMN_NAME']."<br>";
}
}
else
{
echo 'No Results for :"'.$_GET['keyword'].'"';
}
}
当用户键入列名时，所有这样的列名将被返回并即时更新，而无需重新加载页面。希望这对您有帮助
As the user types out a column name all of the column name like this will be returned and updated on the fly, without page reload. Hope this helps
这篇关于创建一个站点以查询表数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
