Inserting an image path into a MySQL table using PHP

Question

I am trying to create a basic form to list inventory on a website using PHP and MySQL. I keep getting errors when I follow some of the guides here on stackoverflow. Any help would be greatly appreciated.

My question is: My insert statement keeps failing when I use it through the PHP form but when I do it through phpMyAdmin it works. How do I figure out where my error is and how do i solve it.

Form:

<form action="add.php" method="post" enctype="multipart/form-data">
Item Type: <input type="text" name="type"  /><br>
Description: <input type="text" name="description"/><br>
Price: <input type="text" name="price"  /><br>
Date: <input type="text" name="date" /><br>
Pic:<input type="file" name="image"> <br/>
<input type="submit" >
</form>

add.php:

<?php 
$type = $_POST['type'];
$desc = $_POST['description'];
$price = $_POST['price'];
$date = $_POST['date'];
$file = $_FILES['image']['tmp_name'];

 $image = addslashes(file_get_contents($_FILES['image']['tmp_name'])); 
$image_size = getimagesize ($_FILES['image']['tmp_name']);


$host = "localhost";
$user = "root";
$password = "";

$cnn = mysql_connect ( $host, $user, $password );
mysql_select_db('inventory');

$insert = mysql_query("INSERT INTO 'newitems' ('ID', 'ItemType', 'Description', 'Price', 'Date', 'Pic')VALUES ('','$type','$desc','$price','$date', '{$image}')");

if (!mysql_query($insert)) { 
    echo "Something went wrong! :(";
    echo '<img src="data:image/jpeg;base64,' . base64_encode( $image ) . '" />';
}


?>

Table Updated with Auto-Number

You forgot to explain the part where you need help. What is the problem? Did you get an error? Did your database blow up? Did the world end when you run this code? — Sherif, Nov 09 '15 at 00:40
not sure if these will solves you problem, whater it may be, but some things to keep in mine. 1) you are using the depreciated library `mysql` and should be using `mysqli`. 2) [SQL Injection](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) 3) you are assigning the ID `4` every time the query is ran, generally a table's ID should be unique. i would suggest altering your table schema to use [AUTO_INCREMENT](http://dev.mysql.com/doc/refman/5.7/en/example-auto-increment.html) as the default value of your id field — Memor-X, Nov 09 '15 at 00:47

score 0 · Answer 1 · edited Jun 20 '20 at 09:12

If your ID column in that schema has a UNIQUE constraint, then it's no wonder the query will fail the second time around since you're inserting every row with the same id (4). Use AUTO_INCREMENT instead and let the dbms assign the ID.

Important Information About Your Code

Also, it's probably crucial that you are aware of a number of critical issues in your code.

You are using a deprecated extension for talking to your mysql database
Your code is vulnerable to SQL injection by using addslashes
You really shouldn't be storing images in your database

Reasons why you shouldn't store binary data in your RDBMS

The filesystem is faster/better at storing binary data
You don't have to carry the blob data in PHP to get it to the user saves CPU and memory
Seperate the webhost and the dbhost, moving blobs back and forth over a database connection is going to be expensive in computation and bandwidth
Single point of failure (even with master/slave replication you are going to incur massive replication lag at scale) where there are much cheaper redundancy solutions like a CDN

Inserting an image path into a MySQL table using PHP

1 Answers1

Important Information About Your Code

Reasons why you shouldn't store binary data in your RDBMS