Multiple Mysql Queries

[bholaday]

Hi,

 

I've got some code that works, but I believe there is a better way to do what I am doing. I am having to make multiple queries to gather and match data. Here's an explanation of what's going on. I am writing a starting script for a game. This script will run once at the beginning of the game to initialize all the settings. The game is much like a game of tag - except that players have a specific target they have to tag. When they tag that person they inherit that person's target.

 

At the start of the game the targets are randomized, a deadline is set, and the players are notified who they must tag. I am having a hard time notifying the players who their target is without making multiple queries to the database. It's not a huge deal, but I'm trying to learn how to write cleaner code.

 

Here's what I have:

function start_game(){
//set up targets

//set all the deadlines

//get id numbers of all players
$id_number = array();
$query = "SELECT id FROM players";  
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result)){
	$id_number[] = $row['id'];
}	

//notify players that the game has begun	
foreach($id_number as $number){
	$result = mysql_query("SELECT current_target FROM players WHERE id = '".$number."'" ) or die(mysql_error());
	if($row = mysql_fetch_array($result, MYSQL_ASSOC)){
		$target_id = $row['current_target'];
	}
	unset($result);
	$result = mysql_query("SELECT first_name, last_name FROM players WHERE id_number = '".$target_id."'" ) or die(mysql_error());
	if($row = mysql_fetch_array($result, MYSQL_ASSOC)){
		$target = $row['first_name'] . " " . $row['last_name'];
	}
	unset($result);

	$msg = "the game has begun! your target is: " . $target;

	unset($target_id);
	unset($target);

	//echo "send_msg(".$number.", ".$msg.")<br />";
	//send_msg($number, $msg);
}	

//close conn

return NULL;
}

 

Here's the pertinent part of my database structure:

first_name

[/td]

last_name

id

current_target

Blake

Holaday

002452

[td]002843

 

I'm not sure of another way to do this. My logic and knowledge of php limit me to solving this problem by:

1) get all the players from the database

2) for each player get the id of the current target

3) based off of that get the first name and last name of the target

 

This might be fine, but if there is a game of 100 people that has to hit the database 201 times (if my math is right). I don't know a ton, but that seems like too much to me.

 

Some problems I ran into:

I tried to fill and array with all the database info and search for the id's but they all exists twice (once in their own row for their ID and once in someone else's row as a target ID)

I couldn't work out the logic of foreach loops to accomplish this.

 

[emopoops]

thats a reallygood script u got there. dont see much wrong with it. i like how u used php echos to echo php functions. cleaver. hon. really.

[premiso]

You can combine the queries pretty easily:

 

$query = "SELECT t.first_name, t.last_name FROM players p, players t WHERE p.current_target = t.id_number ORDER BY p.id";  

 

That will give you the target of players ordered by the player id. This way you only have 1 loop and 1 query. The above is untested, but hopefully you can see / understand what is going on. If not let me know but try and implement first.

 

[emopoops]

premisos right. although i never combone. that looks like a  nice way to do it.

[bholaday]

That query works at returning the names, but how do I know which player they are linked to?

[emopoops]

ADD A WHERE CLAUSE from what they are linked to then a mysql_fetch_array($query);

then do the while($pp = mysql_fetch_array($query){echo $pp[oncolumnuneed];

[premiso]

That query works at returning the names, but how do I know which player they are linked to?

 

$query = "SELECT p.id AS pid, p.first_name AS pfirst, p.last_name AS plast, t.id AS tid, t.first_name AS tfirst, t.last_name AS tlast 
FROM players p, players t WHERE p.current_target = t.id_number ORDER BY p.id"; 

 

Note, when accessing the associative array you will use "pid" to grab the player id, "tid" to grab the target id, etc etc.

[emopoops]

premiso seems to be missing what u are trying to do, op.try rreworkign my old code to ur lickings

[premiso]

premiso seems to be missing what u are trying to do

 

Please do not take me for a fool. I understand perfectly what he wants and provided the way for it. He already knows how to loop through MySQL data, he just did not understand how he could differentiate the player / target. The new query just pulls more columns for him to utilize a bit easier to see who the player is and who their target is. If he wanted help grabbing the data he would have specified that he could not fetch the data instead of

 

but how do I know which player they are linked to?

[emopoops]

hawty hawty im not taking ur fol away for you!. i was well astounded when i read that my code was inadiquite. atleast thats what premiso is trying to establish here by posthing that ugly code such a monstrous pieve od php!