0

I have an iOS app using Swift 3 using a PHP file to receive objects but now working to update objects into the database.

I'm new to PHP so my question is, is the php code below correct? It's supposed to insert a variable from my app which is +1 points from a button to go through php and UPDATE my 'tests' table in mysql database. I already have a column in the database called testPop and the variable is also called testPop which is received in a json object array but I'm just trying to update the table.

So if row1 = id:1 testName:test1 testPop:0

I want testPop to update its value every time someones adds a point +1

Current PHP Code:

<?php

$host = "host";
$db = "db";
$user = "user";
$pass = "pass";

$connection = mysql_connect($host,$user,$pass);

// Guessing: Posting into MySQL Object
$id = $_POST["id"];

// Checking if connection can be established
if(!$connection){
    die("Connection Failed");
}
else
{
    // Selecting Database
    $dbconnect = mysql_select_db($db, $connection);

    // Check if it can connect to Database
    if(!$dbconnect){
        die("Unable to connect to Database");
    }
    else
    {
        $query = sprintf("UPDATE tests SET testPop=testPop+1 WHERE id = %d", $id);

        $resultset = mysql_query($query, $connection);

        echo "Successfully added";
        echo $query;
    }
}

?>

Swift 3 Code: Sending Data to database:

func sendData() {

    let postDataURL = "http://exampleip.com/Send.php"
    let url: NSURL = NSURL(string: postDataURL)!
    let request: NSMutableURLRequest = NSMutableURLRequest(url:url as URL)

    let bodyData = String(1)

    request.httpMethod = "POST"
    request.httpBody = bodyData.data(using: String.Encoding.utf8)
    NSURLConnection.sendAsynchronousRequest(request as URLRequest, queue: OperationQueue.main)
    {
        (response, data, error) in
        print(response!)

        if let httpResponse = response as? HTTPURLResponse {
            let statusCode = httpResponse.statusCode

            if statusCode==200 {
                print("Connection Successful")

            } else {
                print("Connection Failed (!200)")
            }
        }
    }
}

----- Added Additional Code for better understanding -----

New: MySQL Code

CREATE TABLE IF NOT EXISTS `tests` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`testName` varchar(255) DEFAULT NULL,
`testPop` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=13 ;

INSERT INTO `tests` (`id`, `testName`, `testPop`) VALUES
(1, 'Test 1', '0'),
(2, 'Test 2', '0'),
(3, 'Test 3', '0'),
(4, 'Test 4', '0'),
(5, 'Test 5', '0'),
(6, 'Test 6', '0'),
(7, 'Test 7', '0'),
(8, 'Test 8', '0'),
(9, 'Test 9', '0'),
(10, 'Test 10', '0'),
(11, 'Test 11', '0'),
(12, 'Test 12', '0');

Example: On how I receive from the database, using json. Don't know if this helps. Swift 3

func retrieveData() {

let getDataURL = "http://exampleip.org/tests.php"
let url: NSURL = NSURL(string: getDataURL)!

do {

    let data: Data = try Data(contentsOf: url as URL)
    jsonArray = try JSONSerialization.jsonObject(with: data, options: .mutableContainers) as! NSMutableArray

    // Looping through jsonArray
    for i in 0..<jsonArray.count {

        // Create Test Object
        let tID: String = (jsonArray[i] as AnyObject).object(forKey: "id") as! String
        let tName: String = (jsonArray[i] as AnyObject).object(forKey: "testName") as! String
        let tPop: String = (jsonArray[i] as AnyObject).object(forKey: "testPop") as! String

        // Add Test Objects to Test Array
        testArray.append(Test(testName: tName, andTestPop: tPop, andTestID: tID))

    }
}
catch {
    print("Error: (Retrieving Data)")
}

myTableView.reloadData()
}
23
  • what is $_POST["a"] supposed to be equal to? Commented Dec 5, 2016 at 22:48
  • 3
    If you're writing new code, please don't use the mysql_* functions. They are old and broken, were deprecated in PHP 5.5 (which is so old it no longer even receives security updates), and completely removed in PHP 7. Additionally, your code is wide open to SQL injection attacks. Use PDO or mysqli_* with prepared statements and parameter binding instead. See stackoverflow.com/q/12859942/354577 for details. Commented Dec 5, 2016 at 22:49
  • @bugfroggy thats for when I was using INSERT INTO VALUES not UPDATE Commented Dec 5, 2016 at 22:51
  • @Chris Thanks! Was that the only errors in the code? Changing the mysql_* functions to mysqli? Commented Dec 5, 2016 at 22:56
  • Your code is incorrect then because you're using it in your UPDATE query: $testPop = $_POST["a"]; $query = "UPDATE tests SET testPop='$testPop' WHERE $testPop"; Commented Dec 5, 2016 at 22:56

1 Answer 1

2
$id = $_POST["id"];
$query = sprintf("UPDATE tests SET testPop=testPop+1 WHERE id = %d", $id);
  • First, if you're incrementing a value, let the database do it for you. This will prevent a race condition: if/when 2 queries happen simultaneously, only one will be saved properly.

  • Second, don't add user-generated input directly into a query without escaping. Using sprintf with %d forces the id variable to an integer.

Sign up to request clarification or add additional context in comments.

5 Comments

How is this code? $query = sprintf("UPDATE tests SET testPop=$testPop+1 WHERE id = $id"); - Its the same except I changed it to $testPop or do I do it your way?
@BroSimple Since I don't know your application, I cannot tell you exactly what to do. But, your example still uses un-escaped user input. If you don't care about a race-condition and we assume testPop is an integer, you can do $query = sprintf("UPDATE tests SET testPop=%d+1 WHERE id = %d", $testPop, $id);
With the code I have, the database is not being updated. - I added additional code to the question, maybe you'll get a better understanding. Also updated the php code and sendData code.
Since you're +1'ing a test, my original answer seems to be the best. $query = sprintf("UPDATE tests SET testPop=testPop+1 WHERE id = %d", $id); That way, if testPop was 4, it is now 5.
Okay thanks trying this code but is the swift code for sending data to database, is it correct? How would I know the php code worked?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.