BRG BRG - 4 months ago 8
SQL Question

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.

Answer

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);