Page 1 of 1

How to Limit MySQL Results being exported to Excel

Posted: Sun Feb 26, 2012 8:28 pm
by garrick
I currently have a script that will output data from a MySQL table and limiting it to 20 results per page, this script works fine. I am also using a script that will export all the data from the MySQL table to Excel this one works fine except I would like to limit what is being exported based on the results perpage or have a way to specify how many rows to export.

Here is the code that outputs 20 results per page:

Code: Select all

<?php
$tbl_name='table_data';

//check if the starting row variable was passed in the URL or not
if (!isset($_GET['startrow']) or !is_numeric($_GET['startrow'])) {
  //we give the value of the starting row to 0 because nothing was found in URL
  $startrow = 0;
//otherwise we take the value from the URL
} else {
  $startrow = (int)$_GET['startrow'];
}

// Retrieve data from database
$list_selectinfo="SELECT * FROM $tbl_name  ORDER BY id DESC LIMIT $startrow, 20";
$list_res=mysql_query($list_selectinfo);
$list_res2=mysql_query($list_selectinfo);

?>

<div>
<div>
<div>
<div class="table">


<table width="100%" border="1" cellpadding="3" cellspacing="0">
  <tr>
    <td align="center"><strong>ID </strong></td>
    <td align="center"><strong>Organization</strong></td>
    <td align="center"><strong>Name</strong></td>
    <td align="center"><strong>Company</strong></td>
    <td align="center"><strong>Time</strong></td>
    <td align="center"><strong>Number</strong></td>
    <td align="center"><strong>Email</strong></td>
    <td align="center"><strong>Permission</strong></td>
    <td align="center"><strong>Phone</strong></td>
    <td align="center"><strong>Cable</strong></td>
    <td align="center"><strong>Internet</strong></td>
     <td align="center"><strong>Remove</strong></td>
    </tr>
  <?php
  while($rows=mysql_fetch_array($list_res)){
   ?>
  <tr>
    <td align="center"><?php echo $rows['id']; ?>&nbsp;</td>
    <td align="center"><?php echo $rows['org']; ?>&nbsp;</td>
    <td align="center"><?php echo $rows['name']; ?>&nbsp;</td>
    <td align="center"><?php echo $rows['company']; ?>&nbsp;</td>
    <td align="center"><?php echo $rows['time']; ?>&nbsp;</td>
    <td align="center"><?php echo $rows['number']; ?>&nbsp;</td>
    <td align="center"><?php echo $rows['email']; ?>&nbsp;</td>
    <td align="center"><?php echo $rows['permission']; ?>&nbsp;</td>
    <td align="center"><?php echo $rows['phone']; ?>&nbsp;</td>
    <td align="center"><?php echo $rows['cable']; ?>&nbsp;</td>
    <td align="center"><?php echo $rows['internet']; ?>&nbsp;</td>
    <td align="center"><a href="remove_rec.php?id=<?php echo $rows['id']; ?>">Remove</a>&nbsp;</td>
    </tr>
  <?php
  }
  ?>
</table>
</div>
<?php
$prev = $startrow - 20;

//only print a "Previous" link if a "Next" was clicked
if ($prev >= 0)
    echo '<a href="'.$_SERVER['PHP_SELF'].'?startrow='.$prev.'"><img src=images/prev.png border="0"></a>&nbsp;&nbsp;&nbsp;';

//now this is the link..
echo '<a href="'.$_SERVER['PHP_SELF'].'?startrow='.($startrow+20).'"><img src=images/next.png border="0"></a>';
?>
Here is the code that exports all table data to excel:

Code: Select all

<?PHP
include 'dbc.php';

    $result = mysql_query('SELECT * FROM `table_name`');
if (!$result) die('Couldn\'t fetch records');
$num_fields = mysql_num_fields($result);
$headers = array();
for ($i = 0; $i < $num_fields; $i++)
{     
       $headers[] = mysql_field_name($result , $i);
}
$fp = fopen('php://output', 'w');
if ($fp && $result)
{     
       header('Content-Type: text/csv');
       header('Content-Disposition: attachment; filename="export.csv"');
       header('Pragma: no-cache');   
       header('Expires: 0');
       fputcsv($fp, $headers);
       while ($row = mysql_fetch_row($result))
       {
          fputcsv($fp, array_values($row));
       }
die;
}
?>
Any help is much appreciated and I thank everyone in advance for taking the time to read this post.

Re: How to Limit MySQL Results being exported to Excel

Posted: Sun Feb 26, 2012 8:31 pm
by Celauran
Add a LIMIT clause to your query.

Re: How to Limit MySQL Results being exported to Excel

Posted: Sun Feb 26, 2012 8:40 pm
by garrick
I have done that I used this:

Code: Select all

$result = mysql_query('SELECT * FROM `table_name` LIMIT 0, 20'); 
but this will only submit the first 20 records when the export button is clicked. Is there a better way to format the LIMIT clause to allow only the results displaying per page?

Re: How to Limit MySQL Results being exported to Excel

Posted: Mon Feb 27, 2012 1:55 pm
by temidayo
supply the LIMIT clause with PHP variables e.g

Code: Select all

$result = mysql_query('SELECT * FROM `table_name` LIMIT $start_row, $num_of_rows');