B4J Tutorial Updated MySQL & PHP example

Before you start: You need some experience with php, Apache Servers, OkHttpUtils, Lists, Maps and JSON structures.

This is an updated example of how to

- communicate with a apache server via OKHttpUtils calling a php script
- doing MySQL data requests (Insert, Update and Delete) using prepared sql statements
- working with the data like list's & maps (= arrays in php) in both direction (I use this contellation combined with encryption and Base64 a lot in my apps)
- using resumable subs with parameters

It's a simple example It doesn't check any server responses (you can add that of course as the script is returning structured responses as a list)

What you need;


A running Apache/PHP environment

Test: I use XAMPP: https://www.apachefriends.org/de/index.html Just install the package and start the Apache and MySQL process. Don't forget to enable the firewall otherwise your app will be blocked.

Prod: Same thing (almost). I use a cheap hosted server which has MySQL and PHP. Just change the script. Please note that this is a BASIC script to show how it works. You need to add much more security. Don't just run that script in production! Don't think "maybe"


MySQL

I've added a *.sql script. Just run it in the MySQL-admin UI of your php installation. It creates the database and the needed table.

PHP

The script is included, too. Just copy the folder to Apache's htdocs folder.


IP address

Inside the B4J app: Set it to the pc's ip address where your apache & MySQL is running. Can be on the same machine as your B4J-app is running.
PHP: It's localhost (because the Apache is running on the same machine as MySQL here). In production your provider will have all paramaters for you.


Can I use the code in B4A?

Yep. Just copy the code. Adapt it slightly (e.g. use a service for the server calls).


More infos

PHP/MySQL: https://www.w3schools.com/php/php_mysql_prepared_statements.asp

 

[Didier9]

Very useful, thank you!

In the ShowPersons function, make the following change to have the fields properly sized:

    PersonsTV.SetColumnWidth( 0, 50 )
    PersonsTV.SetColumnWidth( 1, 200 )
    PersonsTV.SetColumnWidth( 2, 100 )
    PersonsTV.SetColumnWidth( 3, 425 )

 

[KMatle]

Thanks. Quite an old example which needs an update anyway

 

[Didier9]

I have added search. That is working and I will upload an update tonight

 

[Didier9]

I have added the capability to search for records. If the search for an exact match does not return any record, a second search is performed looking for a LIKE match (this happens in the PHP code).
All of the original comments from KMatle still apply.

 

[Johnson Samuel]

It is a good example . Can you please tell me how to send image from b4J to php and then to mysql blob field. I have used your createmap method but it did not work. Other string data are working ok but not image. I have image in the ImageView. Kindly help

 

[Didier9]

I am on the road for another week and just have my phone until then. I will look at it when I come back. Feel free to ping me via private message if you do not see anything in a couple of weeks.

 

[KMatle]

It is a good example . Can you please tell me how to send image from b4J to php and then to mysql blob field. I have used your createmap method but it did not work. Other string data are working ok but not image. I have image in the ImageView. Kindly help

Read it into a byte array and convert it to a base64 string. Then you don't need a blob. Very easy.

 

[Johnson Samuel]

Can you help me with an example, to be used in B4J?
1. I want to upload an image in the blob field via PHP, from B4J Desktop App.
2. I also would like to retrieve the blob image in B4A mobile App thru PHP
Thanks in Advance.

 

[Didier9]

Read it into a byte array and convert it to a base64 string. Then you don't need a blob. Very easy.

What's wrong with using a blob? For pictures, a base64 converted string has got to take more space than the original binary.
Honest question, I have no started looking into it and I proposed to look at it more as a learning exercise for me than anything

I have written code to do batch updates to a MySQL database through php a while back (unfinished project: http://s230267000.onlinehome.us/hamlookup/index.php) so I think I can do this fairly easily but I have never dealt with image files.

Anyhow, I think he is looking for a turnkey solution, including the php and client code to upload the picture to the server in the first place so the way the data is stored in the database is probably not the most arduous issue.

 

[Johnson Samuel]

Thanks Didier9 for your reply. I have uploaded the image to a file rather than in database.

 

[nwhitfield]

On my main project, a social site, we have around 4000 members, each of whom has various profile photos, including preview images that are used in displaying stuff like grids of members online. In the previous version of the site, I stored these in the database as BLOBs, but experimenting with it showed that, frankly, the fastest way to access them was as an image file.

If you're fetching the rest of the info from the database, you can store the path to the file, or you could do something like use a unique id to create a path name. So, for example, my site is presently set up to have a five digit member id, and then each digit of that is part of the path, with up to ten members' images stored in a single directory.

So, the thumbnail for member id 3555 is in members/0/3/5/5/thumbnail-3555.jpg; easy to get from a member id to that with something like this:

'memberid is an integer
Dim pathinfo as String = NumberFormat(memberid,5,0)

Dim imageURL as String = "https://my.server.com/members/" & pathinfo.charAt(0) & "/" & pathinfo.charAt(1) & "/" & pathinfo.charAt(2) & "/" & pathinfo.charAt(3) & "/thumbnail-" & memberid & ".jpg"

You can, of course, put everything just in one massive directory, but if there are multiple files associated with users, it can become very unwieldy; splitting it up like this makes it much easier to find something if you need to do manual testing/debugging/editing.

If you want to add a layer of security, so people can scrape images easily, move the image files out of the web server's directory tree, and create a PHP script to access them, which does whatever validation you want, before working out the path, and sending the file using the php readfile function. Then you might simply request a thumbnail using a URL like

https://my.server.com/gethumb.php?id=3555&sessionkey=45612362324dad23

 

[Didier9]

Thank you nwhitfield, this is a reasonable approach. No point in storing the image in the database since the main purpose of the database is to search and you can't search much in image data... But that was the op's original request.

Regarding accessing the files indirectly through php, I already do that in my Manuals pages as I have found that a number of users in east Asia were downloading everything in sight multiple times a week, which led me to using over 2 TB of data every month (I have a site ko4bb.com with almost 28,000 technical manuals in pdf). What I do actually is create a symlink to the file to be downloaded. The symlink is accessible by the web server even though the original file is not. That way, the download does not actually go through the php code, the documents are downloaded directly by Apache using the symlink which is faster and uses fewer resources. When the download is complete, I remove the symlink. That site does not use MySQL, it searches through an index updated every time new files are uploaded (using grep).

 

[Didier9]

Read it into a byte array and convert it to a base64 string. Then you don't need a blob. Very easy.

Hi KMatle,
I found your example (https://www.b4x.com/android/forum/t...a-php-script-with-x-parameters.60109/#content) describing just that and indeed it is very simple and works well.
Particularly I like being able to send the file data and metadata in the same transaction with json.
Thank you! I am making progress.

 

[DonManfred]

1. If you use job.PostMultipart you can send fileuploads together with any info you want.
2. Remember that you are using okhttputils and can use special header you send together with the request. On php-side you should be able to extract the header (need to parse the info by yourself though).

 

[KMatle]

In the meantime I have some sort of a standard for security reasons and to reuse code. I only send AES256 encrypted, signed POST requests containing all the data (even files). I don't use any headers anymore. My filesizes are quite small (usually some pdf's for customers which are <250 KB).

This is just a comment. Often you just need uploading big files or so via multipart which is - of course - absolutely ok.

 

[Didier9]

1. If you use job.PostMultipart you can send fileuploads together with any info you want.
2. Remember that you are using okhttputils and can use special header you send together with the request. On php-side you should be able to extract the header (need to parse the info by yourself though).

The php side was not the problem, the problem was the PC side. My only experience with file upload was through the browser. I have done quite a bit of work sending data both ways between microcontrollers and web servers using POST and GET (AES encrypted and clear) also but the apparent simplicity of sending a file through the browser fooled me. The magic rests in these parameters: type="file" and enctype="multipart/form-data". Turns out the browser does convert the file into a base64 or similar encoding anyways, so there was nothing to save trying to get around that. Also doing the base64 decoding on the php side with a single line of code does not hurt...

 

[Didier9]

In the meantime I have some sort of a standard for security reasons and to reuse code. I only send AES256 encrypted, signed POST requests containing all the data (even files). I don't use any headers anymore. My filesizes are quite small (usually some pdf's for customers which are <250 KB).

This is just a comment. Often you just need uploading big files or so via multipart which is - of course - absolutely ok.

I can see that adding AES to your routines and making it standard is a good idea and as long as there are no performance implications, it's all good.
I am not sure what the op wants to do though and I do not intend to get into this for this example but I can see the value as a personal exercise later.

I have used AES Rijndael in a microcontroller project because someone had reverse engineered one of my products by getting into the serial data stream between a microcontroller and the associated embedded WiFi module, so I encrypted end to end and that fixed the problem by turning this idiot's clones into bricks. That was satisfying... Yes, I appreciate the value of encryption! Yes you can do AES Rijndael in an 8 bit microcontroller, the biggest issue is to get good entropy so that your key is not too easy to break.

 

[Didier9]

I have updated the example to illustrate one way to upload/download/attach pictures to records.

It is largely based on the original code. I have added the server's responses in the Log. Clicking on "Debug php code" will force the php module to return more complete error info. This would be error messages coming back from the database as long as php does not crash. If php crashes (because of coding errors in the personsdb3.php module), error code pretty much says "php crashed" and you need to be logged into the server to view the error log from apache itself to see what the error was. That can be tedious. On the other hand, detailed error messages can give a lot of information to someone trying to break into your system so it is good practice to remove debug code from final products.

If a record has a picture associated with it, the picture will be displayed when clicking on the record. From my quick testing on my home cable internet access, files <100k show up almost immediately, bigger files (MB region) have an appreciable and annoying delay (my server is not local). YMMV. The example code puts no limit on file size. If such a hard limit was set, it should be in the php code with a warning in the B4J code when selecting a large file.

The way picture files are handled is a little different from other record information:
1) picture files are stored outside the database
2) the database contains the file name and path to the picture (path implementation is incomplete, example works though)
3) pictures are downloaded directly from the server (you have to know the file name and path but if you have those, you do not need to go through the php code to download the picture. That would be a severe privacy issue in any end user application but since this example makes no attempt to validate users, it's a moot point.)
4) when using the Delete Picture button, only the references to the picture in the database is deleted, the actual picture file is not deleted. I leave this as an exercise for the reader. That should be handled in the php code using the unlink() function.
5) All pictures are stored in the same folder. That would be a problem if multiple pictures of the same name are uploaded for different records. There is a forum message above from nwhitfield showing one way to do it. Another way (not user dependent) which I may add to this example later would be to create a folder with a time stamp when the picture is stored. This time stamp can be stored in the database in the pfolder field. Since Unix time stamps have 1mS resolution, you would be unlikely to have two folders with the same time stamp containing two files of the same name. A complete resolution of this issue is left as an exercise for the reader.

Even though this is in the Tutorial section, my knowledge of MySQL and php is definitely not expert level so be gentle in your comments

 

[nwhitfield]

There are a few other things you can do with uploaded files; some people give impossibly long filenames, which might overflow the field in the database. Others have an annoying habit of including spaces, or - even worse - things like asterisks - in filenames. This PHP snippet does a few things:

First, it strips off the extension - we can set that later from the MIME information.
Then it strips out problematic characters, converting them all to an underscore.
If the resulting name is more than 46 chars long (because we have to add the . and three more for the extension), then a new name is calculated, based on other parameters passed, the time, and a random number - because you might just get two files uploaded at the same time.
Then the file is moved to the appropriate location:

       // fix the name, removing spaces and full stops and making it all lower case
       $imagename = preg_replace('/\.(jpg|png|jpeg)/i','',$_FILES['imageFile']['name']) ;
       $imagename = strtolower(preg_replace('/[\s\.+\(\)\&\?\#\*]/','_',$imagename)) ;
       
       // we only allow filenames up to 50 chars; so if it's > 46 without extension, we rename
       // this saves having really long names that over-run database fields
       
       if ( strlen($imagename) > 46 ) {
           // new name will be made of content path (13), contentid(4), time(10), and random integer (3)
           $imagename = $_REQUEST['contentpath'] . $_REQUEST['contentid'] . '-' . time() . '-' . rand(100,999) ;
       }
       
       if ( $_FILES['imageFile']['type'] == 'image/png' ) {
           $ext = '.png' ;
       } elseif ( $_FILES['imageFile']['type'] == 'video/mp4' ) {
           $ext = '.mp4' ;
       } else {
           $ext = '.jpg' ;
       }
       move_uploaded_file($_FILES['imageFile']['tmp_name'], $destination . '/' . $imagename . $ext ) ;