11-18-2009, 06:44 PM
|
#1
|
|
|
TableFromMySQL Class
Ok, here it is. It's very messy though. 
It needs a lot of clean up and improvements. I also need to add validation and exceptions. I will keep on working on it tomorrow.
If you have any suggestions to simplify the code or find a way to improve the class and methods please let me know.
After I come up with version 0.2 I will post it here.
PHP Code:
class TableFromMySQL{
protected $_tablename;
protected $_select_columns;
protected $_where;
protected $_itemsPerPage;
protected $_pageNumber;
protected $_query;
protected $_table;
public function __construct($tablename, $select_columns, $where, $itemsPerPage, $pageNumber){
$this->_tablename = $tablename;
$this->_select_columns = $select_columns;
$this->_where = $where;
$this->_itemsPerPage = $itemsPerPage;
$this->_pageNumber = $pageNumber;
$this->_query = 'SELECT '.$this->_select_columns.' FROM '.$this->_tablename.' WHERE '.$this->_where;
$this->setLimit($this->_itemsPerPage);
}
/////////////////////////////////////
// //
// add the LIMIT part of the query //
// //
/////////////////////////////////////
private function setLimit($itemsPerPage){
$limit = $itemsPerPage;
if(isset($this->_pageNumber)){
$pageNumber = $this->_pageNumber;
$start = ($pageNumber-1)*$limit;
}
else{
$start = 0;
}
$this->_query = $this->_query.' LIMIT '.$start.', '.$itemsPerPage;
}
////////////////////////////////////////////////////////////
//
// Get total number of records
//
////////////////////////////////////////////////////////////
public function countRecords(){
$query_count = 'SELECT COUNT(*) FROM '.$this->_tablename.' WHERE '.$this->_where;
$result_count = mysql_query($query_count);
$fetch_row = mysql_fetch_row($result_count);
$num_rows = $fetch_row[0];
return $num_rows;
}
////////////////////////////////////////////////////////////
//
// pagination
//
////////////////////////////////////////////////////////////
public function pagination($limit, $add_to_url){
$add_to_url = $_SERVER['PHP_SELF'].'?';
$num_rows = $this->countRecords();
$adjacents = 3;
if(isset($this->_pageNumber)){
$pageNumber = $this->_pageNumber;
$start = ($pageNumber-1)*$limit;
}
else{
$start = 0;
}
if($pageNumber==0) $pageNumber=1;
$prev = $pageNumber-1;
$next = $pageNumber+1;
// $lastpage is = total pages / items per page, rounded up
$lastpage = ceil($num_rows/$limit);
// $last page minus 1
$lpm1 = $lastpage - 1;
$pagination='';
if($lastpage>1){
$pagination.='<div class="pagination">';
// previous
if($pageNumber>1) {$pagination.='<a href="'.$add_to_url.'pagenumber='.$prev.'"> previous </a>';}
else {$pagination .= '<span class="disabled"> previous </span>';}
// pages
if($lastpage<7+($adjacents*2)){
for($counter=1; $counter<=$lastpage; $counter++){
if($counter==$pageNumber) {$pagination .= '<span class"current">'.$counter.'</span>';}
else {$pagination .= '<a href="'.$add_to_url.'pagenumber='.$counter.'">'.$counter.'</a>';}
}
}
elseif($lastpage > 5 + ($adjacents * 2)){
if($pageNumber < 1 + ($adjacents * 2)){
for($counter = 1; $counter < 4 + ($adjacents * 2); $counter++){
if($counter == $pageNumber){
$pagination .= '<span class="pagination_current">'.$counter.'</span>';
}
else{
$pagination .= '<a href="'.$add_to_url.'pagenumber='.$counter.'">'.$counter.'</a>';
}
}
$pagination .= "...";
$pagination .= '<a href="'.$add_to_url.'pagenumber='.$lpm1.'">'.$lpm1.'</a>';
$pagination .= '<a href="'.$add_to_url.'pagenumber='.$lastpage.'">'.$lastpage.'</a>';
}
elseif($lastpage - ($adjacents * 2) > $pageNumber && $pageNumber > ($adjacents * 2)){
$pagination .= '<a href="'.$add_to_url.'pagenumber=1'.'">1</a>';
$pagination .= '<a href="'.$add_to_url.'pagenumber=2'.'">2</a>';
$pagination .= '...';
for($counter = $pageNumber - $adjacents; $counter <= $pageNumber + $adjacents; $counter++){
if($counter == $pageNumber){
$pagination .= '<span class="pagination_current">'.$counter.'</span>';
}
else{
$pagination .= '<a href="'.$add_to_url.'pagenumber='.$counter.'">'.$counter.'</a>';
}
}
$pagination .= '...';
$pagination .= '<a href="'.$add_to_url.'pagenumber='.$lpm1.'">'.$lpm1.'</a>';
$pagination .= '<a href="'.$add_to_url.'pagenumber='.$lastpage.'">'.$lastpage.'</a>';
}
else{
$pagination .= '<a href="'.$add_to_url.'pagenumber=1">1</a>';
$pagination .= '<a href="'.$add_to_url.'pagenumber=2">2</a>';
$pagination .= '...';
for($counter = $lastpage - (2 + ($adjacents * 2)); $counter <= $lastpage; $counter++){
if($counter == $pageNumber){
$pagination .= '<span class="pagination_current">'.$counter.'</span>';
}
else{
$pagination .= '<a href="'.$add_to_url.'pagenumber='.$counter.'">'.$counter.'</a>';
}
}
}
}
# next button
if($pageNumber < $counter - 1){
$pagination .= '<a href="'.$add_to_url.'pagenumber='.$next.'"> next </a>';
}
else{
$pagination .= '<span class="pagination_disabled"> next </span>';
}
$pagination .= '</div><br />';
}
$query = mysql_query($this->_query);
$result_count = mysql_query($this->_query);
$fetch_row = mysql_fetch_row($result_count);
if($fetch_row[0]==0) {
$pagination .= '<p>There is no items to display</p>';
}
while($row=mysql_fetch_array($query)){
// set columns to display
// echo $row['id'].'-'.$row['firstname'].'<br />';
}
return $pagination;
}
public function recommendedStylesForPagination(){
return '
<style>
div.pagination {
padding: 3px;
margin: 3px;
}
div.pagination a {
padding: 2px 5px 2px 5px;
margin: 2px;
border: 1px solid #AAAADD;
text-decoration: none; /* no underline */
color: #000099;
}
div.pagination a:hover, div.pagination a:active {
border: 1px solid #000099;
color: #000;
}
div.pagination span.current {
padding: 2px 5px 2px 5px;
margin: 2px;
border: 1px solid #000099;
font-weight: bold;
background-color: #000099;
color: #FFF;
}
div.pagination span.disabled {
padding: 2px 5px 2px 5px;
margin: 2px;
border: 1px solid #EEE;
color: #DDD;
}</style>';
}
public function tableFromMySQL($display_columns){
$table = '<table>';
$result = mysql_query($this->_query);
$table .= '<tr>';
foreach($display_columns as $key => $value){
$table .= '<th>'.$key.'</th>';
}
$table .= '</tr>';
while($row = mysql_fetch_array($result)){
$table .= '<tr>';
foreach($display_columns as $value){
$table .= '<td>'.$row[$value].'</td>';
}
$table .= '</tr>';
}
$table .= '</table>';
$table .= $this->pagination($this->_itemsPerPage, '');
return $table;
}
///////////////////
// //
// execute query //
// //
///////////////////
public function query(){
$result = mysql_query($this->_query);
// while($row = mysql_fetch_array($result)){
// echo '<pre>';
// print_r($row);
// echo '</pre>';
// }
}
}
if(isset($_GET['pagenumber']) && is_numeric($_GET['pagenumber'])){$pageNumber=$_GET['pagenumber'];}else{$pageNumber=1;}
$display_columns = array(
'clientid'=>'id',
'ID'=>'cardid',
'Name'=>'firstname',
'Last'=>'lastname',
'Email'=>'email',
'Mobile'=>'mobilephone',
'Home'=>'homephone',
'Work'=>'workphone'
);
$select_columns = ' clients.id, clients.cardid, clients.firstname, clients.lastname,
clientsContact.clientID, clientsContact.address, clientsContact.city, clientsContact.state, clientsContact.email,
clientsContact.homephone, clientsContact.workphone, clientsContact.mobilephone ';
$tablename = ' clients, clientsContact ';
$where = 'clients.id=clientsContact.clientID AND userid='.$_SESSION['id'];
$itemsPerPage = 3;
$table = new TableFromMySQL($tablename, $select_columns, $where, $itemsPerPage, $pageNumber);
$table->query();
echo $table->recommendedStylesForPagination();
echo $table->tableFromMySQL($display_columns);
?>
Last edited by imagined; 11-18-2009 at 06:51 PM..
|
|
|
|
11-19-2009, 12:06 PM
|
#2
|
|
|
TableFromMySQL v0.2
I removed some unnecessary code.
I'm open to suggestions: code simplification or separating logic from look.
PHP Code:
class TableFromMySQL{
protected $_tablename;
protected $_select_columns;
protected $_where;
protected $_itemsPerPage;
protected $_pageNumber;
protected $_query;
protected $_table;
public function __construct($tablename, $select_columns, $where, $itemsPerPage, $pageNumber){
$this->_tablename = $tablename;
$this->_select_columns = $select_columns;
$this->_where = $where;
$this->_itemsPerPage = $itemsPerPage;
$this->_pageNumber = $pageNumber;
$this->_query = 'SELECT '.$this->_select_columns.' FROM '.$this->_tablename.' WHERE '.$this->_where;
$this->setLimit($this->_itemsPerPage);
}
/////////////////////////////////////
// //
// add the LIMIT part of the query //
// //
/////////////////////////////////////
private function setLimit($itemsPerPage){
$limit = $itemsPerPage;
if(isset($this->_pageNumber)){
$pageNumber = $this->_pageNumber;
$start = ($pageNumber-1)*$limit;
}
else{
$start = 0;
}
$this->_query = $this->_query.' LIMIT '.$start.', '.$itemsPerPage;
}
////////////////////////////////////////////////////////////
//
// Get total number of records
//
////////////////////////////////////////////////////////////
public function countRecords(){
$query_count = 'SELECT COUNT(*) FROM '.$this->_tablename.' WHERE '.$this->_where;
$result_count = mysql_query($query_count);
$fetch_row = mysql_fetch_row($result_count);
$num_rows = $fetch_row[0];
return $num_rows;
}
////////////////////////////////////////////////////////////
//
// pagination
//
////////////////////////////////////////////////////////////
public function pagination($limit, $add_to_url){
$add_to_url = $_SERVER['PHP_SELF'].'?';
$num_rows = $this->countRecords();
$adjacents = 3;
if(isset($this->_pageNumber)){
$pageNumber = $this->_pageNumber;
$start = ($pageNumber-1)*$limit;
}
else{
$start = 0;
}
if($pageNumber==0) $pageNumber=1;
$prev = $pageNumber-1;
$next = $pageNumber+1;
// $lastpage is = total pages / items per page, rounded up
$lastpage = ceil($num_rows/$limit);
// $last page minus 1
$lpm1 = $lastpage - 1;
$pagination='';
if($lastpage>1){
$pagination.='<div class="pagination">';
// previous
if($pageNumber>1) {$pagination.='<a href="'.$add_to_url.'pagenumber='.$prev.'"> previous </a>';}
else {$pagination .= '<span class="disabled"> previous </span>';}
// pages
if($lastpage<7+($adjacents*2)){
for($counter=1; $counter<=$lastpage; $counter++){
if($counter==$pageNumber) {$pagination .= '<span class"current">'.$counter.'</span>';}
else {$pagination .= '<a href="'.$add_to_url.'pagenumber='.$counter.'">'.$counter.'</a>';}
}
}
elseif($lastpage > 5 + ($adjacents * 2)){
if($pageNumber < 1 + ($adjacents * 2)){
for($counter = 1; $counter < 4 + ($adjacents * 2); $counter++){
if($counter == $pageNumber){
$pagination .= '<span class="pagination_current">'.$counter.'</span>';
}
else{
$pagination .= '<a href="'.$add_to_url.'pagenumber='.$counter.'">'.$counter.'</a>';
}
}
$pagination .= "...";
$pagination .= '<a href="'.$add_to_url.'pagenumber='.$lpm1.'">'.$lpm1.'</a>';
$pagination .= '<a href="'.$add_to_url.'pagenumber='.$lastpage.'">'.$lastpage.'</a>';
}
elseif($lastpage - ($adjacents * 2) > $pageNumber && $pageNumber > ($adjacents * 2)){
$pagination .= '<a href="'.$add_to_url.'pagenumber=1'.'">1</a>';
$pagination .= '<a href="'.$add_to_url.'pagenumber=2'.'">2</a>';
$pagination .= '...';
for($counter = $pageNumber - $adjacents; $counter <= $pageNumber + $adjacents; $counter++){
if($counter == $pageNumber){
$pagination .= '<span class="pagination_current">'.$counter.'</span>';
}
else{
$pagination .= '<a href="'.$add_to_url.'pagenumber='.$counter.'">'.$counter.'</a>';
}
}
$pagination .= '...';
$pagination .= '<a href="'.$add_to_url.'pagenumber='.$lpm1.'">'.$lpm1.'</a>';
$pagination .= '<a href="'.$add_to_url.'pagenumber='.$lastpage.'">'.$lastpage.'</a>';
}
else{
$pagination .= '<a href="'.$add_to_url.'pagenumber=1">1</a>';
$pagination .= '<a href="'.$add_to_url.'pagenumber=2">2</a>';
$pagination .= '...';
for($counter = $lastpage - (2 + ($adjacents * 2)); $counter <= $lastpage; $counter++){
if($counter == $pageNumber){
$pagination .= '<span class="pagination_current">'.$counter.'</span>';
}
else{
$pagination .= '<a href="'.$add_to_url.'pagenumber='.$counter.'">'.$counter.'</a>';
}
}
}
}
# next button
if($pageNumber < $counter - 1){
$pagination .= '<a href="'.$add_to_url.'pagenumber='.$next.'"> next </a>';
}
else{
$pagination .= '<span class="pagination_disabled"> next </span>';
}
$pagination .= '</div><br />';
}
return $pagination;
}
public function recommendedStylesForPagination(){
return '
<style>
div.pagination {
padding: 3px;
margin: 3px;
}
div.pagination a {
padding: 2px 5px 2px 5px;
margin: 2px;
border: 1px solid #AAAADD;
text-decoration: none; /* no underline */
color: #000099;
}
div.pagination a:hover, div.pagination a:active {
border: 1px solid #000099;
color: #000;
}
div.pagination span.current {
padding: 2px 5px 2px 5px;
margin: 2px;
border: 1px solid #000099;
font-weight: bold;
background-color: #000099;
color: #FFF;
}
div.pagination span.disabled {
padding: 2px 5px 2px 5px;
margin: 2px;
border: 1px solid #EEE;
color: #DDD;
}</style>';
}
public function tableFromMySQL($display_columns){
$table = '<table>';
$result = mysql_query($this->_query);
$table .= '<tr>';
foreach($display_columns as $key => $value){
$table .= '<th>'.$key.'</th>';
}
$table .= '</tr>';
while($row = mysql_fetch_array($result)){
$table .= '<tr>';
foreach($display_columns as $value){
$table .= '<td>'.$row[$value].'</td>';
}
$table .= '</tr>';
}
$table .= '</table>';
$table .= $this->pagination($this->_itemsPerPage, '');
return $table;
}
}
if(isset($_GET['pagenumber']) && is_numeric($_GET['pagenumber'])){$pageNumber=$_GET['pagenumber'];}else{$pageNumber=1;}
$display_columns = array(
'clientid'=>'id',
'ID'=>'cardid',
'Name'=>'firstname',
'Last'=>'lastname',
'Email'=>'email',
'Mobile'=>'mobilephone',
'Home'=>'homephone',
'Work'=>'workphone'
);
$select_columns = ' clients.id, clients.cardid, clients.firstname, clients.lastname,
clientsContact.clientID, clientsContact.address, clientsContact.city, clientsContact.state, clientsContact.email,
clientsContact.homephone, clientsContact.workphone, clientsContact.mobilephone ';
$tablename = ' clients, clientsContact ';
$where = 'clients.id=clientsContact.clientID AND userid='.$_SESSION['id'];
$itemsPerPage = 3;
$table = new TableFromMySQL($tablename, $select_columns, $where, $itemsPerPage, $pageNumber);
echo '<p>There is '.$table->countRecords().' record(s)</p>';
echo $table->recommendedStylesForPagination();
echo $table->tableFromMySQL($display_columns);
|
|
|
|
|
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
|
|
|
| Thread Tools |
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
All times are GMT -4. The time now is 05:27 PM.
|
|
