I am a beginner in mysql and wanted to insert an image in mysql table cell but it is not updating.
update studentDetails set studentPic = LOAD_FILE('/images/picture.jpg') where studentID = 4;
Please help!
See Question&Answers more detail:osI am a beginner in mysql and wanted to insert an image in mysql table cell but it is not updating.
update studentDetails set studentPic = LOAD_FILE('/images/picture.jpg') where studentID = 4;
Please help!
See Question&Answers more detail:osA quick excerpt from the documentation of LOAD_FILE()
MySQL function:
LOAD_FILE(file_name)
Reads the file and returns the file contents as a string. To use this function, the file must be located on the server host, you must specify the full path name to the file, and you must have the
FILE
privilege. The file must be readable by all and its size less thanmax_allowed_packet
bytes. If thesecure_file_priv
system variable is set to a nonempty directory name, the file to be loaded must be located in that directory.If the file does not exist or cannot be read because one of the preceding conditions is not satisfied, the function returns
NULL
.
AS you can see, there are myriads of reason your call to LOAD_FILE()
fails:
The file must be present on the computer where the MySQL server runs. This is not always the same computer as the one where the PHP script runs (and this happens for security reasons). If they run on different computers then most probably you cannot use LOAD_FILE()
. Case closed.
In theory you could use FTP
or SFTP
to transfer the file on the computer where MySQL runs but for the same security reasons, you probably don't have access to that computer.
Assuming in your case both PHP and MySQL run on the same computer, /images/picture.jpg
is the path component of the URL, not a path on the file system. You can use dirname()
and __DIR__
to compose the path to the images
directory starting from the path of the current script.
The user you use to connect to the MySQL server must have the FILE
privilege. The privilege is granted to the user by a DBA.
The file must be readable by all. This is the easiest part. When you run ls -l
on the file, the last three symbols in the column of file rights must be rw-
or r--
(there is no point having the executable bit set).
The default value of max_allowed_packet
system variable is 4 MiB
but a DBA can change it. You can run SELECT @@max_allowed_packet FROM dual
to find out its current value.
If the secure_file_priv
system variable is set then the path you provide must be relative to this directory. Again, you can run SELECT @@secure_file_priv FROM dual
to find its current value.