Storing Files/Data with PHP & MySQL – Detailed Tutorial 2017

At some point, most web programmers are going to need to learn databases. They’re the premier way to store information in an online environment, and they’re often the most efficient solution. But do they have limits as to what they can be used for? That’s what we’re going to talk about in this article.

This article assumes that the reader already has some knowledge in HTML, PHP, and MySQL, but one might be able to get by with just PHP. Rather than being a teacher, this is designed to be a helper in the right direction. The reader must also have access to a web server with PHP compiled with MySQL capabilities, as well as access to a MySQL server.

Facts and Myths

Many people believe that storing binary data in a MySQL database is an incredibly bad idea. In a sense, this is entirely true. Large files combined with large amounts of traffic will put needless stress on the database and will hinder performance.

This is not an ideal solution for storing applications in a file repository. On the other hand, storing the information in a database provides a highly centralized system.

Let’s again look at the file repository. When a file is uploaded, it is copied to a directory somewhere on the web server. If an inappropriate file is uploaded, you must search through all those files to find it. There is also, usually, some sort of file information about the file, such as number of downloads, author, and this is usually stored in a database. So now you’ve got to go through the database and remove the record for this file.

So we’ve got two steps. Of course, we already stated that large files shouldn’t be stored, so you’re going to be stuck with that in the above situation. What about on a smaller scale though? Say, a picture post? The files are small, and there is bound to be a lot of them. A centralized database is perfect for this, and the stress will not be too bad. This is the example that will be used in the article. However, it will not cover thumbnail generation or anything of that sort.

Basic Method of Uploading Files

Here we will cover the basics of how to upload a file. You will need an HTTP server with PHP capabilities installed and write access to the folder you’re going to try to upload to.

A. The HTML Form

We’ve all seen that nifty little input box on a form that says “Browse…” next to it. That’s the file input type. Now, let’s start with the HTML form:

<form method=”post” action=”upload.php” enctype=”multipart/form-data”>
<input type=”file” name=”file”>
<input type=”submit”>
</form>

Let us start with the <form> tag. This tells a web browser what to do with the input elements on a page. The first property of the <form> tag is method. method tells the browser how the data will be transferred to the server. There are two chief methods, GET and POST. GET appends the data to the URL (ex. page.php?var=data) while POST appends the data to the header. Next, we have the action property, “action” tells the browser where to send the data.

In this case, we’re sending the data to “upload.php“. It is important to know that you can use both POST and GET within a form. action=”upload.php?var=something” is perfectly valid. Then we have enctype, it is used to tell the server how the browser is sending the data.

PHP with MySQL Connectivity – Detailed Tutorial (CRUD)

For our purposes, “multipart/form-data” is the logical choice. You can read more about HTTP encoding types at www.w3c.org.

Next we have the <input> tags. These are used to create input fields on an HTML document. There are many different kinds, including text, radio, and password. The types we need for this script are file and submit. The file input type gives an input box where a filename may be put. This file will then be uploaded when the form is submitted. The submit type creates a button on the document which will submit the form.

B. The PHP Script

Now that we have our user form created, we can go under the hood and figure out what to do with this file that has now been submitted. PHP has global variable arrays to deal with form submitted information.

These are: $_REQUEST, for all form fields; $_POST, for POST data; $_GET, for GET data; and $_FILES, for file submitted data.

For this, we will be focusing only on the $_FILES array.

The $_FILES array is a two dimensional array. The first index is the name of the form element which was of type file. So for our example, we would use $_FILES[‘file’] to get the information about that uploaded file.

The second index contains a list of attributes about that file. This list includes, but is not limited to: size, for the size of the file; tmp_name, for the temporary name on the server; name, for the original name of the file; and type, for the HTTP content-type of the file.

Now that we have some background on how PHP deals with form data, we can take a look at the sample upload script:

<?php
$upload_dir = “upload/”;
if ($_FILES[‘file’][‘name’])
move_uploaded_file($_FILES[‘file’][‘tmp_name’],
$upload_dir . $_FILES[‘file’][‘name’]);
?>

Not too difficult, is it? It first specifies an upload directory, then checks to see if a file was actually uploaded, and then it moves the uploaded file to the upload directory.

$upload_dir should be set to the full directory path to which you wish to save the file, such as /home/~user/uploads/. The rest is pretty much self explanatory, based on what was explained above. The move_uploaded_file() function will only move files uploaded via HTTP-POST, and will not alter functions that have not.

There we go. We now have a very basic file upload script.

Basic Method of Storing Data In A Database

Now we will go over how to use PHP to write and retrieve information from a database. This article focuses on MySQL, because MySQL is the most popular free SQL server, and the easiest to use with PHP. This section assumes that you have a MySQL server set up on localhost which an account named ‘user‘ with a password of ‘password‘. It also assumes that there is a database named ‘testdb‘ on that server with the table ‘names‘ that contains two fields, ‘first‘ and ‘last‘.

A. Using PHP and MySQL

PHP has a host of capabilities for dealing with databases. The simplest, however, are it’s MySQL functions. There are six main functions that we will be looking at.

They are: mysql_connect(), mysql_select_db(), mysql_query(), mysql_num_rows(), mysql_fetch_array(), and mysql_error().

These are the 6 MySQL functions available in PHP.

Let us start with mysql_connect():
$dbh=mysql_connect ($host, $username, $password);

This function will establish a connection to a MySQL server. The arguments are $host, $username, and $password. These should be defined earlier in your script. $host will almost always be localhost. The function returns a handle for this connection, for if you are connecting to more than one database in your script.

Top 10 PHP Project Ideas for Beginners only – 2017

To select a database, we use the mysql_select_db() function. This function takes the name of a database that exists on the server connected to in mysql_connect(). It does not return anything.

Next we have mysql_query():
$query = mysql_query ($sql_query, $dbh);

The mysql_query() function takes two arguments. The first is required, and is a properly formatted SQL query, such as “SELECT * FROM table_name“.

The second is optional and is the database handle returned by the mysql_connect() function. If there is only one active database in the script, that connection will be used without specifying in the mysql_query() function. The function returns a handle for the results of the query, but you cannot access them yet.

To count the number of rows returned by a query, we use mysql_num_rows():
$rows = mysql_num_rows ($query);

mysql_num_rows() takes the results of the mysql_query() function as an argument and returns the number of rows in for that query.

To access the results of a query, we use mysql_fetch_array ():
$results = mysql_fetch_array ($query);

This function takes the results of the mysql_query() function as an argument. It returns an associative array of each field in the table queried in the order they are received. However, the array returned is one dimensional. This is usually remedied by processing data fetched from an array in a while loop, such as:

while ($results = mysql_fetch_array ($query))
{
// process row
}

The above code snippet will cycle through the results of a query row by row until there are no rows left. Lastly, we have the mysql_error() function. This function takes no arguments and returns the errors generated by a MySQL server if there are any in mysql_query() or mysql_connect() usages.

Typical usage of this function is:
$query = mysql_query($sql) or die(mysql_error());

The above code will execute the query $sql if it can, or it will halt the script and output the MySQL error generated by the query $sql. So now that we know about the basic functions used for interacting with databases, we can take a look at an example script:

<?php
$db = mysql_connect (‘localhost’,’user’,’password’);
mysql_select_db(“testdb”);
$sql = “INSERT INTO names (first,last) VALUES
(‘john’,’doe’);”;
$query = mysql_query($sql);
$sql2 = “SELECT * FROM names WHERE first = ‘john’;”;
$query2 = mysql_query($sql2);
while ($results = mysql_fetch_array ($query2))
echo “name: ” . $results[‘first’] . ” ” . $results[‘last’];
?>

The above example connects to a database on localhost with the username ‘user‘ and the password ‘password‘. It then selects the database ‘testdb‘ from the database. The script then runs an SQL query to insert the values ‘john‘ and ‘doe‘ into the fields ‘first‘ and ‘last‘ which exist in the table ‘names‘ of the database, respectively.

Next, the script runs an SQL query to select all rows out of the ‘names’ table where the field ‘first‘ is equal to ‘john‘. The script then prints them all out.

B. Inputting Values From A Form

Now that we know how PHP deals with MySQL databases, we can write a form from which we can input values from a user.

Let’s start with a simple HTML form that we can use:

<form method=”get” action=”db.php”>
First Name: <input type=”text” name=”fname”><br>
Last Name: <input type=”text” name=”lname”><br>
<input type=”submit”>
</form>

So here’s our sample HTML form we’re going to use. It has two input fields of type text which we will use to add data to our database. Next we have the PHP script:

<?php
$host = “localhost”;
$user = “user”;
$pass = “password”;
$db = mysql_connect ($host,$user,$pass);
mysql_select_db(“testdb”);
$sql = “INSERT INTO names (first,last) VALUES”;
$sql .= “(${_GET[‘fname’]},${_GET[‘lname’]});”);
$query = mysql_query ($sql);
?>

Nothing much new here, except on the tenth line. There, we’re using the .= operator. Using this operator, the following two statements are equivalent:

$sql = $sql . “(${_GET[‘fname’]},${_GET[‘lname’]});”);
$sql .= “(${_GET[‘fname’]},${_GET[‘lname’]});”);

Next, we have the variable structure of ${_GET[‘fname’]}. This structure, the inclusion of curly braces around a variable name, allows the variable to be used inside a string constant. So the following two statements are equivalent:

$string = “Name: ” . $_GET[‘fname’] . ” ” . $_GET[‘lname’];
$string = “Name: ${_GET[‘fname’]} ${_GET[‘lname’]}”;

As you can see, one is far simpler than the other. Using this script and HTML document, you can enter as many first and last names as you want into your database.

Putting It All Together

So you think you’ve got all the above under your belt and are ready to go for the gold, eh? We will first explore the layout of the table.

Once we have the table, with a few modifications. After that, we will take a look at the PHP script for putting the file and information in the database. To top it off, we will write a script to display the images within an <img> tag. You should have some knowledge of SQL and PHP before attempting this section.

A. Database Table Layout

Let’s examine the different elements we’re going to need for this project. We’ll start with the picture itself. We’ll need to store its type, its name, and the data that makes it up. Those three fields we’ll call ftype, fname, and fdata.

We’ll want a description of the file, desc, and the name of the poster, poster_name. Now, we need a way to uniquely identify each picture, and this will be the id field. Let’s look at those again: id, poster_name, desc, ftype, fname, fdata.

Remember, this is just a simple example. For an actual picture repository, you might want to have more fields.

To create the table, we use CREATE TABLE SQL command:

CREATE TABLE `pictures` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`poster_name` CHAR( 25 ) NOT NULL ,
`pdesc` CHAR( 255 ) NOT NULL ,
`ftype` CHAR( 75 ) NOT NULL ,
`fname` CHAR( 255 ) NOT NULL ,
`fdata` LONGBLOB NOT NULL ,
PRIMARY KEY ( `id` )
);

The command above basically just creates a table pictures in the current database with the six fields described above. id is an unsigned integer that cannot be null and auto increments. poster_name is a string type of 25 characters, desc is string type of 255 characters, and so on. Our fdata field is a long blob type which is capable of holding up to two gigabytes.

Lastly, we set the id field as our primary key. This table will let us enter as many pictures as we need into the database, while allowing us to keep everything centralized. It is useful for if someone uploads an offensive picture: all you need to know to delete it is what its ID is.

B. The HTML Form

Now that we’ve got the table, we’ll need to work out a method for uploading and displaying the pictures. We’ll start with the HTML aspect of it. First we’re going to design the upload form. It will be much the same as before, but there are a couple new things. Those we will discuss at the bottom. So, here’s the form:

<form method=”post” action=”uploadpic.php” enctype=”multipart/form-data”>
Your Name: <input type=”text” maxlength=”25″ name=”pname”><br>
Description: <input type=”text” maxlength=”255″ name=”pdesc”><br>
<input type=”hidden” name=”MAX_FILE_SIZE” value=”250000″>
Picture: <input type=”file” name=”pfile”><br>
<input type=”submit” name=”submit”>
</form>

Really there are only three new things in this listing: the hidden type, the maxlength property, and the whole MAX_FILE_SIZE thing.

We’ll start with the hidden type. hidden input fields are usually used for form constants and things that you don’t want the user to see. In this case, it is being used to advise the browser that you don’t want any files to be allowed to be sent that are more than 250,000 bytes, or about 250 kilobytes.

This is a good thing to have, because it saves the user time by not having to upload the entire file only to have the PHP script tell them it was too big. But, remember, this is only an advisory. Browsers do not have to follow it, so it is always a good idea to check the size in the PHP script as well.

The maxlength property specifies the maximum number of characters that can be inputted with that box. We use it because our database table only wants those lengths of strings, so if they enter more, there could be a problem.

C. The PHP Upload Script

Now that we have our form where the user can enter data, we need to write the script to take care of that data. We’ve already discussed how PHP deals with forms and files, so this shouldn’t be too much trouble to understand at all. So, let’s dive in!

<?php
$db = mysql_connect(“localhost”,”user”,”password”);
mysql_select_db(“database”);
$pname = $_REQUEST[‘pname’];
$pdesc = $_REQUEST[‘pdesc’];
$ftype = $_FILES[‘pfile’][‘type’];
$fname = $_FILES[‘pfile’][‘name’];
if ($_FILES[‘pfile’][‘size’] <= $_REQUEST[‘MAX_FILE_SIZE’])
{
$fdata = fread(fopen($_FILES[‘pfile’][‘tmp_name’], “rb”),
$_FILES[‘pfile’][‘size’]);
$fdata = base64_encode($fdata);
$sql = “INSERT INTO pictures “;
$sql .= “(poster_name,pdesc,ftype,fname,fdata) VALUES “;
$sql .= “(\”${pname}\”,\”${pdesc}\”,”;
$sql .= “\”${ftype}\”,\”${fname}\”,\”${fdata}\”)”;
$query = mysql_query($sql) or die(mysql_error());
echo “File Uploaded!”;
} else
echo “File Too Large”;
?>

The script above is functional, but very basic. You wouldn’t want to use it in a production environment, but the general idea is the same. The file data is no different than the other data. The only thing that really needs discussion is the base64_encode() function and why we use it.

Binary file data usually contains special characters when represented in ASCII form that do not do well in a database. They can get corrupt. So to protect against this, we use base64 encoding to make sure that all data that goes into the database is in the form of readable ASCII characters.

Also notice how we do not put a value into ID. That value will generate itself, which is what we want.

D. The PHP View Script

Now that we can store the picture in the database, we need a way to pull it out. This is where the really fancy stuff comes in. Yes, we’re going to make it so your pictures will show up in an <img> tag. It is possible to get a PHP script to act like a picture file! But before we get to the good stuff, we need a way to list which pictures are there. The following script will accomplish this in a rather crude, but efficient method:

<?php
$db = mysql_connect(“localhost”,”user”,”password”);
mysql_select_db(“database”);
$sql = “SELECT id,poster_name,pdesc FROM pictures”;
$query = mysql_query($sql) or die(“No Pictures”);
while ($results = mysql_fetch_array($query))
{
echo “Picture:<br>Posted by: ${results[‘poster_name’]}”;
echo “<br>Description: ${results[‘pdesc’]}<br>”;
echo “<a href=img.php?id=${results[‘id’]}>View</a>”;
echo “<br><br>”;
}
?>

And there you have it. A very simple way to list the pictures in your database. Everything in the above script has been covered before, so it’s not important to explain them again. Now we’ll look at the script in the link above: img.php.

This script will send the image format to the browser. It would be possible to put it in an <img> tag, but for the difficulty level of this article, that would require another script.

To put it in an <img> tag, simply use:

<img src=img.php?id={id number}>

In the above tag, {id number} is the id of some picture that exists in your database. Now that we have the formalities out of the way, let’s look at the img.php file.

<?php
$db = mysql_connect(“localhost”,”user”,”password”);
mysql_select_db(“database”);
if (isset($_REQUEST[‘id’]))
{
$sql = “SELECT ftype,fdata FROM pictures “;
$sql .= “WHERE id=${_REQUEST[‘id’]};”;
$query = mysql_query($sql);
$results = mysql_fetch_array($query);
header(“Content-Type: ” . $results[‘ftype’]);
echo base64_decode($results[‘fdata’]);
} else
echo “No Picture Specified.”;
?>

We don’t have many new things in here, but one blaring thing is the header() function. This function outputs an HTTP header to the browser. You can tell the browser many things, such as what kind of file it is, what the length of the file is, etc. In our case, we use it to tell the browser what type the file is. We use the same type that was gathered at the time of uploading.

This allows the browser to treat the data it will receive like an actual picture file! We also use the base64_decode() function because when we put the data in our database, we encoded it to protect its integrity.

Conclusion

To recap, we have gone over how HTML forms work, how PHP processes forms, how HTML file uploads work, how PHP processes those files, how PHP interacts with MySQL, and how to work with the HTTP headers. Please keep in mind, however, that this is just a preliminary glance at the technology.

There are infinitely more things you can do and the technology goes much deeper.

This was just meant to be a first look at the technology, from which you can go on and do lots of interesting things. All of the scripts here have been tested and work. Play around with them and read the descriptions. Also, be sure to let us know what you think of it!

Post Views: 963

You may also like:

Facts and Myths

Basic Method of Uploading Files

Basic Method of Storing Data In A Database

Putting It All Together

Conclusion

Related Posts