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.

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>"; ?>

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??

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, 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!

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!!

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

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.

Thanks for the great ideas guys!! I appreciate the help.

That did it. I'd actually "tried too hard" to do the query. The Yahoo server this is on a BC version of PHPmyAdmin. Anyhow, I'd tried browsing into the DB/table. This time I just did the query after selecting the DB. Got the data I need. Thanks!

Wobbit, I know little about PHP, and NOTHING about MySQL queries!! I tried, "SELECT DISTINCT Contact_Info_Country FROM birthdays" MySQL said: "#1046 - No database selected" Can you help me correct the query? The database is 'contacts', table is 'birthdays' and field is 'Contact_Info_Country'

fnairb, I haven't had time to try your solution, but looking at your post, I'm not sure I understood it at first. The point is that there are nearly 14,000 records. I don't want to "look" through those records. I need something to tell me all the unique entries.

fnairb Relational would be great. But you understand the challenge. I'll look into your suggestion, which I hear you saying just do that select in mySql?

I would pull your private connection stuff out of your post. Too Much Information to have roaming around.

I have code that is successfully showing all the records (nearly 14,000) in my database. I figured this was the best place to start. Due to some imported records and such, we have some records that need to be "fixed", as it were. My biggest issue is the many ways countries have been added to the db. I know I can change all the "United states" to "US" through MySQL. But I have MANY variations of MANY countries and I don't know how to find records with unique country entries. One I see how many ways people have entered the data, I can straighten it out. So my question is how I would alter my code (or tell me to start over!!) below to just show records that have unique entries in the country field? <?php include("dbinfo.inc.php"); mysql_connect(mysql,$username,$password); @mysql_select_db($database) or die( "Unable to select database"); $query="SELECT * from birthdays"; $result=mysql_query($query); $num=mysql_numrows($result); mysql_close(); ?> <CENTER> <table border="1" cellspacing="2" cellpadding="3" class="sortable" width="90%"> <TR> <TH valign="top">From:</TH> <TH valign="top">City</TH> <TH valign="top">St</TH> <TH valign="top">Country</TH> </TR> <? $i=0; while ($i < $num) { $Active=mysql_result($result,$i,"Active"); $firstname=mysql_result($result,$i,"Contact_Info_FirstName"); $lastname=mysql_result($result,$i,"Contact_Info_LastName"); $city=mysql_result($result,$i,"Contact_Info_City"); $state=mysql_result($result,$i,"Contact_Info_State"); $zip=mysql_result($result,$i,"Contact_Info_ZipCode"); $country=mysql_result($result,$i,"Contact_Info_Country"); $Bfirstname=mysql_result($result,$i,"Birthday_Info_FirstName"); $Blastname=mysql_result($result,$i,"Birthday_Info_LastName"); $Bcity=mysql_result($result,$i,"Birthday_Info_City"); $Bstate=mysql_result($result,$i,"Birthday_Info_State"); $Bzip=mysql_result($result,$i,"Birthday_Info_ZipCode"); $Bcountry=mysql_result($result,$i,"Birthday_Info_Country"); $id=mysql_result($result,$i,"id"); ?> </form> <tr> <td valign="top"><input type="hidden" name="ud_id" value="<? echo $id; ?>"> <STRONG><? echo "$firstname $lastname"; ?></STRONG></TD> <TD valign="top"><? echo "$city"; ?></TD> <TD valign="top"><? echo "$state"; ?></TD> <TD valign="top"><? echo "$country"; ?></TD> </TD> </tr> <? ++$i; } echo "</table></CENTER>"; echo "<P>Total Records: $i</P>"; ?>