2 Database samenevoegen

Status
Niet open voor verdere reacties.
R

Razing

Gebruiker
Lid geworden
13 okt 2008
Berichten
167
We hebben 2 databasen voor sellecties met spelers.
In de database worden de goals opgeslagen.

Dit zijn de printscreen

Database 1
naamloos1%282%29.bmp


Database 2
naamloos2.bmp


Kan ik deze database samen voegen zodat ik 1 overzicht krijg met topscoorders?
 
Wil ze permanent samenvoegen, of wil je de topscoorder berekenen over de 2 databases?

Ik denk dat het laatste makkelijker is dan het eerste, want dan ga je wel moeite krijgen om alle keys over te zetten; ik vermoed dat je dan zelf een scriptje moet maken (tenzij er ergens een verborgen functie is die ik niet ken)
 
Als je het laatste bedoelt wat inderdaad veel gemakkelijker is dan de eerste optie dan kun je gewoon gaan voor:
[sql]
SELECT * from db1.table1, db2.table1
[/sql]
Zolang ze maar op dezelfde server staan en de gebruiker rechten heeft voor beide databases.
 
EDIT: Het zijn trouwens 2 verschillende databases, dezelfde pagina en dezelfde host.
Ik heb even de pagina doorzocht, maar heb niet gevonden waar hij alles (*) uit de tabel haalt, hij haalt het volgens mij los op:

PHP: 
<?php
include('../cms/selectie/user.php');
$connection = mysql_connect("$host","$user","$password")
or die(mysql_error());
mysql_select_db("$txt_db_name",$connection);
mysql_select_db("$txt_db_name_2",$connection);
or die(mysql_error());

$pref = mysql_query("SELECT * FROM tplss_preferences WHERE ID = '0'",$connection)
or die(mysql_error());
$pdata = mysql_fetch_array($pref);
mysql_free_result($pref);

if(!session_is_registered('defaultseasonid_tplss') || !session_is_registered('defaultmatchtypeid_tplss') || !session_is_registered('defaultlanguage_tplss'))
{
	$_SESSION['defaultseasonid_tplss'] = $pdata['DefaultSeasonID'];
	$_SESSION['defaultmatchtypeid_tplss'] = $pdata['DefaultMatchTypeID'];
	$_SESSION['defaultlanguage_tplss'] = $pdata['DefaultLanguage'];
	$defaultseasonid = $_SESSION['defaultseasonid_tplss'];
	$defaultmatchtypeid = $_SESSION['defaultmatchtypeid_tplss'];
	$defaultlanguage = $_SESSION['defaultlanguage_tplss'];
}
else
{
	$defaultseasonid = $_SESSION['defaultseasonid_tplss'];
	$defaultmatchtypeid = $_SESSION['defaultmatchtypeid_tplss'];
	$defaultlanguage = $_SESSION['defaultlanguage_tplss'];
}

include('../selectie/language.inc');

$get_seasons = mysql_query("SELECT * FROM tplss_seasonnames WHERE SeasonPublish = '1' ORDER BY SeasonName DESC",$connection)
or die(mysql_error());
$get_types = mysql_query("SELECT * FROM tplss_matchtypes ORDER BY MatchTypeName",$connection)
or die(mysql_error());

$sort = $_REQUEST['sort'];
if(!isset($sort))
{
	$sort = 'goals';
}

?>
<table width="80%" border="0">

<tr>
<td width="8" align="right" valign="middle">
<b>#</b>
</td>

<td width="518" align="left" valign="middle">
<b>Spelers</b>
</td>

<td width="5" align="center" valign="middle">
<b>B</b>
</td>

<td width="5" align="center" valign="middle">
<b>I</b>
</td>

<td width="5" align="center" valign="middle">
<b>G</b>
</td>
</tr>

<?php

if($defaultseasonid != 0 && $defaultmatchtypeid != 0)
{
	$get_players = mysql_query("
	SELECT P.PlayerID AS id,
	P.PlayerLastName AS lastname,
	P.PlayerFirstName AS firstname,
	P.PlayerPublish AS publish,
	P.PlayerNumber AS number,
	COUNT( G.GoalPlayerID ) AS goals
	FROM tplss_seasons S
	LEFT OUTER JOIN tplss_players P ON P.PlayerID = S.SeasonPlayerID AND S.SeasonID = '$defaultseasonid'
	LEFT OUTER JOIN tplss_matches M ON M.MatchSeasonID = S.SeasonID AND M.MatchTypeID = '$defaultmatchtypeid'
	LEFT OUTER JOIN tplss_goals G ON G.GoalPlayerID = S.SeasonPlayerID AND G.GoalMatchID = M.MatchID AND G.GoalOwn = '0'
	WHERE P.PlayerID != '' AND P.PlayerPositionID != '5'
	GROUP BY id
	ORDER BY id
	",$connection)
	or die(mysql_error());

	$get_yellows = mysql_query("
	SELECT
	P.PlayerID AS id,
	COUNT(Y.YellowCardPlayerID) AS yellows
	FROM tplss_seasons S
	LEFT OUTER JOIN tplss_players P ON P.PlayerID = S.SeasonPlayerID AND S.SeasonID = '$defaultseasonid'
	LEFT OUTER JOIN tplss_matches M ON M.MatchSeasonID = S.SeasonID AND M.MatchTypeID = '$defaultmatchtypeid'
	LEFT OUTER JOIN tplss_yellowcards Y ON Y.YellowCardPlayerID = S.SeasonPlayerID AND Y.YellowCardMatchID = M.MatchID
	WHERE P.PlayerID != '' AND P.PlayerPositionID != '5'
	GROUP BY id
	ORDER BY id
	",$connection)
	or die(mysql_error());

	$get_reds = mysql_query("
	SELECT
	P.PlayerID AS id,
	COUNT(R.RedCardPlayerID) AS reds
	FROM tplss_seasons S
	LEFT OUTER JOIN tplss_players P ON P.PlayerID = S.SeasonPlayerID AND S.SeasonID = '$defaultseasonid'
	LEFT OUTER JOIN tplss_matches M ON M.MatchSeasonID = S.SeasonID AND M.MatchTypeID = '$defaultmatchtypeid'
	LEFT OUTER JOIN tplss_redcards R ON R.RedCardPlayerID = S.SeasonPlayerID AND R.RedCardMatchID = M.MatchID
	WHERE P.PlayerID != '' AND P.PlayerPositionID != '5'
	GROUP BY id
	ORDER BY id
	",$connection)
	or die(mysql_error());

	$get_apps = mysql_query("
	SELECT
	P.PlayerID AS id,
	COUNT(A.AppearancePlayerID) AS apps
	FROM tplss_seasons S
	LEFT OUTER JOIN tplss_players P ON P.PlayerID = S.SeasonPlayerID AND S.SeasonID = '$defaultseasonid'
	LEFT OUTER JOIN tplss_matches M ON M.MatchSeasonID = S.SeasonID AND M.MatchTypeID = '$defaultmatchtypeid'
	LEFT OUTER JOIN tplss_appearances A ON A.AppearancePlayerID = S.SeasonPlayerID AND A.AppearanceMatchID = M.MatchID
	WHERE P.PlayerID != '' AND P.PlayerPositionID != '5'
	GROUP BY id
	ORDER BY id
	",$connection)
	or die(mysql_error());

	$get_ins = mysql_query("
	SELECT
	P.PlayerID AS id,
	COUNT(SU.SubstitutionPlayerIDIn) AS ins
	FROM tplss_seasons S
	LEFT OUTER JOIN tplss_players P ON P.PlayerID = S.SeasonPlayerID AND S.SeasonID = '$defaultseasonid'
	LEFT OUTER JOIN tplss_matches M ON M.MatchSeasonID = S.SeasonID AND M.MatchTypeID = '$defaultmatchtypeid'
	LEFT OUTER JOIN tplss_substitutions SU ON SU.SubstitutionPlayerIDIn = S.SeasonPlayerID AND SU.SubstitutionMatchID = M.MatchID
	WHERE P.PlayerID != '' AND P.PlayerPositionID != '5'
	GROUP BY id
	ORDER BY id
	",$connection)
	or die(mysql_error());


}
elseif($defaultseasonid == 0 && $defaultmatchtypeid != 0)
{
	$get_players = mysql_query("
	SELECT P.PlayerID AS id,
	P.PlayerLastName AS lastname,
	P.PlayerFirstName AS firstname,
	P.PlayerPublish AS publish,
	P.PlayerNumber AS number,
	COUNT( G.GoalPlayerID ) AS goals
	FROM tplss_seasons S
	LEFT OUTER JOIN tplss_players P ON P.PlayerID = S.SeasonPlayerID
	LEFT OUTER JOIN tplss_matches M ON M.MatchSeasonID = S.SeasonID AND M.MatchTypeID = '$defaultmatchtypeid'
	LEFT OUTER JOIN tplss_goals G ON G.GoalPlayerID = S.SeasonPlayerID AND G.GoalMatchID = M.MatchID AND G.GoalOwn = '0'
	WHERE P.PlayerID != '' AND P.PlayerPositionID != '5'
	GROUP BY id
	ORDER BY id
	",$connection)
	or die(mysql_error());

	$get_yellows = mysql_query("
	SELECT
	P.PlayerID AS id,
	COUNT(Y.YellowCardPlayerID) AS yellows
	FROM tplss_seasons S
	LEFT OUTER JOIN tplss_players P ON P.PlayerID = S.SeasonPlayerID
	LEFT OUTER JOIN tplss_matches M ON M.MatchSeasonID = S.SeasonID AND M.MatchTypeID = '$defaultmatchtypeid'
	LEFT OUTER JOIN tplss_yellowcards Y ON Y.YellowCardPlayerID = S.SeasonPlayerID AND Y.YellowCardMatchID = M.MatchID
	WHERE P.PlayerID != '' AND P.PlayerPositionID != '5'
	GROUP BY id
	ORDER BY id
	",$connection)
	or die(mysql_error());

	$get_reds = mysql_query("
	SELECT
	P.PlayerID AS id,
	COUNT(R.RedCardPlayerID) AS reds
	FROM tplss_seasons S
	LEFT OUTER JOIN tplss_players P ON P.PlayerID = S.SeasonPlayerID
	LEFT OUTER JOIN tplss_matches M ON M.MatchSeasonID = S.SeasonID AND M.MatchTypeID = '$defaultmatchtypeid'
	LEFT OUTER JOIN tplss_redcards R ON R.RedCardPlayerID = S.SeasonPlayerID AND R.RedCardMatchID = M.MatchID
	WHERE P.PlayerID != '' AND P.PlayerPositionID != '5'
	GROUP BY id
	ORDER BY id
	",$connection)
	or die(mysql_error());

	$get_apps = mysql_query("
	SELECT
	P.PlayerID AS id,
	COUNT(A.AppearancePlayerID) AS apps
	FROM tplss_seasons S
	LEFT OUTER JOIN tplss_players P ON P.PlayerID = S.SeasonPlayerID
	LEFT OUTER JOIN tplss_matches M ON M.MatchSeasonID = S.SeasonID AND M.MatchTypeID = '$defaultmatchtypeid'
	LEFT OUTER JOIN tplss_appearances A ON A.AppearancePlayerID = S.SeasonPlayerID AND A.AppearanceMatchID = M.MatchID
	WHERE P.PlayerID != '' AND P.PlayerPositionID != '5'
	GROUP BY id
	ORDER BY id
	",$connection)
	or die(mysql_error());

	$get_ins = mysql_query("
	SELECT
	P.PlayerID AS id,
	COUNT(SU.SubstitutionPlayerIDIn) AS ins
	FROM tplss_seasons S
	LEFT OUTER JOIN tplss_players P ON P.PlayerID = S.SeasonPlayerID
	LEFT OUTER JOIN tplss_matches M ON M.MatchSeasonID = S.SeasonID AND M.MatchTypeID = '$defaultmatchtypeid'
	LEFT OUTER JOIN tplss_substitutions SU ON SU.SubstitutionPlayerIDIn = S.SeasonPlayerID AND SU.SubstitutionMatchID = M.MatchID
	WHERE P.PlayerID != '' AND P.PlayerPositionID != '5'
	GROUP BY id
	ORDER BY id
	",$connection)
	or die(mysql_error());

}
elseif($defaultseasonid != 0 && $defaultmatchtypeid == 0)
{
	$get_players = mysql_query("
	SELECT P.PlayerID AS id,
	P.PlayerLastName AS lastname,
	P.PlayerFirstName AS firstname,
	P.PlayerPublish AS publish,
	P.PlayerNumber AS number,
	COUNT( G.GoalPlayerID ) AS goals
	FROM tplss_seasons S
	LEFT OUTER JOIN tplss_players P ON P.PlayerID = S.SeasonPlayerID AND S.SeasonID = '$defaultseasonid'
	LEFT OUTER JOIN tplss_goals G ON G.GoalPlayerID = S.SeasonPlayerID AND G.GoalOwn = '0' AND G.GoalSeasonID = '$defaultseasonid'
	WHERE P.PlayerID != '' AND P.PlayerPositionID != '5'
	GROUP BY id
	ORDER BY id
	",$connection)
	or die(mysql_error());

	$get_yellows = mysql_query("
	SELECT
	P.PlayerID AS id,
	COUNT(Y.YellowCardPlayerID) AS yellows
	FROM tplss_seasons S
	LEFT OUTER JOIN tplss_players P ON P.PlayerID = S.SeasonPlayerID AND S.SeasonID = '$defaultseasonid'
	LEFT OUTER JOIN tplss_yellowcards Y ON Y.YellowCardPlayerID = S.SeasonPlayerID AND Y.YellowCardSeasonID = '$defaultseasonid'
	WHERE P.PlayerID != '' AND P.PlayerPositionID != '5'
	GROUP BY id
	ORDER BY id
	",$connection)
	or die(mysql_error());

	$get_reds = mysql_query("
	SELECT
	P.PlayerID AS id,
	COUNT(R.RedCardPlayerID) AS reds
	FROM tplss_seasons S
	LEFT OUTER JOIN tplss_players P ON P.PlayerID = S.SeasonPlayerID AND S.SeasonID = '$defaultseasonid'
	LEFT OUTER JOIN tplss_redcards R ON R.RedCardPlayerID = S.SeasonPlayerID AND R.RedCardSeasonID = '$defaultseasonid'
	WHERE P.PlayerID != '' AND P.PlayerPositionID != '5'
	GROUP BY id
	ORDER BY id
	",$connection)
	or die(mysql_error());

	$get_apps = mysql_query("
	SELECT
	P.PlayerID AS id,
	COUNT(A.AppearancePlayerID) AS apps
	FROM tplss_seasons S
	LEFT OUTER JOIN tplss_players P ON P.PlayerID = S.SeasonPlayerID AND S.SeasonID = '$defaultseasonid'
	LEFT OUTER JOIN tplss_appearances A ON A.AppearancePlayerID = S.SeasonPlayerID AND A.AppearanceSeasonID = '$defaultseasonid'
	WHERE P.PlayerID != '' AND P.PlayerPositionID != '5'
	GROUP BY id
	ORDER BY id
	",$connection)
	or die(mysql_error());

	$get_ins = mysql_query("
	SELECT
	P.PlayerID AS id,
	COUNT(SU.SubstitutionPlayerIDIn) AS ins
	FROM tplss_seasons S
	LEFT OUTER JOIN tplss_players P ON P.PlayerID = S.SeasonPlayerID AND S.SeasonID = '$defaultseasonid'
	LEFT OUTER JOIN tplss_substitutions SU ON SU.SubstitutionPlayerIDIn = S.SeasonPlayerID AND SU.SubstitutionSeasonID = '$defaultseasonid'
	WHERE P.PlayerID != '' AND P.PlayerPositionID != '5'
	GROUP BY id
	ORDER BY id
	",$connection)
	or die(mysql_error());

}
elseif($defaultseasonid == 0 && $defaultmatchtypeid == 0)
{
	$get_players = mysql_query("
	SELECT P.PlayerID AS id,
	P.PlayerLastName AS lastname,
	P.PlayerFirstName AS firstname,
	P.PlayerPublish AS publish,
	P.PlayerNumber AS number,
	COUNT( G.GoalPlayerID ) AS goals
	FROM tplss_players P
	LEFT OUTER JOIN tplss_goals G ON G.GoalPlayerID = P.PlayerID AND G.GoalOwn = '0'
	WHERE P.PlayerID != '' AND P.PlayerPositionID != '5'
	GROUP BY id
	ORDER BY id
	",$connection)
	or die(mysql_error());

	$get_yellows = mysql_query("
	SELECT P.PlayerID AS id, COUNT( Y.YellowCardPlayerID ) AS yellows
	FROM tplss_players P
	LEFT OUTER JOIN tplss_yellowcards Y ON Y.YellowCardPlayerID = P.PlayerID
	WHERE P.PlayerID != '' AND P.PlayerPositionID != '5'
	GROUP BY id
	ORDER BY id
	",$connection)
	or die(mysql_error());

	$get_reds = mysql_query("
	SELECT P.PlayerID AS id, COUNT( R.RedCardPlayerID ) AS reds
	FROM tplss_players P
	LEFT OUTER JOIN tplss_redcards R ON R.RedCardPlayerID = P.PlayerID
	WHERE P.PlayerID != '' AND P.PlayerPositionID != '5'
	GROUP BY id
	ORDER BY id
	",$connection)
	or die(mysql_error());

	$get_apps = mysql_query("
	SELECT P.PlayerID AS id, COUNT( A.AppearancePlayerID ) AS apps
	FROM tplss_players P
	LEFT OUTER JOIN tplss_appearances A ON A.AppearancePlayerID = P.PlayerID
	WHERE P.PlayerID != '' AND P.PlayerPositionID != '5'
	GROUP BY id
	ORDER BY id
	",$connection)
	or die(mysql_error());

	$get_ins = mysql_query("
	SELECT P.PlayerID AS id, COUNT( SU.SubstitutionPlayerIDIn ) AS ins
	FROM tplss_players P
	LEFT OUTER JOIN tplss_substitutions SU ON SU.SubstitutionPlayerIDIn = P.PlayerID
	WHERE P.PlayerID != '' AND P.PlayerPositionID != '5'
	GROUP BY id
	ORDER BY id
	",$connection)
	or die(mysql_error());

}

$i=0;
while($data = mysql_fetch_array($get_players))
{

		$idt[$i] = $data['id'];

		if($data['lastname'] == '')
		{
			$names[$i] = $data['firstname'];
		}
		else
		{
			$names[$i] = $data['lastname'] . ', ' . $data['firstname'];
		}

		$publish[$i] = $data['publish'];
		$numbers[$i] = $data['number'];
		$goals[$i] = $data['goals'];
		$i++;

}

$qty = mysql_num_rows($get_players);

mysql_free_result($get_players);


$i=0;
while($data = mysql_fetch_array($get_yellows))
{
		$yellows[$i] = $data['yellows'];
		$i++;
}
mysql_free_result($get_yellows);

$i=0;
while($data = mysql_fetch_array($get_reds))
{
		$reds[$i] = $data['reds'];
		$i++;

}
mysql_free_result($get_reds);

$i=0;
while($data = mysql_fetch_array($get_apps))
{

		$apps[$i] = $data['apps'];
		$i++;
}
mysql_free_result($get_apps);

$i=0;
while($data = mysql_fetch_array($get_ins))
{

		$ins[$i] = $data['ins'];
		$i++;
}
mysql_free_result($get_ins);

if($qty > 0)
{

switch($sort)
{
	case 'name':
	array_multisort($names, SORT_ASC, SORT_STRING, $idt, $numbers, $goals, $apps, $yellows, $reds, $ins, $publish);
	break;
	case 'number':
	array_multisort($numbers, SORT_ASC, SORT_NUMERIC, $names, SORT_ASC, SORT_STRING, $idt, $goals, $apps, $yellows, $reds, $ins, $publish);
	break;
	case 'apps':
	array_multisort($apps, SORT_DESC, SORT_NUMERIC, $ins, SORT_DESC, SORT_NUMERIC, $names, SORT_ASC, SORT_STRING, $idt, $goals, $numbers, $yellows, $reds, $publish);
	break;
	case 'ins':
	array_multisort($ins, SORT_DESC, SORT_NUMERIC, $apps, SORT_DESC, SORT_NUMERIC, $names, SORT_ASC, SORT_STRING, $idt, $goals, $numbers, $yellows, $reds, $publish);
	break;
	case 'goals':
	array_multisort($goals, SORT_DESC, SORT_NUMERIC, $names, SORT_ASC, SORT_STRING, $idt, $numbers, $apps, $yellows, $reds, $ins, $publish);
	break;
	case 'yellows':
	array_multisort($yellows, SORT_DESC, SORT_NUMERIC, $names, SORT_ASC, SORT_STRING, $idt, $goals, $apps, $numbers, $reds, $ins, $publish);
	break;
	case 'reds':
	array_multisort($reds, SORT_DESC, SORT_NUMERIC, $names, SORT_ASC, SORT_STRING, $idt, $goals, $apps, $yellows, $numbers, $ins, $publish);
	break;

}


$i=0;
$j=1;
while($i < $qty)
{
	echo"<tr><td align=\"right\" valign=\"middle\">";
	switch($sort)
	{
		case 'apps':
			if($i > 0)
			{
				if($apps[$i] == $apps[$i-1])
				{
					echo'&nbsp;';
					$j++;
				}
				else
				{
					echo"$j.";
					$j++;
				}
			}
			else
			{
				echo"$j.";
				$j++;
			}
		break;
		case 'ins':
					if($i > 0)
					{
						if($ins[$i] == $ins[$i-1])
						{
							echo'&nbsp;';
							$j++;
						}
						else
						{
							echo"$j.";
							$j++;
						}
					}
					else
					{
						echo"$j.";
						$j++;
					}
		break;
		case 'goals':
			if($i > 0)
			{
				if($goals[$i] == $goals[$i-1])
				{
					echo'&nbsp;';
					$j++;
				}
				else
				{
					echo"$j.";
					$j++;
				}
			}
			else
			{
				echo"$j.";
				$j++;
			}
		break;
		case 'yellows':
			if($i > 0)
			{
				if($yellows[$i] == $yellows[$i-1])
				{
					echo'&nbsp;';
					$j++;
				}
				else
				{
					echo"$j.";
					$j++;
				}
			}
			else
			{
				echo"$j.";
				$j++;
			}
		break;
		case 'reds':
			if($i > 0)
			{
				if($reds[$i] == $reds[$i-1])
				{
					echo'&nbsp;';
					$j++;
				}
				else
				{
					echo"$j.";
					$j++;
				}
			}
			else
			{
				echo"$j.";
				$j++;
			}
		break;
		case 'number':
			echo"#$numbers[$i]";
			$j++;
		break;
		case 'name':
			echo"$j.";
			$j++;
		break;
	}
	echo"</td>

	<td align=\"left\" valign=\"middle\">";



	if($publish[$i] == 1)
	{
		echo "$names[$i]";
	}
	else
	{
		echo "$names[$i]";
	}

	echo"</td>

	<td align=\"center\" valign=\"middle\">";

	if($sort == 'apps')
		echo'<b>';

	echo"$apps[$i]";

	if($sort == 'apps')
		echo'</b>';

	echo"</td>

	<td align=\"center\" valign=\"middle\">";
	if($sort == 'ins')
		echo'<b>';

	echo"$ins[$i]";

	if($sort == 'ins')
		echo'</b>';

	echo"</td>

	<td align=\"center\" valign=\"middle\">";
	if($sort == 'goals')
		echo'<b>';

	echo"$goals[$i]";

	if($sort == 'goals')
		echo'</b>';

	echo"</td>
	\n";

	$i++;
}
}
?>
<tr>
</tr>
</table>
<img src="images/systeem/lijn.png" height="1" width="200">
<div align="center">
<b>B: </b>Basis | <b>I: </b>Ingevallen | <b>G: </b>Goals
</div>
 
Laatst bewerkt:
Help

Wie kan dit voor ons oplossen wij komen er niet uit HELP HELP :thumb:
 
Laatst bewerkt:
Status
Niet open voor verdere reacties.
                    Steun ons                    

Nieuwste berichten

Terug
Bovenaan Onderaan