Table Compares (SOLVED)

Need help with an engine or coding not on the list? Need help with a game or the website and forums here? Direct all questions here.
Post Reply
User avatar
SpiritWebb
Posts: 3107
Joined: Sun Jul 12, 2009 11:25 pm

Table Compares (SOLVED)

Post 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'];
}
Image

Image
User avatar
hallsofvallhalla
Site Admin
Posts: 12031
Joined: Wed Apr 22, 2009 11:29 pm

Re: Table Compares

Post by hallsofvallhalla »

could you use $numPl[0] ?
User avatar
SpiritWebb
Posts: 3107
Joined: Sun Jul 12, 2009 11:25 pm

Re: Table Compares

Post 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?
Image

Image
User avatar
hallsofvallhalla
Site Admin
Posts: 12031
Joined: Wed Apr 22, 2009 11:29 pm

Re: Table Compares

Post 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);
Winawer
Posts: 180
Joined: Wed Aug 17, 2011 5:53 am

Re: Table Compares

Post 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?
User avatar
SpiritWebb
Posts: 3107
Joined: Sun Jul 12, 2009 11:25 pm

Re: Table Compares

Post 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.
Image

Image
Winawer
Posts: 180
Joined: Wed Aug 17, 2011 5:53 am

Re: Table Compares

Post 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';
    }    
}
User avatar
KyleMassacre
Posts: 573
Joined: Wed Nov 27, 2013 12:42 pm

Re: Table Compares

Post 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);
 
User avatar
hallsofvallhalla
Site Admin
Posts: 12031
Joined: Wed Apr 22, 2009 11:29 pm

Re: Table Compares

Post by hallsofvallhalla »

maybe something like this

Image
User avatar
SpiritWebb
Posts: 3107
Joined: Sun Jul 12, 2009 11:25 pm

Re: Table Compares

Post by SpiritWebb »

I re-wrote how I wanted to do this area, and got it working.
Image

Image
Post Reply

Return to “Advanced Help and Support”