I am working on a test project that involves storing an image into a mysql database. I have watched this tutorial video on youtube that is using the outdated mysql functions and not PDO to retrieve and send data. I have tried converting the mysql queries into PDO but I am failing in some areas. Does anybody have an idea as to where I’m going wrong?
I have 2 php docs. index.php and get.php.
The code for index.php is as follows:
<html>
<head>
<title> Upload an Image </title>
</head>
<body>
<form action="index.php" method="post" enctype="multipart/form-data">
File: <input type="file" name="image" /> <input type="submit" value="Upload" />
</form>
<?php
include 'inc/db.php';
// file properties
if (isset ($_FILES['image'])) {
$image = addslashes (file_get_contents ($_FILES ['image'] ['tmp_name'])); // get file contents
$image_name = addslashes ($_FILES ['image'] ['name']); // get image name
$image_size = getimagesize ($_FILES ['image']['tmp_name']); // get image size
if ($image_size == FALSE) // check if file is image
echo "That is not an image.";
//upload data if file is image
else {
$sql = "INSERT into store (name,image) VALUES (':image_name',':image')";
$stmt = $pdo->prepare($sql);
$stmt->bindValue (':image_name', $image_name);
$stmt->bindValue (':image', $image);
$stmt->execute();
//fetch id of last uploaded file
$sql = "SELECT id from store order by id desc limit 1";
$results = $pdo->query($sql);
$data = $results->fetch(PDO::FETCH_OBJ);
//using while loop to store the id in a variable
while ($data = $dataitem) {
$lastid = $dataitem->id;
}
//using seperate file get.php to obtain image using the variable $lastid
echo "image uploaded. Your image: <img src=get.php?id=$lastid/>";
}
}
else {
echo 'Please select an image';
}
?>
</body>
</html>
The code for get.php:
<?php
include 'inc/db.php';
//getting id from the index page
$id = $_REQUEST ['id'];
//selecting image by id and storing it in $id
$sql = "SELECT * from store where id=$id";
$image = $pdo->query($sql);
$image = $image->fetch(PDO::FETCH_ASSOC);
$image = $image ['image'];
header ("Content-type: image/jpeg");
echo $image;
?>
Error I’m getting:
Notice: Undefined variable: dataitem in C:\xampp1\htdocs\imagestut\index.php on line 40
Notice: Undefined variable: lastid in C:\xampp1\htdocs\imagestut\index.php on line 45
image uploaded. Your image: (not getting displayed)
When working with PDO you should always make use to try-catch blocks (exceptions), the example below usses a query from your post (and assumes that there will be two or more fields in the result set and that more then one record is expected to be selected:
try {
$stores=array(); // The array that you'be be loading the result set into
$sql="
SELECT
*
FROM
store
WHERE
id = :store_id
";
/*
Unless you need every field, you should just
list the required fields in the SELECT clause instead of using *
*/
$stmt = $this->db->prepare($sql);
$stmt->bindParam(':store_id', $id);
$stmt->execute();
$stores = $stmt->fetchAll(PDO::FETCH_ASSOC);
}
catch (PDOException $e) {
echo "
<p>Something went wrong with the query!</p>
<p>Query that failed: $sql </p>
<p>Reason: ".$e->getMessage()."</p>
";
}
You’re probably not getting any records returned by the result set or you’ve got an error in the query.
There is no need to use addslashes, when you’re using prepared statements. You should use prepared statements whenever you’re using user submitted data (which should be sanitized first).
There is no valid reason to ever use $_REQUEST, if the data is coming from a query string (the URL), then you should always use $_GET and if the data is coming from a form, then you should always use $_POST
This message is self-explanatory.
The problem is here:
while ($data = $dataitem) {
$lastid = $dataitem->id;
}
What is $dataitem? That variable was never initialized in code before. And why do you assign it to $data? And why there is while loop, if you select only one row?
Try to replace this loop with single line:
$lastid = $data->id;
You fetch query result in $data, so i guess you should look for id there.
Loops are for iterating through a collection of items (array elements). When you select one row you get single object, so no loop is required. But when you select more than one row (using fetchAll() instead of fetch()) the result you get is array (collection), where each element contains object with single row data. In that case you should use loop, if you want to perform something with each object individually (say, print it)
My advice - never store images in the database. It’s a really bad practice. Files must be stored as files. In the database you can have only paths to these files.
While for very small systems only storing them outside the database can work, for larger systems storing a copy of the image in the database itself becomes critical.
Usually the best alternative is to store the image in both places - outside the database for direct display to users and inside the database to allow the image to be easily recreated and for backup purposes.
I recently lost some files from one site I have when I moved where the pages were running from. I uploaded all of the fiiles for the new script and all the image files etc from my backup copy to the new location. The database references were the same so it was still accessing the same database. What I had overlooked prior to deleting the old copy of the site was that there were several files that had never been backed up. The database gets backed up at least weekly so if those files had copies stored in the database they could have been easily recreated. Unfortunately I haven’t finished implementing that part of the process yet so tose files are gone for good.
Even with small systems having backup copies of files in the database can be useful. As the size of the system grows the importance of storing files in the database grows with it.
It is one argument. The database administrator will almost certainly ensure that proper database backups are being taken but that person has responsibility for the database - not any files. So lots of big companies backup their database and nothing else because All of the data is expected to be in the database where data belongs.
Probably the more important argument for storing copies of the files in the database is transaction processing.Only the copy included inside of the database will be properly included in any transaction processing - it is the only way you would be able to recreate the file if the transaction that deletes it fails in a later step and needs to be rolled back.
There are about half a dozen or so reasons for storing files in the database given in the book “SQL antipatterns” where not storing the files in the database is considered to be an antipattern.
Prior discussions on this forum as to whether or not to store files in the database presented lots issues that can occur if you don’t store them in the database as well as lots of reasons for keeping copies outside the database - the final conclusion being that having both is the best alternative.