First I must WARN you that I'm not a writer, I can manage your network/server environment and develop web applications using PHP. So, if you notice a [sic] email me. Thanks

Now ...

A friend of mine ran across a number of electronic books and considered making them available for download. Wanting to avoid ftp downloads which could result in someone selecting ALL the books. Using up most of his bandwidth.

As a solution putting the files on a webserver share he could somewhat control how many books where downloaded preventing a mget * after typing prompt to turn off the interactive mode.

Since I do most of my development using MySQL and PHP I decided to use them for this application.

Prior to coding I needed to complete some important task.

Create a database schema
I decided on 2 tables: categories and files

CREATE TABLE categories (
  catid bigint(21) DEFAULT '0' NOT NULL auto_increment,
  catname varchar(32) DEFAULT '' NOT NULL,
  PRIMARY KEY (catid),
  UNIQUE catname (catname)
);

CREATE TABLE files (
  file_id int(11) DEFAULT '0' NOT NULL auto_increment,
  cat_id int(11),
  file_path varchar(100) DEFAULT '' NOT NULL,
  file_name varchar(100) DEFAULT '' NOT NULL,
  file_size varchar(50),
  file_descrp text,
  KEY file_id (file_id),
  UNIQUE file_id_2 (file_id)
);

$ mysqladmin create ebooks -uroot -p
$ mysql ebooks -uuserid -p
$ ebooks> (Cut paste schema)
$ ebooks> \q

Now, I'll start coding... I fire up vi, since I stated I was going to use it for a week. Most of the time I use nedit.

$ vi dbconnect.php

This file will be included in files that interact with your database.
We will include it using the require() function e.g. require('dbconnect.php');

<?php
$db_user = "root";
$db_pass = "";
$db_host = "localhost";
$db_name = "e_books";

$conn = mysql_connect($db_host, $db_user, $db_pass);
?>

$ vi put.php

Here we will create an input form for our little application.

// Include dbconnect.php
<?php require('dbconnect.php'); ?>

<FORM METHOD = POST ACTION ="./insert.php">


<TABLE COLSPAN=2 WIDTH = 100% BORDER = 0>
<TR><TD ALIGN = LEFT><B>Insert New Data</TD>
<TD ALIGN=LEFT><INPUT TYPE=hidden NAME=file_id
VALUE="''"></TD></TR>
<TR><TD ALIGN = LEFT><B>CatID</TD>
<TD ALIGN=LEFT>

<SELECT NAME=cat_id >
<!-- 
Here we get the categories from the [categories] table and make them the
value of our
<SELECT>
-->

<?php 
$qry = "SELECT catid, catname FROM categories";
$res = mysql_db_query($db_name,$qry,$conn);
if(!$res) {
        echo "ERROR:" . mysql_error();
} else {
        
        while($row = mysql_fetch_array($res)) {
                print "<OPTION value=" . $row["catid"]
. ">" . $row["catname"];
                }
}
?>
</SELECT>
</TD></TR>
<TR><TD ALIGN = LEFT><B>File Path</TD>
<TD ALIGN=LEFT><INPUT TYPE=TEXT NAME=file_path SIZE=50
MAXLENGTH=100></TD></TR>
<TR><TD ALIGN = LEFT><B>Filename</TD>
<TD ALIGN=LEFT><INPUT TYPE=TEXT NAME=file_name SIZE=50
MAXLENGTH=100></TD></TR>
<TR><TD ALIGN = LEFT><B>Filesize</TD>
<TD ALIGN=LEFT><INPUT TYPE=TEXT NAME=file_size SIZE=50
MAXLENGTH=50></TD></TR>
<TR><TD ALIGN = LEFT><B>File Descrp</TD>
<TD ALIGN=LEFT><TEXTAREA NAME=file_descrp COLS=50 ROWS=5
WRAP=VIRTUAL></TEXTAREA></TD></TR>
<TR><TD ALIGN = CENTER><INPUT TYPE =SUBMIT></TD>
<TD ALIGN = CENTER><INPUT TYPE =RESET></TD>
</TABLE></FORM>

$ vi insert.php

You will notice that I'm not checking to see if you entered data. You can add that if you like...

<?php
// Include dbconnect.php
require('dbconnect.php');

/* for our needs we really didn't need to check to see if we had entered
all the data
 however if you develop an application if you are not the one putting the
data in you 
 will want to check to make sure data is being entered.
*/

$qry = "INSERT INTO files
VALUES('$file_id','$cat_id','$file_path','$file_name','$file_size','$file_descrp')";
if($result = mysql_db_query($db_name, $qry, $conn)) {
 echo "Data was Entered";
 echo "<br><a href=ebooks.ph>Category List</a> or <a
href=put.php>Add another ebook</a>";
} else {
 echo "ERROR:" . mysqlerror();
}

?>

$ vi list.php

<?php
// Include dbconnect.php
require('dbconnect.php');

if($action == "list"):

$query = "select * from files WHERE cat_id = '$catid' order by file_id ";

if($result = mysql_db_query($db_name, $query, $conn)) {

while($row =  mysql_fetch_array($result)) {
echo "<table width=200><tr>";
echo "<td>".$row["file_descrp"]."</td>";
echo "<tr><td>";
echo "<a  href=".$row["file_path"]. "/" . $row["file_name"]
. ">".$row["file_name"]."</a>\n";
echo "</td></tr>\n";
echo "<tr>\n";
echo "<td> File Size: ".$row["file_size"]. "</td>\n";
echo "</tr>\n";
echo "<tr>\n";
echo "<td>\n";
echo "<hr noshade color=red>";
echo "</td>\n";
echo "</tr>\n";
echo "</table>\n";
    }
} else {
 echo "ERROR:" . mysql_error();
}

endif;



?>

As stated above this is a very simple application.
But I hope that you get the basic understanding of how to create a php/mysql based application

>> Comments/FeedBack