Creating A Site To Query A Database Of Tables

May 28, 2023 Post a Comment

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 d

Solution 1:

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

Solution 2:

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>";

?>

Solution 3:

I'm not sure if I fully understood your issue

Take a look at this:

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

you can get all the tables on a database , store them in an array then filter them using your keywords

Solution 4:

I think this could be done in the following steps without any PHP programming and even without need in any web-server.

Write SQL-script which makes everything to retrieve data you need.
Modify script to add columns to result set with simple html-formatting to make you result record like the following:
```
'<tr><td>', 'resultcolumn1', '</td><td>', 'resultcolumn2','</td></tr>'
```
Run this script using sqlcmd with output option to file. Give resulting file .html extension.
Place sqlcmd call inside cmd file. After calling sqlcmd call web browser with resulting html file name as parameter. This will display your results to tester.

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

Now about your main question about how you can be sure the columns returned are the most suitable. I think the most reliable from the most simple ways is to create thesaurus table which contains synonyms for your column names. (This could be done by testers themselves). So you can search your column names from Information Schema View using LIKE in INFORMATION_SCHEMA.COLUMNS as well as in thesaurus table.

Solution 5:

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

Might take some time to tech them how to use it, but will save a lot of problems in a long run.

Another thing, you can create *.sql files that would populate db automatically.

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 it from command line:

mysql -u USER -pPASSWORD database_name < filename.sql

Lab Sql