Board index   FAQ   Search  
Register  Login
Board index PHP PHP Scripts

MySQL to Excel through php question

Links for php scripts

Moderators: macek, egami, gesf

MySQL to Excel through php question

Postby garrick » Sun Feb 26, 2012 6:00 pm

I hope this is the right place to post this question. Here is the code I am using to export data from a MySQL table 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;
}
?>


The script works fine except that it exports all the data from the table. I current have a script that is displaying 20 records from the same table per page. I need a way to either export only the records that display per page or the ability to specify how many records to export. I hope this makes sense, any help would be greatly appreciated.

Here is the code that I use to display the records from the database 20 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>';
?>


I thank everyone in advance for helping me out.
garrick
New php-forum User
New php-forum User
 
Posts: 1
Joined: Sun Feb 26, 2012 5:49 pm

Return to PHP Scripts

Who is online

Users browsing this forum: No registered users and 1 guest

Sponsored by Sitebuilder Web hosting and Traduzioni Italiano Rumeno and antispam for cPanel.

cron