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

Last ping. No help? Even a "you're asking too much" would be nice at this point.

just found a tutorial that surrounds this issue without really answering it. It says it may be better to just store images in the site folder structure. Then in the table, just store the server path (flagimages/xxx.jpg) for each record. In my case all the flag images are 32x20 px and 1k in size, so space is not really an issue, is it?

I have created a four field table: id [int(4) auto_increment] code [char(2)] country [varchar(50)] flag [blob] On my PC,I have: a CSV file with 252 two-digit country codes (US) and country names (United States) A folder with 252 png common-sized flag images (the flag names correspond to the country codes) I want to populate my table with this information, so I can later compare a user country and display their flag next to their name. I've searched the general web and this forum for a simple upload script, but everything seems to be based on forms and user uploads, resizing, blah, blah, blah. I just need to populate this table with this information, and obviously keep the flags in the right record. Is there a way to write a script to parse a local folder for the data, or if I created a folder on the site, would that be easier. Two important points: 1-I know next to nothing about writing PHP, but I can edit the heck out of it!! 2-My client is on Yahoo, so there are some dumb PHP restrictions and I'm stuck with that for the moment. As always your kind help is appreciated.

Thanks all for the great informative replies. In this case, we'd be limiting users to five image, not unlimited. And for the most part, I FEEL like it could be in one row, one for each user. Is there "more security" in having 'user' information separate from 'demographic' information? I would think this would be moot, but am unsure.

Hey gang. A little discussion here. I need to create a membership area. I found a script here from http://phpsnips.com/snippet.php?id=16 That would allow creation of a basic user. We're going to collect other basic demographic information, including a couple photos. I assume I should only use one database. Should I have this initial user file setup information (above) ONLY in one table and all the other information (call this demographic stuff) in another table? Should the photo images (ONLY) be in a totally separate table, or should the images be in with the demographic stuff? I know little about PHP and nothing about "Joining" tables, but I'm learning. Please give me guidance on how best to proceed.

Hey gang, this one might be a stretch, but hey its Saturday right? Here's the scenario. Due to my inability to have all this PHP stuff down yet, I use a form generator from Forms To Go to produce a "processing" page that processes a given form. They work great, especially when someone enters an apostrophe (Dad's). I have a "backend" page where I can process the record the processing page puts in my table. I display certain records with an edit call in the last cell of the row, then pass the edit to another page. The edit page is working correctly and allows me to edit my table, but does NOT like apostrophes or other nonsense that the processing form handles well. I'm thinking, why not use a somewhat duplicate version of the above processing page to update the record. I KNOW there are two problems. The processing page is always creating a new record. It wouldn't know what record to update unless I passed it the row/record number. So on the display page (showing all records/fields), I show the edit link in the last cell; <TD valign="top"><CENTER> <a href='songs_edit.php?id=<? echo "$id"; ?>'>edit</a><TD> On the edit page I have all the stuff to process the edit: <form method="post"> <input type="hidden" name="ud_id" value="<? echo $id; ?>"> //********Blah, blah to display and allow editing of fields...one instance: Record Label:<input type="text" name="ud_RecordLabel" value="<? echo $RecordLabel; ?>"> // Then: <input type="submit" name="submit" value="Update"> <BR><BR><input type=button value="Cancel" onClick="history.go(-1)"><BR><BR> <input type="submit" name="submit" value="Delete" onClick="return confirm('Are you sure you want to delete this record? This cannot be undone!')"> (In this case I'd be posting to the form instead, but that's whats there now) So that should pass the record number. In the processing form, after it does all the text processing, it uses this section to post to the table: $mysql_link = @mysql_connect("localhost", "Table", "PassWord"); if (mysql_errno() == 0) { @mysql_select_db("music", $mysql_link); if (mysql_errno() == 0) { $sqlcmd = sprintf("INSERT INTO `SongList`(`SongTitle`, `ArtistPrimary`, `ArtistSecondary`, `WriterPrimary`, `WriterSecondary`, `RelatedURL`, `Comments`, `PartOf`, `SongStatus`, `SongPace`, `Minutes`, `YearRecorded`, `YearWritten`, `RecordLabel`, `Lyrics`, `active`) VALUES('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')", mysql_real_escape_string($FTG_SongTitle, $mysql_link), mysql_real_escape_string($FTG_ArtistPrimary, $mysql_link), mysql_real_escape_string($FTG_ArtistSecondary, $mysql_link), mysql_real_escape_string($FTG_WriterPrimary, $mysql_link), mysql_real_escape_string($FTG_WriterSecondary, $mysql_link), mysql_real_escape_string($FTG_RelatedURL, $mysql_link), mysql_real_escape_string($FTG_Comments, $mysql_link), mysql_real_escape_string($FTG_PartOf, $mysql_link), mysql_real_escape_string($FTG_SongStatus, $mysql_link), mysql_real_escape_string($FTG_SongPace, $mysql_link), mysql_real_escape_string($FTG_Minutes, $mysql_link), mysql_real_escape_string($FTG_YearRecorded, $mysql_link), mysql_real_escape_string($FTG_YearWritten, $mysql_link), mysql_real_escape_string($FTG_RecordLabel, $mysql_link), mysql_real_escape_string($FTG_Lyrics, $mysql_link), mysql_real_escape_string($FTG_active, $mysql_link)); @mysql_query($sqlcmd, $mysql_link); } # Redirect user to success page header("Location: http://www.MySite.com/SuccessPage.php"); exit; ?> Is this enough info? Is this possible to modify the processing form?? Asking too much??????

This is the result: <? include("datatube/dbinfo.inc.php"); mysql_connect($dbhost,$username,$password); @mysql_select_db($database) or die( "Unable to select database"); $result = mysql_query("SELECT * from SongList where active='1' ORDER BY RAND() LIMIT 1") or die(mysql_error()); $row = mysql_fetch_array( $result ) or die(mysql_error()); $num=mysql_numrows($result); mysql_close(); ?> <CENTER> <table border="1" cellspacing="2" cellpadding="3" width="90%"> <? $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"); $id=mysql_result($result,$i,"id"); if($SongPace == "") $SongPace = " "; ?> <tr> <TD valign="top"><? echo "$SongTitle"; ?></TD> <TD valign="top"><? echo "$ArtistPrimary"; ?></TD> <TD valign="top"><? echo "$SongPace"; ?></TD> </TD> </tr> <? ++$i; } echo "</table></CENTER>"; ?>

Redarrow, thanks for the reply. I just got it fixed. Maybe its too early!!! Thanks! What happened to the "Solved" button??

Caught one error in my $request, line: 6 fixed that, but still getting same error: <? include("datatube/dbinfo.inc.php"); mysql_connect($dbhost,$username,$password); @mysql_select_db($database) or die( "Unable to select database"); //$query="SELECT * from SongList where active='1'"; $result = mysql_fetch_row(mysql_query("SELECT * from SongList where active='1' ORDER BY RAND(id)") or die(mysql_error())); $row = mysql_fetch_array( $result ) or die(mysql_error()); $num=mysql_numrows($result); mysql_close(); ?> <CENTER> <table border="1" cellspacing="2" cellpadding="3" class="sortable" width="90%"> <? $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"); $id=mysql_result($result,$i,"id"); if($SongPace == "") $SongPace = " "; ?>

I'm trying to pull any random record from my table. I had code (modified below) that was pulling all the active records (see commented row around line 4). I searched the forum and found this post I tried to use that to modify my search, but am getting an error: "Incorrect arguments to RAND" <? include("datatube/dbinfo.inc.php"); mysql_connect($dbhost,$username,$password); @mysql_select_db($database) or die( "Unable to select database"); //$query="SELECT * from SongList where active='1'"; $result = mysql_fetch_row(mysql_query("SELECT * from SongList where active='1' ORDER BY RAND(id)") or die(mysql_error())); $row = mysql_fetch_array( $result2 ) or die(mysql_error()); $num=mysql_numrows($result); mysql_close(); ?> <CENTER> <table border="1" cellspacing="2" cellpadding="3" class="sortable" width="90%"> <? $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"); $id=mysql_result($result,$i,"id"); if($SongPace == "") $SongPace = " "; ?> <tr> <TD valign="top"><? echo "$SongTitle"; ?></TD> <TD valign="top"><? echo "$ArtistPrimary"; ?></TD> <TD valign="top"><? echo "$SongPace"; ?></TD> </TD> </tr> <? ++$i; } echo "</table></CENTER>"; ?>