CODEWITHSUNDEEP
phpjquerymysqlajaxpdo
BRG
BRG

Reputation: 380

Update 2 tables second does not get updated using Ajax

Hello i am facing a very strange problem here. I have 2 tables joined in order to fetch data. Then after user edits data I want to update the tables separately. One of the tables gets updated and the other does not.

My Code:

$contact = $_POST['contact'];
    $address = $_POST['address'];
    $company = $_POST['company'];
    $activated = $_POST['activated'];
    $client_id = $_POST['client_id'];
    $level = $_POST['level'];
    $apikey = $_POST['apikey'];
    $phone = $_POST['phone'];


    $update_rest_clients = $handler->prepare("UPDATE rest_api_clients
        SET contact_person = ?, address = ?, company = ?,
            activated = ?, phone_number = ? WHERE client_id = ? ");

    $update_rest_clients->bindValue(1, $contact);
    $update_rest_clients->bindValue(2, $address);
    $update_rest_clients->bindValue(3, $company);

    if ($activated == 'true') {
        $update_rest_clients->bindValue(4, 'yes');
    } else {
        $update_rest_clients->bindValue(4, 'no');
    }

    $update_rest_clients->bindValue(5, $client_id);
    $update_rest_clients->bindValue(6, $phone);

    $update_rest_clients->execute();
    $update_rest_clients->closeCursor();

    $update_api_key = $handler->prepare("UPDATE rest_api_keys
             SET api_key = ?, level = ? WHERE user_id = ? ");

    $update_api_key->bindValue(1, $apikey);
    $update_api_key->bindValue(2, $level);
    $update_api_key->bindValue(3, $client_id);

    $update_api_key->execute();
    $update_api_key->closeCursor();

    if ($update_api_key) {
        echo "success api key";
    } 

    if ($update_rest_clients) {
        echo "Success rest client";
    }

And the Ajax response in the browser:

Array
(
    [contact] => Name Surname
    [address] => Awesome Str. 8
    [company] => mycompany.com
    [apikey] => 09f9bae2fe72975f7da25d284139dc1ee
    [phone] => 00379305557229
    [level] => 10
    [activated] => true
    [client_id] => 3
)
<br />
success api keySuccess rest client

My PDO $handler:

try{
        $handler = new PDO("mysql:host=".$dbhost.";dbname=".$dbname."", $dbuser, $dbpass);
        $handler->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $handler->setAttribute(PDO::MYSQL_ATTR_INIT_COMMAND, "SET NAMES utf8mb4");
        $handler->exec("SET CHARACTER SET utf8mb4");
    }catch(Exception $e){
        echo $e->getMessage();
        die();
    }

This is the error info of the first query but still I don't understand why table is not getting updated.

Array ( [0] => 00000 [1] => [2] => )

How should I do it successfully?

Any help will be appreciated.

Upvotes: 0

Views: 99

Answers (2)

Paul Stanley
Paul Stanley

Reputation: 4098

Looks to me like your client and phone binding values are the wrong way around, so you're trying to update on ID with a phone number which means it would fail if it starts with 0, since itll never match, nothing will get updated, however the query will still execute. See below:

$update_rest_clients = $handler->prepare("UPDATE rest_api_clients SET contact_person = ?, address = ?, company = ?, activated = ?, phone_number = ? WHERE client_id = ? ");

$update_rest_clients->bindValue(1, $contact);
$update_rest_clients->bindValue(2, $address);
$update_rest_clients->bindValue(3, $company);

if ($activated == 'true') {
    $update_rest_clients->bindValue(4, 'yes');
} else {
    $update_rest_clients->bindValue(4, 'no');
}

//I have swapped these around.
$update_rest_clients->bindValue(6, $client_id);
$update_rest_clients->bindValue(5, $phone);

Upvotes: 2

david strachan
david strachan

Reputation: 7228

level is a reserved word in MySQl 5.7.

Use backticks ` for field names.

Upvotes: 0

Related Questions