Geek Answers Handbook

Creating a site to query a database of tables

I have a little problem. I work with some manual testers who do not do database programming / design. Our current process means that these hand-held testers must insert data into our database at a specific time while we create a graphical interface to facilitate this in the future.

In the meantime, I would like to create a simple site. What I would like to do with the site is simply connect to our database, let the hand tester enter some keywords and return any columns in the tables that are close / related to the provided keywords. This would save a lot of time for our testers to look for columns in our (rather large) database.

How can I create such a site? I think this can be useful for many people, so I decided to post the question here to collect StackOverflow thoughts.

Right now I am thinking of a simple PHP page with a text box that allows the user to enter some data, separated by commas. Expand the data based on commas, hold them in an array. Connect to my database, then use the "Information Schema Schema" to get information about the columns. My main problem is what is the most efficient way to use the information schema view to get columns related to keywords entered by users? How can I guarantee that the returned columns are the most suitable ?

Any input here would be greatly appreciated. Many thanks.

Tl; dr - bold, for busy people :)

+6
source share
7 answers

I think you could achieve this with a simple form and some ajax calls using the up keys. Here is a simple example in which the list will be updated every time the user enters a letter in the name of the column they are looking 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 perform 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'].'"'; } }

When the user selects a column name, an entire column name like this will be returned and updated on the fly, without reloading the page. Hope this helps

+2
source

You should do something like this:

the 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>"; ?>
+2
source

I understand why you are asking this interesting question. If the tester enters a list of keywords, and you use the information schema view to get a list of the relevant columns, then there is a risk that there will be many false matches that could waste time or cause the tester to enter incorrect information into your system. You want to know how to determine which columns are the best match for a tester request. But you want to keep it simple, because this is just a temporary solution, this is not your main application.

The answer is to complement search results using a reputation-based system. Here is a very simple one that should work well for your application.

First, create two simple tables to store rating information for tables and columns in your database. Here is the starting structure.

 TEST_SEARCH_TABLES: TABLE_ID TABLE_NAME RATING TEST_SEARCH_COLUMNS: COLUMN_ID TABLE_ID COLUMN_NAME RATING

Enter the TEST_SEARCH_TABLES name of each table in your database. Fill TEST_SEARCH_COLUMNS with the name of each column and bind it to the corresponding table. Initialize all RATING columns to 1000.0 - you will use the Elo Rating System to supplement your rating, because it is simple, easy to implement, and it works great.

When a user enters a list of keywords, do not use View Schema View. Instead, search the TEST_SEARCH_COLUMNS table for any columns containing any of these keywords. Assign each column a weight based on the number of hits. (For example, if the search is “customer, amount, income”, then the column CUSTOMER_ID will have a weight of 1. Column CUSTOMER_INCOME will have a weight of 2 and CUSTOMER_INCOME_AMOUNT will have a weight of 3.) Calculate the weight of each table is the sum of the weights of its columns.

Now for each table and column returned by your search, multiply the value of WEIGHT by RATING to determine the value of SEARCH. Give the tester a list of matching tables in descending order of search value. In each table, also indicate the columns in descending order of their search value.

Each time a column or column appears in a search, use the Elo rating system to give it WIN against an opponent with a rating of 1000.0. Each time the user selects a column for work, give both columns and his table a win against an opponent with a rating of 1500.0. Thus, the most useful and successful tables and columns will organically float at the top of the search list over time.

A side benefit of this approach (using tables instead of presenting an information scheme) is that this approach is more extensible. As an improvement, you can put the DESCRIPTION and COMMENTS columns in the TEST_SEARCH_TABLES and TEST_SEARCH_COLUMNS tables, and also search in these columns for keyword matches.

Here is another additional extension - you can place the (+) and (-) button next to each table and column and give it a win against a 2000-rated opponent if the user clicks (+) and loses with a zero rating if the user clicks (- ) This will allow your testers to vote for columns that they find important, and to vote against columns that always interfere.

+2
source

I am not sure if I fully understood your problem.

Take a look at this:

http://php.net/manual/en/function.mysql-list-tables.php

you can get all the tables in the database, save them in an array and then filter with your keywords

+1
source

I think that this can be done in the following steps without PHP programming and even without the need for any web server.

  • Write an SQL script that forces everyone to get the data you need.

  • Modify the script to add columns to the result set with simple html formatting to record the result as follows:

     '<tr><td>', 'resultcolumn1', '</td><td>', 'resultcolumn2','</td></tr>'
  • Run this script using sqlcmd with the ability to output to a file. Give the resulting extension file .html .
  • Place sqlcmd call inside cmd file. After calling sqlcmd call the web browser with the resulting html file name as a parameter. This will show your results for the tester.

So, your testers run only the cmd file with some parameters and get an html page with the results. Of course, you need to generate the correct html head and body tags, but this is not a problem.

Now about your main question, how can you be sure that the returned columns are the most suitable. I think the most reliable of the easiest ways is to create a thesaurus table that contains synonyms for your column names. (This can be done by the testers themselves). Thus, you can search for column names from the information schema view using LIKE in INFORMATION_SCHEMA.COLUMNS, as well as in the thesaurus table.

+1
source

Use the mysql_connect method if you are using mysql and enter data such as:

 INSERT INTO tablename

and everyone just read about it.

+1
source

Not sure if you want to spend time writing and supporting your decision. For php / mysql I would use http://www.phpmyadmin.net/home_page/index.php or if users can directly access db http://dev.mysql.com/downloads/gui-tools/5.0.html

It may take some time to teach them how to use, but will save a ton of problems in the long run.

Another thing, you can create * .sql files that will automatically fill db.

query.sql

 CREATE TABLE "example" ( "id" INT NOT NULL AUTO_INCREMENT, "name" VARCHAR(30), "age" INT ); INSERT INTO "example" VALUES ('1', 'a', 1), ('2', 'b', 2);

than you can run from the command line:

mysql -u USER -pPASSWORD database_name <filename.sql

+1
source

More articles:


All Articles