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:

Expand|Select|Wrap|Line Numbers

 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:

Expand|Select|Wrap|Line Numbers

 
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:

Expand|Select|Wrap|Line Numbers

 
  $fields = array(id => "מספר כרטיס",

                  pname => "שם פרטי:",

                  lname => "שם משפחה:",

                  addr => "כתובת:",

                  city => "עיר מגורים:",

                  phone => "מספר טלפון:",

                  cphone => "מספר פלאפון:",

                  jdate => "תאריך הצטרפות:");

the main of my code:

Expand|Select|Wrap|Line Numbers

 
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.

Expand|Select|Wrap|Line Numbers

 
// 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:

Expand|Select|Wrap|Line Numbers

 
        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 :)

Page don't load - using odbc and Access db

re: Page don't load - using odbc and Access db

re: Page don't load - using odbc and Access db

re: Page don't load - using odbc and Access db

re: Page don't load - using odbc and Access db

re: Page don't load - using odbc and Access db

Similar PHP bytes

/bytes/development

/bytes/it

/bytes/about