StarDestroyer.Net BBS

Get your fill of sci-fi, science, and mockery of stupid ideas
http://stardestroyer.dyndns-home.com/

About Mikes announcement

http://stardestroyer.dyndns-home.com/viewtopic.php?f=7&t=1699

Page 1 of 1

About Mikes announcement

Posted: 2002-08-28 02:16pm
by Azeron
Hey mike, why not post the source code so others can take a look at it and make and changes?

Also if you wouldb't describing the dbase you are using to see if enhancements will work that way.

Posted: 2002-08-28 07:34pm
by Darth Wong
OK, here's the current PHP file (but DON't LAUGH at my shitty coding!)

Code: Select all

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
	<META HTTP-EQUIV="CONTENT-TYPE" CONTENT="text/html; charset=iso-8859-1">
	<TITLE>Star Trek Canon Database</TITLE>
	<LINK REL="STYLESHEET" TYPE="text/css" HREF="../style.css" \>
</HEAD>
<BODY>
<H1 ALIGN=CENTER>Star Trek Canon Database</H1>
<?PHP
$db=mysql_connect("localhost","nobody","nobody");
mysql_select_db("canon",$db);

// Run query if form has already been submitted
if ($Submit || $Name || $Category) {
	$sql1="SELECT * FROM startrek";
	if ($Series) { $sql2=" WHERE Series='$Series'"; }
	if ($Number) {
		if ($sql2) { $sql3=" AND Number='$Number'"; }
		else { $sql3=" WHERE Number='$Number'"; }
		}
	if ($Name) {
		if ($sql2 || $sql3) { $sql4=" AND Name LIKE '%$Name%'"; }
		else { $sql4=" WHERE Name LIKE '%$Name%'"; }
		}
	if ($Category) {
		if ($sql2 || $sql3 || $sql4) { $sql5=" AND Category='$Category'"; }
		else { $sql5=" WHERE Category='$Category'"; }
		}
	if ($Quotes) {
		if ($sql2 || $sql3 || $sql4 || $sql5) { $sql6=" AND Quotes LIKE '%$Quotes%'"; }
		else { $sql4=" WHERE Quotes LIKE '%$Quotes%'"; }
		}
	if ($Analysis) {
		if ($sql2 || $sql3 || $sql4 || $sql5 || $sql6 ) { $sql7=" AND Analysis LIKE '%$Analysis%'"; }
		else { $sql7=" WHERE Analysis LIKE '%$Analysis%'"; }
		}
	$sql=$sql1.$sql2.$sql3.$sql4.$sql5.$sql6.$sql7." ORDER BY Number";
	$result=mysql_query($sql,$db);
	$numrows=mysql_num_rows($result);
	echo "<P ALIGN=CENTER>Your search returned $numrows records.</P>";
	if ($myrow=mysql_fetch_array($result)) {
		echo "<P>Database started: 1999-07-27<BR>\n";
		$gendate = date("Y-m-d");
		echo "Page generated: $gendate</P>\n";
		echo "<TABLE BORDER=1>\n";
		do {
			echo "<TR VALIGN=TOP>\n";
			printf("<TD>%s Season %s, Ep# %s: "<A HREF=\"%s?Name=%s\">%s</A>"\n",
			$myrow["Series"],$myrow["Season"],$myrow["Number"],$PHP_SELF,$myrow["Name"],$myrow["Name"]);
			printf("<BR><BR>\n");
			printf("<SPAN CLASS=\"ufp\">%s</SPAN></TD>\n",$myrow["Quotes"]);
			printf("<TD><B>%s</B>: ",$myrow["Category"]);
			printf("%s</TD></TR>\n",$myrow["Analysis"]);
			}
		while ($myrow = mysql_fetch_array($result));
		echo "</TABLE>\n";
		}
	else {
		echo "<P ALIGN=CENTER>Much to learn, you still have.<BR>\n";
		echo "Your search was a failure, my young Padawan.</P>\n";
		}
	echo "<H2 ALIGN=CENTER>Search Database Again</H2>\n";
	}
else {
	echo "<H2 ALIGN=CENTER>Search Database</H2>";
	}
?>
<FORM METHOD="GET" ACTION="<?PHP echo $PHP_SELF?>">
	<TABLE ALIGN=CENTER>
	<TR>
	<TD>Series:
		<SELECT Name="Series">
		<OPTION>
		<OPTION>TOS
		<OPTION>TNG
		<OPTION>DS9
		<OPTION>VOY
		</SELECT></TD>
	<TD>Category:
		<SELECT Name="Category">
		<OPTION>
		<OPTION>Naval Weapons
		<OPTION>Naval Tactics
		<OPTION>Shields and Forcefields
		<OPTION>Propulsion
		<OPTION>Sensors
		<OPTION>Ground Combat
		<OPTION>Power
		<OPTION>Size and Scope
		<OPTION>Communications
		<OPTION>Computers and Androids
		<OPTION>Transporters
		<OPTION>Replicators
		<OPTION>Cloaks
		<OPTION>Design
		<OPTION>Command Structure
		<OPTION>Culture
		<OPTION>Borg
		<OPTION>Realism
		<OPTION>Misc
		</SELECT></TD></TR>
	<TR><TD ALIGN=RIGHT>Episode Name:</TD>
	<TD><INPUT TYPE="Text" NAME="Name" SIZE=30 VALUE="<?PHP echo $Name ?>"></TD></TR>
	<TR><TD ALIGN=RIGHT>Quote keywords:</TD>
	<TD><INPUT TYPE="Text" NAME="Quotes" SIZE=30 VALUE="<?PHP echo $Quotes ?>"></TD></TR>
	<TR><TD ALIGN=RIGHT>Analysis keywords:</TD>
	<TD><INPUT TYPE="Text" NAME="Analysis" SIZE=30 VALUE="<?PHP echo $Analysis ?>"></TD></TR>
	<TR><TD COLSPAN=2 ALIGN=CENTER><INPUT TYPE="Submit" NAME="Submit" VALUE="Submit"></TD></TR>
	</TABLE>
</FORM>
<P><HR>
Return to Database <A HREF="index.html">index</A><BR>
This website is owned and maintained by Michael Wong<BR>
This site is not affiliated with Lucasfilm or Paramount<BR>
All associated materials are used under "Fair Use" provisions of copyright law.<BR>
All original content is &copy; copyrighted by Michael Wong.<BR>
Click <A HREF="../index.html" TARGET="_top">here</A> to go to the main page</P>
</BODY>
</HTML>

Posted: 2002-08-28 08:17pm
by Raptor 597
O_o PHP, got the manual and read a few tutorials, can't get past the basics, real tough to understand...

Posted: 2002-08-28 09:24pm
by Azeron
Well your basic apprach is fine, when you are doing multiple null possible terms, you have to construct it, unless you want to go through the trouble of developing a dbase independent regular expression.
'

but that is irrelevent. After looking at the code, I noticed a few things.
1) your making this more difficult than it needs to be
2) Be careful on how you code you stuff, there are some changes in PHP coming up, and this will need to be updated
3) you have sql hack vulnerabilities built in. I will put in a fix.
4) speed optimizations
5) dbase persistance

I fixed number 1, left 2 alone, about to fix in a little bit 3 (i need you to change a few things in your dbase), and finished 4 and 5.

I might change a few things around though....

I will probably post something in an hour or 2

Posted: 2002-08-28 10:13pm
by Azeron
Well I didn;t go through debugging, but I did review it, and got rid of just about all the bugs I ussually make. Did you adapt the script from someone elses work? I just notcied some variables that weren't set yet included.

well if you want the sql vuln taken out, let me knbow. I don't think its really an issue in this case, though it might be a good idea. in case someone gets a wise idea. Alright, without further adieu,

Code: Select all

<?PHP 
$db=mysql_pconnect("localhost","nobody","nobody"); 
mysql_select_db("canon",$db); 
echo "
<!DOCTYPE HTML PUBLIC '-//W3C//DTD HTML 3.2//EN'> 
<HTML> 
<HEAD> 
   <META HTTP-EQUIV='CONTENT-TYPE' CONTENT='text/html; charset=iso-8859-1'> 
   <TITLE>Star Trek Canon Database</TITLE> 
   <LINK REL='STYLESHEET' TYPE='text/css' HREF='../style.css' > 
</HEAD> 
<BODY> 
<H1 ALIGN=CENTER>Star Trek Canon Database</H1> 
";


// Run query if form has already been submitted 
$where = 0;
function addTerm($var,$varName,$like=false){
 if($where=0){
  if($like=false){
   $sql.="WHERE $varName='$var' ";
	}
	else{
	 $sql.="WHERE $varName LIKE '%$var%' ";
	}
  $where=1;
 }
 else{
  if($like=false){
   $sql.="AND $varName='$var' ";
	}
	else{
	 $sql.="AND $varName LIKE '$var' ";
	}
 }
}


if ($Series || $Category) { 
   $sql="SELECT * FROM startrek "; 
   if ($Series) { 		 addTerm($Series,"Series") } 
   if ($Name) { 			 addTerm($Name,"Name",true) } 
   if ($Category) { 	 addTerm($Category,"Category") } 
   if ($Quotes) { 		 addTerm($Quotes,"Quotes",true) } 
   if ($Analysis) { 	 addTerm($Analysis,"Analaysis",true) } 
   sql+=" ORDER BY Number"; 
   $result=mysql_query($sql) or die($sql); 
   $numrows=mysql_num_rows($result); 
   echo "<P ALIGN=CENTER>Your search returned $numrows records.</P>"; 
   if ($myrow=mysql_fetch_array($result)) { 
      echo "<P>Database started: 1999-07-27<BR>\n"; 
      $gendate = date("Y-m-d"); 
      echo "Page generated: $gendate</P>\n"; 
      echo "<TABLE BORDER=1>\n"; 
      do { 
         echo "<TR VALIGN=TOP>\n"; 
         printf("<TD>%s Season %s, Ep# %s: "<A HREF=\"%s?Name=%s\">%s</A>"\n", 
         $myrow["Series"],$myrow["Season"],$myrow["Number"],$PHP_SELF,$myrow["Name"],$myrow["Name"]); 
         printf("<BR><BR>\n"); 
         printf("<SPAN CLASS=\"ufp\">%s</SPAN></TD>\n",$myrow["Quotes"]); 
         printf("<TD><B>%s</B>: ",$myrow["Category"]); 
         printf("%s</TD></TR>\n",$myrow["Analysis"]); 
         } 
      while ($myrow = mysql_fetch_array($result)); 
      echo "</TABLE>\n"; 
      }
		mysql_free_result($result);	 
   else { 
      echo "<P ALIGN=CENTER>Much to learn, you still have.<BR>\n"; 
      echo "Your search was a failure, my young Padawan.</P>\n"; 
      } 
   echo "<H2 ALIGN=CENTER>Search Database Again</H2>\n"; 
   } 
else { 
   echo "<H2 ALIGN=CENTER>Search Database</H2>"; 
   } 

echo "
<FORM METHOD='GET' ACTION='".$PHP_SELF."'> 
   <TABLE ALIGN=CENTER> 
   <TR> 
   <TD>Series: 
      <SELECT Name='Series'> 
      <OPTION> 
      <OPTION>TOS 
      <OPTION>TNG 
      <OPTION>DS9 
      <OPTION>VOY 
      </SELECT></TD> 
   <TD>Category: 
      <SELECT Name='Category'> 
      <OPTION> 
      <OPTION>Naval Weapons 
      <OPTION>Naval Tactics 
      <OPTION>Shields and Forcefields 
      <OPTION>Propulsion 
      <OPTION>Sensors 
      <OPTION>Ground Combat 
      <OPTION>Power 
      <OPTION>Size and Scope 
      <OPTION>Communications 
      <OPTION>Computers and Androids 
      <OPTION>Transporters 
      <OPTION>Replicators 
      <OPTION>Cloaks 
      <OPTION>Design 
      <OPTION>Command Structure 
      <OPTION>Culture 
      <OPTION>Borg 
      <OPTION>Realism 
      <OPTION>Misc 
      </SELECT></TD></TR> 
   <TR><TD ALIGN=RIGHT>Episode Name:</TD> 
   <TD><INPUT TYPE='Text' NAME='Name' SIZE=30 VALUE='".$Name."'></TD></TR> 
   <TR><TD ALIGN=RIGHT>Quote keywords:</TD> 
   <TD><INPUT TYPE='Text' NAME='Quotes' SIZE=30 VALUE='".$Quotes."'></TD></TR> 
   <TR><TD ALIGN=RIGHT>Analysis keywords:</TD> 
   <TD><INPUT TYPE='Text' NAME='Analysis' SIZE=30 VALUE='".$Analysis."'></TD></TR> 
   <TR><TD COLSPAN=2 ALIGN=CENTER><INPUT TYPE='Submit' NAME='Submit' VALUE='Submit'></TD></TR> 
   </TABLE> 
</FORM> 
<P><HR> 
Return to Database <A HREF='index.html'>index</A><BR> 
This website is owned and maintained by Michael Wong<BR> 
This site is not affiliated with Lucasfilm or Paramount<BR> 
All associated materials are used under "Fair Use" provisions of copyright law.<BR> 
All original content is &copy; copyrighted by Michael Wong.<BR> 
Click <A HREF='../index.html' TARGET='_top'>here</A> to go to the main page</P> 
</body>
</html>
";
%>

Posted: 2002-08-28 11:06pm
by Steve
Darth Wong wrote:OK, here's the current PHP file (but DON't LAUGH at my shitty coding!)
*points at Mike* [Nelson]HA HA![/Nelson]

Posted: 2002-08-29 01:33am
by Darth Wong
Steve wrote:*points at Mike* [Nelson]HA HA![/Nelson]
(motions to dark Imperial minions to hunt down this impudent person and lock him in a room with Jar-Jar Binks)

Posted: 2002-08-29 01:38am
by Darth Wong
Azeron wrote:Well I didn;t go through debugging, but I did review it, and got rid of just about all the bugs I ussually make. Did you adapt the script from someone elses work? I just notcied some variables that weren't set yet included.
No, it was just a little rough around the edges. Thanks for the help; I had to fix a couple of bugs to make it work (adding a global variable definition in the function, etc), but nothing really big.
well if you want the sql vuln taken out, let me know. I don't think its really an issue in this case, though it might be a good idea. in case someone gets a wise idea.
What kind of damage could anyone do, since the PHP script runs as a user with read-only access to the database?

Anyway, I added pagination and your function to the script and now it looks like this:

Code: Select all

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
	<META HTTP-EQUIV="CONTENT-TYPE" CONTENT="text/html; charset=iso-8859-1">
	<TITLE>Star Trek Canon Database</TITLE>
	<LINK REL="STYLESHEET" TYPE="text/css" HREF="../style.css" \>
</HEAD>
<BODY>
<H1 ALIGN=CENTER>Star Trek Canon Database</H1>
<?PHP
// Define page length = 50 rows
$pagelen=50;

$db=mysql_connect("localhost","nobody","nobody");
mysql_select_db("canon",$db);

// Define function for generating SQL query
$where = 0;
function addTerm($var,$varName,$like=false) {
	global $sql,$where;
	if ($where == 0) {
		if ($like == false) { $sql.="WHERE $varName='$var' "; }
		else { $sql.="WHERE $varName LIKE '%$var%' "; }
		$where=1;
		}
	else {
		if ($like == false) { $sql.="AND $varName='$var' "; }
		else { $sql.="AND $varName LIKE '%$var%' "; }
		}
	}

// Run query if variables are present, ie- form has been submitted
if ($Submit || $pagenum || $Series || $Name || $Category || $Quotes || $Analysis) {
	$sql="SELECT * FROM startrek ";
	if ($Series) { addTerm($Series,"Series"); }
	if ($Name) { addTerm($Name,"Name",true); }
	if ($Category) { addTerm($Category,"Category"); }
	if ($Quotes) { addTerm($Quotes,"Quotes",true); }
	if ($Analysis) { addTerm($Analysis,"Analysis",true); }
	$sql.=" ORDER BY Number";
	$result=mysql_query($sql,$db) or die($sql);

// Pagination routine
	$numrows=mysql_num_rows($result);
	$pagetotal = ceil($numrows/$pagelen);
	if ( $numrows > $pagelen ) {
		if ( ! $pagenum ) { $pagenum = 1; }
		$lowerlim = ($pagenum-1)*$pagelen;
		$upperlim = ($pagenum)*$pagelen;
		}
	else
		{
			$lowerlim = 0;
			$upperlim = $pagelen;
			$pagenum = 1;
		}
	$sqlrun=$sql." LIMIT $lowerlim,$pagelen";
	$result=mysql_query($sqlrun,$db);

// If search results non-zero results
	if ($myrow=mysql_fetch_array($result)) {

// Display headers
	if ( $pagenum == ($pagetotal) ) {
		echo "<P ALIGN=CENTER>Displaying $lowerlim to $numrows of $numrows records.</P>";
		}
	else {
		echo "<P ALIGN=CENTER>Displaying $lowerlim to $upperlim of $numrows records.</P>";
		}
	echo "<P>Database started: 1999-07-27<BR>\n";
	$gendate = date("Y-m-d");
	echo "Page generated: $gendate</P>\n";

// Display page numbers
	echo "<P>Page ";
	$page=1;
	for ( $page=1; $page<=$pagetotal ; $page +=1 ) {
		if ( $page == $pagenum ) {
			echo "<B>$page</B> ";
			}
		else {
			echo "<A HREF="$PHP_SELF?Series=$Series&Name=$Name&Category=$Category
			&Quotes=$Quotes&Analysis=$Analysis&pagenum=$page">$page</A> ";
			}
		}

// Display results in table form
		echo "<TABLE BORDER=1>\n";
		do {
			echo "<TR VALIGN=TOP>\n";
			printf("<TD>%s Season %s, Ep# %s: "<A HREF="%s?Name=%s">%s</A>"\n",
			$myrow["Series"],$myrow["Season"],$myrow["Number"],$PHP_SELF,$myrow["Name"],$myrow["Name"]);
			printf("<BR><BR>\n");
			printf("<SPAN CLASS="ufp">%s</SPAN></TD>\n",$myrow["Quotes"]);
			printf("<TD><B>%s</B>: ",$myrow["Category"]);
			printf("%s</TD></TR>\n",$myrow["Analysis"]);
			}
		while ($myrow = mysql_fetch_array($result));
		echo "</TABLE>\n";

// Display page numbers again, right-justified this time
	echo "<P ALIGN=RIGHT>Page ";
	$page=1;
	for ( $page=1; $page<=$pagetotal ; $page +=1 ) {
		if ( $page == $pagenum ) {
			echo "<B>$page</B> ";
			}
		else {
			echo "<A HREF="$PHP_SELF?Series=$Series&Name=$Name&Category=$Category
			&Quotes=$Quotes&Analysis=$Analysis&pagenum=$page">$page</A> ";
			}
		}

// Otherwise, print taunt
		}
	else {
		echo "<P ALIGN=CENTER>Much to learn, you still have.<BR>\n";
		echo "Your search was a failure, my young Padawan.</P>\n";
		}
	echo "<H2 ALIGN=CENTER>Search Database Again</H2>\n";
	}
else {
	echo "<H2 ALIGN=CENTER>Search Database</H2>";
	}
?>
<FORM METHOD="GET" ACTION="<?PHP echo $PHP_SELF?>">
	<TABLE ALIGN=CENTER>
	<TR>
	<TD>Series:
		<SELECT Name="Series">
		<OPTION>
		<OPTION>TOS
		<OPTION>TNG
		<OPTION>DS9
		<OPTION>VOY
		</SELECT></TD>
	<TD>Category:
		<SELECT Name="Category">
		<OPTION>
		<OPTION>Naval Weapons
		<OPTION>Naval Tactics
		<OPTION>Shields and Forcefields
		<OPTION>Propulsion
		<OPTION>Sensors
		<OPTION>Ground Combat
		<OPTION>Power
		<OPTION>Size and Scope
		<OPTION>Communications
		<OPTION>Computers and Androids
		<OPTION>Transporters
		<OPTION>Replicators
		<OPTION>Cloaks
		<OPTION>Design
		<OPTION>Command Structure
		<OPTION>Culture
		<OPTION>Borg
		<OPTION>Realism
		<OPTION>Misc
		</SELECT></TD></TR>
	<TR><TD ALIGN=RIGHT>Episode Name:</TD>
	<TD><INPUT TYPE="Text" NAME="Name" SIZE=30 VALUE="<?PHP echo $Name ?>"></TD></TR>
	<TR><TD ALIGN=RIGHT>Quote keyphrase:</TD>
	<TD><INPUT TYPE="Text" NAME="Quotes" SIZE=30 VALUE="<?PHP echo $Quotes ?>"></TD></TR>
	<TR><TD ALIGN=RIGHT>Analysis keyphrase:</TD>
	<TD><INPUT TYPE="Text" NAME="Analysis" SIZE=30 VALUE="<?PHP echo $Analysis ?>"></TD></TR>
	<TR><TD COLSPAN=2 ALIGN=CENTER><INPUT TYPE="Submit" NAME="Submit" VALUE="Submit"></TD></TR>
	</TABLE>
</FORM>
<P><HR>
Return to Database <A HREF="index.html">index</A><BR>
This website is owned and maintained by Michael Wong<BR>
This site is not affiliated with Lucasfilm or Paramount<BR>
All associated materials are used under "Fair Use" provisions of copyright law.<BR>
All original content is &copy; copyrighted by Michael Wong.<BR>
Click <A HREF="../index.html" TARGET="_top">here</A> to go to the main page</P>
</BODY>
</HTML>
So far, it seems to work OK. The pagination was kind of necessary; I didn't like the idea of someone clicking "submit" and getting 800 records all at once, with no warning.

Posted: 2002-08-29 09:02am
by Steve
Darth Wong wrote:
Steve wrote:*points at Mike* [Nelson]HA HA![/Nelson]
(motions to dark Imperial minions to hunt down this impudent person and lock him in a room with Jar-Jar Binks)
Oh, so I can take out the frustrations of my day to day life by beating Jar Jar to the brink of death? Can't kill him, since when he heals up I can repeat the process. :)

Posted: 2002-08-29 11:02am
by Azeron
Tell you what, I took a glance over it, and I think the sql can be broken down even further. The pagination routine I think is unneeded (but it works), it could use a bit of reworking so its more useful

I think I can simplify the query building proccess even more,(to its most elementary form) but it probably will slow the script down a bit. (I don;'t know how much)

I think you should use mysql_pconnect instead of mysql_connect. if there are more thjan one user on yiour website doing a search, or an dbase activity, it will cut down on the number of dbase connections you have to create. Its important, beacuse if you have too many connections open at once, the script will be put on hold till its max_timeout until a connection frees up. thats a big problem on your site, during heavy traffic times.

Alright I will let you know later when I can take a look at it.

edit: I noticed something I missed and changed my answer accordingly

Posted: 2002-08-29 11:11am
by Azeron
ohh and the sql vulnerability would be if someone used a charecter like ' or that to break out of your search parameter, and used to modify your query or even build a sub query, which I beleive could modifiy data....but I don;t anyone would do that on purpose. if you want to fix the vuln, you have to change every ' and " to another multilength charecter like (*& and then conmvert the output into " or ' depending on the code.

Posted: 2002-08-29 12:38pm
by aronkerkhof
Hey Azeron,

Couldn't Mike avoid being OwNeD by using the addslashes/stripslashes functions? That's pretty much what they are there for, to escape characters that could cause problems with data entry. Then if anyone tried to throw anything fishy in the search terms the db would interpret them as literal apostrophes and quotes instead of data delimiters.

easy as $Quotes = addslashes($Quotes);

Also, Mike, I have a couple of ideas of my own for this. Don't have time right now, but don't have a lot of time on my hands. Got a 3 (or maybe four if I really bust my ass today) day weekend coming up, I'll take a look at this. Question: Wouldn't the different drop downs work better if they were populated based on data available from the database? Then you could add a new record that referenced a new category in the database without having to touch your code.

Posted: 2002-08-29 01:16pm
by Azeron
Yah, thats what I was talking about data entry problems. Its a relatively common vuln and easily fixed, but sometimes I forget the easy things because I try to keep my methodology conssitant accross multiple langagues and platforms.


I htink your suggestion regarding populating dbase is a good one, but I think he wants to keep this quick and fast. It would be better if he redesigned the dbase so he could procces it once rather than reproccess the categories et all, than simply pout it out. More efficent for a high traffic site

on a side note:
I personnally thinkj he should implement my Heir Menu for his site, it would make navigation so much more simplier. and consistant.

Posted: 2002-08-29 02:43pm
by aronkerkhof
I wouldn't worry too much about querying the database to build the menu options. SELECTS with mysql are pretty much "free" as far as resources go. Its the INSERT/UPDATES that kill him with this BBS site. If he moves it to a diff server as he plans, one that has a better mysql build, like innodb, the database load he will place with the cannon database would be trivial in comparison. Actually, just splitting the site between BBS/and everything else, will probably leave the everything else database twiddling its thumbs.

Ooh, now that I've got my juices flowing, I'm envisioning a cannon database where readers can submit their own entries, which are not made public until Mike or another person with trusted status verifies them. And automatically cross references technologies from passage to passage. *begins laughing hysterically* ...and automatically searches gnutella for episode and move scripts to download and import. No! Wait! It will search for divx's of the actual episodes and movies and import them into BLOB fields! Yeah, and it prints free money, gives blowjobs, and brings about world peace!!

*Sigh* Why can't I just quit my job and work on fun side projects?

Posted: 2002-08-29 03:10pm
by Azeron
This is what I was thinking, when I was talking abouit minimizing the routine. As you see it should now consume about half the resources as it did before. Notice the change on the query building. I figure, that if the set fields are null a double %% will just look for anything, so this should qork. the Pagenation routine has been simplifed to its most elemental parts.

thanks fdor the tip on the addslashes

as for the pagenum thing, it just neds to add a thing or to the actual html writing to get it going.

Code: Select all

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"> 
<HTML> 
<HEAD> 
   <META HTTP-EQUIV="CONTENT-TYPE" CONTENT="text/html; charset=iso-8859-1"> 
   <TITLE>Star Trek Canon Database</TITLE> 
   <LINK REL="STYLESHEET" TYPE="text/css" HREF="../style.css" \> 
</HEAD> 
<BODY> 
<H1 ALIGN=CENTER>Star Trek Canon Database</H1> 
<?PHP 
// Define page length = 50 rows 
$pagelen=50; 

$db=mysql_connect("localhost","nobody","nobody"); 
mysql_select_db("canon",$db); 

// Run query if variables are present, ie- form has been submitted 
if ($Submit || $pagenum || $Series || $Name || $Category || $Quotes || $Analysis) { 
   $sql="SELECT * FROM startrek "; 
   $sql.="WHERE Series LIKE '%".addSlashes( $Series)."%' ";
   $sql.="AND Quotes LIKE '%".addSlashes($Quotes)."%' ";
   $sql.="AND Name LIKE '%".addSlahses($Name)."%' ";
   $sql.="AND Category LIKE '%".addSlashes($Category)."%' ";
   $sql.="AND Analysis LIKE '%".addSlashes($Analysis)."%' ";
   $sql.=" ORDER BY Number ";
   if(!$pagenum){$pagenum=0}
   $sql.="LIMIT ".($pagenum *$ pagelen).",".(++$pagenum*$pagelen);
   $result=mysql_query($sql,$db) or die($sql); '

//Stuff Down here needs to be fixed in regards to numbering out.

// If search results non-zero results 
   if ($myrow=mysql_fetch_array($result)) { 

// Display headers 
   if ( $pagenum == ($pagetotal) ) { 
      echo "<P ALIGN=CENTER>Displaying $lowerlim to $numrows of $numrows records.</P>"; 
      } 
   else { 
      echo "<P ALIGN=CENTER>Displaying $lowerlim to $upperlim of $numrows records.</P>"; 
      } 
   echo "<P>Database started: 1999-07-27<BR>\n"; 
   $gendate = date("Y-m-d"); 
   echo "Page generated: $gendate</P>\n"; 

// Display page numbers 
   echo "<P>Page "; 
   $page=1; 
   for ( $page=1; $page<=$pagetotal ; $page +=1 ) { 
      if ( $page == $pagenum ) { 
         echo "<B>$page</B> "; 
         } 
      else { 
         echo "<A HREF=\"$PHP_SELF?Series=$Series&Name=$Name&Category=$Category 
         &Quotes=$Quotes&Analysis=$Analysis&pagenum=$page\">$page</A> "; 
         } 
      } 

// Display results in table form 
      echo "<TABLE BORDER=1>\n"; 
      do { 
         echo "<TR VALIGN=TOP>\n"; 
         printf("<TD>%s Season %s, Ep# %s: "<A HREF=\"%s?Name=%s\">%s</A>"\n", 
         $myrow["Series"],$myrow["Season"],$myrow["Number"],$PHP_SELF,$myrow["Name"],$myrow["Name"]); 
         printf("<BR><BR>\n"); 
         printf("<SPAN CLASS=\"ufp\">%s</SPAN></TD>\n",$myrow["Quotes"]); 
         printf("<TD><B>%s</B>: ",$myrow["Category"]); 
         printf("%s</TD></TR>\n",$myrow["Analysis"]); 
         } 
      while ($myrow = mysql_fetch_array($result)); 
      echo "</TABLE>\n"; 

// Display page numbers again, right-justified this time 
   echo "<P ALIGN=RIGHT>Page "; 
   $page=1; 
   for ( $page=1; $page<=$pagetotal ; $page +=1 ) { 
      if ( $page == $pagenum ) { 
         echo "<B>$page</B> "; 
         } 
      else { 
         echo "<A HREF=\"$PHP_SELF?Series=$Series&Name=$Name&Category=$Category 
         &Quotes=$Quotes&Analysis=$Analysis&pagenum=$page\">$page</A> "; 
         } 
      } 

// Otherwise, print taunt 
      } 
   else { 
      echo "<P ALIGN=CENTER>Much to learn, you still have.<BR>\n"; 
      echo "Your search was a failure, my young Padawan.</P>\n"; 
      } 
   echo "<H2 ALIGN=CENTER>Search Database Again</H2>\n"; 
   } 
else { 
   echo "<H2 ALIGN=CENTER>Search Database</H2>"; 
   } 
?> 
<FORM METHOD="GET" ACTION="<?PHP echo $PHP_SELF?>"> 
   <TABLE ALIGN=CENTER> 
   <TR> 
   <TD>Series: 
      <SELECT Name="Series"> 
      <OPTION> 
      <OPTION>TOS 
      <OPTION>TNG 
      <OPTION>DS9 
      <OPTION>VOY 
      </SELECT></TD> 
   <TD>Category: 
      <SELECT Name="Category"> 
      <OPTION> 
      <OPTION>Naval Weapons 
      <OPTION>Naval Tactics 
      <OPTION>Shields and Forcefields 
      <OPTION>Propulsion 
      <OPTION>Sensors 
      <OPTION>Ground Combat 
      <OPTION>Power 
      <OPTION>Size and Scope 
      <OPTION>Communications 
      <OPTION>Computers and Androids 
      <OPTION>Transporters 
      <OPTION>Replicators 
      <OPTION>Cloaks 
      <OPTION>Design 
      <OPTION>Command Structure 
      <OPTION>Culture 
      <OPTION>Borg 
      <OPTION>Realism 
      <OPTION>Misc 
      </SELECT></TD></TR> 
   <TR><TD ALIGN=RIGHT>Episode Name:</TD> 
   <TD><INPUT TYPE="Text" NAME="Name" SIZE=30 VALUE="<?PHP echo $Name ?>"></TD></TR> 
   <TR><TD ALIGN=RIGHT>Quote keyphrase:</TD> 
   <TD><INPUT TYPE="Text" NAME="Quotes" SIZE=30 VALUE="<?PHP echo $Quotes ?>"></TD></TR> 
   <TR><TD ALIGN=RIGHT>Analysis keyphrase:</TD> 
   <TD><INPUT TYPE="Text" NAME="Analysis" SIZE=30 VALUE="<?PHP echo $Analysis ?>"></TD></TR> 
   <TR><TD COLSPAN=2 ALIGN=CENTER><INPUT TYPE="Submit" NAME="Submit" VALUE="Submit"></TD></TR> 
   </TABLE> 
</FORM> 
<P><HR> 
Return to Database <A HREF="index.html">index</A><BR> 
This website is owned and maintained by Michael Wong<BR> 
This site is not affiliated with Lucasfilm or Paramount<BR> 
All associated materials are used under "Fair Use" provisions of copyright law.<BR> 
All original content is &copy; copyrighted by Michael Wong.<BR> 
Click <A HREF="../index.html" TARGET="_top">here</A> to go to the main page</P> 
</BODY> 
</HTML>

Posted: 2002-08-29 06:46pm
by Azeron
Alright well I fixed what I saw, and I made the script about as effiecent as I can. If anyone else has any ideas, I wouldn't mind hearing them.

Code: Select all

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"> 
<HTML> 
<HEAD> 
   <META HTTP-EQUIV="CONTENT-TYPE" CONTENT="text/html; charset=iso-8859-1"> 
   <TITLE>Star Trek Canon Database</TITLE> 
   <LINK REL="STYLESHEET" TYPE="text/css" HREF="../style.css" \> 
</HEAD> 
<BODY> 
<H1 ALIGN=CENTER>Star Trek Canon Database</H1> 
<?PHP 
// Define page length = 50 rows 
$pagelen=50; 

$db=mysql_connect("localhost","nobody","nobody"); 
mysql_select_db("canon",$db); 

// Run query if variables are present, ie- form has been submitted 
if ($Submit || $pagenum || $Series || $Name || $Category || $Quotes || $Analysis) { 
   $sql="SELECT *,COUNT FROM startrek "; 
   $sql.="WHERE Series LIKE '%".addSlashes( $Series)."%' ";
   $sql.="AND Quotes LIKE '%".addSlashes($Quotes)."%' ";
   $sql.="AND Name LIKE '%".addSlashes($Name)."%' ";
   $sql.="AND Category LIKE '%".addSlashes($Category)."%' ";
   $sql.="AND Analysis LIKE '%".addSlashes($Analysis)."%' ";
   $sql.=" ORDER BY Number ";
   if($result=mysql_query($sql,$db)}{
   //do some page forwarding
	 if(!$pagenum){$pagenum=0}	
	 mysql_data_seek($result,$pagenum*$pagelen);
	 	 
// Display headers 
   echo "<P ALIGN=CENTER>Displaying ";
	 echo $pagelen*$pagenum; 
	 echo " to ";
	 if($numrows>$pagenum*$pagelen+$pagelen){echo $pagenum*$pagelen+$pagelen}
	 else { echo mysql_num_rows($result)}
	 echo " of ";
	 echo mysql_num_rows($result)." records.</P>"; 	 
   echo "<P>Database started: 1999-07-27<BR>\n"; 
   echo "Page generated: ".date("Y-m-d")."</P>\n"; 

// Display page numbers 
   echo "<P>Page ";
	 var $strTemp; 
   var $page=1; 
   for ( $page=1;$page<=ceil(mysql_num_rows($result)/$pagelen);$page++ ) { 
      if ( $page == $pagenum ) { 
         $strTemp.="<B>$page</B> "; 
         } 
      else { 
         $strTemp.="<A HREF=\"$PHP_SELF?Series=$Series&Name=$Name&Category=$Category 
         &Quotes=$Quotes&Analysis=$Analysis&pagenum=$page\">$page</A> "; 
         } 
      }
			echo $strTemp; 

// Display results in table form 
      echo "<TABLE BORDER=1>\n"; 
			$z=0;
      do { 
         echo "<TR VALIGN=TOP>\n"; 
         printf("<TD>%s Season %s, Ep# %s: "<A HREF=\"%s?Name=%s\">%s</A>"\n", 
         $myrow["Series"],$myrow["Season"],$myrow["Number"],$PHP_SELF,$myrow["Name"],$myrow["Name"]); 
         printf("<BR><BR>\n"); 
         printf("<SPAN CLASS=\"ufp\">%s</SPAN></TD>\n",$myrow["Quotes"]); 
         printf("<TD><B>%s</B>: ",$myrow["Category"]); 
         printf("%s</TD></TR>\n",$myrow["Analysis"]);
				 $z++; 
         } 
      while ($myrow = mysql_fetch_array($result)&$z!=$pagelen); 
      echo "</TABLE>\n"; 

// Display page numbers again, right-justified this time 
   echo "<P ALIGN=RIGHT>Page "; 
   echo $strTemp;
   }
//print taunt			
	 else{
	 	echo "<P ALIGN=CENTER>Much to learn, you still have.<BR>\n"; 
    echo "Your search was a failure, my young Padawan.</P>\n"; 
	 } 
   echo "<H2 ALIGN=CENTER>Search Database Again</H2>\n"; 
 } 
else { 
   echo "<H2 ALIGN=CENTER>Search Database</H2>"; 
 } 
?> 
<FORM METHOD="GET" ACTION="<?PHP echo $PHP_SELF?>"> 
   <TABLE ALIGN=CENTER> 
   <TR> 
   <TD>Series: 
      <SELECT Name="Series"> 
      <OPTION> 
      <OPTION>TOS 
      <OPTION>TNG 
      <OPTION>DS9 
      <OPTION>VOY 
      </SELECT></TD> 
   <TD>Category: 
      <SELECT Name="Category"> 
      <OPTION> 
      <OPTION>Naval Weapons 
      <OPTION>Naval Tactics 
      <OPTION>Shields and Forcefields 
      <OPTION>Propulsion 
      <OPTION>Sensors 
      <OPTION>Ground Combat 
      <OPTION>Power 
      <OPTION>Size and Scope 
      <OPTION>Communications 
      <OPTION>Computers and Androids 
      <OPTION>Transporters 
      <OPTION>Replicators 
      <OPTION>Cloaks 
      <OPTION>Design 
      <OPTION>Command Structure 
      <OPTION>Culture 
      <OPTION>Borg 
      <OPTION>Realism 
      <OPTION>Misc 
      </SELECT></TD></TR> 
   <TR><TD ALIGN=RIGHT>Episode Name:</TD> 
   <TD><INPUT TYPE="Text" NAME="Name" SIZE=30 VALUE="<?PHP echo $Name ?>"></TD></TR> 
   <TR><TD ALIGN=RIGHT>Quote keyphrase:</TD> 
   <TD><INPUT TYPE="Text" NAME="Quotes" SIZE=30 VALUE="<?PHP echo $Quotes ?>"></TD></TR> 
   <TR><TD ALIGN=RIGHT>Analysis keyphrase:</TD> 
   <TD><INPUT TYPE="Text" NAME="Analysis" SIZE=30 VALUE="<?PHP echo $Analysis ?>"></TD></TR> 
   <TR><TD COLSPAN=2 ALIGN=CENTER><INPUT TYPE="Submit" NAME="Submit" VALUE="Submit"></TD></TR> 
   </TABLE> 
</FORM> 
<P><HR> 
Return to Database <A HREF="index.html">index</A><BR> 
This website is owned and maintained by Michael Wong<BR> 
This site is not affiliated with Lucasfilm or Paramount<BR> 
All associated materials are used under "Fair Use" provisions of copyright law.<BR> 
All original content is &copy; copyrighted by Michael Wong.<BR> 
Click <A HREF="../index.html" TARGET="_top">here</A> to go to the main page</P> 
</BODY> 
</HTML>

Posted: 2002-08-29 11:39pm
by Darth Wong
Thanks a lot for the optimized code! It's definitely cleaner, and much less embarrassing :)

I made a couple of changes to fix minor issues (eg- changing the do-while loop to a while loop because it was choking on the first row), but it seems to work fine now.

Code: Select all

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
	<META HTTP-EQUIV="CONTENT-TYPE" CONTENT="text/html; charset=iso-8859-1">
	<TITLE>Star Trek Canon Database</TITLE>
	<LINK REL="STYLESHEET" TYPE="text/css" HREF="../style.css" \>
</HEAD>
<BODY>
<H1 ALIGN=CENTER>Star Trek Canon Database</H1>
<?PHP
// Define page length = 50 rows
$pagelen=50;

$db=mysql_connect("localhost","nobody","nobody");
mysql_select_db("canon",$db);

// Run query if variables are present, ie- form has been submitted
if ($Submit || $pagenum || $Series || $EpName || $Category || $Quotes || $Analysis) {
	$sql="SELECT * FROM startrek ";
	$sql.="WHERE Series LIKE '%".addSlashes($Series)."%' ";
	$sql.="AND Name LIKE '%".addSlashes($EpName)."%' ";
	$sql.="AND Category LIKE '%".addSlashes($Category)."%' ";
	$sql.="AND Quotes LIKE '%".addSlashes($Quotes)."%' ";
	$sql.="AND Analysis LIKE '%".addSlashes($Analysis)."%' ";
	$sql.=" ORDER BY Number";

// If we get a result with at least 1 row, do some page forwarding
	if ( ($result=mysql_query($sql,$db)) && (mysql_num_rows($result)>0) ) {
		if( !$pagenum ) { $pagenum=1; }
		mysql_data_seek ($result,($pagenum-1)*$pagelen);

// Display headers
		echo "<P ALIGN=CENTER>Displaying ";
		echo $pagelen*($pagenum-1)+1;
		echo " to ";
		$numrows=mysql_num_rows($result);
		if($numrows>$pagenum*$pagelen) {
			echo $pagenum*$pagelen;
			}
		else { echo $numrows; }
		echo " of ";
		echo $numrows." records.</P>";

		echo "<P>Database started: 1999-07-27<BR>\n";
		echo "Page generated: ".date("Y-m-d")."</P>\n";

// Display page numbers
		echo "<P>Page";
		$strTemp=" ";
		$page=1;
		for ( $page=1; $page<=ceil($numrows/$pagelen); $page++ ) {
			if ( $page == $pagenum ) {
				$strTemp.="<B>$page</B> ";
				}
			else {
				$strTemp.="<A HREF=\"$PHP_SELF?Series=$Series&EpName=$EpName&Category=$Category
				&Quotes=$Quotes&Analysis=$Analysis&pagenum=$page\">$page</A> ";
				}
			}
		echo $strTemp;

// Display results in table form
		echo "<TABLE BORDER=1>\n";
		$tabrow=1;
// Note: we use while rather than do-while because we seek to 0,
// so we want to increment our position by 1 before grabbing the first row
		while (($myrow = mysql_fetch_array($result)) && ($tabrow<=$pagelen) ) {
			echo "<TR VALIGN=TOP>\n";
			printf("<TD>%s Season %s, Ep# %s: "<A HREF=\"%s?EpName=%s\">%s</A>"\n",
			$myrow["Series"],$myrow["Season"],$myrow["Number"],$PHP_SELF,$myrow["Name"],$myrow["Name"]);
			printf("<BR><BR>\n");
			printf("<SPAN CLASS=\"ufp\">%s</SPAN></TD>\n",$myrow["Quotes"]);
			printf("<TD><B>%s</B>: ",$myrow["Category"]);
			printf("%s</TD></TR>\n",$myrow["Analysis"]);
			$tabrow++;
			}
		echo "</TABLE>\n";

// Display page numbers again, right-justified this time
		echo "<P ALIGN=RIGHT>Page";
		echo $strTemp;
		}

// If no records were returned, print taunt
	else {
		echo "<P ALIGN=CENTER>Much to learn, you still have.<BR>\n";
		echo "Your search was a failure, my young Padawan.</P>\n";
		}
	echo "<H2 ALIGN=CENTER>Search Database Again</H2>\n";
	}
else {
	echo "<H2 ALIGN=CENTER>Search Database</H2>";
	}
?>
<FORM METHOD="GET" ACTION="<?PHP echo $PHP_SELF?>">
	<TABLE ALIGN=CENTER>
	<TR>
	<TD>Series:
		<SELECT Name="Series">
		<OPTION>
		<OPTION>TOS
		<OPTION>TNG
		<OPTION>DS9
		<OPTION>VOY
		</SELECT></TD>
	<TD>Category:
		<SELECT Name="Category">
		<OPTION>
		<OPTION>Naval Weapons
		<OPTION>Naval Tactics
		<OPTION>Shields and Forcefields
		<OPTION>Propulsion
		<OPTION>Sensors
		<OPTION>Ground Combat
		<OPTION>Power
		<OPTION>Size and Scope
		<OPTION>Communications
		<OPTION>Computers and Androids
		<OPTION>Transporters
		<OPTION>Replicators
		<OPTION>Cloaks
		<OPTION>Design
		<OPTION>Command Structure
		<OPTION>Culture
		<OPTION>Borg
		<OPTION>Realism
		<OPTION>Misc
		</SELECT></TD></TR>
	<TR><TD ALIGN=RIGHT>Episode Name:</TD>
	<TD><INPUT TYPE="Text" NAME="EpName" SIZE=30 VALUE="<?PHP echo $EpName ?>"></TD></TR>
	<TR><TD ALIGN=RIGHT>Quote keyphrase:</TD>
	<TD><INPUT TYPE="Text" NAME="Quotes" SIZE=30 VALUE="<?PHP echo $Quotes ?>"></TD></TR>
	<TR><TD ALIGN=RIGHT>Analysis keyphrase:</TD>
	<TD><INPUT TYPE="Text" NAME="Analysis" SIZE=30 VALUE="<?PHP echo $Analysis ?>"></TD></TR>
	<TR><TD COLSPAN=2 ALIGN=CENTER><INPUT TYPE="Submit" NAME="Submit" VALUE="Submit"></TD></TR>
	</TABLE>
</FORM>
<P><HR>
Return to Database <A HREF="index.html">index</A><BR>
This website is owned and maintained by Michael Wong<BR>
This site is not affiliated with Lucasfilm or Paramount<BR>
All associated materials are used under "Fair Use" provisions of copyright law.<BR>
All original content is &copy; copyrighted by Michael Wong.<BR>
Click <A HREF="../index.html" TARGET="_top">here</A> to go to the main page</P>
</BODY>
</HTML>
PS. Regarding the interesting idea of making the form generate its own checkboxes from a query, I suppose I should point out that the "Series" and "Category" fields are actually defined as SET datatypes in the database, not CHAR. So in theory, one could get all of the possible values by simply querying the database structure. Anyway, thanks a lot for your help. If there's anything I can do for you, let me know.

Posted: 2002-08-30 10:31am
by Azeron
No prob, its the least I could do for the cool site you put up.
All times are UTC-05:00
Page 1 of 1

Powered by phpBB® Forum Software © phpBB Limited