Page 1 of 1

Table Compares (SOLVED)

Posted: Mon Nov 23, 2015 10:12 pm
by SpiritWebb
Ok, so I have two tables. One is known as planets and the other is players.

When a player enters a sector (which is the universe table in the DB), the players sector field is updated to the sector they are in. Now what I want to do is compare the planets table and the players table to identify how many planets are in that sector.

If the sector_id field in planets and the sector in players match, then that changes x. Planets in sector: x

This is what I have

Code: Select all

<?php
        $plSector = mysql_query("SELECT COUNT(*) AS sector_id FROM planets WHERE sector_id='$sector_id'");
        $numPl = mysql_fetch_array($plSector);
        $sectorPl = $numPl["sector_id"];
        if ($sectorPl == $sector)
        {
          echo "$sectorPl";
        }
        if ($sectorPl != $sector)
        {
          echo "0";
        }
?>
Also tried this, which found online, but did not work either:

Code: Select all

<?php
          $plSector = "SELECT * FROM planets s WHERE EXISTS ( SELECT * FROM players ss WHERE (sector)=(s.sector_id))";
          $plCount = mysql_query($plSector) or die(mysql_error());
          echo "$plCount";
          ?>
I have these two loops running at the top as well which is also causing problems.

Code: Select all

<?php
$sql = "SELECT * FROM players";
$user_query = mysql_query($sql);
while ($row = mysql_fetch_array($user_query))
{
  $id = $row["id"];
  $shipname = $row["shipname"];
  $username = $row["username"];
  $level = $row["level"];
  $exper = $row["exper"];
  $rank_lvl = $row["rank_lvl"];
  $rank = $row["rank"];
  $credits = $row["credits"];
  $tokens = $row["tokens"];
  $iron = $row["iron"];
  $gold = $row["gold"];
  $silver = $row["silver"];
  $food = $row["food"];
  $col = $row["col"];
  $hull = $row["hull"];
  $engine = $row["engine"];
  $power = $row["power"];
  $comp = $row["comp"];
  $sensors = $row["sensors"];
  $beam = $row["beam"];
  $torp_launch = $row["torp_launch"];
  $torp = $row["torp"];
  $shields = $row["shields"];
  $damage = $row["damage"];
  $sector = $row["sector"];
  $pl_create = $row["pl_create"];
  $last_login = $row["last_login"];
  $score = $row["score"];
  $fleet = $row["fleet_id"];
  $planets = $row["planets"];
}

Code: Select all

$sqlPl = "SELECT * FROM planets";
$planet_query = mysql_query($sqlPl);
while ($row1 = mysql_fetch_array($planet_query))
{
  $planet_id = $row1['planet_id'];
  $sector_id = $row1['sector_id'];
  $planet_name = $row1['name'];
  $planet_size = $row1['planet_size'];
  $planet_iron = $row1['iron'];
  $planet_gold = $row1['gold'];
  $planet_silver = row1['silver'];
  $planet_food = $row1['food'];
  $planet_col = $row1['colonists'];
  $planet_creds = $row1['credits'];
  $planet_owner = $row1['owner'];
  $planet_fleet = $row1['fleet'];
}

Re: Table Compares

Posted: Tue Nov 24, 2015 12:20 am
by hallsofvallhalla
could you use $numPl[0] ?

Re: Table Compares

Posted: Tue Nov 24, 2015 12:58 am
by SpiritWebb
hallsofvallhalla wrote:could you use $numPl[0] ?
I put that there, but with the second while loop, it is preventing the page from loading, is there an easier way of doing this?

Re: Table Compares

Posted: Tue Nov 24, 2015 2:09 am
by hallsofvallhalla
no dont loop it. J

$plSector = mysql_query("SELECT COUNT(*) AS sector_id FROM planets WHERE sector_id='$sector_id'");
$numPl = mysql_fetch_array($plSector);

Re: Table Compares

Posted: Tue Nov 24, 2015 7:18 am
by Winawer
Probably something like

Code: Select all

<?php
        $plSector = mysql_query("SELECT COUNT(*) AS num_planets FROM planets WHERE sector_id='$sector"); //assuming $sector is the sector id of the ship
        $numPl = mysql_fetch_array($plSector);
        $num_planets = $numPl["num_planets"];
        
        echo $num_planets;
?>
What are the loops supposed to do?

Re: Table Compares

Posted: Tue Nov 24, 2015 3:14 pm
by SpiritWebb
Winawer wrote:Probably something like

Code: Select all

<?php
        $plSector = mysql_query("SELECT COUNT(*) AS num_planets FROM planets WHERE sector_id='$sector"); //assuming $sector is the sector id of the ship
        $numPl = mysql_fetch_array($plSector);
        $num_planets = $numPl["num_planets"];
        
        echo $num_planets;
?>
What are the loops supposed to do?
I had that setup and it didn't work. $sector is the location of the ship. $sector_id is the sector in the universe. Basically what I want to do is: You click a link which moves the player to another sector. Then when they arrive at that sector, does the sector have a planet? In the planet table there is a planet_id and sector_id, the sector_id is the sector in which that planet is located. When the ship moves it updates the player table in the sector field the new sector and then compares that sector and the planet table to see if a planet is present.
hallsofvallhalla wrote:no dont loop it. J

$plSector = mysql_query("SELECT COUNT(*) AS sector_id FROM planets WHERE sector_id='$sector_id'");
$numPl = mysql_fetch_array($plSector);
I tried that too, and when I take out the while loop it moves through, but doesn't display any data whatsoever. Just like the update had problems with earlier.

Re: Table Compares

Posted: Wed Nov 25, 2015 6:46 am
by Winawer
SpiritWebb wrote:
Winawer wrote:Probably something like

Code: Select all

<?php
        $plSector = mysql_query("SELECT COUNT(*) AS num_planets FROM planets WHERE sector_id='$sector"); //assuming $sector is the sector id of the ship
        $numPl = mysql_fetch_array($plSector);
        $num_planets = $numPl["num_planets"];
        
        echo $num_planets;
?>
What are the loops supposed to do?
I had that setup and it didn't work. $sector is the location of the ship. $sector_id is the sector in the universe. Basically what I want to do is: You click a link which moves the player to another sector. Then when they arrive at that sector, does the sector have a planet? In the planet table there is a planet_id and sector_id, the sector_id is the sector in which that planet is located. When the ship moves it updates the player table in the sector field the new sector and then compares that sector and the planet table to see if a planet is present.
So something like this?

Code: Select all

if( isset( $_GET['moveTo'] ) ) { //$_GET['moveTo'] is the sector id the ship is moving to
    $moveTo = (int)$_GET['moveTo'];

    $query = "UPDATE players SET sector=" . $moveTo . " WHERE id=" . $_SESSION['player_id']; //move the ship to sector $moveTo
    mysql_query( $query );

    $plSector = mysql_query( "SELECT COUNT(*) AS num_planets FROM planets WHERE sector_id=" . $moveTo ); //get planet count in sector $moveTo
    $numPl = mysql_fetch_array( $plSector );
    $num_planets = $numPl["num_planets"];
        
    if( $num_planets >= 1 ) {
        echo 'sector has at least one planet';
    } else {
        echo 'no planets here';
    }    
}

Re: Table Compares

Posted: Thu Nov 26, 2015 4:18 am
by KyleMassacre
Maybe something like:

Code: Select all

$q = mysql_query("select count(*) as np from planets where sector_id in (select sector from user where user_id = ".abs((int)$_SESSION['userid']).")");
$r = mysql_fetch_array($q);
 

Re: Table Compares

Posted: Thu Nov 26, 2015 2:59 pm
by hallsofvallhalla
maybe something like this

Image

Re: Table Compares

Posted: Thu Dec 03, 2015 10:10 pm
by SpiritWebb
I re-wrote how I wanted to do this area, and got it working.