OK so I have a SQL database with six tables all in relation to each other, keeping these items in mind:
a) Table Name: Field_1, Field_2, ...
b) Field_1 is always the primary key for the table. All other fields are foreign keys.
1) urls: URL, Project, POC, Server, ChargeCode, DatabaseName
2) projects: URL, POC, Server, ChargeCode, DatabaseName
3) pocs: URL, Project, Server, ChargeCode, DatabaseName
4) servers: URL, Project, POC, ChargeCode, DatabaseName
5) chargecodes: URL, Project, POC, Server, DatabaseName
6) databasenames: URL, Project, POC, Server, ChargeCode
There is a simple website designed to interact with this database using HTML and PHP. On the home page of this site are several search boxes, each one corresponds to the primary key of each table, which searches that particular table with whatever is entered in the form field. When the 'Search' button is clicked it displays the results of the SQL query onto the page. I'll post the code for the first page and one of the search execution/result pages:
----------BEGIN_INDEX.HTML-------------
<html>
<head>
<title>Website Tracking Project Main</title>
</head>
<body bgcolor=#CCFFFF>
<br>
<font face="Arial Black"><i>Web App Tracking - Main Page</i></font>
<br><br>
<hr width="50%" align="left">
<br>
<font face="Arial">Search for any one of the following variables to obtain a list of all variables for the corresponding form field entry.</font>
<br><br>
<table>
<tr>
<th><u>Search By</u></th>
<th><u>Value</u></th>
<th></th>
</tr>
<form action="searchURL.php" method="POST">
<tr>
<td>URL:</td>
<td><input type="text" name="URL"></td>
<td><input type="submit" value="Search"></td>
</tr>
</form>
<form action="searchProject.php" method="POST">
<tr>
<td>Project:</td>
<td><input type="text" name="Project"></td>
<td><input type="submit" value="Search"></td>
</tr>
</form>
<form action="searchPOC.php" method="POST">
<tr>
<td>Point of Contact:</td>
<td><input type="text" name="POC"></td>
<td><input type="submit" value="Search"></td>
</tr>
</form>
<form action="searchServer.php" method="POST">
<tr>
<td>Server:</td>
<td><input type="text" name="Server"></td>
<td><input type="submit" value="Search"></td>
</tr>
</form>
<form action="searchChargeCode.php" method="POST">
<tr>
<td>Charge Code:</td>
<td><input type="text" name="ChargeCode"></td>
<td><input type="submit" value="Search"></td>
</tr>
</form>
<form action="searchDatabase.php" method="POST">
<tr>
<td>Database:</td>
<td><input type="text" name="DatabaseName"></td>
<td><input type="submit" value="Search"></td>
</tr>
</form>
</table>
</body>
</html>
-----------END_INDEX.HTML---------------
-----------BEGIN_searchURL.php-----------
<html><head><title>URL Search Result</title></head><body bgcolor=#CCFFFF>
<?php
$URL = $_POST["URL"];
$Project = $_POST["Project"];
$POC = $_POST["POC"];
$Server = $_POST["Server"];
$ChargeCode = $_POST["ChargeCode"];
$Database = $_POST["DatabaseName"];
$conn=@mysql_connect("localhost", "user", "pass")
or die("Could not connect");
$rs = @mysql_select_db("database_name", $conn)
or die("Could not select database");
$sql="SELECT * FROM urls WHERE URL LIKE '%" . $URL . "%'";
$rs=mysql_query($sql,$conn)
or die("Could not execute query");
$list = "<table border=\"1\" bordercolor=black width=\"100%\" cellpadding=\"2\">";
$list.="<tr><th><u>URL</u></th>";
$list.="<th><u>Project</u></th>";
$list.="<th><u>POC</u></th>";
$list.="<th><u>Server</u></th>";
$list.="<th><u>ChargeCode</u></th>";
$list.="<th><u>Database</u></th>";
$counter=0;
while($row= mysql_fetch_array($rs))
{
$counter++;
if ($counter % 2)
{
$newRowColor=fuchsia;
}
else
{
$newRowColor=yellow;
}
$list .= "<tr bgcolor=$newRowColor>";
$list .= "<td>".$row["URL"]."</td>";
$list .= "<td>".$row["Project"]."</td>";
$list .= "<td>".$row["POC"]."</td>";
$list .= "<td>".$row["Server"]."</td>";
$list .= "<td>".$row["ChargeCode"]."</td>";
$list .= "<td>".$row["DatabaseName"]."</td>";
$list .= "</tr>";
}
$list .= "</table><hr>";
echo($list);
?>
</body></html>
-------------END_searchURL.php--------------
All of this works fine. The next step is to allow the user to click on any of the table cells from the result of the first query, and conduct a second query that narrows down their search.
So if their first search was for any URL with the string 'abc', then they get the results in a table with all the URLs with that string within them. Then the user decides to click on one of the displayed table cells to narrow down the search, so they click on the table cell POC where the POC is 'xyz'. A second query is executed searching for any data that meets the criteria of the URL containing 'abc' from the original query AND also contains 'xyz' within the POC.
I'm pretty sure it is easy to make the table cells interactive and submit a second query. However I am not sure how to carry over the original query in addition! What do you think?
Reply With Quote
Bookmarks