Loading images to mySQL using a PHP form?

webmaster1 · November 15, 2008

Hi Freaks!

I require a form to post an image (amongst other text values) to a mySQL database which I'll later output in another page.

I've read somewhere that some web hosting providers won't allow this. Is this true and should I be going about this another way?

While I'm badgering you all does anyone know what type my image field should be in mySQL (e.g. VARCHAR etc.)?

webmaster1 · November 15, 2008

I'm hoping that I can post all to the one database via PHP like so:

<form action="inputdata.php" method="post">
	<fieldset>
		<ol>
			<li>
				<label for="make">Make:</label>
				<input type="text" name="make" id="make" class="text">
			</li>				
			<li>
				<label for="model">Model:</label>
				<input type="text" name="model" id="model" class="text">
			</li>
			<li>
				<label for="price">Price:</label>
				<input type="text" name="price" id="price" class="text">
			</li>
			<li>
				<label for="engine">Engine:</label>
				<input type="text" name="engine" id="engine" class="text">
			</li>
			<li>
				<label for="image1">Image 1:</label>
				<input type="file" name="image1" id="image1">			
			</li>
			<li>
				<input type="submit" class="submit">
			</li>
		</ol>
	</fieldset>
</form>
<!--INPUT FORM ENDS HERE-->

cunoodle2 · November 15, 2008

Here is a really nice article on discussing how to store images in a mySQL database....

http://www.phpriot.com/articles/images-in-mysql

Here is the code you need for your inputdata.php file...

<?php
//NULL set all variables at beginning to prevent injections
$make = NULL;
$model = NULL;
$price = NULL;
$engine = NULL;
$image1 = NULL;

//check to see if a form was submitted.
if (isset($_POST[submit]))
{
//check for passed in value and clean them up
$make = isSet($_POST['make']) ? $_POST['make'] : NULL;
$model = isSet($_POST['model']) ? $_POST['model'] : NULL;
$price = isSet($_POST['price']) ? $_POST['price'] : NULL;
$engine = isSet($_POST['engine']) ? $_POST['engine'] : NULL;
$image1 = isSet($_POST['image1']) ? $_POST['image1'] : NULL;

//use this if you are using prepared statements (MUCH more secure)
//prepare SQL query using pdo statement
$stmt = $write->prepare("INSERT INTO table_name (make, model, price, engine, image) VALUES (?,?,?,?,?) LIMIT 1");
$user_info = $stmt->execute(array($make,$model,$price,$engine,$image1));
$result = $stmt->fetch(PDO::FETCH_ASSOC);

//or using basic mysql statement (NOT secure)
$sql = "INSERT INTO table_name (make, model, price, engine, image) VALUES ($make,$model,$price,$engine,$image1) LIMIT 1;";
mysql_query($sql);

}
?>

webmaster1 · November 15, 2008

Thanks cunoodle2! Will attempt to implement this shortly and get back to you.

waynew · November 15, 2008

Is this true and should I be going about this another way?

Yes. You SHOULD store images on your file system and record their whereabouts (i.e. directory/filename.extension) inside the database. Actually storing images inside databases is more trouble than its worth.

1: Your image table will end up being bloated in size.

2: Deleting such large items from your table will cause fragmentation and therefore a decrease in performance.

3: Even displaying the images is much slower, as the database has to give the image to one file, which is then used by the file (page) that is wanting to display the image.

webmaster1 · November 15, 2008

Yes. You SHOULD store images on your file system and record their whereabouts (i.e. directory/filename.extension) inside the database. Actually storing images inside databases is more trouble than its worth.

So I basically load the images to a folder first and just post the path via my form into mySQL?

When outputting the data do I use a string of some sort to combine a little html with the outputted path?

Can mySQL/PHP ouput code into a web page that will be read as regualr code by the browser or do I have to use some sort of a loop?

I don't even know what I'm talking about. ::)

webmaster1 · November 15, 2008

Will cunoodle2's code suffice if I'll only have a maximum of 30 images at any one time in my database.

A possible exception would be if comparatively few images are involved, and all of the images are small in size (thumbnails), and all of the images were web-optimized (compressed) before uploading them.

cunoodle2 · November 15, 2008

Your absolute best bet is to start writing code. See how it works and then you can always modify it. Be sure to document your code really well so that you always come back and make changes. If you end up finishing it and you don't like the way that it works then you can just modify it and chalk this up to a really nice learning experience.

Also when you post actual code on here you are much more likely to get help as people can see what you are seeing and help make minor changes to it. Keep us posted.

waynew · November 15, 2008

Will cunoodle2's code suffice if I'll only have a maximum of 30 images at any one time in my database.

But you're not seeing the bigger picture. EVERY time a user views a page with one of those images on it, MySQL has to shell out 100KB or more. If multiple images are being displayed, then the size will be larger. If the time comes when you want to add more images, the problem will only worsen.

Upload a file to your filesystem and record its whereabouts in the database. Then, when you're outputting, do something like:

while($row = mysql_fetch_assoc($result)){
echo '<img src="'.$row['directory'].'" />';
}

Basically, get the first draft working first, then post it back here and let everybody pick at it. You'll learn more that way.

webmaster1 · November 15, 2008

Thanks guys, still working on the code. Will post it later this evening.

webmaster1 · November 16, 2008

Basically, get the first draft working first, then post it back here and let everybody pick at it. You'll learn more that way.

I've just read through five or six different tutorials on how to upload an image but I simply can not get my head around. I haven't been this frustrated in a long time ???

To clarify I need my form to SIMUTANEOUSLY do the following:

[*]Insert my text fields to a database

[*]Upload three images into designated directory e.g. /images

[*]Insert the url of the images into THE SAME DATABASE that my text fields are sent to.

[*]Validate the inputted information

[*]Redirect the user to an error or success page.

Every tutorial that I've researched, aside from not understanding all the code, only partially shows me what I need to be doing. Some show how to upload an image (I can't even get that much working) but don't show me how to write to a database. I just don't know what parts to pick and string together.

The database is created. All of my text fields are inserting fine. The three image fields (input type="file") do not have a corrosponding field in mySQL yet because I'm unsure as to whether they need to be VARCHAR for the URLS or something else.

Here's my form inputform.php:

<html>
<head>
<title>inputform</title>
<link rel="stylesheet" href="productpage.css" type="text/css" />
</head>

<body>
<h1>inputform</h1><p>Please capitlize your input.</p>

<form action="inputdata.php" method="post">
<fieldset>
<ol>
<li>
<label for="make">Make:</label>
<input type="text" name="make" id="make" class="text">
</li>
<li>
<label for="model">Model:</label>
<input type="text" name="model" id="model" class="text">
</li>
<li>
<label for="price">Price:</label>
<input type="text" name="price" id="price" class="text">
Please input numeric values only. The '€' symbol is automatically appended.
DO NOT USE COMMAS!
</li>
<li>
<label for="engine">Engine:</label>
<input type="text" name="engine" id="engine" class="text">
</li>
<li>
<label for="body">Body Type:</label>
<input type="text" name="body" id="body" class="text">
</li>
<li>
<label for="transmission">Transmission:</label>
<input type="text" name="transmission" id="transmission" class="text">
</li>
<li>
<label for="year">Year:</label>
<input type="text" name="year" id="year" class="text">
Please enter a four digit number.
</li>
<li>
<label for="colour">Colour:</label>
<input type="text" name="colour" id="colour" class="text">
</li>
<li>
<label for="mileagem">Mileage M:</label>
<input type="text" name="mileagem" id="mileagem" class="text">
Please input numeric values only. The 'M' symbol is automatically appended.
</li>
<li>
<label for="mileagekm">Mileage KM:</label>
<input type="text" name="mileagekm" id="mileagekm" class="text">
Please input numeric values only. The 'KM' symbol is automatically appended.
</li>
<li>
<label for="owners">Owners:</label>
<input type="text" name="owners" id="owners" class="text">
Please input a single digit only.
</li>
<li>
<label for="doors">Doors:</label>
<input type="text" name="doors" id="doors" class="text">
Please input a single digit only.
</li>
<li>
<label for="location">Location:</label>
<input type="text" name="location" id="location" class="text">
</li>
<li>
<label for="info">Additional Info:</label>
<textarea name="info" id="info"></textarea>
</li>
<li>
<label for="image1">Image 1:</label>
<input type="file" name="image1" id="image1">
</li>
<li>
<label for="image2">Image 2:</label>
<input type="file" name="image2" id="image2">
</li>
<li>
<label for="image3">Image 3:</label>
<input type="file" name="image3" id="image3">
</li>
<li>
<input type="submit" class="submit">
</li>
</ol>
</fieldset>
</form>


</body>
</html>

Here is my style sheet productpage.css:

/* productpage.css */

/* style the surrounding fieldset border */
fieldset {
position:relative;
left:0px;
top:0px;
margin: 0 0 0 0;
padding: 0px;
border-style:inset;
width:1200px;
}

/* style the overall list, only necessary if more than one list is being used */
fieldset ol {

padding-top:20px;
list-style: none;
}

/* style the individual items within the list */
fieldset li {
padding-bottom:10px;
}

/* style labels and align them */
label {
float: left;
width:90px;
margin-right: 10px;
font-family:Trebuchet MS, Arial;
font-size:12px;
}

/* style text boxes */
input.text {
line-height:12px;
font-family:Trebuchet MS, Arial;
font-size:12px;
color:#999999;
border: 1px solid #888377;
width: 150px;
height:18px;
}

/* style dropdown */
.dropdown {
width: 70px;
}

/* style button and align it */
input.submit{
position:relative;
top:5px;
left:100px;
width:100px;
}

Here is my process form inputdata.php

<?php
// Pick up the form data and assign it to variables
$make = $_POST['make'];
$model = $_POST['model'];
$price = $_POST['price'];
$engine = $_POST['engine'];
$body = $_POST['body'];
$tranmission = $_POST['transmission'];
$year = $_POST['year'];
$colour = $_POST['colour'];
$mileagem = $_POST['mileagem'];
$mileagekm = $_POST['mileagekm'];
$owners = $_POST['owners'];
$doors = $_POST['doors'];
$location = $_POST['location'];
$info = $_POST['info'];

// Additional variables not posted from form
$now_datetime = date('Y-m-d h:i:s');
$ipaddress = getenv('REMOTE_ADDR');

// Connect to database
include("dbinfo.php");
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to establish a connection to database.");

// Insert into database
$query = "INSERT INTO test VALUES ('','$make','$model','$price','$engine','$body','$transmission','$year,','$colour','$mileagem','$mileagekm','$owners','$doors','$location','$info','$now_datetime','$ipaddress')";
mysql_query($query);

// Close connection
mysql_close();

// Redirect to confirmation page
header("Location: success.html");
?>

I know I could have the client upload the images via ftp and manually type in the url of the images into my database via my form but I want to minimise the task for them as much as possible. I want the url and my text fields to be posted and my images to be uploaded simutaneously.

I'm bummed out because I really want to figure out the majority of the code for myself so I could post it back to you guys to focus on validating the user input but I obviously I couldn't even get that far.

Here are the main tutorials that I researched but cannot implement:

http://www.reconn.us/file_uploading.html

http://www.webdeveloper.com/forum/showthread.php?t=101466

I would really apreciate any help with this (As you can tell I'm trying to avoid loading images directly to mySQL).

webmaster1 · November 16, 2008

I've made some progress.

So far I can get the image to write to a folder. I'll attempt to integrate it into my original form shortly. I'm going to post this for any fellow newbs that come across this thread via search engine. I know all of this may seem blatantly obvious for the experts but for newbs like me its a fantastic starting point.

inputform.php

<html>
<body><form action="inputdata.php" method="post"

enctype="multipart/form-data">

<label for="file">Filename:</label>

<input type="file" name="file" id="file" />

<br />

<input type="submit" name="submit" value="Submit" />

</form></body>

</html>

inputdata.php

<?php
if ((($_FILES["file"]["type"] == "image/gif")

|| ($_FILES["file"]["type"] == "image/jpeg")

|| ($_FILES["file"]["type"] == "image/pjpeg"))

&& ($_FILES["file"]["size"] < 20000))

{

if ($_FILES["file"]["error"] > 0)

{

echo "Return Code: " . $_FILES["file"]["error"] . "<br />";

}

else

{

echo "Upload: " . $_FILES["file"]["name"] . "<br />";

echo "Type: " . $_FILES["file"]["type"] . "<br />";

echo "Size: " . ($_FILES["file"]["size"] / 1024) . " Kb<br />";

echo "Temp file: " . $_FILES["file"]["tmp_name"] . "<br />"; if (file_exists("upload/" . $_FILES["file"]["name"]))

{

echo $_FILES["file"]["name"] . " already exists. ";

}

else

{

move_uploaded_file($_FILES["file"]["tmp_name"],

"upload/" . $_FILES["file"]["name"]);

echo "Stored in: " . "upload/" . $_FILES["file"]["name"];

}

}

}

else

{

echo "Invalid file";

}

?>

Once you make these two php pages you'll need to create a folder named upload in the same location as your two php files.

You also need to change the permissions of your newly created upload folder to allow public visitors to upload to site.

webmaster1 · November 16, 2008

I've now combined my original form with the image file upload feature.

inputform.php

<html>

<head>
<link rel="stylesheet" href="productpage.css" type="text/css" />
</head>

<body>

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

<fieldset>
   
    <ol>

    <li>
    <label for="make">Make:</label>
    <input type="text" name="make" id="make" class="text">
    </li>
    <li>
    <label for="model">Model:</label>
    <input type="text" name="model" id="model" class="text">
    </li>
    <li>
    <label for="price">Price:</label>
    <input type="text" name="price" id="price" class="text">
    </li>			
    <li>
    <label for="engine">Engine:</label>
    <input type="text" name="engine" id="engine" class="text">
    </li>
    <li>
    <label for="body">Body Type:</label>
    <input type="text" name="body" id="body" class="text">
    </li>
    <li>
    <label for="transmission">Transmission:</label>
    <input type="text" name="transmission" id="transmission" class="text">
    </li>
    <li>
    <label for="year">Year:</label>
    <input type="text" name="year" id="year" class="text">
    </li>
    <li>
    <label for="colour">Colour:</label>
    <input type="text" name="colour" id="colour" class="text">
    </li>
    <li>
    <label for="mileagem">Mileage M:</label>
    <input type="text" name="mileagem" id="mileagem" class="text">
    </li>
    <li>
    <label for="mileagekm">Mileage KM:</label>
    <input type="text" name="mileagekm" id="mileagekm" class="text">
    </li>
    <li>
    <label for="owners">Owners:</label>
    <input type="text" name="owners" id="owners" class="text">
    </li>
    <li>
    <label for="doors">Doors:</label>
    <input type="text" name="doors" id="doors" class="text">
    </li>
    <li>
    <label for="location">Location:</label>
    <input type="text" name="location" id="location" class="text">
    </li>
    <li>
    <label for="info">Additional Info:</label>
    <textarea name="info" id="info"></textarea>			
    </li>
    <li>
    <label for="file">Filename:</label>
    <input type="file" name="file" id="file" /> 
    </li>
    <li>
    <input type="submit" name="submit" value="Submit" />
    </li>

    </ol>

</fieldset>

</form>

</body>
</html>

inputdata.php

<?php

// CONDITION A IF: file type is jpg or gif and less than 20000/1024 

if ((($_FILES["file"]["type"] == "image/gif")
|| ($_FILES["file"]["type"] == "image/jpeg")
|| ($_FILES["file"]["type"] == "image/pjpeg"))
&& ($_FILES["file"]["size"] < 20000))

// CONDITION A THEN: 

  {

  // CONDITION B IF: errors are greater than 1

  if ($_FILES["file"]["error"] > 0)
  
  // CONDITION B THEN: an error return code is given

    {
    echo "Return Code: " . $_FILES["file"]["error"] . "<br />";
    }

  // CONDITION B OTHERWISE: upload the file to the folder

  else
    {
    echo "Upload: " . $_FILES["file"]["name"] . "<br />";
    echo "Type: " . $_FILES["file"]["type"] . "<br />";
    echo "Size: " . ($_FILES["file"]["size"] / 1024) . " Kb<br />";
    echo "Temp file: " . $_FILES["file"]["tmp_name"] . "<br />"; 

    // CONDITION C IF: file exists 
    
    if (file_exists("upload/" . $_FILES["file"]["name"]))

    // CONDITION C THEN: return an error message to the user  

      {
      echo $_FILES["file"]["name"] . " <b>already exists and HAS NOT BEEN UPLOADED! Please ensure that you have not already uploaded this file and/or its file name is unique.<b> ";
      }

   // CONDITION C OTHERWISE: upload the image and return a message that the file has been loaded successfully

    else
      {
      move_uploaded_file($_FILES["file"]["tmp_name"],
      "upload/" . $_FILES["file"]["name"]);
      echo "Stored in: " . "upload/" . $_FILES["file"]["name"];

        // AND INSERT URL INTO DATABASE

        // define variables
        $imgurl = "upload/" . $_FILES["file"]["name"];
        $make = $_POST['make'];
        $model = $_POST['model'];
        $price = $_POST['price'];
        $engine = $_POST['engine'];
        $body = $_POST['body'];
        $transmission = $_POST['transmission'];
        $year = $_POST['year'];
        $colour = $_POST['colour'];
        $mileagem = $_POST['mileagem'];
        $mileagekm = $_POST['mileagekm'];
        $owners = $_POST['owners'];
        $doors = $_POST['doors'];
        $location = $_POST['location'];
        $info = $_POST['info'];

        // Additional variables not posted from form
        $now_datetime = date('Y-m-d h:i:s');
        $ipaddress = getenv('REMOTE_ADDR');


      	// Connect to database
include("dbinfo.php");
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to establish a connection to database.");

// Insert into database
//$query = "INSERT INTO test VALUES ('','$make','','','','','','','','','','','','','','','','$imgurl')";
//mysql_query($query);

       // Insert into database
$query = "INSERT INTO test VALUES ('','$make','$model','$price','$engine','$body','$transmission','$year','$colour','$mileagem','$mileagekm','$owners','$doors','$location','$info','$now_datetime','$ipaddress','$imgurl')";
mysql_query($query);


      }

  // CONDITION B OTHERWISE CLOSE BRACKET BELOW

    }

// CONDITION A THEN CLOSE BRACKET BELOW

  }


else
  {
  echo "Invalid file. Please submit a .jpg or .gif file only";
  }
?>

As ye can see I placed comments in the above script as recommended by cunoodle2.

Next step: Figure out how to integrate two additional and simutaneous file uploads. ???

Sign In

Loading images to mySQL using a PHP form?

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information