Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Timing out - is this code to cumbersome?

Status
Not open for further replies.

mpopnoe

Programmer
Feb 28, 2002
47
0
0
US
I thought this was a slick approach but now that I've loaded alot more data into MySQL the same PHP code I was using to render the page is now failing. I'll give a brief description of what I am doing and then put some code in for you to see.

The basic approach I'm using is on the load event of the page, I call a javascript function which contains php code. The php code obtains the data and echos out javascript code that populates my <select> objects that the website user will use to drill down to the information they want to obtain. There are 3 <select> objects that are related, so as you make a selection from one of them the others changes their information accordingly. Please keep in mind that when I only had about 10 records in the database this code worked flawlessly, now it throws strange errors. I also have probably not set up the database the best way - although I believe it is normalized correctly. Anyway, here is some code:

//I decided to place the entire page here, if it is too much to look at let me know and I'll strip it down ... thanks in advance!!

<?
$link = mysql_connect('localhost', '******', '******')
or die('Could not connect: ' . mysql_error());

mysql_select_db('******') or die('Could not select database');

if (isset($HTTP_POST_VARS["course_books"])) {
if ($HTTP_POST_VARS["course_books"] == 'NOPE') {
$course_sent = 'NOPE';
//echo $course_sent;
}
else {
//echo "RESET course_books to empty string";
$course_sent = ""; //reset
}
}

$NoRecords = "";

?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2 Final//EN">
<HTML>
<HEAD>
<TITLE></TITLE>
<META NAME="Generator" CONTENT="TextPad 4.6">
<META NAME="Author" CONTENT="?">
<META NAME="Keywords" CONTENT="?">
<META NAME="Description" CONTENT="?">

<SCRIPT language="JavaScript">
<!--

function BodyLoad() {

<?
if (isset($HTTP_POST_VARS["form_sent"]) || isset($HTTP_POST_VARS["course_books"])) {
}
else {

echo "var select_school = document.frmSearch.select_school;";

echo "document.frmSearch.select_course.options[0] = new Option('_________________');\n";
echo "document.frmSearch.select_course.options[0].value = 'spacer';\n";
echo "document.frmSearch.select_prof.options[0] = new Option('_________________');\n";
echo "document.frmSearch.select_prof.options[0].value = 'spacer';\n";

//we query the database for all of the Sub_category's
//where Main_category equals the chosen one. was '$Row1[0]'
$Query2 = "SELECT * FROM school WHERE college_id = 1";

$Result2 = mysql_query($Query2);
echo "document.frmSearch.select_school.options[0] = new Option('Select Department');\n";
echo "document.frmSearch.select_school.options[0].value = '-1';\n";

$ctr = 1;
//This will insert the Sub_categores into the select box
While( $Row2 = mysql_fetch_array($Result2) ) {
echo "document.frmSearch.select_school.options[$ctr] = new Option('$Row2[1]');\n";
echo "document.frmSearch.select_school.options[$ctr].value = '$Row2[0]';\n";
$ctr++;
}

echo "document.frmSearch.select_school.options[$ctr] = new Option('_________________');\n";
echo "document.frmSearch.select_school.options[$ctr].value = 'spacer';\n";

}
//close our database connection
//mysql_close($link);
?>
}

function Fill_Course() {
var select_school = document.frmSearch.select_school;
var select_course = document.frmSearch.select_course;

if(document.frmSearch.select_school.options[document.frmSearch.select_school.selectedIndex].value != 0 ) {
//this clears the Sub_categories select box.
document.frmSearch.select_course.length = 0;
}

<?php
$Query = "SELECT * FROM school";
$Result = mysql_query($Query);

while( $Row1 = mysql_fetch_array($Result) ) {
?>
if(document.frmSearch.select_school.options[document.frmSearch.select_school.selectedIndex].text == "<?PHP echo $Row1[1]; ?>") {
<?PHP
//we query the database for all of the Sub_category's
//where Main_category equals the chosen one.
$Query2 = "SELECT * FROM course WHERE school_id = '$Row1[0]'";

$Result2 = mysql_query($Query2);
echo "document.frmSearch.select_course.options[0] = new Option('Select Course');\n";
echo "document.frmSearch.select_course.options[0].value = '-1';\n";

$ctr = 1;
//This will insert the Sub_categores into the select box
While( $Row2 = mysql_fetch_array($Result2) ) {
echo "document.frmSearch.select_course.options[$ctr] = new Option('$Row2[1]');\n";
echo "document.frmSearch.select_course.options[$ctr].value = '$Row2[0]';\n";
$ctr++;
}
?>
}// close js IF
//Fill_Prof_Department();
<?PHP
}
?>
}

function Fill_Prof() {
//var select_school = document.frmSearch.select_school;
//var select_course = document.frmSearch.select_course;

if(document.frmSearch.select_course.options[document.frmSearch.select_course.selectedIndex].value != 0 ) {
//this clears the Sub_categories select box.
document.frmSearch.select_prof.length = 0;
}

<?php
$Query = "SELECT * FROM course";
$Result = mysql_query($Query);

while( $Row1 = mysql_fetch_array($Result) ) {
?>
if(document.frmSearch.select_course.options[document.frmSearch.select_course.selectedIndex].text == "<?PHP echo $Row1[1]; ?>") {
<?PHP
//we query the database for all of the Sub_category's
//where Main_category equals the chosen one.
$Query2 = "SELECT * FROM professor WHERE course_id = '$Row1[0]'";

$Result2 = mysql_query($Query2);
echo "document.frmSearch.select_prof.options[0] = new Option('Select Professor');\n";
echo "document.frmSearch.select_prof.options[0].value = '-1';\n";

$ctr = 1;
//This will insert the Sub_categores into the select box
While( $Row2 = mysql_fetch_array($Result2) ) {
echo "document.frmSearch.select_prof.options[$ctr] = new Option('$Row2[1]');\n";
echo "document.frmSearch.select_prof.options[$ctr].value = '$Row2[0]';\n";
$ctr++;
}
?>
}

<?PHP
}
//close our database connection
//mysql_close($link);
?>
}

function Get_Books() {
//time to go get the books
<?
echo "document.frmSearch.course_books.value='true';\n";
?>
document.frmSearch.submit();
}

function Get_Books_School() {
//time to go get the books
<?
echo "document.frmSearch.course_books.value='NOPE';\n";
?>
document.frmSearch.submit();
}

function Get_Books_Prof() {
//time to go get the books
<?
echo "document.frmSearch.prof_books.value='NOPE';\n";
?>
document.frmSearch.submit();
}

function Go_Keyword() {
//time to go get the books by keyword search
<?
echo "document.frmSearch.use_keyword.value='DOIT!';\n";
?>
document.frmSearch.submit();
}


-->
</SCRIPT>
</HEAD>

<BODY bgColor=#336699 onload="BodyLoad();"><!-- old color:#6699BB -->
<!-- rounded corners -->
<TABLE width=775 align=center cellspacing=0 cellpadding=0>
<TR>
<TD width=6 height=6><IMG height=6 src="../images/corner_tl_FFFFFF.gif" width=6></TD>
<TD height=6><IMG height=6 src="../images/white.jpg" width=763></TD>
<TD width=6 height=6><IMG height=6 src="../images/corner_tr_FFFFFF.gif" width=6></TD>
</TR>
</TABLE>

<TABLE width=775 align=center bgcolor=#ffffff>
<!-- header -->
<TR>
<TD width=100% align=right>
<a href=""><font style="font-face:verdana; font-size:8pt; color:#FF6600; text-decoration: none;">buy&nbsp;&nbsp;|&nbsp;</font></a>
<a href=""><font style="font-face:verdana; font-size:8pt; color:#FF6600; text-decoration: none;">sell&nbsp;&nbsp;|&nbsp;&nbsp;</font></a>
<a href=""><font style="font-face:verdana; font-size:8pt; color:#FF6600; text-decoration: none;">about us&nbsp;&nbsp;&nbsp;|&nbsp;&nbsp;</font></a>
<a href=""><font style="font-face:verdana; font-size:8pt; color:#FF6600; text-decoration: none;">contact us&nbsp;&nbsp;&nbsp;|&nbsp;&nbsp;</font></a>
<a href=""><font style="font-face:verdana; font-size:8pt; color:#FF6600; text-decoration: none;">our policies&nbsp;&nbsp;</font></a>
</TD>
</TR>
<TR>
<TD width=100% bgcolor=#ffffff>
<table cellpadding=0 cellspacing=0>
<tr>
<td valign="bottom" background="../photos/header.jpg" width=280 height=50>
<font face="verdana" size=4 color="#ffffff">BOOKGHOST.COM</font>&nbsp;&nbsp;&nbsp;
<font face="verdana" size=-1 color="#ffffff">&nbsp;&nbsp;&nbsp;&nbsp;PORTLAND</font>
</td>
<td height=50 valign="bottom">
<font face="verdana" size=-1>
&nbsp;STATE'S TEXTBOOK EXCHANGE</font>&nbsp;&nbsp;
</td>
</tr>
</table>
</TD>
</TR>

<TR>
<TD>
<table width=95% align=center>
<tr>
<td height=6 valign="top" style="border-color: #000000; border-style: none none solid none; border-width: 0 0 1px 0;">
&nbsp;
</td>
</tr>
</table>
</TD>
</TR>

<!-- main contents -->
<TR>
<TD valign=top width=750>
<table>
<tr height=350>
<td valign=top width=150 style="border-color: #000000; border-style: none solid none none; border-width: 0 1px 0 0;"><!-- width was 50 -->
<FORM name="frmSearch" method="post" action="index.php">
<!--<input type=hidden name="form_sent" value="true">-->
<?echo //$frm_sent?>
<?echo //$course_sent?>
<input type=hidden name='form_sent' value='true'>
<input type=hidden name='course_books' value='true'>
<input type=hidden name='prof_books' value='true'>
<input type=hidden name='use_keyword' value='true'>
<TABLE>
<!--<TR>
<TD>
<select id="myState" name="select_state" onchange="Fill_Sub();" style="font-family: Arial; font-size: 9.5px; font-style: normal; background: #FFFFF8">

</select>
</TD>
</TR>-->
<!--<DIV ID="MMDiv" style="visibility:hidden">-->
<!--<TR>
<TD>
<select name="select_college" onchange="Fill_School();" style="font-family: Arial; font-size: 9.5px; font-style: normal; background: #FFFFF8">

</select>
</TD>
</TR>
</DIV>-->
<TR>
<TD>
<select name="select_school" onchange="Get_Books_School();" style="font-family: Arial; font-size: 9.5px; font-style: normal; background: #FFFFF8">
<?
if (isset($HTTP_POST_VARS["form_sent"]) || isset($HTTP_POST_VARS["course_books"])) {
//query system, display all departments and select the chosen dept.
$Query = "SELECT * FROM school WHERE college_id = 1";
$Res = mysql_query($Query);

$ctr = 1;
while($Row1 = mysql_fetch_array($Res) ) {
if ($Row1[0] == $_POST["select_school"]) {
echo "<option value='$Row1[0]' selected>$Row1[1]</option>";
}
else {
echo "<option value='$Row1[0]'>$Row1[1]</option>";
}
$ctr++;
}
echo "<option value='spacer'>_________________</option>";
}
?>
</select>
</TD>
</TR>
<TR>
<TD>
<select name="select_course" onchange="Get_Books();" style="font-family: Arial; font-size: 9.5px; font-style: normal; background: #FFFFF8">
<?
if (isset($HTTP_POST_VARS["form_sent"]) || isset($HTTP_POST_VARS["course_books"])) {
//if ($_POST["select_course"] = 'spacer') {

//query system
//$Query = "SELECT * FROM course WHERE school_id = ".$_POST["select_course"];
$Query = "SELECT * FROM course WHERE school_id = ".$_POST["select_school"];
$Res = mysql_query($Query);
echo "<option value='-1'>Select Course</option>";
$ctr = 1;
while($Row1 = mysql_fetch_array($Res) ) {
if ($Row1[0] == $_POST["select_course"]) {
echo "<option value='$Row1[0]' selected>$Row1[1]</option>";
}
else {
echo "<option value='$Row1[0]'>$Row1[1]</option>";
}
$ctr++;
}
echo "<option value='spacer'>_________________</option>";
}
?>
</select>
</TD>
</TR>
<TR>
<TD>
<select name="select_prof" onchange="Get_Books_Prof();" style="font-family: Arial; font-size: 9.5px; font-style: normal; background: #FFFFF8">
<?
if (isset($HTTP_POST_VARS["form_sent"]) || isset($HTTP_POST_VARS["course_books"])) {
//if ($_POST["select_course"] = 'spacer') {

if ($course_sent == 'NOPE') {

$Query = "SELECT course_id,course_name FROM course WHERE school_id = ".$_POST['select_school']."";
$res1 = mysql_query($Query);

echo "<option value='-1'>Select Professor</option>";

$test = "";

if (mysql_num_rows($res1)<1) {
$NoRecords = "There are no books for this Department";
}else{
$i = 0;

While($Row = mysql_fetch_array($res1)) {
$test = $test.$Row[0].",";
$crsID[$i] = $Row[0]; //Course ID
$crsName[$i] = $Row[1]; //Course Name
$i++;
}
$test = substr($test,0,strlen($test)-1);
//get all books for this department based on course IDs
$Query2 = "SELECT * FROM professor WHERE course_id IN(".$test.")";
$Result2 = mysql_query($Query2);
}

$i = 0;
$ctr = 1;
//This will insert the Sub_categores into the select box
While($Row2 = mysql_fetch_array($Result2) ) {
if ($crsID[$i] == $Row2[2]) {
}else{ //increment counter - must be more courses
$i++;
}
echo "<option value='$Row2[0]'>$Row2[1] | $crsName[$i]</option>";
$ctr++;

}
echo "<option value='spacer'>_________________</option>";

}
else {
//query system
$Query = "SELECT * FROM professor WHERE course_id = ".$_POST["select_course"];
$Res = mysql_query($Query);
echo "<option value='-1'>Select Professor</option>";
$ctr = 1;
while($Row1 = mysql_fetch_array($Res) ) {
if ($Row1[0] == $_POST["select_prof"]) {
echo "<option value='$Row1[0]' selected>$Row1[1]</option>";
}
else {
echo "<option value='$Row1[0]'>$Row1[1]</option>";
}
$ctr++;
}
echo "<option value='spacer'>_________________</option>";
}
}
?>
</select>
<br>
</TD>
</TR>
<TR>
<TD style="font-family: Arial; font-size: 9.5px; font-style: normal;">
<p align=center>Or search by keyword<br>
[ISBN, title, course]<br><input type=text name="keyword_srch" value="" style="font-family: Arial; font-size: 9.5px; font-style: normal"><br>
<input type=button onClick="Go_Keyword();" name="cmdKeywordSrch" value="Find Book(s) >>>" style="font-size: 7.5pt; width: 90;"></p>
</TD>
</TR>
</TABLE>
</FORM>
</td>
<td valign=top colspan=2><!-- width was 690 -->
<p align=left>
<?
//check to see if book search has been submitted
if (isset($HTTP_POST_VARS["form_sent"]) || isset($HTTP_POST_VARS["course_books"])) {
//find book(s)
$link = mysql_connect('localhost', '*****', '******')
or die('Could not connect: ' . mysql_error());

mysql_select_db('*****') or die('Could not select database');

if (isset($HTTP_POST_VARS["course_books"])) {
if ($course_sent == 'NOPE') {
//grab all books in this department
$query1 = "SELECT course_id FROM course WHERE school_id = ".$HTTP_POST_VARS["select_school"]."";
$res1 = mysql_query($query1);
$test = "";

if (mysql_num_rows($res1)<1) {
$NoRecords = "There are no books for this Department";
}else{

While($Row = mysql_fetch_array($res1)) {
$test = $test.$Row[0].",";

}
$test = substr($test,0,strlen($test)-1);
//get all books for this department based on course IDs
$Query2 = "SELECT * FROM book WHERE course_id IN(".$test.")";

$Result2 = mysql_query($Query2);
}

}
elseif ($HTTP_POST_VARS["use_keyword"] == 'DOIT!') {
//get all books with this keyword
$Query0 = "SELECT course_id FROM COURSE WHERE course_name = '".$_POST["keyword_srch"]."'";
$Result0 = mysql_query($Query0);
$Ids = "";
$qry = "";
if (mysql_num_rows($Result0)>0) {
While($Row = mysql_fetch_array($Result0)) {
$Ids = $Ids.$Row[0].",";

}
$Ids = substr($Ids,0,strlen($Ids)-1);
$qry = " OR course_id IN(".$Ids.")";
}

$Query2 = "SELECT * FROM book WHERE book_title like('%".$_POST["keyword_srch"]."%') OR book_isbn = '".$_POST["keyword_srch"]."'".$qry;
//echo $Query2;
$Result2 = mysql_query($Query2);

if (mysql_num_rows($Result2)<1) {
$NoRecords = "There are no books matching your search criteria";
}

}
elseif ($HTTP_POST_VARS["prof_books"] == 'NOPE') {
//get all books belonging to this prof_id
//query professor table and get all course_ids for this prof
$query1 = "SELECT course_id FROM professor WHERE prof_id = ".$HTTP_POST_VARS["select_prof"]."";
$res1 = mysql_query($query1);
$test = "";

if (mysql_num_rows($res1)<1) {
$NoRecords = "There are no books for this Professor";
}else{

While($Row = mysql_fetch_array($res1)) {
$test = $test.$Row[0].",";

}
$test = substr($test,0,strlen($test)-1);
//get all books for this department based on course IDs
$Query2 = "SELECT * FROM book WHERE course_id IN(".$test.")";

$Result2 = mysql_query($Query2);
}

}
else{
//grab book(s) belonging to this course_id
$Query2 = "SELECT * FROM book WHERE course_id = ".$HTTP_POST_VARS["select_course"]."";
$Result2 = mysql_query($Query2);

if (mysql_num_rows($Result2)<1) {
$NoRecords = "There are no books currently available for this course";
}

}
}
else {
//grab all course ids for this particular department
$query1 = "SELECT course_id FROM course WHERE school_id = ".$HTTP_POST_VARS["select_school"]."";
$res1 = mysql_query($query1);
$test = "";

if (mysql_num_rows($res1)<1) {
$NoRecords = "No courses for this department are available";
}else{

While($Row = mysql_fetch_array($res1)) {
$test = $test.$Row[0].",";

/*if ($courseIDs = "") {
$courseIDs = $Row[0];
}
else {
$courseIDs = courseIDs+","+$Row[0];
}
*/

}
$test = substr($test,0,strlen($test)-1);
//get all books for this department based on course IDs
$Query2 = "SELECT * FROM book WHERE course_id IN(".$test.")";
$Result2 = mysql_query($Query2);
}
}

if (strlen($NoRecords) < 1) { //new

//$res = mysql_query($bk_query);
$ResTable = "<table cellpadding=0 cellspacing=0>";
$ResTable = $ResTable."<tr>";
$ResTable = $ResTable."<TD width=6 height=6><IMG height=6 src='../images/corner_tl_000000.gif' width=6></TD>";
$ResTable = $ResTable."<TD height=6 align=center><IMG height=6 src='../images/black.jpg' width=620></TD>";
$ResTable = $ResTable."<TD width=6 height=6><IMG height=6 src='../images/corner_tr_000000.gif' width=6></TD>";
$ResTable = $ResTable."</tr></table>";
$ResTable = $ResTable."<table width=632 bgcolor=#000000><tr bgcolor=#000000>";
$ResTable = $ResTable."<td><font face='verdana' size=1 color=#ffffff>Title</font></td>";
$ResTable = $ResTable."<td><font face='verdana' size=1 color=#ffffff>Edition</font></td>";
$ResTable = $ResTable."<td><font face='verdana' size=1 color=#ffffff>ISBN</font></td>";
$ResTable = $ResTable."<td><font face='verdana' size=1 color=#ffffff>Condition</font></td>";
$ResTable = $ResTable."<td><font face='verdana' size=1 color=#ffffff>Price</font></td>";
$ResTable = $ResTable."<td><font face='verdana' size=1 color=#ffffff>Seller</font></td>";
$ResTable = $ResTable."<td><font face='verdana' size=1 color=#ffffff>Date Avail.</font></td>";

$ResTable = $ResTable."</tr>";


//echo "<table border=1 style='border-style: dotted dotted dotted dotted; border-color: #000000; border-width: 1px 1px 1px 1px;'><tr><td><font face='verdana' size=1>Title</font></td><td><font face='verdana' size=1>Edition</font></td><td><font face='verdana' size=1>ISBN</font></td><td><font face='verdana' size=1>Condition</font></td><td><font face='verdana' size=1>Price</font></td><td><font face='verdana' size=1>Seller</font></td><td><font face='verdana' size=1>Date Avail.</font></td><td><font face='verdana' size=1>Notes</font></td></tr>";
echo $ResTable;
$i = 0;
While($Row = mysql_fetch_array($Result2)) {
if ($i%2 ==0) {
$rowcolor = "#ffffff";
}
else {
$rowcolor = "#F1EEE6"; //#66CCCC
}

$book = "<tr bgcolor=".$rowcolor."><td><font face='verdana' size=1>".$Row[1]."</font></td>";
$book = $book."<td style='border-style: solid solid solid solid; border-width: 1px 1px 1px 1px; border-color: ".$rowcolor."'><font face='verdana' size=1>".$Row[8]."</font></td>";
$book = $book."<td style='border-style: solid solid solid solid; border-width: 1px 1px 1px 1px; border-color: ".$rowcolor."'><font face='verdana' size=1>".$Row[2]."</font></td>";
$book = $book."<td><font face='verdana' size=1>".$Row[3]."</font></td>";
$book = $book."<td><font face='verdana' size=1>".$Row[4]."</font></td>";
$book = $book."<td><font face='verdana' size=1>".$Row[5]."</font></td>";
$book = $book."<td><font face='verdana' size=1>".$Row[7]."</font></td></tr>";
//$book = $book."<td><font face='verdana' size=1>".$Row[6]."</font></td></tr>";
echo $book;
$i=$i+1;
}
echo "</table>";
} //new
else {
echo $NoRecords;
}

mysql_close($link);
}
?>

</p>
</td>

</tr>

</table>
</TD>
</TR>

<!-- footer -->
<TR>
<TD>
<table width=95% align=center>
<tr>
<td height=6 valign="top" style="border-color: #000000; border-style: solid none none none; border-width: 1px 0 0 0;">
<table width=100%>
<tr><td width=150>&nbsp;</td>
<td align=center style="border-color: #000000; border-style: dotted dotted dotted dotted; border-width: 1px 1px 1px 1px;">
<font style="font-size: 7.5pt; font-color: #000000;">
Information provided on this site is reliable but not guaranteed
</font>
</td>
</tr>
</table>
</td>
</tr>
</table>
</TD>
</TR>
<TR>
<TD width=100%>
</TD>
</TR>

</TABLE>
<!-- rounded corners -->
<TABLE width=775 align=center cellspacing=0 cellpadding=0>
<TR>
<TD width=6 height=6><IMG height=6 src="../images/corner_bl_FFFFFF.gif" width=6></TD>
<TD height=6><IMG height=6 src="../images/white.jpg" width=763></TD>
<TD width=6 height=6><IMG height=6 src="../images/corner_br_FFFFFF.gif" width=6></TD>
</TR>
</TABLE>

</BODY>
</HTML>
 
Please don't post so much code at once. It is very hard to follow. Also please surround your code with the [ignore]
Code:
[/ignore]
tags to make your code much more readable.
 
understandable. before I do though, here is a quick question that may solve my problem. My code is basically writing a couple thousand lines of javascript, using the DOM to create <option> for my 3 <select> on the page. I'm thinking this is a little out of hand - but maybe not. I don't do alot of web development so I wanted to check and see if this is the wrong way to go. I'm thiknin it is - but it would be nice to get all the data avaialble and not have to resubmit the page each time the user selects a new value from the <select>. What are your thoughts?

thanks!!!
 
If you're going to submit data to a PHP script, why not just have that script send back a new page with the changed data on it?

Why go through all the DOM indirection in the first place?

Want the best answers? Ask the best questions!

TANSTAAFL!!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top