CODEWITHSUNDEEP
phpmysqljsonswift
WokerHead
WokerHead

Reputation: 967

Correct PHP Code? - To Insert to MySQL

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()
}

Upvotes: 0

Views: 683

Answers (1)

ClickLabs
ClickLabs

Reputation: 557

$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.

Upvotes: 2

Related Questions