Inserting URL in MySQL

Question

Im currently working on iOS application which populate videos in UITableView by using MySQL as rdbms and xampp as a web server. I am facing difficulty in inserting URL in MySQL table. To make it more clear, i have 3 column which is NewID, NewsTitle and NewsVid in the table. when i insert the URL by query such as:

INSERT INTO `dbnews`.`Newsfeed` (`NewsID`, `NewsTitle`, `NewsVid`) VALUES ('1', 'Today's news', LOAD_FILE('Macintosh HD/Applications/XAMPP/xamppfiles/htdocs/video/Crime.mp4'))

An error prompt "#1048 - Column 'NewsVid' cannot be null"

Hoping on providing a guide for me to tackle this issues since i am in process of learning.

pes502 · Accepted Answer · 2015-05-15 09:00:45Z

1

At first - you have got unescaped char ' in your SQL command - here: 'Today's news':

INSERT INTO `dbnews`.`Newsfeed` (`NewsID`, `NewsTitle`, `NewsVid`) VALUES ('1', 'Today's news', LOAD_FILE('Macintosh HD/Applications/XAMPP/xamppfiles/htdocs/video/Crime.mp4'))
                                                                                      ^ HERE

Edit your SQL to (you need to escape the ' with backslash \):

INSERT INTO `dbnews`.`Newsfeed` (`NewsID`, `NewsTitle`, `NewsVid`) VALUES ('1', 'Today\'s news', LOAD_FILE('Macintosh HD/Applications/XAMPP/xamppfiles/htdocs/video/Crime.mp4'))

UPDATE BASED ON COMMENTS:

And then you can try to store only link to file in database, no output of LOAD_FILE function. So your updated command will looks like:

INSERT INTO `dbnews`.`Newsfeed` (`NewsID`, `NewsTitle`, `NewsVid`) VALUES ('1', 'Today\'s news', 'Macintosh HD/Applications/XAMPP/xamppfiles/htdocs/video/Crime.mp4')

You can use LOAD_FILE when you will read data from database, something like:

// DB CONNECT
// DB READ DATA
LOAD_FILE($row['NewsVid']);

edited May 15, 2015 at 9:00

answered May 15, 2015 at 8:06

pes502

1,5973 gold badges17 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Jacob90 Over a year ago

Thanks for response, i did edit my query as you have mention. I'm still getting a same error '#1048 - Column 'NewsVid' cannot be null'

pes502 Over a year ago

Ok, now you need to check, if LOAD_FILE('Macintosh HD/Applications/XAMPP/xamppfiles/htdocs/video/Crime.mp4') returns some value. So what the function LOAD_FILE returning?

Jacob90 Over a year ago

As I'm still new and learning, i understood that by LOAD_FILE i able to play my video file in browser by using php. Do point my mistake :)

pes502 Over a year ago

Yes, but the MySQL error says: Column 'NewsVid' cannot be null and when you looks to your SQL command, you can see, that you adding output of LOAD_FILE('Macintosh HD/Applications/XAMPP/xamppfiles/htdocs/video/Crime.mp4') into NewsVid column. So maybe try to add Macintosh HD/Applications/XAMPP/xamppfiles/htdocs/video/Crime.mp4 only and then use LOAD_FILE when you will read data from database.

Collectives™ on Stack Overflow

Inserting URL in MySQL

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related