hi,
i m creating an OLAP engine.at first,user can choose a database from the selection list.then all the tables in the list will be listed down for user.user can select table(s) to see all the columns in the table and then they can join the related tables which primary key appear in other tables as foreign key.now i need to write a php function to check whether the selected tables can be joined and the way of joining multiple tables.do u have any idea?
the following is my coding:
1st file:a.php
<html>
<head><title>Prototype1</title>
</head>
<body>
<form action = "b.php" method = "post">
<?php
$conn = mysql_connect('localhost', 'root', '');
$db_list = mysql_list_dbs($conn);
print "<select name = db>"; //provide a list of database for users to choose
while ($row = mysql_fetch_object($db_list)) {
print "<option value='$row->Database'>$row->Database</option>";
}
print "</select><br \><br \>";
?>
<input type = "submit" value = "show table lists">
</form>
</body>
</html>
2nd file:b.php
<html>
<head>
<title>Prototype2</title>
<script type = "text/javascript">
<!--
function tableChoice () {
var table;
var checkboxElement = document.getElementById("olap").elements;
for(var index = 0;index<checkboxElement.length; index++){
if(checkboxElement[index].checked){
table = checkboxElement[index].value;
break;
}
}
}
var checkflag = "false";
function check(checkboxElement) {
var checkboxElement = document.getElementById("olap").elements;
if (checkflag == "false") {
for (var i = 0; i < checkboxElement.length; i++) {
checkboxElement*.checked = true;}
checkflag = "true";
return "Uncheck All"; }
else {
for (var i = 0; i < checkboxElement.length; i++) {
checkboxElement*.checked = false; }
checkflag = "false";
return "Check All"; }
}
//-->
</script>
</head>
<body>
<form id = "olap" action = "c.php" method = "post">
<?php
$conn = mysql_connect('localhost', 'root', '');
if (isset($db)){
mysql_select_db("$db", $conn);
$resultID = mysql_query("SHOW tables", $conn);
print "<input type=hidden name='db' value='$db'>";
//The first checkbox for checking all the checkeboxes
print "<tr align = 'center'>";
print "<td><input type= 'checkbox'
value='Check All'
onClick='olap.value=check(olap.tblnm)'></td>Check All<br/>";
print "</tr>";
//Listing all the tables in the selected database
while ($row = mysql_fetch_row($resultID))
{
print "<tr align = 'center'>";
foreach ($row as $field)
{
print "<td><input type= 'checkbox' name= 'tblnm[]' onclick = 'tableChoice()' value=$field>$field<br/>";
}
print "</tr>";
}
print "</table>";
print "<br \><br \>";
}
?>
<input type = "submit" value = "show table">
</form>
</body>
</html>
3rd file:c.php
<html>
<head>
<title>prototype3v3</title>
<script type = "text/javascript">
<!--
function jointable () {
var table;
var checkboxElement = document.getElementById("olap1").elements;
for(var index = 0;index<checkboxElement.length; index++){
if(checkboxElement[index].checked){
table = checkboxElement[index].value;
break;
}
}
}
//-->
</script>
</head>
<body>
<form id = "olap1" action = "d.php" method = "post">
<?
$conn = mysql_connect('localhost', 'root', '');
mysql_select_db("$db", $conn);
print "<input type=hidden name='db' value='$db'>";
$Table=array();
while( list($index,$v)=each($_POST[tblnm]) ) {
$result = mysql_query("select * from $v ") or die("Query failed: " . mysql_error());
$i=0;
while ($i < mysql_num_fields($result)) {
$meta = mysql_fetch_field($result, $i);
$Table[$v][$meta->name]=$meta;
$i++;
}
}
while( list( $table_name , $fields )=each($Table) ){
print "<i>$table_name</i>";
print "<table border = 1>
";
while ( list(,$field)=each( $fields )) {
//if the field is primary key and appears in other tables,then the background color is yellow;
//if the field is primary key but doesnt appear in other tables,then the background color is pink;
//if the field is not primary key and doesnt appear in other tables,then the background color is white;
if($field->primary_key){
$bg=( count_fieldname($field->name, $Table) >1 )?"yellow":"pink";
print"<tr><td bgcolor='$bg' ><input type='checkbox' onclick = 'jointable()'
name='fieldnm[$table_name][]' value='$field->name' >$field->name</td></tr>
";
}else
print"<tr><td $c><input type='checkbox' onclick='jointable()' name='fieldnm[$table_name][]' value='$field>name'>$field->name</td></tr>
";
}
print "</table>";
print "<br \><br \>";
}
function count_fieldname($fname,$Tb){
$c=0;
while(list($k,$f)=each($Tb)){
if(isset($f[$fname]) )
$c++;
}
return $c;
}
?>
<input type = "submit" value = "show table">
</form>
</body>
</html>
4th file:d.php
<html>
<head>
<title>Prototype4</title>
</head>
<body>
<form id = "olap1" action = "e.php" method = "post">
<?php
$conn = mysql_connect('localhost', 'root', '');
mysql_select_db("$db", $conn);
print "<table border = 1>
";
while( list($tbname,$fields)=each($_POST[fieldnm])) {
while( list($index,$field)=each($fields) ) {
if(!empty($field)){
print"<tr><td>$tbname.$field</td></tr>";
}
}
}
print "</table>";
print "<br \><br \>";
?>
<input type = "submit" value = "join table">
</form>
</body>
</html>
i have to use MySQL as this is the user requirement.
all the databases,table names and column names which selected by the user is not fixed.they all must be presented in variables and all the variables can be posted to page by page.
any idea?
thanks.:flower:
