Need Help modifying this query

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Re: Need Help modifying this query

Post by RobertGonzalez »

cdoyle wrote:I want my users to be able to search for other players, and be able to filter out ones that are alive and ones that are dead.
This is a pretty straight forward statement, and one that should be easily enough written with proper data structures.
cdoyle wrote:If a player is dead, their user ID gets entered into the medical ward table (along with some other info like what player killed them, and what weapon was used) the dead players id is stored playerdead_ID.
This throws it off for me. Why would a player that is dead need to be placed into another table? Why wouldn't kill data be stored either in the players table or in a kill table that relates to the player table by that players ID? Then joining would be a snap. Even snappier if there were flags or other simple logic in place in the players table that would identify when a player is dead.
cdoyle wrote:So basically, when a person searches.
If they select alive, it will bring back all the players not listed in the medical ward.
If they select dead, it will bring back those only listed in the medical ward.
Under you current structure this would require two separate queries. One would hit the players table the other would hit the medical_ward table. Since the data structures are totally different for both tables you would literally need two different queries to pull what you want from either.

Though looking at what you have now I would say that if a player is in the medical ward what you are really after is their information that lives in the players table that is related to them. Am I correct in that assumption?
cdoyle
Forum Contributor
Posts: 102
Joined: Wed Feb 13, 2008 7:26 pm

Re: Need Help modifying this query

Post by cdoyle »

cdoyle wrote:If a player is dead, their user ID gets entered into the medical ward table (along with some other info like what player killed them, and what weapon was used) the dead players id is stored playerdead_ID.
This throws it off for me. Why would a player that is dead need to be placed into another table? Why wouldn't kill data be stored either in the players table or in a kill table that relates to the player table by that players ID? Then joining would be a snap. Even snappier if there were flags or other simple logic in place in the players table that would identify when a player is dead.

Originally this is what I was going to do, I was going to add a 'dead' field to the players table, and then make it default 0 for alive and 1 for dead. Which would be easy enough...

But then, I wanted to also store who killed the player, and also what weapon was used, plus the time left until that person is alive again. So then I started thinking, that's 3 extra columns that most of the time would be empty when the player is alive.

So I thought maybe I need to create a table that stores this information.

I have a medical ward page, that uses this query to pull everything together to display on the page. it lists out all that are dead.

Code: Select all

 
$getdead = $db->execute("SELECT p1.username as deadplayer, p2.username as killedby, b.name as weapon, Time_Left FROM medical_ward m 
        INNER JOIN players p1 ON m.playerdead_ID = p1.id 
        INNER JOIN players p2 ON m.Killed_By_ID = p2.id 
        INNER JOIN blueprint_items b ON m.Weapon_Used_ID = b.id
        ORDER BY Time_Left");
 
Is it better to store all that info in the players table instead, even tho some of the fields won't be in use most of the time?
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Re: Need Help modifying this query

Post by RobertGonzalez »

Generally speaking data relevant to a table should stay with that table unless it is only a related bit of data, in which case it would go into another table whose sole design is to relate that data to another table/set of tables.

There is nothing wrong with having emtpy fields. There is nothing wrong with having a lot of columns on your table. However, if it is easier for you to understand and maintain (I am assuming no one else will ever have need to get into the data for this app) then you can set it up however best it fits your knowledge.

You could very easily create a players_dead table with columns like:

dead_id (primary key relates to player_id of the player table)
killer_id (also relates to the player_id column of the player table)
weapon_id (relates to a weapons table)
dead_expiry (datettime column that will auto clear once the threshold is reached)

But personally I would just put all of that in the players table. There is really no reason to make a table for it. It is only a handful of fields that relate directly to a single player.
cdoyle
Forum Contributor
Posts: 102
Joined: Wed Feb 13, 2008 7:26 pm

Re: Need Help modifying this query

Post by cdoyle »

Everah wrote:Generally speaking data relevant to a table should stay with that table unless it is only a related bit of data, in which case it would go into another table whose sole design is to relate that data to another table/set of tables.

There is nothing wrong with having emtpy fields. There is nothing wrong with having a lot of columns on your table. However, if it is easier for you to understand and maintain (I am assuming no one else will ever have need to get into the data for this app) then you can set it up however best it fits your knowledge.

You could very easily create a players_dead table with columns like:

dead_id (primary key relates to player_id of the player table)
killer_id (also relates to the player_id column of the player table)
weapon_id (relates to a weapons table)
dead_expiry (datettime column that will auto clear once the threshold is reached)

But personally I would just put all of that in the players table. There is really no reason to make a table for it. It is only a handful of fields that relate directly to a single player.
hmm, ok.
Maybe I'll just do that then.

I'm still learning this DB stuff, and thought I read it was better to split out data so you don't have fields that are empty or null. Or have tables with loads of fields.

but it sure would be easier to have everything in 1 table.

So would my other fields remain the same?

killer_id
weapon_id
dead_expiry

I'm trying to think how to write the query now..
I want to use the killer_id since later, players will be able to change their name, I don't want them to kill someone, and then change their name right away. But I do want it to display the name on the page, and most likely I'll make it be a link to their profile.

Also I have a question on the whole dead_expiry field.
How I first did it was, in my weapons table I have a field for how much time each weapon will put someone in the hospital.

So how I did it in my medical ward table was, when the player dies it looks at what weapon was used, and then puts that time into the dead_expiry field.

I then have a cron that reduces that amount for each minute that passes, and then when it reaches 0 it dropped that 'dead' player from the table. Which would make them alive again, since they were no longer listed in the table.

Is the cron job way of doing it, an OK way of doing that? Or is there a better way?
On another forum, I was told not to use the cron job. But the way the way they suggested didn't make sense to me.

I want to be able to make it so better weapons, makes it so the loser is dead longer.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Re: Need Help modifying this query

Post by RobertGonzalez »

I wouldn't use a cron job I don't think. I think if you set an expiration time you could easily have it set so that when that expiration time is surpassed and a user is looking at that row you can update the row so the time disappears.

As for the other fields, just put them into the table the way they are. When you need them, use them. When you don't need, don't use them.
cdoyle
Forum Contributor
Posts: 102
Joined: Wed Feb 13, 2008 7:26 pm

Re: Need Help modifying this query

Post by cdoyle »

Everah wrote:I wouldn't use a cron job I don't think. I think if you set an expiration time you could easily have it set so that when that expiration time is surpassed and a user is looking at that row you can update the row so the time disappears.
So how would I go about doing that?
Say if weapon A needs to put someone in for 20 minutes, would my update query that updates the expiry field, take the current time and add 20?

Then on the medical ward page, where it lists out who is in the ward. It also displays how much time is left, Is there a function or something that will take the current time and the expire time and display what is left?

I'm also not sure where would I put the update query, that checks the expiry time so the player can start playing again, once he's alive? It would have to be somewhere, no matter what page he on, that it checks and updates that dead field.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Re: Need Help modifying this query

Post by RobertGonzalez »

A player has to login right? And when a player is logged in you are fetching information about that player on every page right? That is a perfect opportunity to check time remaining on hospital stays.

As for date math, yes, you can use datediff() or any of a host of other date and time functions in MySQL. Worst case is you do date math code side, but I would try to keep that on the database server if at all possible.
cdoyle
Forum Contributor
Posts: 102
Joined: Wed Feb 13, 2008 7:26 pm

Re: Need Help modifying this query

Post by cdoyle »

Hi,
I've taken your suggestion and just scrapped the medical_ward table, and adding the columns into the players table.

I've added killed_by_ID, hos_time, and weapon_used_ID to the players table.
I also eliminated the cron that updated the db each minute and using time instead.

So far so good, everything seems to be working good but I need help on the page where I list out who is dead, and who killed them.

On my medical ward page, I used to have it so it listed out the dead player, who killed them, what weapon was used, and the amount of time left.

The part I'm getting hung up on is how do I get the user name of the player that killed them?

Code: Select all

 
 $getdead = $db->execute("Select id, username, Killed_by_ID, dead_time_remaining, Weapon_Used_id FROM players Where dead_time_remaining > $currenttime");
 
Since this is all in the same table now, what do I need to do to get the username from the Killedby_ID?
Some kind of join?

My players table consists of

id << record id in table
username << name of player
Killedby_ID << id of the player who killed them.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Re: Need Help modifying this query

Post by RobertGonzalez »

Just do a join back to the same table to get the killing players name.
cdoyle
Forum Contributor
Posts: 102
Joined: Wed Feb 13, 2008 7:26 pm

Re: Need Help modifying this query

Post by cdoyle »

Everah wrote:Just do a join back to the same table to get the killing players name.
I've never had much success when I start creating queries like this

Here is what I have so far

Code: Select all

 
$getdead = $db->execute("SELECT p.id, p.username as deadplayer, p.Killed_by_ID as killedby, p.dead_time_remaining, b.name as weapon FROM players p
INNER JOIN blueprint_items b ON p.Weapon_Used_ID = b.id");
 
How do I join back to the same table?
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Re: Need Help modifying this query

Post by RobertGonzalez »

Just a straight ahead join:

[sql]SELECT b.*, d.user_name AS daddy0 FROM test_users d INNER JOIN test_users b ON b.user_daddy = d.user_idWHERE b.user_id = 2[/sql]

Here is the table structure and data I used:
[sql]USE `test`; /*Table structure for table `test_users` */ DROP TABLE IF EXISTS `test_users`; CREATE TABLE `test_users` (  `user_id` int(11) NOT NULL AUTO_INCREMENT,  `user_name` varchar(100) NOT NULL,  `user_daddy` int(11) NOT NULL,  PRIMARY KEY  (`user_id`)) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1; /*Data for the table `test_users` */ INSERT  INTO `test_users`(`user_id`,`user_name`,`user_daddy`) VALUES (1,'Ay Blinkin',0),(2,'Joe Shmoe',1);[/sql]

EDIT | Forgot to mention the result is:

Code: Select all

user_id  user_name  user_daddy  daddy0    
-------  ---------  ----------  ----------
      2  Joe Shmoe           1  Ay Blinkin
Post Reply