Thanks for the advice
So reflecting what advice I have received to date:
Assuming that the Contact table would be used for:
Owners, Breeders, Sportsmen, Ancestors, Associations, and a myriad of
other categories of mankind, would the following normalization be
prudent?
ContactID
FirstN,MidN,LastN,Suffix,Cityid,Stateid,Zip_Code_pk,Addr1,Addr2,Other
Cityid pk
CityName
/*?? What about Norwood, MO...Norwood, MA*/
Stateid pk
Statename
Assnid
Assn (Associations)
Trials/Showsid
Trials/Show
Houndsid
Registry# /*Date to be a Timestamp?...reflecting yyyymmdd001 to infinity
refecting how many hounds born on that date*/
IFSB#
SFSB#
UKC#
AKC#
Whelping Date /*Is there such a thing as Dateid (linking table)*/
Sire (houndid)
Dam (houndid)
Owner (contactid)
Breeder (contactid)
Colorid
Colorname (20+ color combinations)
$table_name = "Chart_Users";
$sql = "SELECT * FROM $table_name order by album_title";
$result = @mysql_query($sql,$connection) or die(" Couldn't execute
query.");
//if(! $result = mysql_query($sql,$connection)) {
// print("ERROR ".mysql_errno().":
".mysql_error()."<br>\n$sql<br>\n");
// }
while ($row = mysql_fetch_array($result)) {
$id = $row['id'];
$username = $row['username'];
$title1 = $row['album_title'];
$title = stripslashes($title1);
$sql1 = "SELECT COUNT(*) FROM Chart_Files where id = '$id'";
$result1 = @mysql_query($sql1,$connection) or die(" Couldn't execute
query.");
//if(! $result = mysql_query($sql1,$connection)) {
//print("ERROR ".mysql_errno().":
".mysql_error()."<br>\n$sql<br>\n");
//}
$count = mysql_result($result1,0,"count(*)");
$sql2 = "SELECT date_format(date, '%b. %D, %Y') as date1 FROM
Chart_Files where id = '$id' order by photoid desc limit 1";
$result2 = @mysql_query($sql2,$connection) or die(" Couldn't execute
query.");
$row = mysql_fetch_array($result2);
$date1 = $row['date1'];
if ($count > 0) {
$display_block .= "<tr><td nowrap align=\"left\"><b><a
href=\"display_album.php?id=$id\">$title </a></b></td><td
align=\"left\">$count</td><td align=\"right\" nowrap>$date1</td></tr>";
}
}
The first thing I see, do you need all of the fields in your "SELECT *"
statement?
Jeff Oien wrote:
Hi Jeff,
the most important rule you should follow: don't query the database
in a loop to avoid a join! The following lines (maybe some changes
are necesary) will do the same job as your code, but considerable
faster.
Especially if 'id' is an index in both tables.
Lutz
$sql = 'SELECT u.id, u.username, u.album_title,'
.' date_format(MAX(f.date), '%b. %D, %Y') as date1,'
.' COUNT(*) AS cnt'
.' FROM Chart_Users AS u'
.' INNER JOIN Chart_Files AS f USING (id)'
.' GROUP BY f.id'
.' ORDER BY u.album_title ASC';
$result = @mysql_query($sql,$connection) or die(" Couldn't execute
query.");
while ($row = mysql_fetch_array($result)) {
$id = $row['id'];
$username = $row['username'];
$title1 = $row['album_title'];
$title = stripslashes($title1);
$date1 = $row['date1'];
$display_block .= "<tr><td nowrap align=\"left\"><b><a
$href=\"display_album.php?id=$id\">$title </a></b></td><td
align=\"left\">$cnt</td><td align=\"right\" nowrap>$date1</td></tr>";
}
[EMAIL PROTECTED] (Jeff Oien) writes:
> Here is some code I have for an index page of people who post
> charts on the Web, kind of like Yahoo Photos or something.
> It displays the album title, number of images and date of last
> upload. The page takes about 5-6 seconds to load which is all
> in the queries I'm sure. Is there a way I can make this more
efficient?
> http://www.webdesigns1.com/temp/code.txt
> Jeff
>
> --------------------------------------
>
> $table_name = "Chart_Users";
> $sql = "SELECT * FROM $table_name order by album_title";
> $result = @mysql_query($sql,$connection) or die(" Couldn't execute
query.");
> //if(! $result = mysql_query($sql,$connection)) {
> // print("ERROR ".mysql_errno().":
".mysql_error()."<br>\n$sql<br>\n");
> // }
>
> while ($row = mysql_fetch_array($result)) {
> $id = $row['id'];
> $username = $row['username'];
> $title1 = $row['album_title'];
> $title = stripslashes($title1);
>
> $sql1 = "SELECT COUNT(*) FROM Chart_Files where id = '$id'";
> $result1 = @mysql_query($sql1,$connection) or die(" Couldn't execute
query.");
> //if(! $result = mysql_query($sql1,$connection)) {
> //print("ERROR ".mysql_errno().":
".mysql_error()."<br>\n$sql<br>\n");
> //}
> $count = mysql_result($result1,0,"count(*)");
>
> $sql2 = "SELECT date_format(date, '%b. %D, %Y') as date1 FROM
Chart_Files where
> id = '$id' order by photoid desc limit 1";
> $result2 = @mysql_query($sql2,$connection) or die(" Couldn't execute
query.");
> $row = mysql_fetch_array($result2);
> $date1 = $row['date1'];
>
> if ($count > 0) {
>
> $display_block .= "<tr><td nowrap align=\"left\"><b><a
> href=\"display_album.php?id=$id\">$title </a></b></td><td
> align=\"left\">$count</td><td align=\"right\"
nowrap>$date1</td></tr>";
> }
> }
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Fantastic! I'm going to have to study the code more to really understand
it. Thanks for the help. I had to change the code just a little for it
to work:
$sql = "SELECT u.id, u.username, u.album_title,
date_format(MAX(f.date), '%b %D, %Y') as date1,
COUNT(*) AS cnt FROM Chart_Users AS u
INNER JOIN Chart_Files AS f USING (id)
GROUP BY f.id ORDER BY u.album_title ASC";
$result = @mysql_query($sql,$connection)
or die(" Couldn't execute query.");
while ($row = mysql_fetch_array($result)) {
$id = $row['id'];
$username = $row['username'];
$title1 = $row['album_title'];
$title = stripslashes($title1);
$date1 = $row['date1'];
$cnt = $row['cnt'];
$display_block .= "<tr><td nowrap align=\"left\">
<b><a href=\"display_album.php?id=$id\">$title </a></b></td>
<td align=\"left\">$cnt</td><td align=\"right\"
nowrap>$date1</td></tr>";
}
> Hi Jeff,
>
> the most important rule you should follow: don't query the database
> in a loop to avoid a join! The following lines (maybe some changes
> are necesary) will do the same job as your code, but considerable
faster.
> Especially if 'id' is an index in both tables.
>
> Lutz
>
>
> $sql = 'SELECT u.id, u.username, u.album_title,'
> .' date_format(MAX(f.date), '%b. %D, %Y') as date1,'
> .' COUNT(*) AS cnt'
> .' FROM Chart_Users AS u'
> .' INNER JOIN Chart_Files AS f USING (id)'
> .' GROUP BY f.id'
> .' ORDER BY u.album_title ASC';
>
> $result = @mysql_query($sql,$connection) or die(" Couldn't execute
query.");
>
> while ($row = mysql_fetch_array($result)) {
> $id = $row['id'];
> $username = $row['username'];
> $title1 = $row['album_title'];
> $title = stripslashes($title1);
> $date1 = $row['date1'];
>
> $display_block .= "<tr><td nowrap align=\"left\"><b><a
> $href=\"display_album.php?id=$id\">$title </a></b></td><td
> align=\"left\">$cnt</td><td align=\"right\" nowrap>$date1</td></tr>";
> }
>
>
> [EMAIL PROTECTED] (Jeff Oien) writes:
>
> > Here is some code I have for an index page of people who post
> > charts on the Web, kind of like Yahoo Photos or something.
> > It displays the album title, number of images and date of last
> > upload. The page takes about 5-6 seconds to load which is all
> > in the queries I'm sure. Is there a way I can make this more
efficient?
> > http://www.webdesigns1.com/temp/code.txt
> > Jeff
> >
> > --------------------------------------
> >
> > $table_name = "Chart_Users";
> > $sql = "SELECT * FROM $table_name order by album_title";
> > $result = @mysql_query($sql,$connection) or die(" Couldn't execute
query.");
> > //if(! $result = mysql_query($sql,$connection)) {
> > // print("ERROR ".mysql_errno().":
> ".mysql_error()."<br>\n$sql<br>\n");
> > // }
> >
> > while ($row = mysql_fetch_array($result)) {
> > $id = $row['id'];
> > $username = $row['username'];
> > $title1 = $row['album_title'];
> > $title = stripslashes($title1);
> >
> > $sql1 = "SELECT COUNT(*) FROM Chart_Files where id = '$id'";
> > $result1 = @mysql_query($sql1,$connection) or die(" Couldn't
> execute query.");
> > //if(! $result = mysql_query($sql1,$connection)) {
> > //print("ERROR ".mysql_errno().":
> ".mysql_error()."<br>\n$sql<br>\n");
> > //}
> > $count = mysql_result($result1,0,"count(*)");
> >
> > $sql2 = "SELECT date_format(date, '%b. %D,
> %Y') as date1 FROM Chart_Files where
> > id = '$id' order by photoid desc limit 1";
> > $result2 = @mysql_query($sql2,$connection)
> or die(" Couldn't execute query.");
> > $row = mysql_fetch_array($result2);
> > $date1 = $row['date1'];
> >
> > if ($count > 0) {
> >
> > $display_block .= "<tr><td nowrap align=\"left\"><b><a
> > href=\"display_album.php?id=$id\">$title </a></b></td><td
> > align=\"left\">$count</td><td align=\"right\"
nowrap>$date1</td></tr>";
> > }
> > }
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
So reflecting what advice I have received to date:
[Sportsmen] name to be registered of www
ContactID
FirstN,MidN,LastN,Suffix,Cityid,Stateid,Zip_Codepk,Addr1,Addr2,Other
Cityid pk
CityName
Stateid pk
Statename
Assnid
Assn (Associations)
Trials/Showsid
Trials/Show
Houndsid
Registry# /*Date to be a Timestamp?...reflecting yyyymmdd001 to infinity
refecting how many hounds born on that date*/
IFSB#
SFSB#
UKC#
AKC#
Whelping Date /*Is there such a thing as Dateid (linking table)*/
Sire (houndid)
Dam (houndid)
Owner (contactid)
Breeder (contactid)
Colorid (20+ color combinations)
/*I have developed a CSS file that will display hound names in a
heirarchial display to display a pedigree. My hope is to be able to use
only the Sireid and Damid to present 5 generations???*/
Thanks for the advice
So reflecting what advice I have received to date:
Assuming that the Contact table would be used for:
Owners, Breeders, Sportsmen, Ancestors, Associations, and a myriad of
other categories of mankind, would the following normalization be
prudent?
ContactID
FirstN,MidN,LastN,Suffix,Cityid,Stateid,Zip_Code_pk,Addr1,Addr2,Other
Cityid pk
CityName
/*?? What about Norwood, MO...Norwood, MA*/
Stateid pk
Statename
Assnid
Assn (Associations)
Trials/Showsid
Trials/Show
Houndsid
Hound Name
Registry# /*Date to be a Timestamp?...reflecting yyyymmdd001 to infinity
refecting how many hounds born on that date*/
IFSB#
SFSB#
UKC#
AKC#
Whelping Date /*Is there such a thing as Dateid (linking table) ?*/
Sire (houndid)
Dam (houndid)
Owner (contactid)
Breeder (contactid)
Colorid (20+ color combinations)
Colorid
Colorname
Russell Griechen
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
