<?php
	session_start();
	include("ini.php");
	$db = $_SESSION["Db"];
	$query = $_SESSION["query"];

	function DeleteLine($dbcon, $table, $delLine)
	{
		$sql = "SELECT * FROM ".$table;
		$rows = mysqli_query ($dbcon, $sql);
		$rowNr = 0;
		while($row = mysqli_fetch_array($rows))
		{
			if($rowNr == $delLine) break;
			$rowNr++;
		}

		$sql = "SHOW COLUMNS FROM ".$table;
		$cols = mysqli_query ($dbcon, $sql);
		$nrCols=0;
		$sql = "DELETE FROM ".$table." WHERE (";
		while($col = mysqli_fetch_array($cols))
		{
			$sql = $sql.$col[0]."='".$row[$nrCols]."' AND ";
			$nrCols++;
		}

		$sql = substr($sql, 0, -4).")";

		mysqli_query ($dbcon, $sql);
	}
	
	if(isset($_GET["AddTable"]) && $query != "")
	{
		$addtable = $_GET["AddTable"];
		$join  = $_GET["Join"];
		$dbcon = mysqli_connect (SERVER, USER, PASSWORD, $db);
		if (mysqli_connect_errno()==0)
		{
			$sql = "SELECT MAX(Pos) AS cPos FROM x_querytables";
			$tables = mysqli_query ($dbcon, $sql);
			if($table = mysqli_fetch_array($tables))
				$pos = $table["cPos"]+1;
			else
				$pos = 1;
			$sql = "INSERT INTO x_querytables (Query, QTable, QJoin, Pos) VALUES ('".$query."', '".$addtable."', '".$join."', '".$pos."')";
			mysqli_query ($dbcon, $sql);
		}
		mysqli_close($dbcon);
		if(substr($join,-4) != "JOIN")
		{
			echo "<html><meta http-equiv='refresh' content='1; URL=SelectData.php'><head><title>Automatische Weiterleitung erfolgt</title></head><body></body></html>";
			return;
		}

		$_SESSION["AddTable"] = $addtable;
	}
	
	if(isset($_SESSION["AddTable"]))
	{
		$dbcon = mysqli_connect (SERVER, USER, PASSWORD, $db);
		if (mysqli_connect_errno()==0)
		{
			$addtable = $_SESSION["AddTable"];
			if(isset($_GET["AddLine"]))
			{
				$fullfield = $_POST["LeftField"];
				$qfield = $_POST["QField"];
				$ltable=substr($fullfield,0,strpos($fullfield,'.'));
				$lfield=substr($fullfield,strpos($fullfield,'.')+1);
				$sql = "INSERT INTO x_queryjoins (Query, QTable, QField, LTable, LField) VALUES ('".$query."', '".$addtable."', '".$qfield."', '".$ltable."', '".$lfield."')";
				mysqli_query ($dbcon, $sql);
			}
	
			if(isset($_GET["DelLine"]))
				DeleteLine($dbcon, "x_queryjoins", $_GET["DelLine"]);
		}
		mysqli_close($dbcon);
	}
	
?>
<!DOCTYPE html>
<html lang="de">
	<head>
		<link rel="stylesheet" href="Standard.css">
		<title>Edit JOIN</title>
	</head>
	<body>
		<header>
			<h1>Add <?PHP echo $addtable; ?> to <?PHP echo $query; ?></h1>
		</header>
		<aside>
			<h2>Tabellen</h2>
			<p>
<?PHP 
	if($query != "")
	{
		$dbcon = mysqli_connect (SERVER, USER, PASSWORD, $db);
		if (mysqli_connect_errno()==0)
		{
			$sql = "SELECT QTable,QJoin FROM x_querytables WHERE Query='".$query."' ORDER BY Pos";
			$tables = mysqli_query ($dbcon, $sql);
			while($table = mysqli_fetch_array($tables))
				echo $table["QJoin"]." ".$table["QTable"]."<br>";
		}
		mysqli_close($dbcon);
	}
?>			
			</p>
		</aside>
		<article>
			<h2><?PHP echo $join; ?> <?PHP echo $addtable; ?> ON </h2>
			<section>
			<table>
				<tr><th>Tabelle.Feld</th><th>=</th><th><?PHP echo $addtable; ?>.Feld</th></tr>
<?PHP 
	if($query != "")
	{
		$dbcon = mysqli_connect (SERVER, USER, PASSWORD, $db);
		if (mysqli_connect_errno()==0)
		{
			$sql = "SELECT * FROM x_queryjoins WHERE Query='".$query."' AND QTable='".$addtable."'";
			$rows = mysqli_query ($dbcon, $sql);
			$rowNr = 0;
			while($row = mysqli_fetch_array($rows))
			{
				echo "<tr><td>".$row["LTable"].".".$row["LField"]."</td><td>=</td><td>".$addtable.".".$row["QField"]."</td>";
				echo "<td><a href='EditJoin.php?DelLine=".$rowNr."'><img src='Delete.png'></a></td></tr>";
				$rowNr++;
			}
		}
		mysqli_close($dbcon);
	}
?>			
				<tr><form action='EditJoin.php?AddLine=true' method='POST'>
				<td>
					<select name="LeftField">
<?PHP
	$dbcon = mysqli_connect (SERVER, USER, PASSWORD, $db);
	if (mysqli_connect_errno()==0)
	{
		$sql = "SELECT QTable FROM x_querytables WHERE Query='".$query."' AND QTable !='".$addtable."'";
		$tables = mysqli_query ($dbcon, $sql);
		while($table = mysqli_fetch_array($tables))
		{
			$sql = "SHOW COLUMNS FROM ".$table[0];
			$cols = mysqli_query ($dbcon, $sql);
			while($col = mysqli_fetch_array($cols))
				echo "<option>".$table[0].".".$col[0]."</option>";
		}
	}
	mysqli_close($dbcon);
?>						
					</select>
				</td><td>=</td>
				<td><?PHP echo $addtable; ?>.
					<select name="QField">
<?PHP
	$dbcon = mysqli_connect (SERVER, USER, PASSWORD, $db);
	if (mysqli_connect_errno()==0)
	{
		$sql = "SHOW COLUMNS FROM ".$addtable;
		$cols = mysqli_query ($dbcon, $sql);
		while($col = mysqli_fetch_array($cols))
			echo "<option>".$col[0]."</option>";
	}
	mysqli_close($dbcon);
?>						
					</select>
				</td>
				<td><input type='submit' value='Add'></td></form></tr>
			</table>
			</section>
		</article>
		<aside>
			<h2>Aktionen</h2>
			<p>
				<a href="SelectData.php">Zur&uuml;ck zur Abfrage</a>
			</p>
		</aside>
		<footer>
			<p>&copy; 2019 by L&uuml;der Reimers</p>
		</footer>  
	</body>
</html>