Saving a "length in minutes" variable

[Grant Holmes]

I am an entertainer and have a "play list" on my site(http://www.GrantHolmes.com/playlist.php), where I show the music titles that I could perform at a gig. At the bottom, I  accumulate how many songs I sing. I'd like to show the total minutes of those songs too.

 

When I first setup my table in MySQL, I set the "Minutes" field to a numeric type so I could store the value of the length of a song. For instance a song of 3 minutes, 26 seconds, would be stored as: 3:26. As you already know, a numeric field cannot store that and truncates it at the ":" and only stores the "3".

 

My first fix was to change it to VARCHAR to accept the ":". But now the field isn't really numeric and I have no idea how to accumulate the time.

 

My questions:

1) At the bottom of the referenced page, I want to show the total value of the "xx" songs that are listed. So if there's 71 songs that total 93minutes and 39 seconds, at the bottom where I show the number of records add up the minutes of those. Is there any way to do that math with the field set as a VARCHAR?

 

2) Do I need to change back to a numeric field and store the time (for each song) in seconds (183) then convert on the page where they numerics could be accumulated then displayed?

 

I'm pretty 'shallow' on the php stuff, so please excuse my questions if ignorant.

[MadTechie]

why not store them in seconds and when you list them do something like this

<?php
#SELECT Name, Seconds FROM MusicList;
$total = 0;
//loop
$total = $total + $row['Seconds'];
$time = mktime(0, 0, $row['Seconds']);
$time = date("i.s",$time);
echo $row['Name']." - ".$time;
//end loop
$time = mktime(0, 0, $total);
$time = date("i.s",$time);
echo "Total Time = $time";
?>

[Grant Holmes]

MadT,

 

I will look at that this afternoon and see if it works. Thank you.

[Grant Holmes]

MT,

 

Here is the code in my page as of now. I've rebuilt a second table for testing with only 5 records of known values (totaling 345 seconds) and changed my Minutes field to a SMALLINT (4). So some of this may not work, but is what I'll be starting from (testing here with this code). Could you suggest how to insert your code?

 

<?
include("datatube/dbinfo.inc.php");
mysql_connect($dbhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * from SongList2 where active='1'";
$result=mysql_query($query);

$num=mysql_numrows($result); 

mysql_close();

echo "<center><H1>What would you like to hear Grant sing?</H1>";
?>
<CENTER>
<table border="1" cellspacing="2" cellpadding="3" class="sortable" width="90%" class="sortable">

<TR>
	<TH valign="top" sortdir="asc">Song Title</TH>
	<TH valign="top">In the style of</TH>	
	<TH valign="top" width="100">Pace</TH>
	<TH valign="top" width="100">Length</TH>							
</TR>
<?
$i=0;
while ($i < $num) {
$Active=mysql_result($result,$i,"Active");
$SongTitle=mysql_result($result,$i,"SongTitle");
$ArtistPrimary=mysql_result($result,$i,"ArtistPrimary");
$SongPace=mysql_result($result,$i,"SongPace");
$Minutes=mysql_result($result,$i,"Minutes");
$id=mysql_result($result,$i,"id"); 
if($SongPace == "")  $SongPace = " "; 
?>
</form>
      <tr>   
            <TD valign="top"><? echo "$SongTitle"; ?></TD>
            <TD valign="top"><? echo "$ArtistPrimary"; ?></TD>				   	   
            <TD valign="top"><? echo "$SongPace"; ?></TD>	   		
            <TD valign="top"><? echo "$Minutes"; ?></TD>	 		      
            </TD>
      </tr>
<?
++$i;
} 
echo "</table></CENTER>";
echo "<P>Total songs on playlist: $i</P>";
?>

 

I also just noticed that now the length shows up as "90" in seconds, so I'll want to convert that too.

 

Thanks for your help!!

[MadTechie]

sure

<?php
include("datatube/dbinfo.inc.php");
mysql_connect($dbhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * from SongList2 where active='1'";
$result=mysql_query($query);

$num=mysql_numrows($result); 

mysql_close();

echo "<center><H1>What would you like to hear Grant sing?</H1>";
?>
<CENTER>
<table border="1" cellspacing="2" cellpadding="3" class="sortable" width="90%" class="sortable">

<TR>
	<TH valign="top" sortdir="asc">Song Title</TH>
	<TH valign="top">In the style of</TH>	
	<TH valign="top" width="100">Pace</TH>
	<TH valign="top" width="100">Length</TH>							
</TR>
<?php
$i=0;
$total = 0; //ADDED
while ($i < $num)
{
$Active=mysql_result($result,$i,"Active");
$SongTitle=mysql_result($result,$i,"SongTitle");
$ArtistPrimary=mysql_result($result,$i,"ArtistPrimary");
$SongPace=mysql_result($result,$i,"SongPace");
$Minutes=mysql_result($result,$i,"Minutes");

//Added
$total = $total + $Minutes;
$time = mktime(0, 0, $Minutes);
$time = date("i.s",$time);

$id=mysql_result($result,$i,"id"); 
if($SongPace == "")  $SongPace = " "; 
?>
</form>
      <tr>   
            <TD valign="top"><? echo "$SongTitle"; ?></TD>
            <TD valign="top"><? echo "$ArtistPrimary"; ?></TD>				   	   
            <TD valign="top"><? echo "$SongPace"; ?></TD>	   		
            <TD valign="top"><? echo "$time"; ?></TD>	 		      
            </TD>
      </tr>
<?php
++$i;
} 
echo "</table></CENTER>";

$time = mktime(0, 0, $total);
$time = date("i.s",$time);

echo "<P>Total songs on playlist: $i ($time)</P>";
?>

[Grant Holmes]

MadTechie, You ROCK, dude!! That is absolutely fantastic! I made a wording change or two and substituted a ":" for the ".", but otherwise, I love it!!

 

Wish we could give ratings!! Thanks so much!

 

[Grant Holmes]

Now the really dumb/hard question. Is there an easy way to convert the 260 odd records I have as VARCHAR (with the : in the field [3:06]) AND convert to "seconds" [186]?

 

I could make a duplicate table, but don't know how to convert the data then change the table name- IF there is a way.

 

Just hate to have to do it manually!!

[MadTechie]

Create another field called `Seconds` and run this script, confirm the seconds are correct then delete the minutes field and rename the seconds to minutes

 

 

I have included a basic database for the example

<?php
/*
CREATE TABLE `test` (
  `ID` varchar(10) character set latin1 NOT NULL,
  `Minutes` varchar(15) character set latin1 NOT NULL,
  `Seconds` varchar(15) character set latin1 NOT NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `test` (`ID`, `Minutes`, `Seconds`) VALUES 
('1', '3:06', ''),
('2', '2:16', ''),
('3', '5:6', ''),
('4', '7:36', '');

*/


mysql_connect("localhost", "user", "pass") or die(mysql_error());
mysql_select_db("test") or die( "Unable to select database");

$table = "test";
$query="SELECT ID,Minutes FROM $table";
$result = mysql_query($query)or die(mysql_error());

while($row = mysql_fetch_assoc($result))
{
$ID = $row['ID'];
list($m, $s) = split(":",$row['Minutes']);
$sec = ($m*60)+$s;
$query="UPDATE $table SET Seconds='$sec' WHERE ID = $ID";
mysql_query($query)or die(mysql_error());
}
?>

[Grant Holmes]

This would be very cool! Let me ask a question out of ignorance... when I use your strategy from below;["confirm the seconds are correct then delete the minutes field and rename the seconds to minutes"], does the order of the table being different alter the way I insert new rows via PHP, or as long as the field name is "minutes" there are no worries??

[MadTechie]

the order of the fields doesn't matter.. infact you could, do

$query="UPDATE $table SET Minutes='$sec' WHERE ID = $ID";

but if it messed up your need to restore your table from a backup..

thats why i said use seconds (just incase), also running it twice would probably mess it up using the SQL statement above

[Grant Holmes]

I'm not sure I understood all that last, but knowing that the field order does not matter in new record creation is cool.

 

Stepping through the process, the conversion worked perfectly. Thank you. Before I deleted anything, I wanted to see the results page. It appears in making edits that I broke something. The TOTAL minutes/seconds at the page bottom is off.

 

Here's the testing page: http://grantholmes.com/playlist2.php

Here's the code for display: (Seconds now stored in "Seconds" field).

<?php
$i=0;
$total = 0; //ADDED
while ($i < $num)
{
$Active=mysql_result($result,$i,"Active");
$SongTitle=mysql_result($result,$i,"SongTitle");
$ArtistPrimary=mysql_result($result,$i,"ArtistPrimary");
$SongPace=mysql_result($result,$i,"SongPace");
        $Seconds=mysql_result($result,$i,"Seconds");

//Added
$total = $total + $Seconds;  //"Seconds" were "Minutes" in your example above
$time = mktime(0, 0, $Seconds);
$time = date("i:s",$time);

$id=mysql_result($result,$i,"id"); 
if($SongPace == "")  $SongPace = " "; 
?>
</form>
      <tr>   
            <TD valign="top"><? echo "$SongTitle"; ?></TD>
            <TD valign="top"><? echo "$ArtistPrimary"; ?></TD>				   	   
            <TD valign="top"><? echo "$SongPace"; ?></TD>	   		
            <TD valign="top"><? echo "$time"; ?></TD>	 		      
            </TD>
      </tr>
<?php
++$i;
} 
echo "</table></CENTER>";

$time = mktime(0, 0, $total);
$time = date("i:s",$time);

echo "<P>Total playlist: $i songs totaling: $time (minutes:seconds)</P>";
?>

[MadTechie]

Nope thats correct.. well kinda

 

for the total you are no longer just in minutes so we need to add hours

$time = date("i:s",$time);

echo "<P>Total playlist: $i songs totaling: $time (minutes:seconds)</P>";
?>

to

 

$time = date("H:i:s",$time); //Add hours

echo "<P>Total playlist: $i songs totaling: $time (hours:minutes:seconds)</P>";
?>

 

OR

$time = round($time/60); //convert to minutes
echo "<P>Total playlist: $i songs totaling: $time minutes</P>";

[Grant Holmes]

SWEET! That, ALL works. I've deleted the 'old' field, done the rename and all is well.

 

Thanks again for all your help. Make sure to see my PM.