How to upload a photo to a mysql database?

Anything beyond the basics in using the LiveCode language. Share your handlers, functions and magic here.

Moderators: Klaus, FourthWorld, heatherlaine, robinmiller, kevinmiller

Post Reply
hsh_636
Posts: 31
Joined: Sun Mar 02, 2014 5:15 pm

How to upload a photo to a mysql database?

Post by hsh_636 » Tue Mar 11, 2014 9:39 pm

I'm trying to use the mobilePickPhoto "library" to upload photos from my simulator or device to mysql database, and I really don't know where to start.

splash21
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 369
Joined: Sun Dec 19, 2010 1:10 am
Location: UK
Contact:

Re: How to upload a photo to a mysql database?

Post by splash21 » Tue Mar 11, 2014 10:30 pm

What are your server's capabilities? LiveCode server, PHP, ...? Have you already created the database?

The following thread may help - there's a .lc server script to receive files and a stack with the code to send them...
http://forums.runrev.com/viewtopic.php? ... 144#p96810
LiveCode Development & Training : http://splash21.com

EssoAir
Posts: 52
Joined: Thu Feb 20, 2014 7:53 pm

Re: How to upload a photo to a mysql database?

Post by EssoAir » Wed Mar 12, 2014 6:36 pm

hsh_636 wrote:I'm trying to use the mobilePickPhoto "library" to upload photos from my simulator or device to mysql database, and I really don't know where to start.
If you're dealing with a text-only server, have the client app encode it in base64, send that string to the server, then decode it. Simple as that

hsh_636
Posts: 31
Joined: Sun Mar 02, 2014 5:15 pm

Re: How to upload a photo to a mysql database?

Post by hsh_636 » Thu May 08, 2014 10:37 pm

I know that my reply is insanely late, but I was busy doing other features of my app and deciding what database to use I ended using a mysql database hosted on a website that is managed by phpmyadmin can I use the function mobilePickPhoto to insert the uploaded image to mysql database?

Simon
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 3901
Joined: Sat Mar 24, 2007 2:54 am
Location: Palo Alto

Re: How to upload a photo to a mysql database?

Post by Simon » Fri May 09, 2014 2:43 am

Hi hsh_636,
can I use the function mobilePickPhoto to insert the uploaded image to mysql database?
Well no...
But I think you mean "use mobilePickPhoto and some other stuff"
Yes, I like EssoAir, use base64Encode to turn the data into a string and upload that to mysql. When you need to view it it's just base64Decode and it becomes an image again. :)

Simon
I used to be a newbie but then I learned how to spell teh correctly and now I'm a noob!

robertem
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 24
Joined: Tue Mar 19, 2013 2:58 pm

Re: How to upload a photo to a mysql database?

Post by robertem » Fri May 09, 2014 3:09 pm

If you actually want to but the photo in the database, you would need to encode it as the others have suggested but most hosts will not allow direct access to a MYSQL instance for security reasons.

Is there a reason you need to put the actual file in the database? Why wouldn't you upload the file via ftp or php/lc middleware and reference the file in a database field?

You may want to have a look at this thread, http://forums.runrev.com/viewtopic.php?f=11&t=19746, which includes a php script to upload a file. The database connection and insert is fairly straight forward, code can be found by googling something like "php mysql connect."

hsh_636
Posts: 31
Joined: Sun Mar 02, 2014 5:15 pm

Re: How to upload a photo to a mysql database?

Post by hsh_636 » Fri May 09, 2014 3:43 pm

Thank you all for all the help I will try all the options you mentioned and hopefully one of them will work out for me.

EddieLee
Posts: 23
Joined: Thu Oct 25, 2018 1:06 pm

Re: How to upload a photo to a mysql database?

Post by EddieLee » Fri Oct 26, 2018 4:54 pm

Hi all,

Im fairly new to livecode and i have a similar problem like this. I tried using the mobilepickphoto function to allow user to select a photo, afterwards i used a base64encode to insert it into the database. Can anyone let me know if this is the right way to do it? :D

Code: Select all

put the text of img "profilepicture" into pImg
   put base64encode (pImg) into pImage
   put "INSERT INTO `image` (`profileimage`) VALUES ('" & pImage & "')" into tSQL
   revExecuteSQL gConnectionID,tSQL
   if the result is a number then
      answer info "image submitted"
   else 
      answer error "failed to submit image"
   end if
This is the value that was uploaded to the database. (im using a varchar fieldtype)
/9j/4AAQSkZJRgABAQAAAQABAAD/2wBDAAgGBgcGBQgHBwcJCQgKDBQNDAsLDBkSEw8UHRof

On a side note, for the field type in database do i use varchar or blob ?
Would appreciate for any comments/advice.

Klaus
Posts: 11333
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

Re: How to upload a photo to a mysql database?

Post by Klaus » Sat Oct 27, 2018 1:33 pm

Hi Eddie,

1. welcome to the forum!

2. This is the right way to go. :-)
Since base64encode will convert BINARY data to TEXT data, varchar is a good choice for this.

3. Better start a new thread next time, even if you have found an ancient thread with the same topic. 8)


Best

Klaus

EddieLee
Posts: 23
Joined: Thu Oct 25, 2018 1:06 pm

Re: How to upload a photo to a mysql database?

Post by EddieLee » Tue Oct 30, 2018 11:31 am

Hi Klaus,

Thanks for the reply, really appreciate it. Im still new to this forum as well and i did not know where to go to create the forum, but i will figure it out eventually. For now, my insert code has no problem and i am able to insert it into my SQL database. However, the problem lies with retrieving the image. Here are the codes which i tried to retrieve it.

Code: Select all

put "SELECT `image` FROM `user` WHERE `email` ="' & email & '" into theQuery
   put revDataFromQuery(tab, cr, gConnectionID, theQuery) into tQuery
   put base64decode(tQuery) into tImageData
   set the text of img "profileimage" to tImageData
Is there anything wrong or anything i should include for the retrieval of image?

Klaus
Posts: 11333
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

Re: How to upload a photo to a mysql database?

Post by Klaus » Tue Oct 30, 2018 1:07 pm

Hi Eddie,

the code looks correct, but what does this mean:
However, the problem lies with retrieving the image
Can you be a bit more specific? Do you get an error?

You coud and should check everything that may go wrong:

Code: Select all

...
put "SELECT `image` FROM `user` WHERE `email` ="' & email & '" into theQuery
put revDataFromQuery(tab, cr, gConnectionID, theQuery) into tQuery

## Error when accessing database?
if tQuery begins with "revdberr" then
   answer "An error occurred:" & CR & tQuery
   exit to top
end if
put base64decode(tQuery) into tImageData
set the text of img "profileimage" to tImageData
...
Best

Klaus

EddieLee
Posts: 23
Joined: Thu Oct 25, 2018 1:06 pm

Re: How to upload a photo to a mysql database?

Post by EddieLee » Tue Oct 30, 2018 4:02 pm

Hi Klaus,

I have solved my own issue, surprisingly the issue was with the database as i have to make the field type into blob to be able to retrieve and upload the image successfully, if the field type is varchar, lifecode will crash and my application on my android will crash as well. Nevertheless, thanks for the advice that you gave me :D

Klaus
Posts: 11333
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

Re: How to upload a photo to a mysql database?

Post by Klaus » Wed Oct 31, 2018 3:10 pm

Hi Eddie,

oh, OK, again learned something new! :D
Glad you got it working.


Best

Klaus

EddieLee
Posts: 23
Joined: Thu Oct 25, 2018 1:06 pm

Re: How to upload a photo to a mysql database?

Post by EddieLee » Thu Nov 22, 2018 10:59 am

Hi ,

After importing the image, I would wish to put the image in a circle, however lifecode image tool only provides square shaped images. Any ways to bypass this?

Any help will be appreciated (:

Klaus
Posts: 11333
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

Re: How to upload a photo to a mysql database?

Post by Klaus » Thu Nov 22, 2018 1:23 pm

Hi Eddie,

could you please open a new thread for this?
So other users will be able to find my solution I am going to post then. :-)


Best

Klaus

Post Reply

Return to “Talking LiveCode”