Simple SQL help

[aace]

Thanks Defender for your help.

Now. I need help with SQL.

This is what my table looks like...

name time
allen 400
horn 300

So, what I need to do is to pull allen and 300 out of the db and horn and 300. Before I display them so the screen I need to do some math with the time. How do I pull out individuals rows then work with one of the attributes? The display will look like...

Allen 3hrs 2 minutes (arbitrary numbers here)
Horn 2hrs 12 minutes (arbitrary numbers here)

Thanks in advance.

[TheDefender]

$qry = mysql_query("SELECT name,time FROM table"):

WHILE($row = mysql_fetch_array($qry)){
  // Pull out your variables and manipulate them.
  $name = $row['name'];
  $time = $row['time'];

  // Now manipulate the data...  For example, we'll Uppercase the name and add Smith as the last name for each row.  Then we'll add 1 hour to each of the times.
  $name = ucfirst($name)." Smith";
  $time = ($time+100);


  // Now we will echo them to make sure it worked.
  echo $name." - ".$time."<BR />";

  //Output: (based on the sample data you provided)
  // Allen Smith - 500
  // Horn Smith - 400

// Now we'll close the WHILE loop.
}

This while loop basically goes row by row, and essentially says: While in this row, do something, then move to the next row... Everything is done on that one row before it moves onto the next.

[aace]

Works great. Next scenario though. The loop works, but now I have to work with the same records but different dates. Here's the records in the database

Name Date Total
Allen 2004-07-07 500
Allen 2004-07-05 600
Allen 2004-07-04 500
Horn 2004-07-07 800
Horn 2004-07-05 600
Horn 2004-07-04 600

So basically what I need to do, is go through that loop you gave me, but the query needs to be for a date period. The when it has the records of that date period I need to manipulate the Total data. I hope I explained it well. You guys are helping me a lot, and I am learning a lot. Your help is very very well appreciated.

[TheDefender]

How is your table structured? Specifically, what kind of column is the date and time stored in... If I may make a suggestion, I recommend you store it in UNIX TIMESTAMP format (seconds since the epoch). It's easier to extract and manipulate it than in YYYY-MM-DD format, especially if you have to account for time as well.

In other words, do all of your calcs in terms of seconds:

WHILE($row = mysql_fetch_array($qry)){ 
  // Pull out your variables and manipulate them. 
  $name = $row['name']; 
  $time = $row['time']; // In your first example, the UNIX TIMESTAMP value is equal to: 1089190800 (for 2004-07-07 500)



  // Now manipulate the data...  For example, we'll  add 1 hour to each of the times, (1 hour is equal to 3600 seconds) and format it into something more readable.
  $time = date("l F jS, Y g:i A", ($time+3600)); 


  // Now we will echo them to make sure it worked. 
  echo $name." - ".$time."<BR />"; 

  //Output: (based on the sample data you provided) 
  // Allen - Wednesday July 7th, 2004 6:00 AM 
  // Allen - Monday July 5th, 2004 7:00 AM
  // Allen - Sunday July 4th, 2004 6:00 AM
  // Horn - Wednesday July 7th, 2004 9:00 AM
  // Horn -  Monday July 5th, 2004 7:00 AM
  // Horn - Sunday July 4th, 2004 7:00 AM

// Now we'll close the WHILE loop. 
}

[aace]

Yeah, my date in the table is using the mysql date feature. It's yyyy-mm-dd. The time is stored in billions of seconds(epoch form). So what I need to do is...

Get the total number of hours and minutes for a given time period, maybe something like July 7 2004 to July 21 2004.

[TheDefender]

OK, so if I am hearing you correctly, are you storing the date and time in two separate fields? Date being in YYYY-MM-DD and time in Epoch seconds (UNIX Timestamp).

If that's the case, I would highly recommend you change the way you are doing it. Much easier to just combine you date and time into one UNIX Timestamp and store it all in one column. (from a manipulation standpoint). Is that something that's feasible for you to do? I would be more than happy to help you out there. Your life will be so much easier, because you can do anything you want with it then... Get the time only, get the date only, get the day of the month, get the week of the year, calculate time differences, etc...

With how you are storing it now (if I read you right), you have to pull both pieces, convert the time to a readable format, combine them, convert to UNIX Timestamp with strtotime(), do the calculation, and then do the math to figure hours, minutes, seconds, etc...

With it already stored in one field, all you have to do is pull the value, do the calculation, and then do the math to figure hours, minutes, seconds, etc...

[aace]

Yeah, I am storing them in 2 different fields. I would love to try it your way.

[TheDefender]

OK, how are you collecting the data to be stored. We'll start there, and figure out how to get it combined and into a UNIX Timestamp to store in one field first. From there, we can move on to the calculations.

[aace]

Using SUBMIT buttons, when I click submit it inserts the date, time, and username in to the database...like this...

Name Date Time
allen 2004-07-08 1089213201

[TheDefender]

When is the time converted into that format? Or are you just doing a current timestamp? Or are you taking user input and converting that?

[aace]

User does not input anything, they click the button and then I use the time().

[TheDefender]

OK, and what about the date (Sorry, I should have asked about both in my last post)

[aace]

Using the NOW(),,, so this is what the query looks like and this executes once the submit button is pushed...

$newt = time();

$testingTime = "INSERT INTO TestTime (Name,Dat,TimeIn) values ('$memStat',NOW(),$newt)";

//where memstat equals username

[TheDefender]

OK, nevermind about the date anyhow, because if you are just using the time() function to generate the time, that whole string converts to the date and time anyhow...

If you use date() to convert the example you just gave (1089213201), it converts to: 2004-07-07 11:13:21 am... Does that sound about right?

OK, so you can just drop the date field... and do something like this:

$newt = time(); 

$testingTime = "INSERT INTO TestTime (Name,TimeIn) values ('$memStat', $newt)"; 

Then we can work on the manipulation... I have a meeting to go to (about 45 minutes) and then I will be heading home (about 10 minutes) Bottom line, I will be back online about 6PM EDT, and will post some stuff you can use for calculating time differences and whatnot.

[aace]

Yeah, that's cool about getting the date and time out of that. I should have done that in the beginning. That would be great if you could post somethings. I am leaving the office soon too, won't be back until Monday. Defender, thanks for your help.

[TheDefender]

OK, so I have time... No pressure to have it for you tonight then... LOL whew 😉 Have a nice weekend, and I will get some good info up hhere for you.

[TheDefender]

First off, here is our function to do the comparison. It will output the number of years, weeks, days, hours, minutes and seconds between two dates.

function calcdiff($starttime, $endtime) {

// Define our global variables that we are going to return from the function
global $years, $weeks, $hours, $days, $minutes, $seconds;

// If the $starttime is later than the $endtime, we're going to flip the values for our calculations
IF($starttime > $endtime) {
	$temptime = $starttime;
	$starttime = $endtime;
	$endtime = $temptime;
	unset($temptime);
}
	// Define our calculation variables
	$minsec = 60;
	$hoursec = 3600;
	$daysec = 86400;
	$weeksec = 604800;
	$yearsec = 31536000;

	// Calculate the difference between the nowtime and thentime
	$difference = ($endtime-$starttime);

	// First, determine the number of years if needed
	IF($difference >= $yearsec){
		$years = floor($difference/$yearsec);
		$difference = ($difference%$yearsec);
	} else {
		$years = 0;
	}

	// Second, determine the number of weeks if needed
	IF($difference >= $weeksec){
		$weeks = floor($difference/$weeksec);
		$difference = ($difference%$weeksec);
	} else {
		$weeks = 0;
	}

	// Third, determine the number of days if needed
	IF($difference >= $daysec){
		$days = floor($difference/$daysec);
		$difference = ($difference%$daysec);
	} else {
		$days = 0;
	}

	// Fourth, determine the number of hours if needed
	IF($difference >= $hoursec){
		$hours = floor($difference/$hoursec);
		$difference = ($difference%$hoursec);
	} else {
		$hours = 0;
	}

	// Fifth, determine the number of minutes if needed
	IF($difference >= $minsec){
		$minutes = floor($difference/$minsec);
		$difference = ($difference%$minsec);
	} else {
		$minutes = 0;
	}


	// Lastly, the remaining difference is the seconds
	$seconds = $difference;


	// OK, now return all of our values so we can use them later
	return $years;
	return $weeks;
	return $days;
	return $hours;
	return $minutes;
	return $seconds;

}

OK, here is our query, and output.

$endtime = strtotime("2004-10-03 04:45:00 AM"); // This is our comparison date and time. (which the UNIX Timestamp for is 1096793100)

$qry = mysql_query("SELECT * FROM TestTime");
WHILE($row = mysql_fetch_array($qry)){  

  // Pull out your variables and manipulate them. 
  $name = $row['Name'];  

  $starttime = $row['TimeIn']; 



  // OK, we're going to change things around here.  We're going to find the difference between the example times from your earlier post, and the date and time defined by the $endtime variable above.  All of the work is done inside of the function above.
  calcdiff($starttime, $endtime);  


  // Now we will echo them to make sure it worked. 
  echo $name." - ";
  echo "There are ";
  echo $years." year(s), ";
  echo $weeks." week(s), ";
  echo $days." day(s), ";
  echo $hours." hour(s), ";
  echo $minutes." minute(s) ";
  echo "and ".$seconds." second(s) ";
  echo "between <B>".date("l, F jS Y h:i:s A", $starttime)."</B> and <B>".date("l, F jS Y h:i:s A", $endtime)."</B><BR />";


  //Output: (based on the sample data you provided) 
  // Allen - There are 0 year(s), 12 week(s), 3 day(s), 23 hour(s), 45 minute(s) and 0 second(s) between Wednesday, July 7th 2004 05:00:00 AM and Sunday, October 3rd 2004 04:45:00 AM
  // Allen - There are 0 year(s), 12 week(s), 5 day(s), 22 hour(s), 45 minute(s) and 0 second(s) between Monday, July 5th 2004 06:00:00 AM and Sunday, October 3rd 2004 04:45:00 AM
  // Allen - There are 0 year(s), 12 week(s), 6 day(s), 23 hour(s), 45 minute(s) and 0 second(s) between Sunday, July 4th 2004 05:00:00 AM and Sunday, October 3rd 2004 04:45:00 AM
  // Horn - There are 0 year(s), 12 week(s), 3 day(s), 20 hour(s), 45 minute(s) and 0 second(s) between Wednesday, July 7th 2004 08:00:00 AM and Sunday, October 3rd 2004 04:45:00 AM
  // Horn -  There are 0 year(s), 12 week(s), 5 day(s), 22 hour(s), 45 minute(s) and 0 second(s) between Monday, July 5th 2004 06:00:00 AM and Sunday, October 3rd 2004 04:45:00 AM
  // Horn - There are 0 year(s), 12 week(s), 6 day(s), 22 hour(s), 45 minute(s) and 0 second(s) between Sunday, July 4th 2004 06:00:00 AM and Sunday, October 3rd 2004 04:45:00 AM

// Now we'll close the WHILE loop. 
}

Let me know if you need any help with this...

[aace]

Thanks. I have it finding the difference now between two dates and corresponding times. But, I need the total (i.e. total hours in a given week). The while loop doesn't look like it can add the data then spit out the final total. Right now, the code query's the database for a certain record with a certain date. But I really need it to pull out the records of dates between 2004-07-05 to 2004-07-12 and then spit out the total. Maybe I am just not using your code correctly. Let me know. Thanks.

[aace]

Should I change the Date attribute in the DB? I am really starting to mess up this code now.