SaucedApples SaucedApples - 5 months ago 18
SQL Question

Update two MySQL Databases

I have read on several SO threads that it is possible to update two databases using an

INNER JOIN
update query but I am unable to get it to work, it just throws error:


Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'category' in field list is ambiguous' in C:\xampp\htdocs\update.php:79 Stack trace: #0 C:\xampp\htdocs\update.php(79): PDOStatement->execute(Array) #1 {main} thrown in C:\xampp\htdocs\update.php on line 79


LINE 79:
$result = $stmt->execute($prepare);


if (isset($_POST['update'])) {
$category = isset($_POST['category']) ? $_POST['category'] : null;
$manufactuer = isset($_POST['manufactuer']) ? $_POST['manufactuer'] : null;
$model = isset($_POST['model']) ? $_POST['model'] : null;
$serial = isset($_POST['serial']) ? $_POST['serial'] : null;
$itemcondition = isset($_POST['itemcondition']) ? $_POST['itemcondition'] : null;
$locationb = isset($_POST['locationb']) ? $_POST['locationb'] : null;
$locationr = isset($_POST['locationr']) ? $_POST['locationr'] : null;
$comments = isset($_POST['comments']) ? $_POST['comments'] : null;
$purchased = isset($_POST['purchased']) ? $_POST['purchased'] : null;
$retired = isset($_POST['retired']) ? $_POST['retired'] : null;
$stolen = isset($_POST['stolen']) ? $_POST['stolen'] : null;

$sql_part = array();
$prepare = array();
if ($category){
$sql_part[] = 'category = :category';
$prepare[':category'] = $category;
}
if($manufactuer){
$sql_part[] = 'manufactuer = :manufactuer';
$prepare[':manufactuer'] = $manufactuer;
}
if($model){
$sql_part[] = 'model = :model';
$prepare[':model'] = $model;
}
if($serial){
$sql_part[] = 'serial = :serial';
$prepare[':serial'] = $serial;
}
if($itemcondition){
$sql_part[] = 'itemcondition = :itemcondition';
$prepare[':itemcondition'] = $itemcondition;
}
if($locationb){
$sql_part[] = 'locationb = :locationb';
$prepare[':locationb'] = $locationb;
}
if($locationr){
$sql_part[] = 'locationr = :locationr';
$prepare[':locationr'] = $locationr;
}
if($comments){
$sql_part[] = 'comments = :comments';
$prepare[':comments'] = $comments;
}
if($purchased){
$sql_part[] = 'purchased = :purchased';
$prepare[':purchased'] = $purchased;
}
if($retired){
$sql_part[] = 'retired = :retired';
$prepare[':retired'] = $retired;
}
if($stolen){
$sql_part[] = 'stolen = :stolen';
$prepare[':stolen'] = $stolen;
}
$prepare[':barcode'] = $barcode;

if(count($sql_part)){
$sql = 'UPDATE assets a INNER JOIN assets_history b ON (a.barcode = b.barcode) SET ';
$sql .= implode(', ', $sql_part);
$sql .= ' WHERE a.barcode = :barcode AND b.barcode = :barcode';

$stmt = $conn->prepare($sql);

if($stmt){
$result = $stmt->execute($prepare);
$count = $stmt->rowCount();
header('Location: ./usearch.php');
exit;
}
}
}


This is the database structure, in case it's needed:

`barcode` int(6) UNSIGNED ZEROFILL NOT NULL
`category` text NOT NULL
`manufactuer` text NOT NULL
`model` varchar(255) NOT NULL
`serial` varchar(255) NOT NULL
`itemcondition` text NOT NULL
`locationb` text NOT NULL
`locationr` text NOT NULL,
`comments` varchar(255) NOT NULL
`purchased` varchar(30) NOT NULL
`retired` varchar(30) NOT NULL
`stolen` varchar(30) NOT NULL


Is there a better way to do it or am I missing something stupid.

I've also just seen the possibility of relating the two table's columns in PHPMyAdmin but not tried yet. The two tables are identical, one will just keep all updates made to records.

I've had no luck with these articles, one of them is the basis of my code.

MySQL UPDATE syntax with multiple tables using WHERE clause

MySql update two tables at once

How to update two tables in one statement in SQL Server 2005?

MySQL, update multiple tables with one query

Answer

Use table name with column category its present in both table

if ($category){
    $sql_part[] = 'assets.category = :category';
    $prepare[':category'] = $category;
}
Comments