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.

Table Compares (SOLVED)

Postby SpiritWebb » Mon Nov 23, 2015 5:12 pm

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
SpiritWebb
Rockywood Studios
 
Posts: 3092
Joined: Sun Jul 12, 2009 6:25 pm
Location: Little Rock, AR
Has thanked: 6 times
Been thanked: 11 times
Blog: View Blog (2)

Re: Table Compares

Postby hallsofvallhalla » Mon Nov 23, 2015 7:20 pm

could you use $numPl[0] ?
User avatar
hallsofvallhalla
Site Admin
 
Posts: 11942
Images: 13
Joined: Wed Apr 22, 2009 6:29 pm
Location: mobile, al
Has thanked: 11 times
Been thanked: 158 times
Blog: View Blog (3)

Re: Table Compares

Postby SpiritWebb » Mon Nov 23, 2015 7:58 pm

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
SpiritWebb
Rockywood Studios
 
Posts: 3092
Joined: Sun Jul 12, 2009 6:25 pm
Location: Little Rock, AR
Has thanked: 6 times
Been thanked: 11 times
Blog: View Blog (2)

Re: Table Compares

Postby hallsofvallhalla » Mon Nov 23, 2015 9:09 pm

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);
User avatar
hallsofvallhalla
Site Admin
 
Posts: 11942
Images: 13
Joined: Wed Apr 22, 2009 6:29 pm
Location: mobile, al
Has thanked: 11 times
Been thanked: 158 times
Blog: View Blog (3)

Re: Table Compares

Postby Winawer » Tue Nov 24, 2015 2:18 am

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?
Winawer
 
Posts: 180
Joined: Wed Aug 17, 2011 12:53 am
Location: Finland
Has thanked: 0 time
Been thanked: 1 time

Re: Table Compares

Postby SpiritWebb » Tue Nov 24, 2015 10:14 am

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
User avatar
SpiritWebb
Rockywood Studios
 
Posts: 3092
Joined: Sun Jul 12, 2009 6:25 pm
Location: Little Rock, AR
Has thanked: 6 times
Been thanked: 11 times
Blog: View Blog (2)

Re: Table Compares

Postby Winawer » Wed Nov 25, 2015 1:46 am

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';
    }    
}
Winawer
 
Posts: 180
Joined: Wed Aug 17, 2011 12:53 am
Location: Finland
Has thanked: 0 time
Been thanked: 1 time

Re: Table Compares

Postby KyleMassacre » Wed Nov 25, 2015 11:18 pm

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);
 
Skype: KyleMassacre
My NWE Modules
User avatar
KyleMassacre
 
Posts: 567
Joined: Wed Nov 27, 2013 7:42 am
Has thanked: 19 times
Been thanked: 36 times

Re: Table Compares

Postby hallsofvallhalla » Thu Nov 26, 2015 9:59 am

maybe something like this

Image
User avatar
hallsofvallhalla
Site Admin
 
Posts: 11942
Images: 13
Joined: Wed Apr 22, 2009 6:29 pm
Location: mobile, al
Has thanked: 11 times
Been thanked: 158 times
Blog: View Blog (3)

Re: Table Compares

Postby SpiritWebb » Thu Dec 03, 2015 5:10 pm

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

Image
User avatar
SpiritWebb
Rockywood Studios
 
Posts: 3092
Joined: Sun Jul 12, 2009 6:25 pm
Location: Little Rock, AR
Has thanked: 6 times
Been thanked: 11 times
Blog: View Blog (2)


Return to Advanced Help and Support

Who is online

Users browsing this forum: No registered users and 1 guest

cron

x