Array and loop Help Needed

[ptmuldoon]

I've put together the below code, but something's amiss somewhere.  The code's suppose to do the following:

 

1.  get all the information from a 'games' table.  One of the fields in the table is id

2.  Then it should loop through the database, and get each table of game_XX

3.  Then look at each game_XX table and get the current, next and last players.

 

Currently, its listing each game, but the the current, next, and last player is screwed up.  I think I may have the query screwed up the query to the each game, but unsure.  Or maybe theres a simpler approach, but with my limited skills, this is all I could figure out at this point.

 

$sql = "SELECT * FROM games WHERE id > 1";
$result = mysql_query($sql);
$games = array();  

for ($i = 1; $i < mysql_num_rows($result); $i++) {

//Get the Games
$games[$i] = mysql_fetch_assoc($result);

//Get the Individual Game Data
$sql = "SELECT * FROM game_".$games[$i]['id']." WHERE id != 1 AND state != 'dead' ";
$result2 = mysql_query($sql);
$player_data = array();

while ($row = mysql_fetch_assoc($result2)) {
    		$player_data[] = $row;
}

for ($row = 0; $row < mysql_num_rows($result2); $row++){
    	foreach($player_data[$row] as $key => $value){    

    		// Get the Current Player Info
		if($player_data[$row]['state'] == 'trading'){
			$cid = $player_data[$row]['id'];
			$cplayer = $player_data[$row]['player'];
			}

			// Get the Next Player Info.
		if($player_data[$row]['state'] == 'trading'){
			$next = $row + 1;
			if($next = $row < mysql_num_rows($result)){
				$next = 2;
			}
			$nid = $player_data[$next]['id'];
			$nplayer = $player_data[$next]['player'];
		}

			// Get the Last Player Info.
				if($player_data[$row]['state'] == 'trading'){
			$last = $row - 1;
			if($last < 0){
				$last = $row < mysql_num_rows($result);
			}
			$lid = $player_data[$last]['id'];
			$lplayer = $player_data[$last]['player'];
		} 		
    	}	
}
}

 

Also, if it matters, I'm working with the Smarty XTemplate, so the above is later beign parsed as follows:

 

// assign array data
$xtpl->assign('GAME', $games[$i]);
$xtpl->assign('CPLAYER', $cplayer);
$xtpl->assign('NPLAYER', $nplayer);
$xtpl->assign('LPLAYER', $lplayer);

[gerkintrigg]

Gosh - with the greatest of respect, what a mess.

 

I'm going to try to answer this because that code reminds me very much of me a few years back.

 

It's a very long-winded way of doing it.

 

i don't really understand why you need so many tables. You could just have them in a few tables and use the database to do all the hard work (that's what it's supposed to be used for)...

 

So...

I'd suggest doing something like a SQL join like this:

 

<?php 
$q="SELECT table1.field_name, table1.field_2, table2.field_name, table2.field_2 FROM table1,table2 
WHERE table1.primary_key=table2.foreign_key";
$sql=mysql_query($q);
while($r=mysql_fetch_array){
}?>

 

If that doesn't make sense, then have a good delve into the SQL tutorials, you'll learn a lot about how to retrieve multiple data without faffing with multiple connections and using up too much resources on your server, which your host will NOT thank you for (especially on shared servers).

 

I hope I got the right end of that stick...

[ptmuldoon]

Yeah, your not the first one to point out to me about the database structure.    The issue is I didn't create the original scripts, and am only looking to improve on what was already there.  And in this instance, there are multiple games going at all times, with each game maintained in a separate table. 

 

Maybe I should begin trying to learn how to redo the database structure.

[ptmuldoon]

ok, I'm going to begin attempting to redesign my database to get away from having a bunch of tables called game_XX, and to use one main game table instead.  So I'm trying to move/copy data from all of my game_XX tables into the new table.  But the below code is only inserting 1 row of data.  Can someone show me where I went wrong?

 

$sql = "SELECT * FROM games WHERE id > 1";
$query = mysql_query($sql);

for ($i = 1; $i < mysql_num_rows($query); $i++) {
$game = mysql_fetch_assoc($query);
echo $game['id'] . "<BR/>";
$sql2 = "INSERT INTO risk_game (gid) VALUES (".$game['id'].") ";
$query2 = mysql_query($sql2);
}

[Ken2k7]

Try

<?php
$sql = "SELECT * FROM games WHERE id > 1";
$query = mysql_query($sql) or die(mysql_error());

while($game = mysql_fetch_assoc($query)){
echo $game['id'] . "<BR/>";
$sql2 = "INSERT INTO risk_game (gid) VALUES ('".$game['id']."') ";
mysql_query($sql2) or die(mysql_error());
}
?>

[ptmuldoon]

Thanks,  That got me started.

 

Now, I'm trying to get the data from all of my game_XX tables into the one/single new table.  I think the below is close, but I keep getting a duplicate key error.  I'm not sure if the duplicate key error is do to the table structure or something in the code.  I think something is wrong below since its inserting only the first row of info from the first game_XX table.

 

<?php
$sql = "SELECT * FROM games WHERE id > 1";
$query = mysql_query($sql) or die(mysql_error());

while($game = mysql_fetch_assoc($query)){
//Get Each Games Data
$sql2 = "SELECT * FROM game_".$game['id']." ";
$query2 = mysql_query($sql2) or die(mysql_error());

while($gdata = mysql_fetch_assoc($query2)){
	//Insert Each Games Data into the new table
	$sql3 = "INSERT INTO game (gid, pid, pname) VALUES ('".$game['id']."','".$gdata['id']."','".$gdata['name']."' ) ";
	mysql_query($sql3) or die(mysql_error());	
}
}
?>

[Ken2k7]

This line: sql2 = "SELECT * FROM game_".$game['id']." ";

 

What are you trying to do with that?

[ptmuldoon]

I currently have a number of game tables such as game_24 game_35 game_39, etc.  And each game table includes a number of field such as player id, player name, etc.

 

The way the code was originally written, a new table is created for each new game, and then deleted when its over.  Its been mentioned that having all of these separate tables, and querying them all is not very efficient.  So I'm starting on full rewrite (hopefully not to over my head) to learn and improve on things.

 

So I've created a new table that includes a game id field (gid).  So to populate the new table, I'm looking to loop through each game_XX table, get the game info, and to insert it into the new game table.

 

Then I can begin to recode everything else to work off the new game id (gid) field and avoid constantly looping through tables to get game information.

[ptmuldoon]

Edit***

 

Ok, My table structure was using a primary key on my game id (gid) field.  So when removing that from the table structure, I got it to imsert the game data from each table.

 

I'm just getting started though, so may have another 100+ questions or so 

[ptmuldoon]

Well, I've made some progress in moving things into a single table for better management, but.......

 

I can't seem to get the the game names to insert into the new table.  The issue has to do with various game names having a single quote in their name, ie:  Let's Play or It's Great!! 

 

I've tried using the following as the value, but it didn't work. 

'".mysql_real_escape_string($game['name'])."'

 

I do have magic quotes turned off,

[ptmuldoon]

Hey everyone. 

 

I've successfully redone my database management, and am now attempting to learn how to search and loop through arrays to find specific values.  I created the below code which will get all the game info from one table, and then compare the game id (gid) to a players table and, and create an array of each player's info (At least thats what I believe i'm doing as i'm still green at php).

 

But I can't seem to figure out how to identify specific players.  I'm trying to check the pstate field in the players table.

 

<?php
$sql = "SELECT * FROM game_info WHERE gid > 0";
$result = mysql_query($sql);

while($games = mysql_fetch_assoc($result)){
//Get an Array of each games info
print "<pre>";
print_r($games);
print "</pre>";

//Get an array of all the players in each game
$sql2 = "SELECT pid, pname, pstate FROM game_players WHERE gid = ".$games['gid']." ";
$result2 = mysql_query($sql2);

while($player_data = mysql_fetch_assoc($result2)){
	print "<pre>";
	print_r($player_data);
	print "</pre>";

	//Get the Last, Current, and Next Players in each game
	//Current Player is pstate = 'trading'  
	//Unsure how to proceed.  Need the current player for each game.

}  

}
?>

[Ken2k7]

$player_data['name']?