[prev in list] [next in list] [prev in thread] [next in thread]
List: pear-general
Subject: Re: [PEAR] DB Error: constraint violation
From: Shane Antyr <santyr () macosx ! com>
Date: 2002-12-28 22:10:46
[Download RAW message or body]
-----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-----
--
PEAR General Mailing List (http://pear.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic