Menu
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.
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.
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 is the bolded part, for busy people :)
331
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
<!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
//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
132
You should do something like this:
Form:
<form action="search.php" method="post">
<textarea name="words"></textarea>
<input type="submit">
</form>
search.php
<?php
// You will need a DB user with enough permissions
$link = mysqli_connect($server,$user,$pass);
mysqli_select_db($link,$database_name);
print "<table>";
// Comma separated
$words = explode(",",$_POST['words']);
foreach ($words as $word)
{
$sql = "SELECT COLUMN_NAME FROM ".$database_name.".INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%".$word."%'";
$res = mysqli_query($link,$sql);
while ($row = mysqli_fetch_assoc($res))
{
print "<tr><td>".$row['COLUMN_NAME']."</td></tr>";
}
}
print "</table>";
?>
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
