Jump to content

nOOb help needed. Pagination but looks at over 3000 entires and then times out


bseven

Recommended Posts

Firstly, hello and thanks for taking the time to look at my question
Secondly don't you hate people who register just to ask for help?  Sorry about that.

Ok, my problem is that I'm really new to PHP, and while many moons ago I was a CF developer, I'm having some beginner problems with PHP.

When I'm Searching for cat1 = 'Comic' (it has 3,217 matching records) and I get a [quote]Fatal error: Maximum execution time of 60 seconds exceeded in F:\Webpage\_php\nav.php on line 36[/quote]

I know I can use 'limit' but I need to get a total number of records to do the pagination.  I'm using PHPmyAdmin 2.8.2.4 can do the following in 0.0560 sec.
[quote]
Showing rows 0 - 3216 (3,217 total, Query took 0.0560 sec)
[code]
SQL query:SELECT *
FROM `myob`
WHERE cat1 = 'comic'
LIMIT 0 , 4000
[/code]
[/quote]

Also I'm running MySql 5.0.22 and PHP 5.1.4 and Apache 5.1.4.  Maybe that info helps.

I'm sure that the answer prbably really easy, but I've searched these forums and many other sites, and can not find an aswer to help me.

Here is a cut of my code I got off the web and modified:
[code]
<?php
// FILENAME IS nav.php
//PURPOSE IS dispaying a category of results and breaking them up by pages
//STATUS IS in development.  Has errors at present (13 Sep 2006)

//WRITE OUT CURRENT PAGE NUMBER TEXT
echo "Current Page No:";

//GET LAST RECORD COUNT FROM URL OR SET IF NONE EXIST
$x=$_GET["x"];
if(!isset($x)) $x=0;

//GET FROM URL NUMBER OF RECORDS TO SHOW OR SET DEFAULT (24)
$y=$_GET["y"];
if(!isset($y)) $y=24;

//SET AND OUTPUT THE ACTUAL PAGE NUMBER
$pageno=$x/$y +1;
echo "<b>".$pageno."</b><br>";

//SET TABLE CELL LEFT OR RIGHT
if(!isset($SetRecNo)) $SetRecNo='Left';

//WRITE OUT THE TABLE HEADER
echo "<table border=1>";

//GET CAT1 FIELD FORM URL OR SET IF DOES NOT EXIST
$SCat1=$_GET["SCat1"];
if(!isset($SCat1)) $SCat1="comic"; //IF I USE ANOTHER ONE WITH LESS RECORDS I GET NO TIMEOUT ERROR

//CONNECT TO DATABASE
include 'inc/dbconnect.php';

//RUN QUERY TO GET TOTAL NUMBER OF RECORDS FOR PAGINATION
$tsql = "SELECT * FROM `myob`, `diamond` WHERE diamond.Custom2=myob.Custom2 AND myob.Cat1= '$SCat1' ORDER BY `DescriptionText`";
$tresult=mysql_query($tsql) or die(mysql_error());
$num=mysql_num_rows($tresult);

//RUN QUERY TO GET RECORDS TO DISPLAY
$sql = "SELECT * FROM `myob`, `diamond` WHERE diamond.Custom2=myob.Custom2 AND myob.Cat1= '$SCat1' ORDER BY `DescriptionText` limit $x,$y";

$result=mysql_query($sql) or die(mysql_error());
while($row=mysql_fetch_array($result))
{

//SET VARIABLES FROM DATABASE RESULTS
    $ThisRec = $row["Custom2"];
    $ThisDesc = $row["DescriptionText"];

//MAKE THE TABLE WITH THE RESULTS DISPLAYED
if ($SetRecNo=='Left'){
 
    echo "<tr><td><a href='product.php?ProdCode=$ThisRec'>$ThisDesc</a></td>";
$SetRecNo='Right';
} else {
  echo "<td><a href='product.php?ProdCode=$ThisRec'>$ThisDesc</a></td></tr>";
  $SetRecNo='Left';

}
}
if ($SetRecNo=='Right'){
echo "<td>&nbsp</td></tr>";
  }

//WRITE THE END OF TABLE
echo "</table>";

//SET VARIABLES FOR DISPLAYING PAGE NUMBERS AT BOTTOM OF RESULTS
$page=1;
$increment=0;
$showpage=$num/$y;

//DISPLAY PAGE NUMBERS
echo"Page:&nbsp;|&nbsp";
for($j=0;$j<$showpage;$j++)
{
if($page==$pageno) {
//IF CURRENT PAGE NUMBER DONT PUT A HYPERLINK
echo"$page&nbsp;|&nbsp;";
} else {
//IF ISNT CURRENT PAGE NUMBER PUT A HYPERLINK
echo"<a href='?x=$increment&y=$y&SCat1=$SCat1'>$page</a>&nbsp;|&nbsp;";
}

//DO THE ADDITIONS FOR NEXT IN LOOP
$page=$page+1;
$increment=$increment+$y;
}

//ALL OVER RED ROVER
?>
[/code]

Thanks so much just for looking at this for me.
Link to comment
Share on other sites

You can find the total number of entries with

[code]$result = mysql_query("SELECT count(*) FROM `myob`, `diamond` WHERE diamond.Custom2=myob.Custom2 AND myob.Cat1= '$SCat1'");
if (!$result) die("Mysql error: " . mysql_error());
if (mysql_num_rows($result) != 1) die("Didn't get 1 row from count query??");
$row = mysql_fetch_assoc($result);
$num = $row['count'];[/code]

Not 100% sure that it's $row['count'], but the count is definitely in that array.  $row[0] will definitely get it.

In Mysql using standard tables, count(*) is very fast.  Much faster than select * for sure.

Still it's odd that your script times out.  Maybe you can try running it from the command line (Hardcode the arguments for this) and see what output it displays.  Can you tell us what indexes you have on those tables, and the number of entries in each table?
Link to comment
Share on other sites

Maybe you should check that the query actually contains what you expect ... make a small change:

[code]//RUN QUERY TO GET RECORDS TO DISPLAY
$sql = "SELECT * FROM `myob`, `diamond` WHERE diamond.Custom2=myob.Custom2 AND myob.Cat1= '$SCat1' ORDER BY `DescriptionText` limit $x,$y";
echo "Query: ". $sql. "<br/>"; // display the query that bombs[/code]
Link to comment
Share on other sites

[u]btherl[/u],
Firstly no more timeouts.  It returns results - WOOHOO & Thanks!!!!!

It wasn't working, so i replaced your example of  [i]mysql_fetch_assoc[/i] so i replaced it with [i][b]mysql_fetch_array[/i][/b].
It now seems to work.

Thanks to all for your help.  Very appreciated.
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.