Posted By

brownrl on 07/29/11


Tagged

mysql database php table sorting


Versions (?)

Who likes this?

1 person have marked this snippet as a favorite

tux-world


PHP DB Table to HTML Datagrid


 / Published in: PHP
 

URL: http://www.itsgotto.be/cv.php

This is a skeleton mock up for showing a database table in a basic datagrid with sorting and pagination.

  1. <?php
  2. /**
  3. *
  4. * Simple Data Grid Skeleton
  5. *
  6. * Load a table from the database and display it in HTML with pagination and sorting.
  7. *
  8. * Please keep in mind that this a basic skeleton mock up to get the ball rolling
  9. *
  10. * You really will need to modify this! This code here is merely a skeleton for making
  11. * a datagrid presentation of db table. It will not work out of the box but should save you
  12. * precious time in getting off the ground.
  13. *
  14. * http://snipplr.com/users/brownrl/
  15. *
  16. **/
  17.  
  18. // Some variables
  19.  
  20. // set these to connect to you databse or even better you should use db class or wrapper
  21. $dbhost = "127.0.0.1";
  22. $dbname = "dbname";
  23. $dbuser = "user";
  24. $dbpass = "password";
  25.  
  26.  
  27. // Here we start a PHP/Mysql connection you probably already have a
  28. // connection or a some db object
  29. $dbc = mysql_connect( $dbhost , $dbuser , $dbpass ) or die( "Can't connect to the DB" );
  30. mysql_select_db( $dbname , $dbc ) or die( "Can't select DB" );
  31.  
  32.  
  33. // The table that you want to data grid
  34. //
  35. $table = "the_table";
  36.  
  37.  
  38. // The fields that you want to show in the data grid
  39. // If you want to show everything then you need to user '*'
  40. // and make some fixes below
  41.  
  42.  
  43. $fields[] = "field1";
  44. $fields[] = "field2";
  45. $fields[] = "field3";
  46. $fields[] = "field4";
  47. $fields[] = "field5";
  48.  
  49.  
  50. // How many to show per page
  51. $per_page = 10;
  52.  
  53.  
  54. /// Done with config and variables
  55.  
  56. // Now we start to setup some other variables
  57. // You probably do want to edit after this too
  58. // make cool customizations, etc...
  59.  
  60.  
  61.  
  62. // Lets get the total in the table
  63. $q = "SELECT COUNT( ".$fields[0]." ) as total FROM ".$table."";
  64. $qr = mysql_query( $q ) or die( "Can't select the total" );
  65. $qrow = mysql_fetch_assoc( $qr );
  66. $total_records = $qrow['total'];
  67.  
  68.  
  69. // Lets get an offset started amd handle if we go off the ends with prev and next button
  70. $offset = intval( $_GET['offset'] );
  71. if( $offset < 0 ) // if less than 0 just go to 0
  72. {
  73. $offset = 0;
  74. }
  75.  
  76. while( $offset > $total_records ) // if over the total keep subtracting till under
  77. {
  78. $offset = $offset - $per_page;
  79. }
  80.  
  81.  
  82. // lets get a sort by field
  83. // take field[0] if not in there
  84. //
  85. $sort_by = $_GET['sort_by'];
  86. if( ! in_array( $sort_by , $fields ) )
  87. {
  88. $sort_by = $fields[0];
  89. }
  90.  
  91.  
  92. // Now we are going to define the sorting order either ASC or DESC.
  93. $directions[] = "ASC";
  94. $directions[] = "DESC";
  95.  
  96. $order = $_GET['order'];
  97. if( ! in_array( $order , $directions ) )
  98. {
  99. $order = "ASC"; // ASC is default
  100. }
  101.  
  102. // Switch directions click on the ascending, get descending vice versa
  103. $odirections['ASC'] = "DESC";
  104. $odirections['DESC'] = "ASC";
  105.  
  106. // order signals up and down arrows maybe you want to use a picture?
  107. $osignals['ASC'] = "&uarr;";
  108. $osignals['DESC'] = "&darr;";
  109.  
  110.  
  111. // BUILDING THE QUERY
  112. // join the fields
  113. $fs = join( ", " , $fields );
  114. $q = "SELECT ".$fs." FROM ".$table." "."ORDER BY ".$sort_by." ".$order." LIMIT ".$per_page." OFFSET ".$offset;
  115. $qr = mysql_query( $q ) or die( "Can't select for showing :: $q" );
  116.  
  117.  
  118. // BUILD the HTML table
  119. // This point we go out into html world
  120.  
  121. ?>
  122. <table>
  123. <!-- first row with headers -->
  124. <tr>
  125. <?php foreach( $fields as $k => $v ) { ?>
  126. <th>
  127. <a href="?sort_by=<?= $v ?>&order=<?= $odirections[$order] ?>"><?= ucwords( strtolower( preg_replace( "/_/" , " " , $v ) ) ) ?></a>
  128. <?= ( $sort_by == $v ) ? $osignals[$order] : "" ?>
  129. </th>
  130. <?php } ?>
  131. </tr>
  132. <!-- data row -->
  133. <?php while( $qrow = mysql_fetch_assoc( $qr ) ) { ?>
  134. <tr>
  135. <?php foreach( $qrow as $k => $v ) { ?>
  136. <td><?= $v ?></td>
  137. <?php } ?>
  138. </tr>
  139. <?php } ?>
  140. </table><!-- end table -->
  141.  
  142. <!-- previous button -->
  143. <a href="?sort_by=<?= $sort_by ?>&order=<?= $order ?>&offset=<?= $offset - $per_page ?>">previous</a>
  144.  
  145. <!-- pagination links -->
  146. <?php for( $i = 0 ; $i < $total_records ; $i = $i + $per_page ) { ?>
  147. &nbsp;<a href="?sort_by=<?= $sort_by ?>&order=<?= $order ?>&offset=<?= $i ?>"><?= ( $i / $per_page ) + 1 ?></a>&nbsp;
  148. <?php } ?>
  149.  
  150. <!-- next button -->
  151. <a href="?sort_by=<?= $sort_by ?>&order=<?= $order ?>&offset=<?= $offset + $per_page ?>">next</a>
  152.  
  153. <!-- total records -->
  154. <p>Total Records: <?= $total_records ?></p>

Report this snippet  

You need to login to post a comment.