PHP999.blogspot.in: Store File In DATABASE

Thursday, January 30, 2014

Store File In DATABASE

Here I am going to tell you how can we send a file in database.
This is very general problem for a php developer
its is very basic PHP code you have to study and apply it.

1.upload_file.php
2.Add_file.php
3.List_file.php
4.Get_file.php

create database

username:root
password:
datbasename:test2
tablename:file

table entity:

CREATE TABLE `file` (

    `id`    &nbsp;   Int Unsigned Not Null Auto_Increment,

    `name`      VarChar(255) Not Null Default 'Untitled.txt',

    `mime`      VarChar(50) Not Null Default 'text/plain',

    `size`      BigInt Unsigned Not Null&nbsp;Default 0,

    `data`      MediumBlob Not Null,

    `created`   DateTime Not Null,

    PRIMARY KEY (`id`)

)

<!DOCTYPE html>
<head>
    <title>MySQL file upload example</title>
    <meta http-equiv="content-type" content="text/html; charset=UTF-8">
</head>
<body>
    <form action="add_file.php" method="post" enctype="multipart/form-data">
        <input type="file" name="uploaded_file"><br>
        <input type="submit" value="Upload file">
    </form>
    <p>
        <a href="list_files.php">See all files</a>
    </p>
</body>
</html>

1.upload_file.php

<!DOCTYPE html>
<head>
    <title>MySQL file upload example</title>
    <meta http-equiv="content-type" content="text/html; charset=UTF-8">
</head>
<body>
    <form action="add_file.php" method="post" enctype="multipart/form-data">
        <input type="file" name="uploaded_file"><br>
        <input type="submit" value="Upload file">
    </form>
    <p>
        <a href="list_files.php">See all files</a>
    </p>
</body>
</html>

<?php

// Check if a file has been uploaded

if(isset($_FILES['uploaded_file'])) {

    // Make sure the file was sent without errors

    if($_FILES['uploaded_file']['error'] == 0) {

        // Connect to the database

        $dbLink = new mysqli('127.0.0.1', 'user', 'pwd', 'myTable');

        if(mysqli_connect_errno()) {

            die("MySQL connection failed: ". mysqli_connect_error());

        }

        // Gather all required data

        $name = $dbLink->real_escape_string($_FILES['uploaded_file']['name']);

        $mime = $dbLink->real_escape_string($_FILES['uploaded_file']['type']);

        $data = $dbLink->real_escape_string(file_get_contents($_FILES  ['uploaded_file']['tmp_name']));

        $size = intval($_FILES['uploaded_file']['size']);

        // Create the SQL query

        $query = "

            INSERT INTO `file` (

                `name`, `mime`, `size`, `data`, `created`

            )

            VALUES (

                '{$name}', '{$mime}', {$size}, '{$data}', NOW()

            )";

        // Execute the query

        $result = $dbLink->query($query);

        // Check if it was successfull

        if($result) {

            echo 'Success! Your file was successfully added!';

        }

        else {

            echo 'Error! Failed to insert the file'

               . "<pre>{$dbLink->error}</pre>";

        }

    }

    else {

        echo 'An error accured while the file was being uploaded. '

           . 'Error code: '. intval($_FILES['uploaded_file']['error']);

    }

    // Close the mysql connection

    $dbLink->close();

}

else {

    echo 'Error! A file was not sent!';

}

// Echo a link back to the main page

echo '<p>Click <a href="index.html">here</a> to go back</p>';

?>

4.Get_file.php

<?php

// Connect to the database

$dbLink = new mysqli('127.0.0.1', 'user', 'pwd', 'myTable');

if(mysqli_connect_errno()) {

    die("MySQL connection failed: ". mysqli_connect_error());

}

// Query for a list of all existing files

$sql = 'SELECT `id`, `name`, `mime`, `size`, `created` FROM `file`';

$result = $dbLink->query($sql);

// Check if it was successfull

if($result) {

    // Make sure there are some files in there

    if($result->num_rows == 0) {

        echo '<p>There are no files in the database</p>';

    }

    else {

        // Print the top of a table

        echo '<table width="100%">

                <tr>

                    <td><b>Name</b></td>

                    <td><b>Mime</b></td>

                    <td><b>Size (bytes)</b></td>

                    <td><b>Created</b></td>

                    <td><b>&nbsp;</b></td>

                </tr>';

        // Print each file

        while($row = $result->fetch_assoc()) {

            echo "

                <tr>

                    <td>{$row['name']}</td>

                    <td>{$row['mime']}</td>

                    <td>{$row['size']}</td>

                    <td>{$row['created']}</td>

                    <td><a href='get_file.php?id={$row['id']}'>Download</a></td>

                </tr>";

        }

        // Close table

        echo '</table>';

    }

    // Free the result

    $result->free();

}

else

{

    echo 'Error! SQL query failed:';

    echo "<pre>{$dbLink->error}</pre>";

}

// Close the mysql connection

$dbLink->close();

?>