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

[bseven]

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.

[Hepp]

Try getting rid of your LIMIT tag in the SQL Query, or change it to something like this:

[code]
SQL query:SELECT *
FROM `myob`
WHERE cat1 = 'comic'
LIMIT 5000
[/code]

This is an attempted abortion guess. I'm slightly new to PHP as well.

-Hepp

[bseven]

Thanks Hepp, but the code you are trying to change is the PHPmyAdmin search string, not my actual code.  I wouldn't want to limit the results to 5000 for the record count though, as it will grow bigger over time.
Thanks anyway.

[btherl]

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?

[AndyB]

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]

[bseven]

[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.