Compare a field in two querys

[mrt003003]

Hi there im not quite sure where im going wrong here.

 

I have 2 queries; the first selects all records in a table == Session username. The second selects all records in a table <> Session username

 

Both work independently but when i try to compare a field in each table (The: PlanetName) it doesnt work. Im trying to make it so that if the same PlanetName is in  the first query and the second in any of the records it will output an ID.

 

<?php $colname_Fleet = "-1";
if (isset($_SESSION['MM_Username'])) {
  $colname_Fleet = (get_magic_quotes_gpc()) ? $_SESSION['MM_Username'] : addslashes($_SESSION['MM_Username']);
}
mysql_select_db($database_swb, $swb);
$query_Fleet = sprintf("SELECT FleetName, PlanetName FROM fleet WHERE PlayerName = %s", GetSQLValueString($colname_Fleet, "text"));
$Fleet = mysql_query($query_Fleet, $swb) or die(mysql_error());
$totalRows_Fleet = mysql_num_rows($Fleet);

$colname_FleetE = "-1";
if (isset($_SESSION['MM_Username'])) {
  $colname_FleetE = (get_magic_quotes_gpc()) ? $_SESSION['MM_Username'] : addslashes($_SESSION['MM_Username']);
}
mysql_select_db($database_swb, $swb);
$query_FleetE = sprintf("SELECT FleetName, PlanetName FROM fleet WHERE PlayerName <> %s", GetSQLValueString($colname_FleetE, "text"));
$FleetE = mysql_query($query_FleetE, $swb) or die(mysql_error());
$totalRows_FleetE = mysql_num_rows($FleetE);



while ($row_FleetE = mysql_fetch_assoc($FleetE) && $row_Fleet = mysql_fetch_assoc($Fleet)){

if($row_Fleet['PlanetName'] == $row_FleetE['PlanetName']){
echo $row_Fleet['FleetName'];
echo' ';
}}
?>

 

Any help would be appreciated.

 

Thanks

[mrt003003]

Ive been playing with the code and found that the first query only need select a single record from its ID:

<?php 
$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 FleetName = %s", GetSQLValueString($colname_Fleet, "text"));
$Fleet = mysql_query($query_Fleet, $swb) or die(mysql_error());
$row_Fleet = mysql_fetch_assoc($Fleet);
$totalRows_Fleet = mysql_num_rows($Fleet);


$colname_PlanetName = $row_Fleet['PlanetName'];

$colname_FleetE = "-1";
if (isset($_SESSION['MM_Username'])) {
  $colname_FleetE = (get_magic_quotes_gpc()) ? $_SESSION['MM_Username'] : addslashes($_SESSION['MM_Username']);
}
mysql_select_db($database_swb, $swb);
$query_FleetE = sprintf("SELECT FleetName, PlanetName FROM fleet WHERE PlayerName <> %s AND PlanetName=%s", GetSQLValueString($colname_FleetE, "text"),GetSQLValueString($colname_PlanetName, "text"));
$FleetE = mysql_query($query_FleetE, $swb) or die(mysql_error());
$totalRows_FleetE = mysql_num_rows($FleetE);

<?php while ($row_FleetE = mysql_fetch_assoc($FleetE)){

if ($row_FleetE['PlanetName'] == 'Mon Calamari'){
echo $row_Fleet['FleetName'];
echo' ';
}} ?>

 

This way it works! However it outputs the same $row_Fleet['FleetName'] as many times as it checks each of the FleetE records.

 

So my question has changed to: How can i limit the number of outputs to a single one???

[coder71]

There are other ways to do this, but this will work.

 

<?php 
     $blnMatch = false;
     while(($row_FleetE = mysql_fetch_assoc($FleetE) && (!blnMatch)){

if ($row_FleetE['PlanetName'] == 'Mon Calamari'){
           $blnMatch = true;
   echo $row_Fleet['FleetName'];
   echo' ';
}}
?>

[mrt003003]

Hi there thanks for the reply, ive tested it and get a wierd error ive never seen before: Notice: Use of undefined constant blnMatch - assumed 'blnMatch' in C:\wamp\www\SWB\fleet_edit.php on line 269

 

<?php 
$blnMatch = false;
     while(($row_FleetE = mysql_fetch_assoc($FleetE) && (!blnMatch)){

if ($row_FleetE['PlanetName'] == $colname_PlanetName){
           $blnMatch = true;
   echo $row_Fleet['FleetName'];
   echo' ';
}} ?>

 

I had to remove one of the braces on left of th while else it would error: Parse error: syntax error, unexpected '{' in C:\wamp\www\SWB\fleet_edit.php on line 269

I cant see the problem with {.

 

I also wondered if it could be ! infrom of the blnMatch on the right of the while loop. If i removed the ! id get many errors: Notice: Use of undefined constant blnMatch - assumed 'blnMatch' in C:\wamp\www\SWB\fleet_edit.php on line 269

 

That would be outputted as many times as their are records that are being searched in the while loop.

 

Its close i can feel it in my bones. What would you suggest please??

 

Thanks

 

 

[PaulRyan]

Replace this...

     while(($row_FleetE = mysql_fetch_assoc($FleetE) && (!blnMatch)){

with the following...

     while(($row_FleetE = mysql_fetch_assoc($FleetE) && (!$blnMatch)){

 

Regards, PaulRyan.

[mrt003003]

Haha yes the $. Thank you, its now error free, however i should say that nothing is outputted when it should be.. Hmmmmmmmmm.

 

[wildteen88]

What is your code supposed to do?

[mrt003003]

FLEET TABLE

FleetName

PlanetName

Detected

PlayerName

 

The first query Fleet searches the table where the FleetName equal to a url parsed parameter from a previous page.

 

<?php $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 FleetName = %s", GetSQLValueString($colname_Fleet, "text"));
$Fleet = mysql_query($query_Fleet, $swb) or die(mysql_error());
$row_Fleet = mysql_fetch_assoc($Fleet);
$totalRows_Fleet = mysql_num_rows($Fleet); ?>

 

The second query FleetE searches the same table for the all the Fleets where the playername is NOT equal the session username (playername) AND is equal to PlanetName of the previous query Fleet.

 

<?php $colname_PlanetName = $row_Fleet['PlanetName'];

$colname_FleetE = "-1";
if (isset($_SESSION['MM_Username'])) {
  $colname_FleetE = (get_magic_quotes_gpc()) ? $_SESSION['MM_Username'] : addslashes($_SESSION['MM_Username']);
}
mysql_select_db($database_swb, $swb);
$query_FleetE = sprintf("SELECT FleetName, PlanetName FROM fleet WHERE PlayerName <> %s AND PlanetName=%s", GetSQLValueString($colname_FleetE, "text"),GetSQLValueString($colname_PlanetName, "text"));
$FleetE = mysql_query($query_FleetE, $swb) or die(mysql_error());
$totalRows_FleetE = mysql_num_rows($FleetE);
?>

 

The idea is that all FleetE records are compared with the single Fleet record matching the PlanetName. So if the PlanetName of any FleetE record is the same as the PlanetName in the single Fleet record then i want it to output the single FleetName of the Fleet record. Before i added the $blnMatch variable it would output the same FleetName however many times as there are records in the FleetE query.

 

<?php 
$blnMatch = false;
  while($row_FleetE = mysql_fetch_assoc($FleetE) && (!$blnMatch)){

if ($row_FleetE['PlanetName'] == $colname_PlanetName){
           $blnMatch = true;
   echo $row_Fleet['FleetName'];
   echo' ';
}} ?>

 

I hope ive explained well enough.

 

Thanks

[mrt003003]

Ive done it! OMG it was suprisingly simple.

 

I removed the While loop and $blnMatch variable:

 

	if ($row_FleetE['PlanetName'] == $colname_PlanetName){

   echo $row_Fleet['FleetName'];
   echo' ';
} ?>

[mrt003003]

What am i chatting about... It doesnt work at all. It gives me mental results. Infact when i echo out the 2nd query here it outputs a FleetName that has the same PlayerName as the logged in session username. Which is completely nuts! Furthermore i need to cater for th fact that their maybe more than one FleetE on the same PlanetName as Fleet. Jeees im lost.

[mikosiko]

You should do this:

- Post some of the data in your table  ... real data.

- Post an example of what is the expect result

 

don't post any code... just the data and the results that you want... sure someone will chime with a solution.

[mrt003003]

Double OMG it does work i just missed the session_start();

 

Thank you all for your help