Page don't load - using odbc and Access db

[yarivot26]

I'm using ODBC to read a office access db

the db have almost 10,000 rows so I want to split it into pages

The first page load nice and quick, but in other pages i get:

Maximum execution time of 30 seconds exceeded in c:\apache\htdocs\eyal\new.php on line 100

the line 100 is the odbc_exec command.

in the top of the page i get:

Warning: Use of undefined constant id - assumed 'id' in c:\apache\htdocs\eyal\new.php on line 7
Warning: Use of undefined constant pname - assumed 'pname' in c:\apache\htdocs\eyal\new.php on line 8
Warning: Use of undefined constant lname - assumed 'lname' in c:\apache\htdocs\eyal\new.php on line 9
Warning: Use of undefined constant addr - assumed 'addr' in c:\apache\htdocs\eyal\new.php on line 10
Warning: Use of undefined constant city - assumed 'city' in c:\apache\htdocs\eyal\new.php on line 11
Warning: Use of undefined constant phone - assumed 'phone' in c:\apache\htdocs\eyal\new.php on line 12
Warning: Use of undefined constant cphone - assumed 'cphone' in c:\apache\htdocs\eyal\new.php on line 13
Warning: Use of undefined constant jdate - assumed 'jdate' in c:\apache\htdocs\eyal\new.php on line 14

the lines:

  $fields = array(id => "מספר כרטיס",
                  pname => "שם פרטי:",
                  lname => "שם משפחה:",
                  addr => "כתובת:",
                  city => "עיר מגורים:",
                  phone => "מספר טלפון:",
                  cphone => "מספר פלאפון:",
                  jdate => "תאריך הצטרפות:");
 

the main of my code:

switch ($do) {
    case "list":
        if ($page > 0) {
            $npage = $page + 1;
            $ppage = $page - 1;
        }
        else {
            $page = 0;
            $npage = 1;
        }
        $gpage = $page * 50;
        print("<table dir=\"rtl\" cellspacing=0 cellpadding=5>
               <tr dir=\"rtl\">");
        foreach ($fields as $key => $value) {
            $value = str_replace(":", " ", $value); 
            print("<th class=main>$value</th>");
        }
        print("<th class=endmain>ניהול</th>
               </tr>");
        if ($gpage == 0) {
            $sql_query = "SELECT TOP 50 * FROM tbl_clients";
        }
        else {
            $sql_query = "SELECT TOP 50 * FROM tbl_clients where ( ID NOT IN ( SELECT TOP $gpage id FROM tbl_clients ORDER BY id ASC ) ) ORDER BY id ASC";
        }
        $do_query = odbc_exec($conn, $sql_query);                               
        while (odbc_fetch_row($do_query)) {
            print("<tr>");
            foreach ($fields as $key => $value) {
                $text = odbc_result($do_query,$key);
                if ($key == "id") { $text = round($text); }
                if($key == "jdate") { $text = ereg_replace(" 00:00:00" , "", $text); }
                print("<td class=main>&nbsp;$text</td>");
            }
            print("<td class=endmain>&nbsp;" . dolink("$_SERVER[PHP_SELF]?do=edit", "עריכה"));
            print("</tr>");  
        }
        print("<tr>");
        if (empty($page) OR !isset($page)) {
            $form = $_SERVER['QUERY_STRING'];
            if ($page == 0) {
                $form = ereg_replace("page=0", "page=$npage", $form);
 
            }
            else {
                $form .= "&page=1";
            }
            echo $form;
            print("<td>");
            pp($form, "next");
            print("</td>");
        }
        else {
            if ($page > 0) {
                $form = $_SERVER[QUERY_STRING];
                $form = ereg_replace("page=$page", "page=$ppage", $form);
                print("<td>");
                pp($form, "prev");
                print("</td>");
            }
            print("<td>&nbsp;</td>");
            if (($page > 0) AND ($page < $count)) {
                $form = $_SERVER[QUERY_STRING];
                $form = ereg_replace("page=$page", "page=$npage", $form);
                print("<td>");
                pp($form, "next");
                print("</td>");
            }
        }
        print("</tr>");
        doexit();                   
}
 

Help

Thanks :)

[Markus]

You could set the time limit of a page so that your script doesn't time out.

[yarivot26]

That helps, thanks.

But why is it taking so long (about 3 min) for the page to load?

I don't think it's my comp... duel core with 2gb ram...

maybe something in the code?

[Atli]

Hi.

First, your array keys are defined incorrectly. Assuming they are meant to be strings, they should be quoted.

// This is incorrect. It will try to load the value for the
// constant named id, but if it fails to find it, it will
// assume it was meant to be a string and show a warning.
$arr = array(id => 'some value');
 
// This is how it should be done
$arr = array('id' => 'some value'); //Correct
 

As to the execution time, I would assume it is database related. The PHP code itself should not take anywhere near that long to execute.
The SQL query, however, could be taking some time on a large table. Especially if that table was not designed for such a query on a large amount of data.

How to fix such a problem I do not know. I don't know enough about Access to really know that. Maybe it has something to do with the sub-query? I know they can cause performance issues on other SQL servers.

[yarivot26]

Thanks, it solved my array error.

Do you know another way for me to use instead of sub-query?

I searched google alot to find this solution for paging in mssql.

[yarivot26]

Okay do I decided to do the page system the "long" way: selecting all the db and just printing the "right" page.

The problem now is that It's skipping rows (id 1, 3, 5, 7 and so on)

Here's the code:

        if ($page > 0) {
            $npage = $page + 1;
            $ppage = $page - 1;
        }
        else {      
            $page = 0;
            $npage = 1;
        } 
        $gpage = $page * 50;
        print("<table dir=\"rtl\" cellspacing=0 cellpadding=5>
               <tr dir=\"rtl\">");
        foreach ($fields as $key => $value) {
            $value = str_replace(":", " ", $value); 
            print("<th class=main>$value</th>");
        }
        print("<th class=endmain>ניהול</th>
               </tr>");
        $sql_query = "SELECT * FROM tbl_clients ORDER BY id ASC";
        $do_query = odbc_exec($conn, $sql_query);
        $target = $gpage + 50;
        $row_count = 0;
        while (odbc_fetch_row($do_query)) {
            if ($row_count < $gpage) {
                $row_count++;
                continue;
            }
            else {
                if ($gpage < $target) {
                    print("<tr>");
                    foreach ($fields as $key => $value) {
                        $text = odbc_result($do_query,$key);
                        if ($key == "id") { $text = round($text); }
                        if($key == "jdate") { $text = ereg_replace(" 00:00:00" , "", $text); }
                        print("<td class=main>&nbsp;$text</td>");
                    }
                    print("<td class=endmain>&nbsp;" . dolink("$_SERVER[PHP_SELF]?do=edit", "עריכה"));
                    print("</tr>");
                    $gpage++;
                }
                else if ($gpage > $target) {
                    break;
                }
            }
        }
 

Why?

Thanks :)