This is a simple and working UPDATE query (well, with an INNER JOIN) and there are no errors in the php code. The query is working if I execute it outside php:
UPDATE address_book ab
INNER JOIN address_book_client abc ON abc.contact_id = ab.id
SET ab.name = 'name1', ab.surname = 'surname', ab.cc = '34', ab.phone = '123456789', ab.email = 'a@a.aa', ab.nif = '12345678A', ab.note = 'Blah blah blah...'
WHERE abc.contact_id = 1 AND abc.company_id = 1
The code that does the UPDATE:
private $name;
// Populate AddressBook Object From User Input
public function PopulateFromUserInput($address_book) {
$this->name = $address_book['name'];
}
// New / Modify / Delete Contact
public function contact($option = '') {
$mysqli = $this->aet->getAetSql();
$exit = FALSE;
} else if ($option == 'modify') {
if ($stmt = $mysqli->prepare('UPDATE address_book ab
INNER JOIN address_book_client abc ON abc.contact_id = ab.id
SET ab.name = ?, ab.surname = ?, ab.cc = ?, ab.phone = ?, ab.email = ?, ab.nif = ?, ab.note = ?
WHERE abc.contact_id = ? AND abc.company_id = ?')) {
$stmt->bind_param('ssiisssii', $this->name, $this->surname, $this->cc, $this->phone, $this->email, $this->nif, $this->note,
$this->id, $this->uploader);
$exit = 'User modified an existing contact.';
} else return array(FALSE, $mysqli->error . '. ID: ' . $this->id);
}
if (!$stmt->execute()) {
$exit = [FALSE, $stmt->error . '. ID: ' . $this->id];
}
return $exit;
}
I made sure the new data is there by doing a var_dump($this->name); before the prepare() and no problem there. Also, no errors in $mysqli->error; nor $stmt->error;. I get the $exit string in my log, that means the $stmt->execute() returned TRUE.
Now, I enabled the mysql log and took a look:
1353 Connect user@localhost as anonymous on table
1353 Query SET NAMES utf8mb4
1353 Prepare UPDATE address_book ab
INNER JOIN address_book_client abc ON abc.contact_id = ab.id
SET ab.name = ?, ab.surname = ?, ab.cc = ?, ab.phone = ?, ab.email = ?, ab.nif = ?, ab.note = ?
WHERE abc.contact_id = ? AND abc.company_id = ?
1353 Execute UPDATE address_book ab
INNER JOIN address_book_client abc ON abc.contact_id = ab.id
SET ab.name = 'name1', ab.surname = 'surname', ab.cc = 34, ab.phone = 123456789, ab.email = 'a@a.aa', ab.nif = '12345678A', ab.not$
WHERE abc.contact_id = NULL AND abc.company_id = 1
1353 Quit
For some reason, the generated query does have a problem with the last field.
This is happening with a UPDATE query, not with the INSERT:
1433 Connect user@localhost as anonymous on table
1433 Query SET NAMES utf8mb4
1433 Prepare INSERT INTO address_book (name, surname, cc, phone, email, nif, note)
VALUES (?, ?, ?, ?, ?, ?, ?)
1433 Execute INSERT INTO address_book (name, surname, cc, phone, email, nif, note)
VALUES ('name', 'surname', 34, 123456789, 'a@a.aa', '12345678A', 'Blah blah blah...')
1433 Close stmt
1433 Prepare INSERT INTO address_book_client (contact_id, company_id)
VALUES (?, ?)
1433 Execute INSERT INTO address_book_client (contact_id, company_id)
VALUES (3, 1)
1433 Quit
For some unknown reason, execute() ends that line with a $ after ab.not.
To make sure it's not a problem with the variable I did:
// inside the contact() function
var_dump($this->note);
What I get is:
string(17) "Blah blah blah..."
Any idea where the problem could be?
