Re: [PHP] my paging task with PHP does not work. It uses cookie.
Hi Arvid, thank you very much for your remark. It is useful for me. === On Fri, 14 Sep 2007 12:01:40 +0300 Arvids Godjuks [EMAIL PROTECTED] wrote: Don't use SQL_CALC_FOUND ROWS on simple queries, when you have to run a simple query on one or two (with join) tables with a simple WHERE. Especialy if tables are big. I found out that single SELECT COUNT(id) FROM table is far more faster when query has simple WHERE conditions. I use SQL_CALC_FOUND_ROWS only with a rather complex queries - that SQL_CALC_FOUND_ROWS doesn't affect query execution at all (well, it affects ofcourse, but affect is so small, you can't see it). -- Patrik Hasibuan [EMAIL PROTECTED] Junior Programmer -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] my paging task with PHP does not work. It uses cookie.
Dear Brian, My problem is solved. thank you very much for your help. === On Wed, 12 Sep 2007 14:04:11 -0400 brian [EMAIL PROTECTED] wrote: Patrik Hasibuan wrote: Dear my friends... I am trying to display the content of a table. Each page must content only 5 records maximum. Each page has Previous and Next buttons (made from anchor). I dump the primary of the working table and keep it in a cookie. So than the paging task work with the index of cookie array. But the $curguruescomidiklan stays 0 each time I click Next button. Patrick, i can't say what the problem is, but i recommend using the PEAR Pager package for this if you have PEAR available. It takes care of a lot of the heavy lifting and is very configurable. http://pear.php.net/package/Pager This does not require setting any cookies. brian -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Patrik Hasibuan [EMAIL PROTECTED] Junior Programmer -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] my paging task with PHP does not work. It uses cookie.
Dear mike, your thread solved my problem. I really appreciate your help. Thank you very much. On Thu, 13 Sep 2007 14:49:29 -0700 mike [EMAIL PROTECTED] wrote: warning: this is VERY UGLY CODE. i wrote it 3-4 years ago now i think and it just keeps working. you call it this way: # pagination. if(isset($_GET['pg'])) { $page = intval($_GET['pg']); } else { $page = 1; } $page = sprintf(%02d,$page); $query = Array( 'query' = SELECT foo FROM bar, 'itemsperpage' = 50, 'maxdisplay'= 20, 'linkprefix'= $_SERVER[PHP_SELF]?f=$forum_idpg= ); list($pages,$numpages,$items,$threads) = paginate_sql($page,$query); $pages is the html formatted links $numpages is the total number of pages i dont actually use $items much but i think it just tells you how many items (which is the same as itemsperpage... i believe) $threads is the mysql rows resource (you can use mysql_fetch_rows on it) function paginate_sql($thispage,$options) { $offset = $options['itemsperpage'] * ($thispage - 1); if(isset($options['server'])) { $results = db_query(eregi_replace(^SELECT,SELECT SQL_CALC_FOUND_ROWS,$options['query']).LIMIT $offset,$options[itemsperpage],$options['server']); $rows = db_query(SELECT FOUND_ROWS(),$options['server']); } else { $results = db_query(eregi_replace(^SELECT,SELECT SQL_CALC_FOUND_ROWS,$options['query']).LIMIT $offset,$options[itemsperpage]); $rows = db_query(SELECT FOUND_ROWS()); } list($totalitems) = db_rows($rows); db_free($rows); $html = ; $numpages = ceil($totalitems / $options['itemsperpage']); if(isset($options['maxpages']) $numpages $options['maxpages']) { $numpages = $options['maxpages']; } if($thispage 1 || $totalitems $options['itemsperpage']) { $thispage = sprintf(%02d,1); } if($thispage $numpages $numpages 0) { header(sprintf(Location: %s01, str_replace(amp;,,$options['linkprefix']))); exit(); } $start = (ceil($thispage / $options['maxdisplay'])-1) * $options['maxdisplay'] + 1; if($start == 1) { $html .= laquo;; } else { $html .= sprintf(a href=\%s%02d\laquo;/a, $options['linkprefix'], $start-1); } if(isset($options['maxdisplay']) $options['maxdisplay'] != 0 $numpages $options['maxdisplay']) { $numlist = $options['maxdisplay'] + $start - 1; } else { $numlist = $numpages + $start - 1; } if($numlist $numpages) { $numlist = $numpages; } for($x=$start; $x=$numlist; $x++) { if($x == $thispage) { $html .= sprintf( %02d, $x); } else { $html .= sprintf( a href=\%s%02d\%02d/a, $options['linkprefix'], $x, $x); } } if($numlist == $numpages) { $html .= raquo;; } else { $html .= sprintf( a href=\%s%02d\raquo;/a, $options['linkprefix'], $numlist+1); } if($totalitems = $options['itemsperpage']) { $html = ; } return Array($html,sprintf(%02d,$numpages),$totalitems,$results); } On 9/13/07, Patrik Hasibuan [EMAIL PROTECTED] wrote: Hi Mike, I am intrested for the solution you gave me. But I am confused of the way in implementing select FOUND_ROWS(). I wrote another very simple codes as the first step for understanding this threat: ?php $konek=mysql_connect(127.0.0.1,root,mypassword); if ($konek){ for ($i=0;$i2;$i++){ $sqlku=select SQL_CALC_FOUND_ROWS id_iklan from lowongan limit 5;; $sqlkupage=select FOUND_ROWS(); $mybd=mysql_select_db(headhunter,$konek); $kueri=mysql_query($sqlku,$konek) or die('MYSQL QUERY ERROR ['.mysql_errno($konek).'] '.mysql_error($konek)); $kueri=mysql_query($sqlkupage,$konek) or die('MYSQL QUERY ERROR ['.mysql_errno($konek).'] '.mysql_error($konek)); while($brs=mysql_fetch_row($kueri)){ list($idiklan)=$brs; echo FirstItem a href=\showit.php?id=$idiklan\$idiklan/abr; } } }else{ echo I can't talk to the serverbr; exit(); } ? I've put nine records into the table of lowongan, idiklan 1 to 9. Look I get of course only one record, namely the: '9'. This is the output of my codes in my internet browser. === FirstItem 9 FirstItem 9 === The output what I am expecting suppose to be: on the $i=0 FirstItem 1 FirstItem 2 FirstItem 3 FirstItem 4 FirstItem 5 and on the $i=1 FirstItem 6 FirstItem 7 FirstItem 8 FirstItem 9 FirstItem 9 I read on the documentation of MySQL but I couldn't find any code of sample also in the
Re: [PHP] my paging task with PHP does not work. It uses cookie.
Don't use SQL_CALC_FOUND ROWS on simple queries, when you have to run a simple query on one or two (with join) tables with a simple WHERE. Especialy if tables are big. I found out that single SELECT COUNT(id) FROM table is far more faster when query has simple WHERE conditions. I use SQL_CALC_FOUND_ROWS only with a rather complex queries - that SQL_CALC_FOUND_ROWS doesn't affect query execution at all (well, it affects ofcourse, but affect is so small, you can't see it).
Re: [PHP] my paging task with PHP does not work. It uses cookie.
except i think innodb does not have a full row count stored. anyway the design was meant for a simple one function call. it's worked great for small and data loads. On 9/14/07, Arvids Godjuks [EMAIL PROTECTED] wrote: Don't use SQL_CALC_FOUND ROWS on simple queries, when you have to run a simple query on one or two (with join) tables with a simple WHERE. Especialy if tables are big. I found out that single SELECT COUNT(id) FROM table is far more faster when query has simple WHERE conditions. I use SQL_CALC_FOUND_ROWS only with a rather complex queries - that SQL_CALC_FOUND_ROWS doesn't affect query execution at all (well, it affects ofcourse, but affect is so small, you can't see it). -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] my paging task with PHP does not work. It uses cookie.
2007. 09. 14, péntek keltezéssel 10.50-kor mike ezt írta: except i think innodb does not have a full row count stored. of course not, but SQL_CALC_FOUND_ROWS does not use that. it is working like it selects the rows and counts them. and in the case of complex queries (where you use more than one table) you could not make use of that stored value anyway. greets Zoltán Németh anyway the design was meant for a simple one function call. it's worked great for small and data loads. On 9/14/07, Arvids Godjuks [EMAIL PROTECTED] wrote: Don't use SQL_CALC_FOUND ROWS on simple queries, when you have to run a simple query on one or two (with join) tables with a simple WHERE. Especialy if tables are big. I found out that single SELECT COUNT(id) FROM table is far more faster when query has simple WHERE conditions. I use SQL_CALC_FOUND_ROWS only with a rather complex queries - that SQL_CALC_FOUND_ROWS doesn't affect query execution at all (well, it affects ofcourse, but affect is so small, you can't see it). -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] my paging task with PHP does not work. It uses cookie.
Hi Mike, I am intrested for the solution you gave me. But I am confused of the way in implementing select FOUND_ROWS(). I wrote another very simple codes as the first step for understanding this threat: ?php $konek=mysql_connect(127.0.0.1,root,mypassword); if ($konek){ for ($i=0;$i2;$i++){ $sqlku=select SQL_CALC_FOUND_ROWS id_iklan from lowongan limit 5;; $sqlkupage=select FOUND_ROWS(); $mybd=mysql_select_db(headhunter,$konek); $kueri=mysql_query($sqlku,$konek) or die('MYSQL QUERY ERROR ['.mysql_errno($konek).'] '.mysql_error($konek)); $kueri=mysql_query($sqlkupage,$konek) or die('MYSQL QUERY ERROR ['.mysql_errno($konek).'] '.mysql_error($konek)); while($brs=mysql_fetch_row($kueri)){ list($idiklan)=$brs; echo FirstItem a href=\showit.php?id=$idiklan\$idiklan/abr; } } }else{ echo I can't talk to the serverbr; exit(); } ? I've put nine records into the table of lowongan, idiklan 1 to 9. Look I get of course only one record, namely the: '9'. This is the output of my codes in my internet browser. === FirstItem 9 FirstItem 9 === The output what I am expecting suppose to be: on the $i=0 FirstItem 1 FirstItem 2 FirstItem 3 FirstItem 4 FirstItem 5 and on the $i=1 FirstItem 6 FirstItem 7 FirstItem 8 FirstItem 9 FirstItem 9 I read on the documentation of MySQL but I couldn't find any code of sample also in the php.net. Would be so kind to give me a very simple code of sample? Thank you very much in advance. On Wed, 12 Sep 2007 11:58:04 -0700 mike [EMAIL PROTECTED] wrote: On 9/12/07, Patrik Hasibuan [EMAIL PROTECTED] wrote: Dear my friends... I am trying to display the content of a table. Each page must content only 5 records maximum. Each page has Previous and Next buttons (made from anchor). I dump the primary of the working table and keep it in a cookie. So than the paging task work with the index of cookie array. But the $curguruescomidiklan stays 0 each time I click Next button. yeah there is no reason to be using cookies for this. just using query string parameters should work. for pagination you need to know: total number of items number of items per page after that you know how many pages (numitems / itemsperpage) and work out the links appropriately (i.e. page1 has no previous, $maxpage has no next) and you can put the number of pages down how you want (if you want to do prev 3 4 5 next, or next 1 2 3 4 5 6 7 8 9 prev, there's a ton of strategies to print out what pages to show, and next, previous, jump to end, jump to beginning, etc) i've never used the PEAR class or anything else, i made a function a long time ago that actually works in conjunction with mysql's SELECT FOUND_ROWS() and uses a LIMIT offset,number to save the overhead of fetching every row from the database to only show the ones for that page. it's actually worked well for 4 years now without a single change... at some point perhaps i'll clean it up and post it somewhere, and someone might be able to make it even better. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Patrik Hasibuan [EMAIL PROTECTED] Junior Programmer -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] my paging task with PHP does not work. It uses cookie.
warning: this is VERY UGLY CODE. i wrote it 3-4 years ago now i think and it just keeps working. you call it this way: # pagination. if(isset($_GET['pg'])) { $page = intval($_GET['pg']); } else { $page = 1; } $page = sprintf(%02d,$page); $query = Array( 'query' = SELECT foo FROM bar, 'itemsperpage' = 50, 'maxdisplay'= 20, 'linkprefix'= $_SERVER[PHP_SELF]?f=$forum_idpg= ); list($pages,$numpages,$items,$threads) = paginate_sql($page,$query); $pages is the html formatted links $numpages is the total number of pages i dont actually use $items much but i think it just tells you how many items (which is the same as itemsperpage... i believe) $threads is the mysql rows resource (you can use mysql_fetch_rows on it) function paginate_sql($thispage,$options) { $offset = $options['itemsperpage'] * ($thispage - 1); if(isset($options['server'])) { $results = db_query(eregi_replace(^SELECT,SELECT SQL_CALC_FOUND_ROWS,$options['query']).LIMIT $offset,$options[itemsperpage],$options['server']); $rows = db_query(SELECT FOUND_ROWS(),$options['server']); } else { $results = db_query(eregi_replace(^SELECT,SELECT SQL_CALC_FOUND_ROWS,$options['query']).LIMIT $offset,$options[itemsperpage]); $rows = db_query(SELECT FOUND_ROWS()); } list($totalitems) = db_rows($rows); db_free($rows); $html = ; $numpages = ceil($totalitems / $options['itemsperpage']); if(isset($options['maxpages']) $numpages $options['maxpages']) { $numpages = $options['maxpages']; } if($thispage 1 || $totalitems $options['itemsperpage']) { $thispage = sprintf(%02d,1); } if($thispage $numpages $numpages 0) { header(sprintf(Location: %s01, str_replace(amp;,,$options['linkprefix']))); exit(); } $start = (ceil($thispage / $options['maxdisplay'])-1) * $options['maxdisplay'] + 1; if($start == 1) { $html .= laquo;; } else { $html .= sprintf(a href=\%s%02d\laquo;/a, $options['linkprefix'], $start-1); } if(isset($options['maxdisplay']) $options['maxdisplay'] != 0 $numpages $options['maxdisplay']) { $numlist = $options['maxdisplay'] + $start - 1; } else { $numlist = $numpages + $start - 1; } if($numlist $numpages) { $numlist = $numpages; } for($x=$start; $x=$numlist; $x++) { if($x == $thispage) { $html .= sprintf( %02d, $x); } else { $html .= sprintf( a href=\%s%02d\%02d/a, $options['linkprefix'], $x, $x); } } if($numlist == $numpages) { $html .= raquo;; } else { $html .= sprintf( a href=\%s%02d\raquo;/a, $options['linkprefix'], $numlist+1); } if($totalitems = $options['itemsperpage']) { $html = ; } return Array($html,sprintf(%02d,$numpages),$totalitems,$results); } On 9/13/07, Patrik Hasibuan [EMAIL PROTECTED] wrote: Hi Mike, I am intrested for the solution you gave me. But I am confused of the way in implementing select FOUND_ROWS(). I wrote another very simple codes as the first step for understanding this threat: ?php $konek=mysql_connect(127.0.0.1,root,mypassword); if ($konek){ for ($i=0;$i2;$i++){ $sqlku=select SQL_CALC_FOUND_ROWS id_iklan from lowongan limit 5;; $sqlkupage=select FOUND_ROWS(); $mybd=mysql_select_db(headhunter,$konek); $kueri=mysql_query($sqlku,$konek) or die('MYSQL QUERY ERROR ['.mysql_errno($konek).'] '.mysql_error($konek)); $kueri=mysql_query($sqlkupage,$konek) or die('MYSQL QUERY ERROR ['.mysql_errno($konek).'] '.mysql_error($konek)); while($brs=mysql_fetch_row($kueri)){ list($idiklan)=$brs; echo FirstItem a href=\showit.php?id=$idiklan\$idiklan/abr; } } }else{ echo I can't talk to the serverbr; exit(); } ? I've put nine records into the table of lowongan, idiklan 1 to 9. Look I get of course only one record, namely the: '9'. This is the output of my codes in my internet browser. === FirstItem 9 FirstItem 9 === The output what I am expecting suppose to be: on the $i=0 FirstItem 1 FirstItem 2 FirstItem 3 FirstItem 4 FirstItem 5 and on the $i=1 FirstItem 6 FirstItem 7 FirstItem 8 FirstItem 9 FirstItem 9 I read on the documentation of MySQL but I couldn't find any code of sample also in the php.net. Would be so kind to give me a very simple code of sample? Thank you very much in advance. On Wed, 12 Sep 2007 11:58:04 -0700 mike [EMAIL PROTECTED] wrote: On 9/12/07, Patrik Hasibuan [EMAIL PROTECTED] wrote: Dear my friends... I am trying to display the content of a table. Each page must
[PHP] my paging task with PHP does not work. It uses cookie.
Dear my friends... I am trying to display the content of a table. Each page must content only 5 records maximum. Each page has Previous and Next buttons (made from anchor). I dump the primary of the working table and keep it in a cookie. So than the paging task work with the index of cookie array. But the $curguruescomidiklan stays 0 each time I click Next button. The main part of doing the paging task is this lines: for ($it=$curguruescomidiklan;$it$itot;$it++){ $idad=$guruescomidiklan[$it]; echo idad= $idadbr; echo it: $itbr; bukaiklan($idad); } . Please tell me why PHP does not work as I expect. Thank you very much in advance. = html ?php session_start(); session_register(tiket); setcookie(curguruescomidiklan); setcookie(guruescomidiklan,); setcookie(jmli); $tiket=session_id(); $pageamount = 5; ? table align=center tr !--Bagian Pencari Kerja-- td valign=top background=gambar/gbkiri.gif table align=right border=0 cellpadding=0 cellspacing=0 width=300 tr td align=left table border=1 cellpadding=0 cellspacing=0 ?php if ($curguruescomidiklan0){ echo trtda href=\index.php?p=t\ style=\text-decoration:none;\Previous/a/td/tr; } ? /table /td td/td td align=right table border=1 cellpadding=0 cellspacing=0 trtd ?php if (($jmli % $curguruescomidiklan)1 or $curguruescomidiklan==0){ echo a href=\index.php?p=n\ style=\text-decoration:none;\Next/a/td/tr; } ? /td/tr /table /td /tr trtd ?php echo trth colspan=12Lowongan Kerja/th/tr; include_once koneksi.php; $sqlnya= select l.id_iklan from perusahaan as p right join lowongan as l on l.id_account=p.id_account where l.lama_tayang = (current_date-l.mulai_tayang) order by bid desc ; $kelas=new koneksi(); $klas=$kelas-getkoneksi(headhunter,$sqlnya); $jmli=mysql_num_rows($klas); if ( $jmli 0 ) { $i=0; while(list($idiklan)=mysql_fetch_row($klas)) { // echo ID Iklannya: $idiklanbr; setcookie(guruescomidiklan[$i],$idiklan); $i++; } } else { echo No Ad of job vacancy currently in our databasebr; } if (empty($curguruescomidiklan)){ $curguruescomidiklan=0; } //echo pa: $pageamountbr; if (isset($p)){ $nt=$_GET['p']; } //I meant this part should direct the page content to be the next array contents if ($jmli=$pageamount){ echo curguruescomidiklan --$curguruescomidiklanbr; if ($nt=='t'){ $itot=$_COOKIE['curguruescomidiklan']-$pageamount; }else{ $itot=$_COOKIE['curguruescomidiklan']+$pageamount; echo lewat sini $curguruescomidiklan $pageamountbr; } echo itot 1 --$itotbr; }else{ $itot=$jmltot; echo itot 2 --$itotbr; } for ($it=$curguruescomidiklan;$it$itot;$it++){ $idad=$guruescomidiklan[$it]; echo idad= $idadbr; echo it: $itbr; bukaiklan($idad); } $itot=$itot+$pageamount; setcookie(curguruescomidiklan,$itot); echo curguruescomidiklan-- $curguruescomidiklanbr; function bukaiklan($myid){ //echo myid=$myidbr; $sqlnya= select p.company_fullname, p.company_address, p.city, p.country, p.postcode, p.phone, p.fax, p.url, p.company_description, l.iklan, l.id_iklan from perusahaan as p right join lowongan as l on l.id_account=p.id_account where l.lama_tayang = (current_date-l.mulai_tayang) and l.id_iklan='$myid' order by bid desc ; $kelassqlbukaiklan=new koneksi(); $klassqlbukaiklan=$kelassqlbukaiklan-getkoneksi(headhunter,$sqlnya); if ( mysql_num_rows($klassqlbukaiklan) 0 ) { while(list($namaprsh, $alamat, $kota, $negara, $kodepos, $telfon, $faks, $url, $comdesc, $iklan, $idiklan)=mysql_fetch_row($klassqlbukaiklan)) { echo trtd valign=\top\Employer/tdtd valign=\top\:/tdtd valign=\top\ colspan=10$namaprsh/td/tr; echo trtd valign=\top\Address/tdtd valign=\top\:/tdtd valign=\top\ colspan=10$alamat/td/tr; echo trtd valign=\top\City/tdtd valign=\top\:/tdtd valign=\top\ colspan=10$kota/td/tr; echo trtd valign=\top\Country/tdtd valign=\top\:/tdtd valign=\top\ colspan=10$negara/td/tr; echo trtd valign=\top\Postcode/tdtd valign=\top\:/tdtd colspan=10 valign=\top\$kodepos/td/tr; echo trtd valign=\top\Fax/tdtd valign=\top\:/tdtd valign=\top\ colspan=10$faks/td/tr; echo trtd valign=\top\Website/tdtd valign=\top\:/tdtd colspan=10 valign=\top\$url/td/tr; echo trtd valign=\top\Business Field/tdtd valign=\bottom\:/tdtd valign=\bottom\ colspan=10$comdesc/td/tr; echo trtd colspan=12$iklan/td/tr; echo trtd colspan=12 height=16/td/tr; } } else { echo No Ad of job vacancy currently in our database, possibly ads-id wrong.br; } } ? /td/tr/table /td !--Bagian Toko Buku Guru-- td valign=top /td !--Bagian Employer-- td valign=top /td /tr /table /body /html -- Patrik
Re: [PHP] my paging task with PHP does not work. It uses cookie.
Patrik Hasibuan wrote: Dear my friends... I am trying to display the content of a table. Each page must content only 5 records maximum. Each page has Previous and Next buttons (made from anchor). I dump the primary of the working table and keep it in a cookie. So than the paging task work with the index of cookie array. But the $curguruescomidiklan stays 0 each time I click Next button. Patrick, i can't say what the problem is, but i recommend using the PEAR Pager package for this if you have PEAR available. It takes care of a lot of the heavy lifting and is very configurable. http://pear.php.net/package/Pager This does not require setting any cookies. brian -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] my paging task with PHP does not work. It uses cookie.
On 9/12/07, Patrik Hasibuan [EMAIL PROTECTED] wrote: Dear my friends... I am trying to display the content of a table. Each page must content only 5 records maximum. Each page has Previous and Next buttons (made from anchor). I dump the primary of the working table and keep it in a cookie. So than the paging task work with the index of cookie array. But the $curguruescomidiklan stays 0 each time I click Next button. yeah there is no reason to be using cookies for this. just using query string parameters should work. for pagination you need to know: total number of items number of items per page after that you know how many pages (numitems / itemsperpage) and work out the links appropriately (i.e. page1 has no previous, $maxpage has no next) and you can put the number of pages down how you want (if you want to do prev 3 4 5 next, or next 1 2 3 4 5 6 7 8 9 prev, there's a ton of strategies to print out what pages to show, and next, previous, jump to end, jump to beginning, etc) i've never used the PEAR class or anything else, i made a function a long time ago that actually works in conjunction with mysql's SELECT FOUND_ROWS() and uses a LIMIT offset,number to save the overhead of fetching every row from the database to only show the ones for that page. it's actually worked well for 4 years now without a single change... at some point perhaps i'll clean it up and post it somewhere, and someone might be able to make it even better. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php