Need guidance on saving an image to mySQL

Question

I've been piecing a few things together but I've hit a bit of a snag. This is my first attempt at saving an image to a server. I'm including the components I have worked up so far. cs script, php file and my table setup. Right now I'm saving to server but I'm getting empty entries. Can someone show me how to set things up properly in my code?

How do I transfer 'bytes' (byte array in C# file) to the server and save it in the 'image' blob? How do I pass the form.AddBinaryData paramaters and store them in the table that I've created? Based on the code I have written above, can someone tell me why this isn't writing the screenshot to the MySQL DB? Could someone point out my errors?

---------- C# ----------

     public string screenShotCGIURL= "https://locomoku.com/projects/samnoble/animalmatch/php/upload.php";
 int yourInt;

 public void TakeScreenshot() 
 { 
     StartCoroutine("UploadPNG"); 
 }

 IEnumerator UploadPNG()
 {
     // We should only read the screen buffer after rendering is complete
     yield return new WaitForEndOfFrame();
     
     // Create a texture the size of the screen, RGB24 format
     int width = Screen.width;
     int height = Screen.height/2;
     Texture2D tex = new Texture2D(width, height, TextureFormat.RGB24, false);
     
     // Read screen contents into the texture
     tex.ReadPixels(new Rect(0, 0, width, height), 0, 0);
     tex.Apply();
     
     // Encode texture into PNG
     byte[] bytes = tex.EncodeToPNG();
     Object.Destroy(tex);

     // Create a Web Form
     WWWForm form = new WWWForm();
 
     // Set file name
     string name = "animalmatch_" + System.DateTime.Now.ToString("MM-dd-yyyy_HH-mm-ss");
     name = name+".png";
     Debug.Log("The image name is " + name);

     // Calculate image size
     int size = tex.GetRawTextureData().Length;
     Debug.Log("The image size is " + size);

     // Upload to a cgi script
     WWW upload = new WWW(screenShotCGIURL, form);

     form.AddField("frameCount", Time.frameCount.ToString());

     form.AddField("image_id", "");
     form.AddField("image_type", "image/png");
     form.AddBinaryData("image", bytes, name, "image/png");
     form.AddField("image_size", size);
     form.AddField("image_name", name);

     yield return upload;

     Debug.Log(upload);

     if (!string.IsNullOrEmpty(upload.error)) {
         print(upload.error);
     }
     else {
         print("Finished Uploading Screenshot");
     }

 }

---------- PHP ----------

 $db_host         =     "localhost";     // MySQL Host Name.

 $db_user         =     "********";     // MySQL User Name.

 $db_password     =     "********";     // MySQL Password.

 $db_name         =     "********";     // MySQL Database Name.

 
 $imgData = file_get_contents($filename);
 $size = getimagesize($filename);
 
 mysql_connect("$db_host", "$db_user", "$db_password");
 mysql_select_db ("$db_name");
 
 $sql = "INSERT INTO screenshot
     (image_id ,image_type ,image, image_size, image_name)
     VALUES
     ('', '{$size['mime']}', '{$imgData}', '{$size[3]}', 
      '{$_FILES['userfile']['name']}')";
 
 mysql_query($sql);

----------mySQL ----------

 image_id        tinyint(6)  not null default '0',
 
 image_type      varchar(25) not null default '',
 
 image           largeblob   not null,
 
 image_size      varchar(25) not null default '',
 
 image_name      varchar(50) not null default ''

Answer 1

I ran into the exact same problem when trying to upload .png from Unity to my database, but I found a way to solve it. Bear with me that I am new to this stuff so I am not sure if this is the perfect solution but it works:

Your C# script looks fine. Uploading the -png with the AddBinaryData looks similar to what I did. However, the entries never listed properly in the db. This is because what we get from the PHP-script's $_FILES['image'] is an array with the following attributes:

 $_FILES['userfile']['name']
 $_FILES['userfile']['tmp_name']
 $_FILES['userfile']['size']
 $_FILES['userfile']['type']

None of these attributes contain the content as we are looking for, right, only string of names and types and the like. So we have to open a file stream to extract our actual content, our png image.

Add the following to your PHP code:

 $tmpName  = $_FILES['image']['tmp_name'];
 
 $fp      = fopen($tmpName, 'r');
 $content = fread($fp, filesize($tmpName));
 $content = addslashes($content);
 fclose($fp);

Then add the $content variable in your SQL query as shown below:

 $query = "INSERT INTO screenshot (Image) VALUES ('$content')";
 mysqli_query($conn, $query) or die('Error, sql query failed');

This page and some whisky helped me solve it. Hope it can help you too if my solution is not an exact match to what you were looking for.

Good luck!

PS: I see you use your database connection settings in your php file. Many sites like W3schools and such still keep this up but if you are unlucky the information may leak.

You could replace that code with this:

 $config = parse_ini_file('../config/config.ini');

Then keep a config.ini in another inaccessible directory looking like this:

 [database]
 servername = ***
 username = ***
 password = ***
 dbname = ***

PHP have good support for ini-files. To make the directory inaccessible in an Apache environment you cold add a .htaccess script saying simply:

 Deny from all

DS

Answer 2

Answer by nisovin · Jul 21, 2015 at 05:48 AM

I believe you need to move your form.AddField() calls before you do the WWW call.

Add comment · Share

Need guidance on saving an image to mySQL

2 Replies

Your answer

Follow this Question

Related Questions