Hi,
redneck_kiwi wrote:[color=blue]
>
> Need some opinions on how best to display in excess of 14K records
> based on users search criteria.
>
> Application Concept:
> -----------------------
>
> [1] User enters various search criteria (as many as 13 different
> fields) AND can select 2 fields to sort on.
> [2] User can also limit the results displayed per page
> [3] Once the user presses "Submit", the SQL string is built and the DB
> is queried.
> [4] Results are displayed to the user, x records per page with "Next
> <<->> Previous" link under results. If user
> did not choose a limit, I limit to 20 records per page.
> [5] Clicking on the Next or Previous links walks the user through the
> data.
>
> Application Logic
> -----------------------
>
> [PHP]
>
> if($submit) {
> // Code to display results etc
> } else {
> // Code to gather search criteria
> }
>
> [/PHP]
>
> Sounds simple, right? Basically it is, however I am attempting to do
> something here I have never done before. In other
> applications I have developed, I have only used the Next - Previous
> link idea when I have simply pulled the entire database
> so that the user could browse the db. This time though I need to allow
> the user to set parameters before browsing.
>
> Here's my problem from a code perspective:
>
> [PHP]
> <?
> if($start OR $submit) {
> if(!isset($start)) $start = 0;
>
> // First step, let's connect to the DB and then build the SQL
> string
>
> $db = mysql_connect("host", "user", "password") or die("Could
> not connect to db: ". mysql_error());
> mysql_select_db("dbname",$db);
>
> // Our various search terms from from the initial form. Let's
> build the SQL string we are going to use
>
> // First, change the cost figures to something SQL will
> recognize.
>
> $val1=dollarval($dolval=$AYVAL);$val2=dollarval($d olval=$BYVAL);
> $val3=dollarval($dolval=$EYVAL);$val4=dollarval($d olval=$Y4VAL);
>
> // Set up to use wildcards by default if the user chooses
> nothing for the field
> if(!$fsc) $fsc='%';if(!$niin) $niin='%';if(!$mmac)
> $mmac='%';if(!$ims) $ims='%';if(!$smc) $smc='%';
> if(!$PMS_BUYER) $PMS_BUYER='%';if(!$PMS_SELLER)
> $PMS_SELLER='%';
>
> $sql="SELECT * FROM Repair WHERE ALC LIKE '%' AND BP LIKE
> '$bpc' AND FSC LIKE '$fsc' AND NIIN LIKE '$niin'
> AND SGM_MMAC LIKE '$mmac' AND IMS LIKE '$ims' AND SMC LIKE
> '$smc' AND PMS_BUYER LIKE '$PMS_BUYER' AND
> PMS_SELLER LIKE '$PMS_SELLER' AND AY_VAL_ACT_ROLLUP $val1 AND
> BY_VAL_ACT_ROLLUP $val2 AND EY_VAL_ACT_ROLLUP
> $val3 AND Y4_VAL_ACT_ROLLUP $val4 ORDER BY $orderby1,$orderby2
> LIMIT $start, 20";
> $result=mysql_query($sql,$db);
>
> // get the count of the number of rows of data
> $query = "SELECT count(*) as count FROM Repair";
> $res = mysql_query($query);
> $row = mysql_fetch_array($res);
> $numrows = $row['count'];
>
> $result = mysql_query($sql);
>
> echo $sql;
> echo "<CENTER>There are " . $numrows . " records</CENTER>";
>
> // Now, switch to HTML for easier coding
> ?>
> <TABLE BORDER=2 CELLSPACING=2 CELLPADDING=2 ALIGN=CENTER>
> <COL><COL><COL><COL><COL>
> <TBODY>
> <TR>
> <TD>ALC</TD>
> <TD>FSC</TD>
> <TD>NIIN</TD>
> <TD>MMAC</TD>
> <TD>Noun</TD>
> <TD>First Year Qty</TD>
> <TD>Options</TD>
> </TR>
> <?
> while($myrow=mysql_fetch_array($result)) {
> echo "<TR>";
> echo "<TD
> CLASS=data>".$myrow["ALC"]."</TD>";
> echo "<TD
> CLASS=data>".$myrow["FSC"]."</TD>";
> echo "<TD
> CLASS=data>".$myrow["NIIN"]."</TD>";
> echo "<TD
> CLASS=data>".$myrow["SGM_MMAC"]."</TD>";
> echo "<TD
> CLASS=data>".$myrow["NOUN"]."</TD>";
> echo "<TD
> CLASS=data>".$myrow["AY_QTY_ACT_ROLLUP"]."</TD>";
> echo "<TD CLASS=data><a TARGET=_BLANK
> href=viewrep.php?id=".$myrow["ID"].">View</a></TD>";
> echo "</TR>";
> }
> ?>
> </TBODY>
> </TABLE>
> <?
> if($start > 0)
> echo "<CENTER><A HREF=\"" . $PHP_SELF . "?start=" .
> ($start - 20) . "\">Previous</A></CENTER><BR>\n";
> if($numrows > ($start + 20))
> echo "<CENTER><A HREF=\"" . $PHP_SELF . "?start=" .
> ($start + 20) . "\">Next</A></CENTER><BR>\n";
>
> } else {
>
> ?>
> User input code here
> <TR><TD COLSPAN=5 ALIGN=center><INPUT TYPE="submit"
> NAME="submit" VALUE="Find Data"></TD></TR>
> </TBODY>
> </TABLE>
>
> [/PHP]
>
> My problem appears to be that my variables are not carrying over
> whenever Next or Previous is clicked. Anybody have a suggestion as to
> how I can get this to work?[/color]
Hi,
I didn't actually read through all your code, but you should probably validate,
then set the validated variables as session variables. Then you can just use
the session variables as-is on any subsequent page and use them to build your
query.
Shawn
--
Shawn Wilson
shawn@glassgiant.com http://www.glassgiant.com 
