Hi Ian, I tried a couple different things, trying to chop off leading articles.
I didn't want to maintain a 2nd column for titles, but eventually had to succumb to this. (My difficulty is that I'm using French and English titles (for a bilingual interface), so I actually have four fields: my fields are labelled as follows: nameE (english title), nameF (french title), namesortE (english title with leading article removed), namesortF (with french leading article removed. When I enter a title into my database, using a php form, the leading article is lopped off the front off the title and this new form of the title is thrown into the record's namesortE column. (the same thing happens for the french fields). Leading articles that I want to remove are stored in a text file called 'noisewords.txt' my script looks for words in the text file, and if it finds a match between the text file entries and the title I've just entered, it removes the appearance of the word, in the title. the 'varnameE' and 'varnameF' fields may throw you off. it is used as working title. our records go by more than one title so in order to view the records by title or by variant title (alphabetically skipping leading articles) I've had to have a separate table for titles just for the sake of holding variant names. please overlook my coding practices in the snippet below. I've had no formal training in programming, since my trs-80 days learning BASIC on 2K radio shack computers. be assured though, the following does work for me. I came up with this solution thanks to olinux who forwarded me the URL for this article: http://phpbuilder.com/columns/clay19990421.php3 which even includes a list of "noisewords" for you. Cameron (code below) ******************* following is the code I currently use: stage==1 is the process of data being entered into the database. stage==2 is the input form that supplies the data for stage==1. <?php if($stage == 1) { // PROCESS ADD NEW DATABASE RECORD FORM $connect = mysql_connect("localhost", "$username", "$password"); $select = mysql_select_db("bases"); // INSERT NEW DATA INTO PRINCIPLE TABLE while(list($key, $vala) = each ($accessID)) while(list($key, $valf) = each ($formatID)) while(list($key, $valu) = each ($urlID)) while(list($key, $valv) = each ($vendorID)) { $query = "INSERT INTO principle (nameE, nameF, daterange, descriptionE, subjectE, notesE, descriptionF, subjectF, notesF, license, accessID, formatID, urlID, vendorID, orbispo, livedate) VALUES ('$nameE', '$nameF', '$daterange', '$descriptionE', '$subjectE', '$notesE', '$descriptionF', '$subjectF', '$notesF', '$license', '$vala', '$valf', '$valu', '$valv', '$orbispo', '$livedate')"; $result = mysql_query($query) OR die($query . mysql_error()); } ?> <?php // INSERT NEW DATA INTO SOFTWAREDETAILS TABLE while(list($key, $vals) = each ($softwareID)) { $query_2 = "INSERT INTO softwaredetails (baseID, softwareID) VALUES ('$newbaseID', '$vals')"; $result_2 = mysql_query($query_2) OR die($query . mysql_error()); } ?> <?php // INSERT NEW DATA INTO VARNAME TABLE { $query_3 = "INSERT INTO varname (varnameE, varnameF, baseID) VALUES ('$nameE', '$nameF', '$newbaseID')"; $result_3 = mysql_query($query_3) OR die($query . mysql_error()); } ?> <?php //TRIMSCRIPT FOR ENGLISH NAMESORT COLUMN IN TABLE PRINCIPLE $query = "select nameE, baseID from principle"; $result = mysql_query($query); $number = mysql_numrows($result); $j = 0; WHILE ($j < $number) { $title = mysql_result($result,$j,"nameE"); $qid = mysql_result($result,$j,"baseID"); $noise_words = file ("noisewords.txt"); $filtered = $title; $filtered = ereg_replace("^"," ",$filtered); for ($i=0; $i < count($noise_words); $i++) { $filterword = trim($noise_words[$i]); $filtered = eregi_replace(" $filterword "," ",$filtered); } $filtered = trim($filtered); $filtered = addslashes($filtered); $querywords = ereg_replace(",","",$filtered); $entersorts = "UPDATE principle set namesortE = \"$querywords\" WHERE baseID = $qid"; $result_0 = mysql_query($entersorts) OR die($entersorts . mysql_error()); $j++; } //E-STAT special case update ENGLISH $estate = "UPDATE principle set namesortE = \"E-STAT\" WHERE baseID = 73"; $result_0 = mysql_query($estate) OR die($estate . mysql_error()); ?> <?php //TRIMSCRIPT FOR FRENCH NAMESORT COLUMN IN TABLE PRINCIPLE $query = "select nameF, baseID from principle"; $result = mysql_query($query); $number = mysql_numrows($result); $j = 0; WHILE ($j < $number) { $title = mysql_result($result,$j,"nameF"); $qid = mysql_result($result,$j,"baseID"); $noise_words = file ("noisewords.txt"); $filtered = $title; $filtered = ereg_replace("^"," ",$filtered); for ($i=0; $i < count($noise_words); $i++) { $filterword = trim($noise_words[$i]); $filtered = eregi_replace(" $filterword "," ",$filtered); } $filtered = trim($filtered); $filtered = addslashes($filtered); $querywords = ereg_replace(",","",$filtered); $entersorts = "UPDATE principle set namesortF = \"$querywords\" WHERE baseID = $qid"; $result_0 = mysql_query($entersorts) OR die($entersorts . mysql_error()); $j++; } //E-STAT special case update FRENCH $estatf = "UPDATE principle set namesortF = \"E-STAT\" WHERE baseID = 73"; $result_0 = mysql_query($estatf) OR die($estatf . mysql_error()); ?> <?php //TRIMSCRIPT FOR ENGLISH VARNAMESORT COLUMN IN TABLE VARNAME $query = "select varnameE, varnameID from varname"; $result = mysql_query($query); $number = mysql_numrows($result); $j = 0; WHILE ($j < $number) { $title = mysql_result($result,$j,"varnameE"); $qid = mysql_result($result,$j,"varnameID"); $noise_words = file ("noisewords.txt"); $filtered = $title; $filtered = ereg_replace("^"," ",$filtered); for ($i=0; $i < count($noise_words); $i++) { $filterword = trim($noise_words[$i]); $filtered = eregi_replace(" $filterword "," ",$filtered); } $filtered = trim($filtered); $filtered = addslashes($filtered); $querywords = ereg_replace(",","",$filtered); $entersorts = "UPDATE varname set namesortE = \"$querywords\" WHERE varnameID = $qid"; $result_0 = mysql_query($entersorts) OR die($entersorts . mysql_error()); $j++; } //E-STAT special case update ENGLISH $estate = "UPDATE varname set namesortE = \"E-STAT\" WHERE varnameID = 64"; $result_0 = mysql_query($estate) OR die($estate . mysql_error()); ?> <?php //TRIMSCRIPT FOR FRENCH VARNAMESORT COLUMN IN TABLE VARNAME $query = "select varnameF, varnameID from varname"; $result = mysql_query($query); $number = mysql_numrows($result); $j = 0; WHILE ($j < $number) { $title = mysql_result($result,$j,"varnameF"); $qid = mysql_result($result,$j,"varnameID"); $noise_words = file ("noisewords.txt"); $filtered = $title; $filtered = ereg_replace("^"," ",$filtered); for ($i=0; $i < count($noise_words); $i++) { $filterword = trim($noise_words[$i]); $filtered = eregi_replace(" $filterword "," ",$filtered); } $filtered = trim($filtered); $filtered = addslashes($filtered); $querywords = ereg_replace(",","",$filtered); $entersorts = "UPDATE varname set namesortF = \"$querywords\" WHERE varnameID = $qid"; $result_0 = mysql_query($entersorts) OR die($entersorts . mysql_error()); $j++; } //E-STAT special case update FRENCH $estatf = "UPDATE varname set namesortF = \"E-STAT\" WHERE varnameID = 64"; $result_0 = mysql_query($estatf) OR die($estatf . mysql_error()); { print ("<a href=\"menu.php3\"> Return to main menu</a> <br><a href=\"entre5.php3?stage=2\">Add another new database record</a> <br><a href=\"namesortF.php3\">View resulting namesortF column</a> <br><a href=\"namesortE.php3\">View resulting namesortE column</a> <br><a href=\"varnamesortF.php3\">View resulting varnamesrtF column</a> <br><a href=\"varnamesortE.php3\">View resulting varnamesortE column</a> (doublecheck for E-STAT)"); } }elseif($stage == 2) { // DATA ENTRY FORM to ADD A NEW DATABASE RECORD into the system ?> <FORM METHOD=POST ACTION="<?php print ("$PHP_SELF"); ?>"> <table><tr><td colspan="2"> <p><h1>New DATABASE record - Entry Form</h1></p></td></tr> <?php $connect = mysql_connect("localhost", "username","password"); $select = mysql_select_db("bases"); $query_max = "SELECT MAX(baseID) FROM principle"; $result_max = mysql_query($query_max); while($lastbaseID = mysql_fetch_array($result_max)) $baseID = ($lastbaseID[0] + 1); { echo ("<tr><td valign=\"top\" \"nowrap\" ><b><font color=\"#FF0000\">New Database Unique Identifier: </b></td> <td valign=\"top\" \"nowrap\"><b> $baseID</b> (cannot be edited). <input type=\"hidden\" name=\"newbaseID\" value=\"$baseID\"></td></tr>"); } ?> <tr> <td valign="top" "nowrap" ><b><font color="#FF0000">Your system UserName </b></td> <td valign="top" "nowrap"><input type="text" name="username"></font></td></tr> <tr><td valign="top" "nowrap"><b><font color="#FF0000">Your system PAssWord: (english): </b></td> <td valign="top" "nowrap"><input type="password" name="password"></font></td></tr> <tr bgcolor="#99CCCC"><td valign="top" "nowrap"><b>Name (english): </b></td> <td valign="top" "nowrap"><input type="text" name="nameE"></td></tr> <tr><td valign="top" "nowrap"><b>Name (french): </b></td> <td valign="top" "nowrap"><input type="text" name="nameF"></td></tr> <tr bgcolor="#99CCCC"><td valign="top" "nowrap"><b>Date Range of database contents: </b></td> <td valign="top" "nowrap"><input type="text" name="daterange"></td></tr> <tr><td valign="top" "nowrap"><b>Description (english): </b></td> <td valign="top" "nowrap"><TEXTAREA name="descriptionE" cols="30" rows="4"></TEXTAREA></td></tr> <tr bgcolor="#99CCCC"><td valign="top" "nowrap"><b>Subjects covered (english): </b></td> <td valign="top" "nowrap"><TEXTAREA name="subjectE" cols="30" rows="4"></TEXTAREA></td></tr> <tr><td valign="top" "nowrap"><b>Notes (english): </b></td> <td valign="top" "nowrap"><TEXTAREA name="notesE" cols="30" rows="4"></TEXTAREA></td></tr> <tr bgcolor="#99CCCC"><td valign="top" "nowrap"><b>Description (french): </b></td> <td valign="top" "nowrap"><TEXTAREA name="descriptionF" cols="30" rows="4"></TEXTAREA></td></tr> <tr><td valign="top" "nowrap"><b>Subjects covered (french): </b></td> <td valign="top" "nowrap"><TEXTAREA name="subjectF" cols="30" rows="4"></TEXTAREA></td></tr> <tr bgcolor="#99CCCC"><td valign="top" "nowrap"><b>Notes (french): </b></td> <td valign="top" "nowrap"><TEXTAREA name="notesF" cols="30" rows="4"></TEXTAREA></td></tr> <tr><td valign="top" "nowrap"><b>Number of licensed users: </b></td> <td valign="top" "nowrap"><input type="text" name="license"></td></tr> <tr bgcolor="#99CCCC"> <td valign="top" "nowrap"><b>Live date: </b></td> <td valign="top" "nowrap"><input type="text" name="livedate"></td></tr> <tr><td valign="top" "nowrap"><b>ORBIS purchase order number: </b></td> <td valign="top" "nowrap"><input type="text" name="orbispo"></td></tr> <tr bgcolor="#99CCCC"> <td valign="top" "nowrap"><b>Choose its ACCESSTYPE</td> <td valign="top" "nowrap"><SELECT NAME="accessID[]" SIZE="1"> <?php $query = "select accessID, accessE FROM accesstype ORDER BY accessE"; $result = mysql_query ("$query"); while ($access = mysql_fetch_array ($result)) { print ("<OPTION VALUE=\"$access[0]\">$access[1]\n"); } ?> </SELECT></td></tr> <tr><td valign="top" "nowrap"><b>Choose its FORMAT:</td> <td valign="top" "nowrap"><SELECT NAME="formatID[]" SIZE="1"> <?php $query_format = "select formatID, formatE FROM format ORDER BY formatE"; $result_format = mysql_query ("$query_format"); while ($format = mysql_fetch_array ($result_format)) { print ("<OPTION VALUE=\"$format[0]\">$format[1]\n"); } ?> </SELECT></td></tr> <tr bgcolor="#99CCCC"><td valign="top" "nowrap"><b>Choose its URL:</td> <td><SELECT NAME="urlID[]" SIZE="1"> <?php $query_url = "select urlID, urlE FROM url ORDER BY urlE"; $result_url = mysql_query ("$query_url"); while ($url = mysql_fetch_array ($result_url)) { print ("<OPTION VALUE=\"$url[0]\">$url[1]\n"); } ?> </SELECT></td></tr> <tr> <td valign="top" "nowrap"><b>Choose any Required software:</td> <td><SELECT NAME="softwareID[]" SIZE="1" MULTIPLE> <?php $query_soft = "select softwareID, softwareE FROM software ORDER BY softwareE"; $result_soft = mysql_query ("$query_soft"); while ($software = mysql_fetch_array ($result_soft)) { print ("<OPTION VALUE=\"$software[0]\">$software[1]\n"); } ?> </SELECT></td></tr> <tr bgcolor="#99CCCC"><td valign="top" "nowrap"><b>Choose its vendor:</td> <td><SELECT NAME="vendorID[]" SIZE="1"> <?php $query_vend = "SELECT vendorID, vendor FROM vendor ORDER BY vendor"; $result_vend = mysql_query ("$query_vend"); while ($vendor = mysql_fetch_array ($result_vend)) { print ("<OPTION VALUE=\"$vendor[0]\">$vendor[1]\n"); } ?> </SELECT></td> </tr> <tr> <td><p> <INPUT TYPE="hidden" name="stage" value="1"> <INPUT TYPE="submit" name="submit" value="submit this new database"> </p></td></tr> </table> </center> </FORM> "Ian M. Evans" wrote: > > I just saw your posting and wondered if you ever rec'd an answer. > > I'm looking to alphabetize film titles while ignoring 'a', 'an' and 'the'. > > It's too bad they don't realize a lot of people would like this and add it > to the SQL language so you could do something like: > > SELECT date, titles FROM movies ORDER BY date DESC, titles LIBRARY > > -- > Ian Evans > Digital Hit Entertainment > http://www.digitalhit.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
