Jump to content

Connecting and Diplaying tables

Neptunus Maris

By Neptunus Maris
in PHP Coding Help

Recommended Posts

Neptunus Maris Member

Neptunus Maris

Posted
Posted
Hi, I have a problem displaying a connection between 3 tables in PHP...

here is my database set up:

<?php
include "testlib.php";
connect();

$query = <<<HERE
CREATE TABLE `test_one` (
`test_oneID` tinyint(2) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(15) NOT NULL default '',
PRIMARY KEY (`test_oneID`)
);
HERE;
$result = mysql_query($query) or die(mysql_error());

$query = <<<HERE
INSERT INTO `test_one` VALUES('1', 'Neptunus');
HERE;
$result = mysql_query($query) or die(mysql_error());
print "test_one table is created.";

$query = <<<HERE
CREATE TABLE `test_two` (
`test_twoID` tinyint(2) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(15) NOT NULL default '',
`troops` int(5) unsigned NOT NULL default '',
`test_oneID` tinyint(2) NULL,
`test_threeID` tinyint(2) NULL,
PRIMARY KEY (`test_twoID`),
FOREIGN KEY (`test_oneID`) REFERENCES test_one (`test_oneID`),
FOREIGN KEY (`test_threeID`) REFERENCES test_three (`test_threeID`)
);
HERE;
$result = mysql_query($query) or die(mysql_error());
print "<p>test_two table created.</p>";

$query = <<<HERE
CREATE TABLE `test_three` (
`test_threeID` tinyint(2) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(15) NOT NULL default '',
PRIMARY KEY (`test_threeID`)
);
HERE;
$result = mysql_query($query) or die(mysql_error());

$query = <<<HERE
INSERT INTO `test_three` VALUES('1', 'Latium');
HERE;
$result = mysql_query($query) or die(mysql_error());

$query = <<<HERE
INSERT INTO `test_three` VALUES('2', 'Campania');
HERE;
$result = mysql_query($query) or die(mysql_error());

print "<p>test_three table created, with insertion in test_three.</p>";
?>

NOTICE how my test_two table is connected to both test_one and test_three.
Now This next page allows you to add an army to test_two.

<?php

include "testlib.php";
connect();


$display = <<<HERE
<form action = "proccess.php" method = "post">
<table border = 1>
<tr>
<th>Name</th>
<th>Troops</th>
</tr>

<tr>
<td><input type = "text" name = "name" size = 10 maxlength = 15 value = ""></td>
<td><input type = "text" name = "troops" size = 10 maxlength = 15 value = ""></td>
</tr>

<tr>
<td></td>
<td><input type = "submit" name = "submit" value = "Add"></td>
</tr>
</table>
</form>
HERE;
?>


<html>
<head>
<title>Test Add</title>
</head>
<body>

<?php print "$display"; ?>

</body>
</html>

//Next process the adding of the army
<?php
include "testlib.php";
connect();
//NOTICE: how i pointed to insert directly into the 1st ID number in the test_oneID field and the 1st ID number in the test_threeID field
//in the test_two table.
if (isset($_POST[submit])) {
$query = "INSERT INTO test_two (test_twoID, name, troops, test_oneID, test_threeID) VALUES('', '$_POST[name]', '$_POST[troops]', '1', '1')";
$result = mysql_query($query) or die(mysql_error());
}
header("Location: test_army.php");
exit;
?>

//The following page will display each army added to the database
//This is what im having problems with.  When you view this page its shows that you've added two of the same armies.
//take a look at the code.

<?php
include "testlib.php";
connect();

$query = "SELECT test_two.name AS army, test_two.troops AS troops, test_one.name AS commander, test_three.name AS location FROM test_two, test_one, test_three";
$result = mysql_query($query) or die(mysql_error());

$display = "<center>";
$display .= "<table border = 1>\n";
$display .= "<tr>\n";
$display .= "<th>Name</th>\n";
$display .= "<th>Troops</th>\n";
$display .= "<th>Commander</th>\n";
$display .= "<th>Location</th>\n";
$display .= "<tr>\n\n";

while ($row = mysql_fetch_array($result)) {
$name = $row[army];
$troops = $row[troops];
$commander = $row[commander];
$location = $row[location];

$display .= "<tr>\n";
$display .= "<td>$name</td>\n";
$display .= "<td>$troops</td>\n";
$display .= "<td>$commander</td>\n";
$display .= "<td>$location</td>\n";
$display .= "<tr>\n";
}
$display .= "</table>\n";
$display .= "</center>\n";
?>

<html>
<head>
<title>Test Amry</title>
</head>
<body>

<?php print "$display"; ?>

</body>
</html>

Here is the link of what the page looks like [url=http://www.rdrvision.com/test_view.php]http://www.rdrvision.com/test_view.php[/url].

I just need to know why its displaying two of the same armies when really you have only entered one.

Thanks in advanced
Neptune
Link to comment
https://forums.phpfreaks.com/topic/27412-connecting-and-diplaying-tables/
Share on other sites
Psycho Prolific Member

Psycho

Posted
Posted
Change this
[code]$query = "SELECT test_two.name AS army, test_two.troops AS troops,
                  test_one.name AS commander, test_three.name AS location
              FROM test_two
              WHERE test_two.test_oneID = test_one.test_oneID
                AND test_two.test_threeID - test_three.test_threeID";[/code]

To this
[code]$query = "SELECT test_two.name AS army, test_two.troops AS troops,
                  test_one.name AS commander, test_three.name AS location
              FROM test_two, test_one, test_three";[/code]
Neptunus Maris Member

Neptunus Maris

Posted
  • Author
Posted
yes I have it like that here:

$query = "SELECT test_two.name AS army, test_two.troops AS troops, test_one.name AS commander, test_three.name AS location FROM test_two, test_one, test_three";
$result = mysql_query($query) or die(mysql_error());

$display = "<center>";
$display .= "<table border = 1>\n";
$display .= "<tr>\n";
$display .= "<th>Name</th>\n";
$display .= "<th>Troops</th>\n";
$display .= "<th>Commander</th>\n";
$display .= "<th>Location</th>\n";
$display .= "<tr>\n\n";

while ($row = mysql_fetch_array($result)) {
$name = $row[army];
$troops = $row[troops];
$commander = $row[commander];
$location = $row[location];

$display .= "<tr>\n";
$display .= "<td>$name</td>\n";
$display .= "<td>$troops</td>\n";
$display .= "<td>$commander</td>\n";
$display .= "<td>$location</td>\n";
$display .= "<tr>\n";
}
$display .= "</table>\n";
$display .= "</center>\n";

Archived

This topic is now archived and is closed to further replies.

Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.