Can't insert data to MySQL remote server via PHP script from Android

Question

I'm trying to insert data into two database tables from Android via a PHP script. I managed to load something from database, so permissions and database seem ok, but this code fails even though I think I have followed exactly what is presented in some examples.

These are the tables:

CREATE TABLE `sales` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order_date` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `status` enum('Waiting', 'Accepted', 'Production','Transport', 'Finalised', 'Canceled') NOT NULL DEFAULT 'Waiting',
  `user_id` varchar(16) NOT NULL,
  `pizza_id` int(11) NOT NULL,
  `pizza_size` tinyint(4) unsigned NOT NULL,
  `income` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `pizza_id` (`pizza_id`),
  CONSTRAINT `sales_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`login`) ON UPDATE CASCADE,
  CONSTRAINT `sales_ibfk_2` FOREIGN KEY (`pizza_id`) REFERENCES `pizzas` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8

CREATE TABLE `deliveries` (
  `order_id` int(11) NOT NULL,   //sale id actually
  `adress` varchar(50) NOT NULL,
  `client_name` varchar(35) DEFAULT NULL,
  `telephone` varchar(20) DEFAULT NULL,
  `cost` decimal(10,2) NOT NULL DEFAULT '0.00',
  UNIQUE KEY `order_id` (`order_id`),
  CONSTRAINT `delivery_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `sales` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

This is the .php script that I wrote to insert data to these tables:

<?php
mysql_connect("www.db4free.net","user","password") or die(mysql_error());
mysql_select_db("database");

$user = $_POST['user'];
$pizzaID = $_POST['pizzaID'];
$pizzaSize = $_POST['pizzaSize'];
$income = $_POST['income'];
$address = $_POST['address'];
$clientName = $_POST['clientName'];
$telephone = $_POST['telephone'];

$time = time();                 //I want to pass current timestamp
$SQLInsertSale = "INSERT INTO sales VALUES(Null, '{$time}', 'Waiting', '{$user}', '{$pizzaID}', '{$pizzaSize}', '{$income}');";
$someResult = mysql_query($SQLInsertSale);

$SQLLastID = "SELECT LAST_INSERT_ID() AS id;";
$result = mysql_query($SQLLastID);
$row = mysql_fetch_assoc($result); 
$lastID = $row['id'];


$SQLInsertDelivery = "INSERT INTO deliveries VALUES('{$lastID}', '{$address}', '{$clientName}', '{$telephone}', 0.0);";
$someResult = mysql_query($SQLInsertDelivery);

mysql_close();
?>

and finally this is the code I run on android (I call this method inside doInBackground method of AsyncTask):

public static void sendData(String user, int pizzaID, int pizzaSize,  double income,
                         String address, String clientName, String telephone){

        HttpClient httpclient = new DefaultHttpClient();
        HttpPost httppost = new HttpPost("http://www.myservice.byethost33.com/insertOrder.php");

        try {
            // Add your data
            List<NameValuePair> nameValuePairs = new ArrayList<NameValuePair>(7);
            nameValuePairs.add(new BasicNameValuePair("user", user));
            nameValuePairs.add(new BasicNameValuePair("pizzaID", String.valueOf(pizzaID)));
            nameValuePairs.add(new BasicNameValuePair("pizzaSize", String.valueOf(pizzaSize)));
            nameValuePairs.add(new BasicNameValuePair("income", String.valueOf(income)));
            nameValuePairs.add(new BasicNameValuePair("address", address));
            nameValuePairs.add(new BasicNameValuePair("clientName", clientName));
            nameValuePairs.add(new BasicNameValuePair("telephone", telephone));

            httppost.setEntity(new UrlEncodedFormEntity(nameValuePairs));
            // Execute HTTP Post Request
            HttpResponse response = httpclient.execute(httppost);

        } catch (ClientProtocolException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

I'm new to android and php, but tell me if something isn't clear and I will try to explain.

Answer 1

It turned out that it was simple php language issue and I wass passing wrong thing into timestamp field. I should be using now().

$SQLInsertSale = "INSERT INTO sales VALUES(Null, now(), 'Waiting', '$user', '$pizzaID', '$pizzaSize', '$income');";
$someResult = mysql_query($SQLInsertSale);

EDIT: As I learned little bit more about php, I started using PDO connection as you guys suggested to(with sanitizing and some error handling):

<?php
$connection = new PDO('mysql:host=db4free.net; dbname=database; charset=utf8', 'user', 'password');
$connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$connection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

$user = $_REQUEST['user'];
$pizzaID = $_REQUEST['pizzaID'];
$pizzaSize = $_REQUEST['pizzaSize'];
$income = $_REQUEST['income'];
$address = $_REQUEST['address'];
$clientName = $_REQUEST['clientName'];
$telephone = $_REQUEST['telephone'];

try {
$insertSale = "INSERT INTO sales VALUES(Null, now(), 'Waiting', :user, :pizzaID, :pizzaSize, :income);";
$saleStatement = $connection->prepare($insertSale);
$saleStatement->execute(array(':user' => $user, ':pizzaID' => $pizzaID, 'pizzaSize' => $pizzaSize, 'income' => $income));

$lastID = $connection->lastInsertId();

$insertDelivery = 'INSERT INTO deliveries VALUES(:lastID, :address, :clientName, :telephone, 0.0);';
$deliveryStatement = $connection->prepare($insertDelivery);
$deliveryStatement->execute(array(':lastID' => $lastID, ':address' => $address,':clientName' => $clientName, ':telephone' => $telephone));

$connection = null;
} catch(PDOException $ex) {
    print($ex->getMessage());
}
?>  

I also change code in android class, because previous was deprecated. I included also some error handling(I get log message what happend after insert statement if it went wrong):

public static void sendData(String user, Integer pizzaID, Integer pizzaSize,  Double income,
                                                      String address, String clientName, String telephone){
    try {
        URL url = new URL("http://www.pvice.byethost33.com/insertOrder.php");
        URLConnection connection = url.openConnection();
        connection.setDoOutput(true);

        StringBuilder data = new StringBuilder();
        data.append(URLEncoder.encode("user", "UTF-8") + "=" + URLEncoder.encode(user, "UTF-8"));
        data.append("&" + URLEncoder.encode("pizzaID", "UTF-8") + "=" + URLEncoder.encode(pizzaID.toString(), "UTF-8"));
        data.append("&" + URLEncoder.encode("pizzaSize", "UTF-8") + "=" + URLEncoder.encode(pizzaSize.toString(), "UTF-8"));
        data.append("&" + URLEncoder.encode("income", "UTF-8") + "=" + URLEncoder.encode(income.toString(), "UTF-8"));
        data.append("&" + URLEncoder.encode("address", "UTF-8") + "=" + URLEncoder.encode(address, "UTF-8"));
        data.append("&" + URLEncoder.encode("clientName", "UTF-8") + "=" + URLEncoder.encode(clientName, "UTF-8"));
        data.append("&" + URLEncoder.encode("telephone", "UTF-8") + "=" + URLEncoder.encode(telephone, "UTF-8"));

        OutputStreamWriter writer = new OutputStreamWriter(connection.getOutputStream());
        writer.write(data.toString());
        writer.flush();

        BufferedReader reader = new BufferedReader(new InputStreamReader(connection.getInputStream()));
        String line;
        while ((line = reader.readLine()) != null) {
            Log.e("LOGG", line);
        }

        writer.close();
        reader.close();
    } catch (IOException e) {
        e.printStackTrace();
    }

}