Re: DB Error: constraint violation

-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hi Everyone. Thank you for your suggestions. I fixed everything and got my functions to work. Lucas when I get done with this project, I'll look more closely at MDB (anything to make my job easier). For the gumbie developers such as myself out there here's the functions I wrote, released GPL. When I get V1 of this project done I'll put it on Sourceforge as OpenDiriectory and begin on making all the code more flexible. Together the following functions insert or update mySQL tables as needed. Any feedback or suggestions on how to improve the code anyone has is welcome. // }}} // {{{ getCols() /** * getCols() - gets the column names from a table and returns them in an array. * * @author Shane S. Antyr * @version 1 * @since 12/19/2002 * @copyright 2002, Shane S. Antyr *

* @param	array 	$dsn	    		database array

* @param string $table table to query * @return array $colName array of column names */ function getCols($dsn, $table) { // open a db connection $dbh = DB::connect($dsn, TRUE); /* run a query and get number of cols and col names for $table */ // define query $result = $dbh->query("SHOW COLUMNS FROM ".$table.""); // get results // build an array of column names while($columns = $result->fetchRow()){ $colName[] =$columns[0]; } // ERROR if(PEAR::isError($result)){ echo"get column count for ".$table." failed:\n\n"; dbError($result); } // end ERROR // close the db connection $dbh->disconnect(); return $colName; } // end 'getCols()' function // }}} // {{{ insertData() /** * insertData() - inserts data into a given table * * @author Shane S. Antyr * @version 1 * @since 12/19/2002 * @copyright 2002, Shane S. Antyr * * @param array $dsn database array * @param string $table table to query * @param array $values values to enter into the database * @param int $id db record key * @return string returns a PEAR error message */ function insertData($dsn, $table, $values, $id = '') { // create columns for insert, required by prepare() // if its there add id to the insert array if($id){ $insertVal[] = $id; } foreach($values as $val){ $insertVal[] .= $val; } // get column names // used to build subsequent query $colName = getCols($dsn, $table); // get number of columns $count = count($colName); // initialize columns - used in query preparation for($i=0; $i < $count -1; $i++){ $columns .= "?, "; } $columns .= "?"; // open a db connection $dbh = DB::connect($dsn, TRUE); // prepare insert query $sth = $dbh->prepare("INSERT INTO ".$table." VALUES(".$columns.")"); // execute query $result = $dbh->execute($sth, $insertVal); // ERROR if(PEAR::isError($result)){ $error = $result->getMessage(); return $error; } // close the db connection $dbh->disconnect(); } // end 'insertData()' function // }}} // {{{ updateData() /** * updateData() - updates table data * * @author Shane S. Antyr * @version 1 * @since 12/19/2002 * @copyright 2002, Shane S. Antyr * * @param array $dsn database array * @param string $table table to query * @param array $values values to enter into the database * @param int $id db record key */ function updateData($dsn, $table, $values, $id) { // get column names // used to build subsequent query $colName = getCols($dsn, $table); // get number of columns $count = count($colName); // initialize columns - used in query preparation // start columns after the id column // ACK! what if theres no id column or its not the first column? for($i=1; $i < $count -1; $i++){ $columns .= $colName[$i]."=?, "; } // last column $columns .= $colName[$i]."=?"; // open a db connection $dbh = DB::connect($dsn, TRUE); // prepare update query $sth = $dbh->prepare("UPDATE ".$table." SET ".$columns. " WHERE id = '".$id."'"); // execute query $result = $dbh->execute($sth, $values); // close the db connection $dbh->disconnect(); // ERROR if(PEAR::isError($result)){ echo $table." update failed:\n\n"; dbError($result); } // end ERROR } // end 'updateData()' function // }}} // {{{ insertOrUpdate() /** * insertOrUpdate() - inserts or updates data as needed * * @author Shane S. Antyr * @version 1 * @since 12/19/2002 * @copyright 2002, Shane S. Antyr * * @param array $dsn database array * @param string $table table to query * @param array $values values to enter into the database * @param int $id db record key */ function insertOrUpdate($dsn, $table, $values, $id) { // try inserting $result = insertData($dsn, $table, $values, $id); // record already exists update instead // ACK! ACK! ACK! - not clear on what insertdata() returns // thought I'd get PEAR messages such as 'DB_OK' get ints // (boolean?) if($result !=''){ updateData($dsn, $table, $values, $id); } } // end function 'insertOrUpdate()' On Thursday, December 19, 2002, at 01:16 PM, Shane Antyr wrote:

-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Thanks for the advice Lucas. This function is driving me nuts. I think I'll try out your MDB suggestion. Shane On Wednesday, December 18, 2002, at 12:16 AM, Lukas Smith wrote:

I assume it means that one of your queries violates a contraint that you set in the database (like uniquieness of values in the primary key column). I would recomment good old debugging tactics and simply manually checking each query on the console until you get your constraint violation. Whenever you output pear errors you may also want to add the __LINE__ constant and/or $[name of the variable containing the pear error]->getUserinfo(). This should help you debug. Finally there is MDB (http://pear.php.net/package-info.php?package=MDB). Which already provides what you are trying here with the replace() method. Regards, Lukas Smith smith@dybnet.de _______________________________ DybNet Internet Solutions GbR Reuchlinstr. 10-11 Gebäude 4 1.OG Raum 6 (4.1.6) 10553 Berlin Germany Tel. : +49 30 83 22 50 00 Fax : +49 30 83 22 50 07 www.dybnet.de info@dybnet.de

-----Original Message----- From: Shane Antyr [mailto:santyr@macosx.com] Sent: Wednesday, December 18, 2002 5:33 AM To: pear-general@lists.php.net Subject: [PEAR] DB Error: constraint violation -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hi, I'm gettiing a ' DB Error: constraint violation' when I run the following function against the following table. Note the error only happens on an insert. Anyone have any ideas, or know what this means? Thanks in advance. Shane Antyr /** * insertOrUpdate() V1 * * @param dbh database handler * @param table table to query * @param values array: values to enter into the database * @param id db record key * */ function insertOrUpdate($dbh, $table, $values, $id) {

    	/* run a query and get number of columns for $table */

// define query $result = $dbh->query("SHOW COLUMNS FROM ".$table.""); // get results // build an array of column names while($columns = $result->fetchRow()){ $colName[] =$columns[0]; } // ERROR if(PEAR::isError($result)){ echo"get column count for ".$table." failed:\n\n"; dbError($result); } // end ERROR // get how many columns there are // used to build subsequent queries $count = count($colName); /* check to see if the record exists */ // define query $stmt = "SELECT id FROM ".$table." WHERE id = '".$id."'"; // send query $result1 = $dbh->query($stmt); // get results $row = $result1->fetchRow(DB_FETCHMODE_ORDERED); // ERROR if(PEAR::isError($result)){ echo"get id from ".$table." failed:\n\n"; dbError($result); } // end ERROR /* the record exits. update it */ if($row[0]){ // create columns for query, required by prepare() for($i=1; $i < $count -1; $i++){ $columns .= $colName[$i]."=?, "; } // last column $columns .= $colName[$i]."=?"; $sth = $dbh->prepare("UPDATE ".$table." SET ".$columns.

				    		 " WHERE  id =

'".$id."'");

$result3 = $dbh->execute($sth, $values); // ERROR if(PEAR::isError($result3)){ echo $table." update failed:\n\n"; dbError($result3); } // end ERROR } // end if /* record doesn't exist, insert the data */ else{ // create columns for query, required by prepare() for($i=0; $i < $count -2; $i++){ $columns .= "?, "; } $columns .= "?"; $sth = $dbh->prepare("INSERT INTO ".$table."

VALUES('$id',".$columns.")");

$result2 = $dbh->execute($sth, $values); // ERROR if(PEAR::isError($result2)){ echo $table." insert failed:\n\n"; dbError($result2); } // end ERROR } // end else } // end function 'insertOrUpdate()' # # Table structure for table guide_dining_meals # CREATE TABLE guide_dining_meals ( id int(11) NOT NULL default '0', breakfast tinyint(4) NOT NULL default '0', lunch tinyint(4) NOT NULL default '0', dinner tinyint(4) NOT NULL default '0', PRIMARY KEY (id) ) TYPE=MyISAM; -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.1 (Darwin) iD8DBQE9//pmmU+jltT1nOoRAm9oAKCUFJSVjKPHr0hfEeO8oWT+4StHegCfd/tA zQTnj5RQuCQAFnn2xioQjkQ= =ODns -----END PGP SIGNATURE----- -- PEAR General Mailing List (http://pear.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php

-- PEAR General Mailing List (http://pear.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php

-----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.1 (Darwin) iD8DBQE+AikQmU+jltT1nOoRAqhqAJwObgmQ5xeYOE2QTxP8AlQmB9TWyACfUi1p LZ4+bwgRYA/9CNsuAdGx1zA= =5DOV -----END PGP SIGNATURE----- -- PEAR General Mailing List (http://pear.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -----BEGIN PGP SIGNATURE-----

Version: GnuPG v1.2.1 (Darwin) iD8DBQE+DiFumU+jltT1nOoRAo7FAJ0Ur1Thcgrr0vxpf7uzj1pWVvPTlwCePStt KN5kZx1/Xce+1/6fZ7rFWGc= =rGzE -----END PGP SIGNATURE-----