Here is a PHP Script which may help you.
First of all, Let us create a table named tbl_test in a MySQL Database. The table contains three fields:
1. field_id
2. category
3 description
Now here is the
CREATE TABLE CODE for you.
First of all, we'll drop Table, if it exists.
CODE
DROP table if exists `tbl_test`;
Now we'll create the table tbl_test.
CODE
CREATE TABLE `tbl_test` (
`field_id` int(11) NOT NULL auto_increment,
`category` char(20) default 'General',
`description` char(100) default 'Not Specified',
PRIMARY KEY (`field_id`)
)
Now insert some test data in the table.
Now is the time to play with the following PHP Script.
The script starts at this point.
CODE
<?php
global $db_name,$db_host,$db_user_name,$db_pass;
$db_name='test';
$db_host='localhost';
$db_username='test';
$db_pass='test';
Connecting to Database:
CODE
$new_file="myfile.csv";
Consider giving a dynamic file name rather than a static one.
This is the file that will store the fetched data.
Create a file using fwrite():
CODE
$fp=fopen($new_file,"w");
if(!$fp) die("Error creating file");
It will be used later on to store the fetched records.
CODE
$link_id=mysql_connect($db_host, $db_username,$db_pass);
if(!$link_id) die("connection failed");
Hopefully you can add a more appropriate error handler.
Setting the current database:
CODE
$current_db=mysql_select_db($db_name,$link_id);
if(!$current_db) die("error connecting host");
Creating a HTML table
CODE
echo " <table width=\"100%\" border=\"0\" cellspacing=\"1\" cellpadding=\"0\">";
echo "<tr>";
The table will hold a form which will display the options ( i.e. category as in my example).
Your users will choose a value from the options given.
CODE
echo "<td><form name=\"form1\" enctype=\"multipart/form-data\" method=\"post\" action=\"$PHP_SELF\">";
echo "<p>Please select a category </p>";
Now selecting the records ( i.e. category) one after another from the category table and displaying it in a list box
CODE
echo "<select name=\"category\" size=\"1\">";
$search_string="SELECT DISTINCT category FROM tbl_test";
$result_of_search_string=mysql_query($search_string);
while($data_search_string=mysql_fetch_row($result_of_search_string))
{
echo "<option> $data_search_string[0] </option>";
}
echo "</select>";
A simple one line instruction for the user:
CODE
echo "Click here to start :";
CODE
echo "<input type=\"submit\" name=\"Submit\" value=\"search\">";
echo "</form>";
echo"</tr>";
The FORM ends at this point Note the action attribute of the FORM tag set to $PHP_SELF
So when the user clicks on the submit botton the same page reloads with a value saved in the $category variable.
Now we'll show the record based on the options chosen by the user.
CODE
echo "<tr><td><h2> Your records </h2></td></tr>";
Let us set few global variables.
CODE
global $records_per_page, $cur_page, $PHP_SELF, $search_category, $category;
Checking if $category variable stotes any value or not, if it does not, we set it to default : General/ Or any value you prefer, but it must be the one of the values you have entered in the category field of your table.
CODE
if(empty($_POST["category"]))
$search_category="General";
else
$search_category=$_POST["category"];
Checking is done. Now we'll show the user, what value he wanted to see.
CODE
echo "You are searching for $search_category";
Now counting the number of records found in the database for that category:
CODE
$count_query="select count(*) from tbl_test where category = '$search_category' ";
CODE
$result = mysql_query($count_query);
if(!$result) die("error");
$query_data = mysql_fetch_row($result);
$total_record = $query_data[0];
if(!$total_record) die('No Record Found!');
Now setting the page number.
CODE
$page_num = $cur_page + 1;
Now setting the maximum number of records to be displayed in one page:
CODE
$records_per_page=10;
You can use another form to allow the user to select the records per page as we have done for selecting the category from the table.
Now we are calculating the total number of pages.
CODE
$total_num_page = $last_page_num
= ceil($total_record/$records_per_page);
CODE
echo "<tr><td><CENTER><H3>$total_record found on $search_category - Displaying the page
$page_num out of $last_page_num.</H3></CENTER>\n</tr></td>";
Now set the current page number. To start with it will have a value = 0.
CODE
if(empty($cur_page))
{
$cur_page = 0;
}
Now we set the limit for the records to be displayed per page.
CODE
$limit_str = "LIMIT ". $cur_page * $records_per_page .
", $records_per_page";
$new_query="SELECT description FROM tbl_test where category LIKE '$search_category'";
$query=$new_query ." ". $limit_str;
Again a database query to fetch the records as per the option selected by the user. And display the records in a table.
CODE
$result = mysql_query($query);
if(!$result) die("No record found");
while($data=mysql_fetch_row($result))
{
echo " <tr><td> $data[0] </td></tr> "; // this will show records
fwrite($fp,$data[0]); // this will write to the file
fwrite($fp,","); // This is the ", " seperater
}
We have finished fetching records and at the same time stroring it in a file.
Now we are creating links for the user to nevigate to continuation pages.
CODE
if($page_num > 1)
{
$prev_page = $cur_page - 1;
echo "<A HREF=\"$PHP_SELF?&cur_page=0\">[Top]</A>";
echo "<A HREF=\"$PHP_SELF?&cur_page=$prev_page\">[Prev]</A> ";
}
if($page_num < $total_num_page)
{
$next_page = $cur_page + 1;
$last_page = $total_num_page - 1;
echo "<A HREF=\"$PHP_SELF?&cur_page=$next_page\">[Next]</A> ";
echo "<A HREF=\"$PHP_SELF?&cur_page=$last_page\">[Bottom]</A>";
}
Now we'll allow the user to download the file.
CODE
echo "<tr><td><a href =\"$new_file\">Click here to download</a>";
Our task is over. Now some closing HTML tags again.
CODE
echo" </Td>";
echo " </tr>";
echo "</table>";
?>
So far as the present script is concerned, I got a great help from a book entitle
BEGINNING PHP4 published by Wrox Press Ltd. Infact page transition part has actually been scripted in that great book. It works nicely.
Regards,
Sid
Reply
.
