Koren
Posts: 83
|
| Posted: 01/24/2006, 12:30 PM |
|
have 3 tables in MySQL DB
properties (which lists properties and creates their _id)
amenities (which lists amenities and creates their _id)
property_amenity (many to many- property_id and amenity_id)
I used the example pack codes to help generate the boxes and enter the data in the db and all seemed well UNTIL...
I changed data for existing property_id, then the amenity_id duplicates in the table.
For example: (first record entry: prop_id=1 has only amenity_id=1)
property_amenity Table
prop_id|amen_id
1|1
--------(next entry:I only added a check to amenty #2)
DB shows:
1|1
1|1
1|2
--------(next entry:I added a check to amenty #3)
DB shows:
1|1
1|1
1|1
1|1
1|2
1|3
--------(next entry:I added a check to amenty #4)
DB shows:
1|1
1|1
1|1
1|1
1|1
1|1
1|1
1|2
1|3
1|4
--------(next entry: I only unchecked amenty #1)
DB shows:
1|2
1|2
1|2
1|2
1|2
1|2
1|2
1|2
1|2
1|2
1|2
1|3
1|4
sorry I could't find a pattern to describe the problem more clearly. I didn't need the "GetLastInsKey" feature in the code. I was getting the prop_id from the URL. So I altered the code a smidge for that. Any insight would be GREAT!
Thanks So Much!
|
 |
 |
Koren
Posts: 83
|
| Posted: 01/24/2006, 1:37 PM |
|
UPDATE!
I found a "spot" in the function ProjectEmployeesModify code where I left out a stinikin space! SOO that fixed the wierd duplication of amenity_id numbers. BUT now I have a new problem. This one is easier to explain and I'm hoping that means it is an easy one to fix... 
The new problem:
I can enter and edit one property's (prop_id) amenity list (amen_id)without any problems. BUT when I add amenities (amen_id) for an additional property, it completely deletes all the data from the first property. Perhaps I need to include that function code that I messed with.
I'm still keeping up hope! 
function ProjectEmployeesModify($Actions){
global $DBvbhma;
$EmpProjectConn = null;
$PropID = 0;
$AmenID = 0;
$AmenityList = array();
$GetLastInsKey = 0;
//Create a new database connection object
$EmpProjectConn = new clsDBvbhma();
//Retrieve the current project
$PropID = CCGetFromGet("property_id",0);
$AmenityList = CCGetFromPost("AmenityList",array());
if($Actions == "Insert"){
//Retrieve the last inserted key
//Use MS SQL method
//$GetLastInsKey = CCDLookup("@@IDENTITY","employees","",$DBvbhma);
//Use a method compatible with all databases (unsafe when multiple users insert records at the same time)
//$GetLastInsKey = CCDLookup("max(property_id)", "employees", "", $DBvbhma);
//Insert New links
reset($AmenityList);
while(list($key,$AmenID) = each($AmenityList)){
$EmpProjectConn->query("INSERT INTO property_amenities(amenity_id, property_id) VALUES (".$EmpProjectConn->ToSQL($AmenID,ccsInteger).",".$EmpProjectConn->ToSQL($GetLastInsKey,ccsInteger).")");
}
}
if($PropID >0){
if( ($Actions == "Delete") Or ($Actions == "Update")){
//Delete project employees links
$EmpProjectConn->query("DELETE FROM property_amenities WHERE property_id=".$EmpProjectConn->ToSQL($PropID,ccsInteger));
}
if($Actions == "Update"){
//Insert assigned employees
reset($AmenityList);
while(list($key,$AmenID) = each($AmenityList)){
$EmpProjectConn->query("INSERT INTO property_amenities(amenity_id, property_id) VALUES (".$EmpProjectConn->ToSQL($AmenID,ccsInteger).",".$EmpProjectConn->ToSQL($PropID,ccsInteger).")");
}
}
}
//Close and destroy the database connection object
$EmpProjectConn->close();
}
|
|
|
|
Walter Kempees
|
| Posted: 01/24/2006, 2:59 PM |
|
Fast reply?
Dont get the URL variable.
Uncomment the GetLastInsKey
> //$GetLastInsKey = CCDLookup("max(property_id)", "employees", "",
> $DBvbhma);
What happens is you commented out the GetLastInsKey but further on it is
being used.
> (".$EmpProjectConn->ToSQL($AmenID,ccsInteger).",".$EmpProjectConn->ToSQL($GetLastInsKey,ccsInteger).")");
OR if you want to pursue your approach then stuff the URL PropID into
GetLastInsKey to keep functionality working.
I would have rewritten it 4U but it's bedtime, sorry
"Koren" <Koren@forum.codecharge> schreef in bericht
news:543d69e23a355a@news.codecharge.com...
> UPDATE!
> I found a "spot" in the function ProjectEmployeesModify code where I left
> out a
> stinikin space! SOO that fixed the wierd duplication of amenity_id
> numbers. BUT
> now I have a new problem. This one is easier to explain and I'm hoping
> that
> means it is an easy one to fix...
> The new problem:
> I can enter and edit one property's (prop_id) amenity list
> (amen_id)without any problems. BUT when I add amenities (amen_id) for an
> additional property, it completely deletes all the data from the first
> property. Perhaps I need to include that function code that I messed with.
> I'm still keeping up hope!
>
> function ProjectEmployeesModify($Actions){
> global $DBvbhma;
>
> $EmpProjectConn = null;
> $PropID = 0;
> $AmenID = 0;
> $AmenityList = array();
> $GetLastInsKey = 0;
>
> //Create a new database connection object
> $EmpProjectConn = new clsDBvbhma();
>
> //Retrieve the current project
> $PropID = CCGetFromGet("property_id",0);
> $AmenityList = CCGetFromPost("AmenityList",array());
>
> if($Actions == "Insert"){
> //Retrieve the last inserted key
> //Use MS SQL method
> //$GetLastInsKey = CCDLookup("@@IDENTITY","employees","",$DBvbhma);
> //Use a method compatible with all databases (unsafe when multiple
> users
> insert records at the same time)
> //$GetLastInsKey = CCDLookup("max(property_id)", "employees", "",
> $DBvbhma);
>
> //Insert New links
> reset($AmenityList);
> while(list($key,$AmenID) = each($AmenityList)){
> $EmpProjectConn->query("INSERT INTO property_amenities(amenity_id,
> property_id) VALUES
> (".$EmpProjectConn->ToSQL($AmenID,ccsInteger).",".$EmpProjectConn->ToSQL($GetLastInsKey,ccsInteger).")");
> }
> }
> if($PropID >0){
> if( ($Actions == "Delete") Or ($Actions == "Update")){
> //Delete project employees links
> $EmpProjectConn->query("DELETE FROM property_amenities WHERE
> property_id=".$EmpProjectConn->ToSQL($PropID,ccsInteger));
> }
> if($Actions == "Update"){
> //Insert assigned employees
> reset($AmenityList);
> while(list($key,$AmenID) = each($AmenityList)){
> $EmpProjectConn->query("INSERT INTO
> property_amenities(amenity_id,
> property_id) VALUES
> (".$EmpProjectConn->ToSQL($AmenID,ccsInteger).",".$EmpProjectConn->ToSQL($PropID,ccsInteger).")");
> }
> }
> }
> //Close and destroy the database connection object
> $EmpProjectConn->close();
> }
>
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
Koren
Posts: 83
|
| Posted: 01/25/2006, 6:16 AM |
|
Thanks so much Walter for your response!
Uncommented the $GetLastInsKey and altered the table name to correspond with my db, with no avail. It still clears the table of the first property amenities and replaces the newly inserted amenities of the new property. I'm struggling!
Here is the updated code:
function ProjectEmployeesModify($Actions){
global $DBVBHMA;
$EmpProjectConn = null;
$PropID = 0;
$AmenID = 0;
$AmenityList = array();
$GetLastInsKey = 0;
//Create a new database connection object
$EmpProjectConn = new clsDBVBHMA();
//Retrieve the current project
$PropID = CCGetFromGet("property_id",0);
$AmenityList = CCGetFromPost("AmenityList",array());
if($Actions == "Insert"){
//Retrieve the last inserted key
//Use MS SQL method
//$GetLastInsKey = CCDLookup("@@IDENTITY","employees","",$DBVBHMA);
//Use a method compatible with all databases (unsafe when multiple users insert records at the same time)
$GetLastInsKey = CCDLookup("max(property_id)", "properties", "", $DBVBHMA);
//Insert New links
reset($AmenityList);
while(list($key,$AmenID) = each($AmenityList)){
$EmpProjectConn->query("INSERT INTO property_amenities(amenity_id, property_id) VALUES (".$EmpProjectConn->ToSQL($AmenID,ccsInteger).",".$EmpProjectConn->ToSQL($GetLastInsKey,ccsInteger).")");
}
}
if($PropID >0){
if( ($Actions == "Delete") Or ($Actions == "Update")){
//Delete project employees links
$EmpProjectConn->query("DELETE FROM property_amenities WHERE property_id=".$EmpProjectConn->ToSQL($PropID,ccsInteger));
}
if($Actions == "Update"){
//Insert assigned employees
reset($AmenityList);
while(list($key,$AmenID) = each($AmenityList)){
$EmpProjectConn->query("INSERT INTO property_amenities(amenity_id, property_id) VALUES (".$EmpProjectConn->ToSQL($AmenID,ccsInteger).",".$EmpProjectConn->ToSQL($PropID,ccsInteger).")");
}
}
}
//Close and destroy the database connection object
$EmpProjectConn->close();
}
|
|
|
|
Damian Hupfeld
|
| Posted: 01/26/2006, 2:07 AM |
|
Koren,
It sounds like you are UPDATING and not INSERTING....
Damian
"Koren" <Koren@forum.codecharge> wrote in message
news:543d788462c8b3@news.codecharge.com...
> Thanks so much Walter for your response!
> Uncommented the $GetLastInsKey and altered the table name to correspond
> with my
> db, with no avail. It still clears the table of the first property
> amenities and
> replaces the newly inserted amenities of the new property. I'm struggling!
>
> Here is the updated code:
> function ProjectEmployeesModify($Actions){
> global $DBVBHMA;
>
> $EmpProjectConn = null;
> $PropID = 0;
> $AmenID = 0;
> $AmenityList = array();
> $GetLastInsKey = 0;
>
> //Create a new database connection object
> $EmpProjectConn = new clsDBVBHMA();
>
> //Retrieve the current project
> $PropID = CCGetFromGet("property_id",0);
> $AmenityList = CCGetFromPost("AmenityList",array());
>
> if($Actions == "Insert"){
> //Retrieve the last inserted key
> //Use MS SQL method
> //$GetLastInsKey = CCDLookup("@@IDENTITY","employees","",$DBVBHMA);
> //Use a method compatible with all databases (unsafe when multiple
> users
> insert records at the same time)
> $GetLastInsKey = CCDLookup("max(property_id)", "properties", "",
> $DBVBHMA);
>
> //Insert New links
> reset($AmenityList);
> while(list($key,$AmenID) = each($AmenityList)){
> $EmpProjectConn->query("INSERT INTO property_amenities(amenity_id,
> property_id) VALUES
> (".$EmpProjectConn->ToSQL($AmenID,ccsInteger).",".$EmpProjectConn->ToSQL($GetLastInsKey,ccsInteger).")");
> }
> }
> if($PropID >0){
> if( ($Actions == "Delete") Or ($Actions == "Update")){
> //Delete project employees links
> $EmpProjectConn->query("DELETE FROM property_amenities WHERE
> property_id=".$EmpProjectConn->ToSQL($PropID,ccsInteger));
> }
> if($Actions == "Update"){
> //Insert assigned employees
> reset($AmenityList);
> while(list($key,$AmenID) = each($AmenityList)){
> $EmpProjectConn->query("INSERT INTO
> property_amenities(amenity_id,
> property_id) VALUES
> (".$EmpProjectConn->ToSQL($AmenID,ccsInteger).",".$EmpProjectConn->ToSQL($PropID,ccsInteger).")");
> }
> }
> }
> //Close and destroy the database connection object
> $EmpProjectConn->close();
> }
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
|
