Re: Ordering titles in MySQL (long)
| From: | Cameron Metcalf | Date: | Thu, 01 Nov 2001 15:12:03 +0000 |
| Subject: | Re: Ordering titles in MySQL (long) | ||
| References: | 1 | Groups: | php.db |
| Request: | Send a blank email to php-db+get-13914@lists.php.net to get a copy of this message | ||
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