Select from 2 tables

[mrt003003]

Hi there I have 2 tables:

 

Fleet

FleetName

PlanetName

Status

 

Planet

PlanetName

PlayerName

 

Im trying to write a select query that will search through the fleet table and display Fleet table details Where the PlanetName from the fleet table is = to the PlanetName of the Planet table. Ive tried joining the tables but i dont think the logic quite works..

 

mysql_select_db($database_swb, $swb);
$query_Fleet = sprintf("SELECT fleet.FleetName, planet.PlanetName FROM fleet, planet WHERE fleet.PlanetName = planet.PlanetName");
$Fleet = mysql_query($query_Fleet, $swb) or die(mysql_error());
$row_Fleet = mysql_fetch_assoc($Fleet);
$totalRows_Fleet = mysql_num_rows($Fleet);

 

I want ONLY the Fleets from the planet i am looking at to be displayed not all of them. I have created another query that displays the planet name of the particular one I am looking at as it is parsed from a hyperlink on a previous page. So is there some way i can use both queries together?

 

"SELECT FROM fleet, WHERE $fleet.PlanetName = $planet.PlanetName"

 

Im a little bit confussed, please help

[mrt003003]

Heres my first query that determines and displays the planet i am looking at:

$colname_Planet = "-1";

if (isset($_GET['recordID'])) {

  $colname_Planet = (get_magic_quotes_gpc()) ? $_GET['recordID'] : addslashes($_GET['recordID']);

}

mysql_select_db($database_swb, $swb);

$query_Planet = sprintf("SELECT * FROM planet WHERE PlanetName = %s", GetSQLValueString($colname_Planet, "text"));

$Planet = mysql_query($query_Planet, $swb) or die(mysql_error());

$row_Planet = mysql_fetch_assoc($Planet);

$totalRows_Planet = mysql_num_rows($Planet);

 

So basically i need to use this query to search the Fleet table and display only the records that have the same PlanetName as this one.

 

Thanks

[fugix]

So you would create another query telling it to grab the rows where planetname = $row_planetname

[mrt003003]

Hi there thanks for the reply..

 

I have a syntax error with the PlanetName bit..can you have a look please.

 

$query_Fleet = sprintf("SELECT * FROM fleet WHERE PlanetName =  $row_Planet.PlanetName");

 

Thank you

[gevensen]

theres a free tool called toad that helps make muti table queries

[fugix]

What is the error that you are receiving

[mrt003003]

Ive changed it to:

 

$query_Fleet = sprintf("SELECT * FROM fleet WHERE PlanetName =  '$Planet'");

 

Im slightly confused because i thought it would need to select the row:

 

$query_Fleet = sprintf("SELECT * FROM fleet WHERE PlanetName =  '$row_Planet ['Planet']'");

 

The first way doesnt give any error at all, but it also doesnt display results when i echo them...  The second way gives me the error:

 

Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING in C:\wamp\www\SWB\planet.php on line 48

 

Any ideas? :|

[fugix]

Okay say you set the $planetname = $row_planetname. Make a query like.

mysqli_query("select * from fleet where planetname=$planetname")

[mrt003003]

Thanks for the help fugix, i must be well stupid thought coz i just cant get working:

 

mysql_select_db($database_swb, $swb);

$query_Planet = sprintf("SELECT * FROM planet WHERE PlanetName = %s", GetSQLValueString($colname_Planet, "text"));

$Planet = mysql_query($query_Planet, $swb) or die(mysql_error());

$row_Planet = mysql_fetch_assoc($Planet);

$totalRows_Planet = mysql_num_rows($Planet);

 

$plans = $row_Planet['PlanetName'];

 

$colname_Fleet = "-1";

if (isset($_GET['recordID'])) {

  $colname_Fleet = (get_magic_quotes_gpc()) ? $_GET['recordID'] : addslashes($_GET['recordID']);

}

mysql_select_db($database_swb, $swb);

$query_Fleet = sprintf("SELECT * FROM fleet WHERE PlanetName =  $plans");

$Fleet = mysql_query($query_Fleet, $swb) or die(mysql_error());

$row_Fleet = mysql_fetch_assoc($Fleet);

$totalRows_Fleet = mysql_num_rows($Fleet);

 

I get: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Calamari' at line 1

 

 

It must be close...

[wildteen88]

$plans need to be wrapped in (single) quotes within your $query_Fleet string.

 

However  you could do the above in one query using a simple join

SELECT p.PlanetName, p.PlayerName, f.FleetName, f.Status FROM Planets p
LEFT JOIN Fleet f ON (p.PlanetName = f.PlanetName)
WHERE p.PlanetName = '$colname_Planet'

[fugix]

In mysql queries. I've found that variables do not need to be inside single quotes. So plans does not need to be wrapped in quotes. Where did calamari come from?

[mrt003003]

Thats it thank you very much! I did attempt at trying a join originally but ended up doing it the long way around.

 

Thanks again thats brilliant!

[mrt003003]

Can i just ask... i assume its possible to add a 3rd table (Ships):

 

$fleetships = $row_Fleet['FleetName'];

mysql_select_db($database_swb, $swb);
$query_Ships = sprintf("SELECT * FROM ships WHERE FleetName = '$fleetships'");
$Ships = mysql_query($query_Ships, $swb) or die(mysql_error());
$row_Ships = mysql_fetch_assoc($Ships);
$totalRows_Ships = mysql_num_rows($Ships);

 

Which leads me to another question if i may, will it work if the outputted results of Fleet and Ships are both in a php do loop? So that the Fleet loop surrounds the Ships loop???

Ive just tried this and the $Ship query isnt outputting anything...

 

I did wonder if it was  because of the order of the code and modified it to:

$colname_Planet = "-1";
if (isset($_GET['recordID'])) {
  $colname_Planet = (get_magic_quotes_gpc()) ? $_GET['recordID'] : addslashes($_GET['recordID']);
}
mysql_select_db($database_swb, $swb);
$query_Planet = sprintf("SELECT * FROM planet WHERE PlanetName = %s", GetSQLValueString($colname_Planet, "text"));
$Planet = mysql_query($query_Planet, $swb) or die(mysql_error());
$row_Planet = mysql_fetch_assoc($Planet);
$totalRows_Planet = mysql_num_rows($Planet);

$plans = $row_Planet['PlanetName'];

$colname_Fleet = "-1";
if (isset($_GET['recordID'])) {
  $colname_Fleet = (get_magic_quotes_gpc()) ? $_GET['recordID'] : addslashes($_GET['recordID']);
}
mysql_select_db($database_swb, $swb);
$query_Fleet = sprintf("SELECT * FROM fleet WHERE PlanetName =  '$plans'");
$Fleet = mysql_query($query_Fleet, $swb) or die(mysql_error());
$row_Fleet = mysql_fetch_assoc($Fleet);
$totalRows_Fleet = mysql_num_rows($Fleet);

$fleetships = $row_Fleet['FleetName'];

$colname_Ships = "-1";
if (isset($_GET['recordID'])) {
  $colname_Ships = (get_magic_quotes_gpc()) ? $_GET['recordID'] : addslashes($_GET['recordID']);
}
mysql_select_db($database_swb, $swb);
$query_Ships = sprintf("SELECT * FROM ships WHERE FleetName = '$fleetships'");
$Ships = mysql_query($query_Ships, $swb) or die(mysql_error());
$row_Ships = mysql_fetch_assoc($Ships);
$totalRows_Ships = mysql_num_rows($Ships); ?>
<?php do { ?>
<?php echo $row_Fleet['FleetName']; ?>
<?php echo $row_Fleet['PlanetName']; ?>
<?php do { ?>
<?php echo $row_Ships['ShipName']; ?>
<?php } while ($row_Ships = mysql_fetch_assoc($Ships)); ?>
<?php } while ($row_Fleet = mysql_fetch_assoc($Fleet)); ?>

 

What am i doing wrong?

 

Thanks