html php and sql connection an upload

[Gabrielroos]

Ok, before I start, DANIWEB is my last resort to get help. it seems the people on the other sites are having trouble understanding and/or helping me with this... anyway, here is what I am trying to do: I have a website with a folder structure that I allow users to browse to select a category where they can upload they’re relevant pdf documents, for instance, the root folder is “data”, then they have to select a grade, like grade 12 or 11 or 10 whatever, then a subject say “geography”, “maths” or whatever, inside the selected subject they have to pick a sub category say: if they selected “geography” then ”erosion” is a subtopic for geography. This works well with the folder structure on the website, but I want to change the system to use a sql database with the relevant tables instead of folders. I have already created the sql database and written a php script to connect to the database, now I need to get the rest done…. Can anyone help me write this code, I will put dedicated pages on my website advertising your help!!! I am truly desperate

Regards
Gabriel Roos

[pritaeas]

now I need to get the rest done

Are you looking for someone to write it for you, or do you have specific smaller problems you want help with? In case of the latter, please describe the first issue.

[Gabrielroos]

Hi, yes, I seem to be fairly useless with writing my own code, if I can find someone to do it for me I will give all credits to that person as well as create a dedicated page on my own website for his/her benefit... I really am desperate...

[AARTI SHRIVAS]

why don't you use any CMS for this

[veedeoo]

Can we at least see your database table structure? Also, it would be nice if you can provide a wish list as in SCRUM prioritize wish list or back log. It will be a lot easier to estimates how much work is needed.

[Gabrielroos]

as far as I know a sql database will allow pdf files to be uploaded in binary streams which is very secure? anyway, I know a bit about html and php but not sure about cms...I will do some reasearch...

[Gabrielroos]

Hi veedeoo... can I mail you my database? or do you need access to my cpanel, the site is empty and useless at the moment? I dont mind making this a community project?

[pritaeas]

I dont mind making this a community project

How about GitHub, or SourceForge?

[veedeoo]

Hi,

GitHub or sourceForge are cool either one would be nice, all you need to do is go to your myPHPAdmin and then click on the database table for this project and then click on the export button. On the export page, select sql, click go, download should commence. After the download, save the file as text file or you can leave it with .sql extension.

You can either include it to your response or upload it to your server and give us the link, or send it to your github account e.g. myaccount.

If you take the github or sourceforge route, then it will be very helpful to include all of your pHP files in there.

[veedeoo]

Attached image is a good example of a cms backlog or wish list. I just finished developing it 3 days ago. It took me months but it came out pretty cool I think. It is now in alpha version awaiting to be scrutinized by my fellow geeks.

The idea is to be able to monitor the progress or at least see the panoramic view of the entire project. Most importantly is the ease of designing the database.

[Gabrielroos]

ok, here is the database, its all related to education, the afrikaans is a language subject for instance...

[Gabrielroos]

database extention changed crom sql to txt

[veedeoo]

Hi,

After looking at your database file, I just noticed that non of them were assigned as primary or auto-incremented. It will be defficult to sort by relationship, parent-to-child relationship without any pilot table ( the table that can be use as reference as we conduct a JOIN query).

For example, the table called SUBJECT_mysql should make the subjectid column as auto-incremented. By doing so, we can easily search subject name, and then use the queried subjectid for something else later on.

Another example, is the table called TOPIC_mysq. We can set the topicid column as auto-incremented and then on the SUBTOPIC_mysql topic table the topicid column is equivalent to the topicid of TOPIC_mysql table, and once again make the subtopicid as auto-incremented. By using this type of a database design, we can sort the children of all topics using only the topicid. This is pretty similar to category - sub-category - sub-sub-category relationship design model presented in the mySQL consortium some years ago.

What i want you to provide us is a simple block diagram on how the users will interact with your website. Please take a look at the attached image on my previous response. That is the only clearer way we can make an effective database design, because it is the anticipation of all transactions between the users and the application.

Give us the diagram, and I will do my best to rewrite your database structure. We cannot proceed writing codes without the operational core of the application.

[veedeoo]

I might left a cloudy ideas. So, here is a simple flow of the design.

Let us take Chemistry as the subject of interest.

parent
CourseId = 1
CouseName = Chemistry

Sub-Field of concentration in chemistry ( this will act as child)
subId = 2 , parentId = 1 ( chemistry), subCourseName = generalChemistry
subId = 3 , parentId = 1 (chemistry), subCourseName = Organic Chemistry
subId = 4 , parentId = 1 (chemistry), subCourseName = BioChemistry

Grand Children of Chemistry
sub_subId= 3 , parentId = 2, GrandParentId = 1, tags = valence eletron, oxidation reduction, intro to neuclear chemistry, pdfFileName = generalChem, available = 1 ( switch to true or false if the pdf file is available).

sub_subId= 4 , parentId = 3, GrandParentId = 1, tags = aromatic, isomerism of organic compound, organic bases and acids, pdfFileName = organicChem, available = 1 ( switch to true or false if the pdf file is available).

[Gabrielroos]

Good morning veedeoo... ok, I changed n few things in the database, see attatched. I have also made a diagram of how the whole thing should work... Thank you so much for your willingness to help me with this, I realy appreciate it...

[veedeoo]

Warning? This is a prototype script. So, there will be no css and javascript included. The upload.php is not intended for public access. This script is not intended for production server until further notice or until some of its vulnerabilities are fixed.

Let's start from the very basic. I normally write codes in OOP, but for the clarification, I will be writing this in procedural. We will be using the MysQLI and PDO. Besides, I really missed the freedom of writing codes in procedural, no design patterns, not too many things to follow, but trying to write codes. However, as we test our script, we will slowly convert all the codes to OOP so that it will become more extensible for future improvements and upgrades.

The reason for the mysqli implementation is that PDO have a minor bugs in handling PDF as BLOB ( insert only). To minimize the trouble of trying to fix this minor bug, I decided to use mysqli for the upload part.

Ready?

1. On your phpMyAdmin, create a new database. You can name this any name you want. After creating the database, create a table named edudata_mysql and click on the sql tab and import codes below.

tableName: edudata_mysql

CREATE TABLE IF NOT EXISTS `edudata_mysql` (
  `edudataid` int(20) NOT NULL AUTO_INCREMENT,
  `filename` varchar(255) NOT NULL,
  `size` int(25) NOT NULL,
  `filecontent` longblob NOT NULL,
  `subtopicid` int(11) NOT NULL,
  `type` varchar(100) NOT NULL,
   PRIMARY KEY (`edudataid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

I eliminated the 'sortkey' column, because I don't see any important use for it.

1. Create another table named subject_mysql and once again, import the following.

   CREATE TABLE IF NOT EXISTS `subject_mysql` (
     `subjectid` int(11) NOT NULL AUTO_INCREMENT,
     `subjectname` varchar(255) NOT NULL,
      PRIMARY KEY (`subjectid`),
      UNIQUE KEY `subjectname` (`subjectname`)
   ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=18 ;
   
   
   
   INSERT INTO `subject_mysql` (`subjectid`, `subjectname`) VALUES
   (1, 'Accounting'),
   (2, 'Afrikaans FAL'),
   (3, 'Business Studies'),
   (17, 'Climate&Weather'),
   (4, 'Economics'),
   (5, 'English FAL'),
   (6, 'English HL'),
   (7, 'Geography'),
   (8, 'History'),
   (9, 'Information Technology'),
   (10, 'Life Orientation'),
   (13, 'Life Sciences'),
   (14, 'Mathematical Literacy'),
   (15, 'Mathematics'),
   (16, 'Physical Science');
   

   1. On your public directory, create a new directory called tester and create a new php page called config.php. Copy codes below and paste it to config.php page.

   fileName: config.php

    <?php
   
   ## fill in your database credentials
   $db_host = 'localhost';
   $db_user = '';
   $db_pass = '';
   $db_name = '';
   
   ## setting the allowDownload to true, user will have download the pdf file
   $allowDownload = false;
   ## we call the mysqli constructor just for the purpose of inserting the PDF file as BLOB
   ## there has been a substantial PDO bug on PDF as BLOB.
   $con= new mysqli( $db_host, $db_user, $db_pass,$db_name);
   if($con->connect_errno){
   
       ## comment this on production server
       die('Error Connecting to Database: ' . mysqli_error());
   
       ## uncomment this instead
       //exit;
   
   }
   
   ## we call the PDO constructor. We will be using the PDO accross the application, except the PDF upload. The reason I already mentioned above.
   try{
           $dbCon = new PDO('mysql:host='.$db_host.';dbname='.$db_name.';charset=utf8', $db_user, $db_pass ,array(PDO::ATTR_PERSISTENT => true));
   
       $dbCon->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT);
       //echo 'connected';
         return true;
       }
        catch(PDOException $e) {
                       return false;
                       die('There is a connection error: ' .$e->getMessage());
                   } 
   

Fill in your database credentials..
this code right here..

$allowDownload = false;

allows you to set if you want other users to download the PDF file. Just set it to true.

1. Now let us create the upload.php . This will test the pdf upload. Data will be saved on the database table called edudata_mysql.

Warning! the processor does not have any sanitizing functions in it. Also, it does not check for the file extension. We can fix this later. DO NOT allow any public access on this file for now.

fileName: upload.php

<?php

require_once 'config.php';

## prepare the select option from the subject_mysql table
$subject_query = "SELECT subjectid, subjectname FROM subject_mysql";
$res = $con->query($subject_query);
$option = '';
while ($row = $res->fetch_array(MYSQLI_ASSOC)){
    $option .= '<option value="'.$row['subjectid'].'">'.$row['subjectname'].'</option>';

    }  

if(isset($_POST['upload']) && $_FILES['pdffile']['size'] > 0)
{
$fileName = $_FILES['pdffile']['name'];
$tmpName  = $_FILES['pdffile']['tmp_name'];
$fileSize = $_FILES['pdffile']['size'];
$fileType = $_FILES['pdffile']['type'];
$subtopicId = $_POST['subtopicid'];

$fp      = fopen($tmpName, 'r');
$content = fread($fp, filesize($tmpName));
$content = addslashes($content);
fclose($fp);

if(!get_magic_quotes_gpc())
{
    $fileName = addslashes($fileName);
}

$query = "INSERT INTO edudata_mysql (filename, size, filecontent, subtopicid,type ) ".
"VALUES ('$fileName', '$fileSize', '$content', '$subtopicId', '$fileType')";
$result = $con->query($query);


echo "<br>File $fileName uploaded<br>";
echo '<br/>';
echo '<a href="view.php?id='.$con->insert_id.'">Click to view uploaded file</a>';
}
mysqli_close($con);
?>

<form method="post" enctype="multipart/form-data" action="">

<input type="hidden" name="MAX_FILE_SIZE" value="2000000">
<input name="pdffile" type="file" id="pdffile">
<br/>
<label>Select Subject</label>
<select name="subtopicid">
<?php echo $option; ?>
</select>
<br/>
<input name="upload" type="submit" class="box" id="upload" value=" Upload ">
<br/>
</form>

1. Creat another file called view.php. This is for testing purposes only. We want to make sure that uploaded PDF file can be viewed.

fileName: view.php

<?php

if(isset($_GET['id']))
{

include 'config.php';

$id    = $_GET['id'];

$query = 'SELECT edudataid, filename, size, filecontent, type FROM edudata_mysql WHERE edudataid = :id';

$res = $dbCon->prepare($query);
$res->execute(array('id'=>$id));

while ($row = $res->fetch()){

header("Content-length: ".$row['size']);
header("Content-type: ".$row['type']);

if($allowDownload){
    header("Content-Disposition: attachment; filename=".$row['filename']);
}


    echo $row['filecontent'];

}

$dbCon = null;
exit;
}

1. Direct your browser to upload.php, and test the upload function and make sure the script works. Try uploading a couple of PDF files or until you are satisfied.

Make sure PDF files are viewable on the browser.

1. Create another page named index.php . This page will provide the complete list of all PDF files saved in the edudata_mysql table.

fileName: index.php

<?php
include 'config.php';

$query = "SELECT edudataid, filename FROM edudata_mysql";
$res = $dbCon->prepare($query);
$res->execute();
$rows = $res->fetchAll();
?>

<html>
<head>
<title>School Files</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>


<?php
foreach ($rows as $row){
?>
     <a href="view.php?id=<?php echo $row['edudataid'];?>"><?php echo $row['filename'];?></a> 
     <br>

<?php
}

$dbCon = null;
?>


</body>
</html>

After doing test uploads, direct your browser to index.php. There should be list of all pdf files uploaded from previous steps. Click on one of the displayed file name.

Next, we will test the administration interface. That is the option of adding subjects to the subject table.

That's it for now, I pretty much written 90% of the script. I just want you to see and be able to visualize how the final application will work.

[Gabrielroos]

Hi, Ok I am changing the database now, please just have a look at what I dit with the config.php, I hope it is right, but I doubt it....:)

[Gabrielroos]

I am getting this erros with the database...

[pritaeas]

You appear to be using single quotes instead of backticks in your query.

[Gabrielroos]

oh.. thanks, that worked...

[Gabrielroos]

Thanks veedeoo, I ceated the php scripts, but when u point my borwser to the upload.php script, it gives:

Fatal error: Call to a member function fetch_array() on a non-object in /home/edynam0/public_html/Upload.php on line 9

[Gabrielroos]

back to the database.... I have changed the edudata_mysql table, but the subjectid values in the SUBJECT_mysql table link up with the subjectid field in the TOPIC_mysql table. If these values change, wont the links be broken? The sortkey field in the EDUDATA_mysql table will be used because the PDF's must display in a specific order in the final version. It is educational documents that follow in a specific order set by the user that uploaded the data?...This feature is not important yet and can be left out for now... Do i make any sence?

[Gabrielroos]

this is what my database looks like at the moment?