Working With Time

[Epiales]

Okay all... I use this formula to determine how many times a person has been attacked/robbed within a 24 hour time period, and if they have been attacked so many times, there is no attack button on them so that nobody can attack them.

Code: Select all

$attacks_check = mysql_query("SELECT `id` FROM `logs` WHERE `attacker`='".$_SESSION['uid']."' AND `guards`='".$id."' AND `time`>'".(time() - 86400)."'") or die(mysql_error());

What I would like to do is show a list of your previous attackers for the day, but I'm having much trouble trying to figure it out, since the logs only show the id's of the attacker and the one that was attacked. The usernames are in the user table, while the logs show the time, attacker, the defender. I've tried to run a loop, but ran into problems where it showed all the logs information instead of the information needed only for one specific user session. Below I have the tables and the infomation:

Table that holds the users information is User

Code: Select all

id
username

Table that holds the person attacked and the attackers / time information is Logs

Code: Select all

id
attacker - Person that attacked another person
guards - Person that was attacked
time - Time they were attacked

*NOTE* the ID's are not the same in the tables.

My problems been trying to pull the attacker out of the database, line it up with the users table username, and then make it specific for each player to see their own last attackers.

And would also like to display how many mins/seconds are left before they can be hit again...

Any Idears? Thank you!

[MikuzA]

By using inner joining towards the users table when retreiving the logs.
Logs.ID and Logs.attacker -> users.id -> users.username.

example below, might not work straight away.

Code: Select all

$attacks_check = mysql_query("SELECT `id` FROM `logs` WHERE `attacker`='".$_SESSION['uid']."' AND `guards`='".$id."' AND `time`>'".(time() - 86400)."'") or die(mysql_error()); 

-->

Code: Select all

$attacks_check = mysql_query("SELECT lgs.`id`, usrs.username FROM `logs` lgs INNER JOIN `users` usrs ON lgs.attacker = usrs.id WHERE lgs.`attacker`='".$_SESSION['uid']."' AND lgs.`guards`='".$id."' AND lgs.`time`>'".(time() - 86400)."'") or die(mysql_error()); 

Hope this helps!

[Epiales]

By using inner joining towards the users table when retreiving the logs.
Logs.ID and Logs.attacker -> users.id -> users.username.

example below, might not work straight away.

Code: Select all

$attacks_check = mysql_query("SELECT `id` FROM `logs` WHERE `attacker`='".$_SESSION['uid']."' AND `guards`='".$id."' AND `time`>'".(time() - 86400)."'") or die(mysql_error());

-->

Code: Select all

$attacks_check = mysql_query("SELECT lgs.`id`, usrs.username FROM `logs` lgs INNER JOIN `users` usrs ON lgs.attacker = usrs.id WHERE lgs.`attacker`='".$_SESSION['uid']."' AND lgs.`guards`='".$id."' AND lgs.`time`>'".(time() - 86400)."'") or die(mysql_error());

Hope this helps!

Thank you! I've been diving into the inner join stuff. This will help. Frustrating if ya ask me lol, but getting there. Any idea on how to show the time left though? I always end up with like a billion seconds and such and never know how to convert it.

[MikuzA]

to master table joining, I suggest this>>

http://www.codeproject.com/Articles/330 ... -SQL-Joins

and most of all the picture>>

http://www.codeproject.com/KB/database/ ... S_orig.jpg

I printed out this picture at my last job and sticked it to the wall. Seriously helps in brainstorming on how to approach the data I need.

[Epiales]

Okay, back to this thread, since topic deals with time.

Okay, as example. What I would like to do. Not sure how to set it up, as I will probably need some type of cron to check the time.... Anyway, here goes...

I want to be able to setup a way to purchase a cab company and a limo company, for travelling between cities. And then an airplane to travel to other countries. I want to be able to set a minimum time for the cab company to be operatable. Like that word? Anyway, like when you go to purchase the cab company, that it takes a half hour before you can start purchasing cabs in order to travel... So it's like a building time for it...

I think I need a buildings table, and then a user buildings table. One to store the building cab company and one to store the users cab company when they go to buy it.

So in the buildings table, I would need.

id
name
time (initial time it takes to build the cab company (ex: 30 mins)

Then in the users building table I would need:

id
time
finish time

I think anyway. So when they purchase a cab company, then in this table, it would insert the TIME they bought it, then somehow work with the Time that's in the buildings table to figure out how much longer their time is before it's done.

I'm so lost at how to figure this out in my head. I can kind of see it, but just can't grasp it yet. Any help would be great.

[Luke111]

You don't need a cron. You can easily get this to work when the page is accessed. I find cron useful for things like removing dead accounts and regular database maintenance and stuff like that. Anyways, here's a possible solution (possible because you may find a better way):

Your first table is great (the one for the global buildings). Now for the user buildings, do a table with this:
id
buildingid (the id of the global building)
starttime (must be an integer (INT). a php time() or equivalent value stating when the building was purchased or something like that.)
...(whatever else you may want, like the owner user id, etc.)...

starttime can have a sentinel value so you know when the time is up, from which point you may want either a cron to remove it or keep it as is so the row can be used again.
Think of it like this:
(buildingtime is the time in the global building table, or how long the user has total. 30min or something like that. it is in real-world seconds, so 30min would be 30*60)

Code: Select all

if (starttime == 2)
{
  //the time is up
} elseif (time() - starttime > buildingtime) {
  //set starttime to 2
  //the time is up.
} else {
  //Good to go! the time is not up!
}

[Epiales]

You don't need a cron. You can easily get this to work when the page is accessed. I find cron useful for things like removing dead accounts and regular database maintenance and stuff like that. Anyways, here's a possible solution (possible because you may find a better way):

Your first table is great (the one for the global buildings). Now for the user buildings, do a table with this:
id
buildingid (the id of the global building)
starttime (must be an integer (INT). a php time() or equivalent value stating when the building was purchased or something like that.)
...(whatever else you may want, like the owner user id, etc.)...

starttime can have a sentinel value so you know when the time is up, from which point you may want either a cron to remove it or keep it as is so the row can be used again.
Think of it like this:
(buildingtime is the time in the global building table, or how long the user has total. 30min or something like that. it is in real-world seconds, so 30min would be 30*60)

Code: Select all

if (starttime == 2)
{
  //the time is up
} elseif (time() - starttime > buildingtime) {
  //set starttime to 2
  //the time is up.
} else {
  //Good to go! the time is not up!
}

Thanks for the info... Okay, how would I go about putting like a 30 minute time in the database? Then echo it out to show the time remaining in real time, and not the time from 60 years ago in second lol.

This is the buildings table:

Code: Select all

CREATE TABLE IF NOT EXISTS `buildings` (
  `bdlgid` int(11) NOT NULL,
  `name` text NOT NULL,
  `times1` int(11) NOT NULL,
  `randid` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

This is the userbuildings table:

Code: Select all

CREATE TABLE IF NOT EXISTS `userbdlg` (
  `bdlgid` int(11) NOT NULL,
  `playerid` int(11) NOT NULL,
  `times1` int(11) NOT NULL,
  `finish` int(11) NOT NULL,
  `randid` int(11) NOT NULL,
  PRIMARY KEY (`playerid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I think I have all the fields covered...

[Epiales]

I know I"m way off here, but I have this so far. Still moving things and working with it. Am I on the right track?

Code: Select all

if(isset($_POST['build'])) {

$sql = "SELECT * from buildings inner join userbdlg on buildings.bdlgid = userbdlg.bdlgid";
$user_query = mysqli_query($db_conx, $sql);
$numrows = mysqli_num_rows($user_query);
$currenttime = time();
        $sql = "UPDATE buildings SET times='$currenttime'";
        $query = mysqli_query($db_conx, $sql);
            
$time = time() + strtotime($row['times1']) + (30 * 1); // I put 30 for 30 seconds for experimenting to see if it works
$randid = rand(999,9999999);
$bdlgid = 1;

        $sql = "INSERT INTO userbdlg (bdlgid, playerid, times1, randid) VALUES ('$bdlgid','$_SESSION[userid]','$time', '$randid')";
        $query = mysqli_query($db_conx, $sql);
        
}

if ($row['times1'] < $row['times']) {

Echo "not Done";
echo $currenttime;

}else{

echo "done";
}

[Epiales]

Okay, I have this worked so far. It puts the current time in the database and then adds 30 seconds to the current time to get the finish time, but nothings happening on the after result. I always shows done; even when the time is less than the finish time ??

Code: Select all

<?php
if(isset($_POST['build'])) {

$sql = "SELECT * from userbdlg";
$user_query = mysqli_query($db_conx, $sql);
$numrows = mysqli_num_rows($user_query);        
        
$time = time();
$finish = time() + strtotime($row['times']) + (30 * 1);
$randid = rand(999,9999999);
$bdlgid = 1;

        $sql = "UPDATE userbdlg SET times1='$currenttime'";
        $query = mysqli_query($db_conx, $sql);

        $sql = "INSERT INTO userbdlg (bdlgid, playerid, times1, finish, randid) VALUES ('$bdlgid','$_SESSION[userid]','$time', '$finish', '$randid')";
        $query = mysqli_query($db_conx, $sql);
        
}

$sql = "SELECT * from userbdlg";
$user_query = mysqli_query($db_conx, $sql);
$numrows = mysqli_num_rows($user_query);        

if ($row['times1'] < $row['finish']) {

Echo "not Done";
echo $currenttime;

}else{

echo "done";
} 

[Epiales]

Okay, had to make it where the time updated at the load of the page, so here's the change. Doesn't matter though, in the table it shows the times1 is 30 seconds less than then the finish time, and when I refresh, the times1 time changes as it should, but no matter what, it always says the building has finished building.

Current Code:

Code: Select all

<?php
$sql = "UPDATE userbdlg SET times1='$currenttime'";
$query = mysqli_query($db_conx, $sql);
        
$sql = "SELECT * from userbdlg";
$user_query = mysqli_query($db_conx, $sql);
$numrows = mysqli_num_rows($user_query);        

if ($row['times1'] >= $row['finish']) {

Echo "Finished Building";

}else{

echo "Not Done";
echo $currenttime;

}        

if(isset($_POST['build'])) {

$sql = "SELECT * from userbdlg";
$user_query = mysqli_query($db_conx, $sql);
$numrows = mysqli_num_rows($user_query);        
        
$time = time();
$finish = time() + strtotime($row['times']) + (30 * 1);
$randid = rand(999,9999999);
$bdlgid = 1;

        $sql = "UPDATE userbdlg SET times1='$currenttime'";
        $query = mysqli_query($db_conx, $sql);

        $sql = "INSERT INTO userbdlg (bdlgid, playerid, times1, finish, randid) VALUES ('$bdlgid','$_SESSION[userid]','$time', '$finish', '$randid')";
        $query = mysqli_query($db_conx, $sql);
 

Not sure I should be doing it this way, but all I can get in my head to figure it out lol

Okay, I forgot my while statment:

Code: Select all

while ($row = mysqli_fetch_array($user_query, MYSQLI_ASSOC)) {

Works great now...Just doesn't echo the time in real minutes and seconds... only the long time like: 12645494564 grrr lol.