php - Update two MySQL Databases -
i have read on several threads possible update 2 databases using inner join update query unable work, throws error:
fatal error: uncaught exception 'pdoexception' message 'sqlstate[23000]: integrity constraint violation: 1052 column 'category' in field list 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 inner join assets_history b on (a.barcode = b.barcode) set '; $sql .= implode(', ', $sql_part); $sql .= ' a.barcode = :barcode , b.barcode = :barcode'; $stmt = $conn->prepare($sql); if($stmt){ $result = $stmt->execute($prepare); $count = $stmt->rowcount(); header('location: ./usearch.php'); exit; } } } this 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 better way or missing stupid.
i've seen possibility of relating 2 table's columns in phpmyadmin not tried yet. 2 tables identical, 1 keep updates made records.
i've had no luck these articles, 1 of them basis of code.
mysql update syntax multiple tables using clause
use table name column category present in both table
if ($category){ $sql_part[] = 'assets.category = :category'; $prepare[':category'] = $category; }
Comments
Post a Comment