Hey guys,

So i've been working on a piece that will be hosted online.  The user will draw something, and this is added to a database of drawings.  I've got everything working; the user can draw, the data gets passed via php to my database, and the sketch can read from the database and draw the images on the screen.  

Despite it working, there is a big issue of loading time.  I'm entering the information into the DB as strings that contain information to re-draw the image (ie. mouse cords, size, color).  When I retrieve the info from the DB it can gather all the data and draw a handful of images in seconds, however when I upload the data it can take a few minutes for one drawing.  

I've been using the GET method in PHP and passing one line at a time in a for loop via loadStrings, and I think this is the issue.  I believe it is connecting to my DB every time, which makes it incredibly slow.  I tried just passing a giant string with all the data at once but it gave me a http 414 error (url too long).  Here is my php code, any suggestions would be greatly appreciated.  Thanks.

Code:

<?PHP

$dbhost = 'localhost';
$dbuser = 'username';
$dbpass = 'password';
$dbname = 'name';
$dbtable = 'info';

$dblink = mysql_connect("$dbhost", "$dbuser", "$dbpass")  or die ('I cannot connect to the database because: ' . mysql_error());


mysql_select_db("$dbname");


$id = $_GET['id'];
$xcord = $_GET['xcord'];
$ycord = $_GET['ycord'];
$brush = $_GET['brush'];
$alpha = $_GET['alpha'];
$color = $_GET['color'];


$sql = "INSERT INTO info (id,xcord,ycord,brush,color,alpha) VALUES ('$id', '$xcord', '$ycord', '$brush', '$color', '$alpha')"; 
$result = mysql_query($sql);

?> 

russell wrote on Feb 19^th, 2010, 9:17am:

In this case, switch to POST mode...

Indeed you should pass all data to PHP in one shot. Each time you call loadStrings(), your applet needs to resolve an URL, run your PHP script which connects to the database, and proceed one query. This is too slow if you have a lot of data.

You can pass the data though a POST request, or save it in a text file on the server, that will later be read by PHP. (I'm searching for more info about POST requests in Java...)

Here is a light example:

Processing
Quote:


PHP
Code:

<?php
mysql_connect('localhost', 'root', '');
$x[0] = mysql_real_escape_string($_POST['x'][0]);
$x[1] = mysql_real_escape_string($_POST['x'][1]);
mysql_query("INSERT INTO `my_database`.`my_table` (`x_0`, `x_1`) VALUES(Now(), '{$x[0]}', '{$x[1]}');"); 

Notice that the brackets in x[]=50&x[]=100 allows you to pass all values in a simple array.