TonyE
|
| Posted: 02/11/2004, 7:15 AM |
|
I created an editable grid with 2 tables
(clients table and contacts table) A client can have multiple contacts. The contacts table has also a client_id field to reference to a client.
There are 2 problems that I am trying to resolve.
I created the editable grid but I have problems with my custom update.
1. Custom update query not working
I created the following custom update query
UPDATE clients SET `client_id`={clients_client_id}, client_name='{client_name}', client_city='{client_city}' Where client_id = {clients_client_id};
Update contacts Set `contact_id`={contact_id}, last_name='{last_name}',client_city='{client_city}' Where contact_id = {contact_id};
I also configured all input parameters at the parameter window.
If I remove the Update contacts the query will work but not when I am trying to update both tables.
Anybody any clue whats wrong with my query?
2. Custom Insert
If I get the update query to work I will be able to create an custom insert that will insert records into both tables.
But how can I create an insert function for "adding a new contact to an existing client". Can there be a second custom insert function.
I am looking for function that will clear the contact fields, but not the client fields, turn tthe editable grid into an insert mode with the second insert option function and then insert a new contact but not a new client.
To increase the user friendlyness I would like to do this with the same editable grid instead of creating a second form.
I am using PHP MYSQL
Any hints would be appreciated.
|
|
|
 |
peterr
Posts: 5971
|
| Posted: 02/11/2004, 12:48 PM |
|
You cannot update two tables at the same time when using MySQL.
In both Insert and Update cases you will need to add some programming code in the "After Insert" and "After Update" events that will update the 2nd table. This is shown in several of our examples in CCS Example Pack, such as:
"Multi-Step User Registration"
"Updating Many-to-Many via CheckBox List"
"Updating Many-to-Many via Multi-select ListBox"
"Updating Many-to-Many via Two ListBoxes"
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
 |
|
Aaron
Posts: 145
|
| Posted: 03/25/2004, 2:11 PM |
|
I am actually trying to do the same thing with being able to add an individual, then move on and add their address to another table (called address). The primary key in the individual table is indID and there is also a corresponding column name in the address table. Here is what i have for my code after the entry of the individual; it should carry with it to the address addition page, the indID for linking purposes. I get a parse error and am not sure why...
//Custom Code
// -------------------------
global $DBConnection1;
global $individual;
if(!CCGetFromGet("indID",0)) {
// Error happens on this line
$LastID = CCDLookup("max(indID)","individual","indID=".CCGetFromPost("indID",""),ccsText),$DBConnection1);
//end error
if (strpos($Redirect,"?") == false ) {
$Redirect = $Redirect."?indID=".$LastID;
} else if (substr($Redirect,-1) == "?" ) {
$Redirect = $Redirect."indID=".$LastID;
} else {
$Redirect = $Redirect."&indID=".$LastID;
}
}
Thanks.
Aaron
|
|
|
|
peterr
Posts: 5971
|
| Posted: 03/25/2004, 2:34 PM |
|
Can you please provide the full error message.
Although I see a possible logical mistake. If you already know the value of indID (CCGetFromPost("indID",""),ccsText)) then there should be no reason to look it up. You should usually use CCDLookup to find the value of a field different from the one you are looking up.
And if "indID" is an auto-incremented field then it is not posted anyway, because the HTML form doesn't know what the auto-incremented value should be. Our "Multi-Step User Registration" example is rather a better way to go about this.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
Aaron
Posts: 145
|
| Posted: 03/26/2004, 4:04 AM |
|
Parse error: parse error in /var/www/aaron/address_maint2_events.php on line 23
Where I had mentioned above is line 23 in the code.
Aaron
|
|
|
|
Aaron
Posts: 145
|
| Posted: 03/26/2004, 4:07 AM |
|
Sorry, also ment to add here that the Multistep registration is where I took this code from, then am trying to modify it to work with what I am trying to accomplish.
This project was thrown at me very recently and I have no PHP, mySQL, or CodeCharge experience at all.
Thanks for the help.
Aaron
|
|
|
|
peterr
Posts: 5971
|
| Posted: 03/26/2004, 8:24 AM |
|
OK.
1. The part ,ccsText) shouldn't be there since it causes you to have 4 closing parentheis, but only 3 are opened.
I see that our Multistep Registration example includes .$DBInternetDB->ToSQL( , which seems missing from your code.
2. I'm also assuming that CCGetFromPost("indID","") is empty.
The example shows that the field being looked up should be different from the field passes from the previous page. You will need to choose some field from the previous page for this.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
Aaron
Posts: 145
|
| Posted: 03/26/2004, 8:51 AM |
|
Right. Was just going through it again with fresh eyes and noticed the lack of parentheis. I've gotten that fixed I think. I'm not sure why I had deleted or removed the ->ToSQL but is now back.
Perhaps I'm just following the wrong train of thought. After a user inputs a new individual, I need the add address record to link that new address to the new individual that was just entered. The indID is generated by the DB when the new record is entered, so I basically need the add address form to pull in the last indID and "assign" it to that address. In both tables there is an indID in there so there isn't (or should be) any naming problems.
Thanks.
Aaron
ps- here is the code now after changes mentioned above...
global $DBConnection1;
global $individual;
if(!CCGetFromGet("indID",0)) {
$LastID = CCDLookup("(indID)","individual","indID=".$DBConnection1->ToSQL(CCGetFromPost("indID","")),$DBConnection1);
if (strpos($Redirect,"?") == false ) {
$Redirect = $Redirect."?indID=".$LastID;
} else if (substr($Redirect,-1) == "?" ) {
$Redirect = $Redirect."indID=".$LastID;
} else {
$Redirect = $Redirect."&indID=".$LastID;
}
}
|
|
|
|
peterr
Posts: 5971
|
| Posted: 03/26/2004, 9:09 AM |
|
Now you'd need to put the max function back as you had it earlier.
However, you cannot use the function ToSQL(CCGetFromPost("indID","")). The value "indID" is what you don't know and that's why you you're looking it up. And to look it up (find what it is) you need to provide some value that is already known. Therefore it must be a value submitted by the user on the previous page. The user doesn't create and submit "indID" therefore it cannot be it. It must be ToSQL(CCGetFromPost("SomethingElse",""))
$LastID = CCDLookup("max(indID)","individual","indID=".$DBConnection1->ToSQL(CCGetFromPost("Something Else","")),$DBConnection1);
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
Aaron
Posts: 145
|
| Posted: 03/26/2004, 10:37 AM |
|
Ok. Thanks, by the way for all the help!!
Got max in there and the field called fullName is a required field that has to be unique (this will more than likely change and I will have to modify code later, but for now...)
It is putting in the individual's name into the DB and will take you to the address, but, on a field I have to pull in the indID and just show it on the add address record, it doesn't show up.
If you then add the address and take a look at the DB (doing so through phpmyAdmin), the address information gets added, but with an indID as 0 (zero). I'm using my own name and address as a test, so when it doesn't work, I go back in to the raw data and delete both the individual name and the address...
Also, after hitting add on the address form, I get these errors:
Warning: Missing argument 2 for tosql() in /var/www/aaron/Common.php on line 106
Line 106 is as follows: function ToSQL($Value, $ValueType)
So I'm guessing that since I'm asking for a value, it also wants me to ask for a valueType?
The other error is as follows:
Warning: Cannot add header information - headers already sent by
(output started at /var/www/aaron/Common.php:106) in /var/www/aaron/address_maint2.php on line 618
Line 618 is as follows:
header("Location: " . $Redirect);
I have the project setup to not include headers, so am not sure what this means but since it references a problem in the first error, I have a feeling that once I fix the first error, this will work ok.
The code now stands as follows:
global $DBConnection1;
global $individual;
if(!CCGetFromGet("indID",0)) {
$LastID = CCDLookup("max(indID)","individual","indID=".$DBConnection1->ToSQL(CCGetFromPost("fullName","")),$DBConnection1);
if (strpos($Redirect,"?") == false ) {
$Redirect = $Redirect."?indID=".$LastID;
} else if (substr($Redirect,-1) == "?" ) {
$Redirect = $Redirect."indID=".$LastID;
} else {
$Redirect = $Redirect."&indID=".$LastID;
}
}
Aaron
|
|
|
|
Aaron
Posts: 145
|
| Posted: 03/29/2004, 5:16 AM |
|
Bump
|
|
|
|
peterr
Posts: 5971
|
| Posted: 03/29/2004, 11:24 AM |
|
ToSQL requires 2 parameters.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
|
