frustrated
|
| Posted: 10/12/2005, 7:36 AM |
|
I have researched all the previous posts about this topic and tried to interperate the lingo as best I can. With zero luck. Complete PHP/CCS newbie!
I have a record form that when submited creates a new show (its a show schedule) in the DB. I need that new show's ID to appear in the URL (.php?performer_id=XX) that when brought to the next page to complete the entry info all of the pertinent info is there. You guys have helped many with this problem and I have tried my best to follow the proceedures that were suggested with no luck. Being so new to PHP I'm sure that the custom code must be my issue.
I used the multi-registration example and pasted/altered it into the events.php.
Here it is:
/show_contact_AfterInsert @205-63B43B90
function show_contact_AfterInsert()
{
$show_contact_AfterInsert = true;
//End show_contact_AfterInsert
//Custom Code @259-35B0037F
// -------------------------
global $show_contact;
global $Redirect;
// Write your own code here.
if(!CCGetFromGet("performer_id",0)) {
$LastID = CCDLookup("max(performer_id)","performer","performer=".$DBshowsched->ToSQL(CCGetFromPost("performer",""),ccsText),$DBshowsched);
if (strpos($Redirect,"?") == false ) {
$Redirect = $Redirect."?performer_id=".$LastID;
} else if (substr($Redirect,-1) == "?" ) {
$Redirect = $Redirect."performer_id=".$LastID;
} else {
$Redirect = $Redirect."&performer_id=".$LastID;
}
}
// -------------------------
//End Custom Code
To give you a bit more insight:
Server Info:
Unix/Mysql/PHP
Database Info:
DB:showsched
table:performer
PK Field:performer_id
a show name field is entered here too. It would be a unique entry and if needed the field name for that entry is "performer"
Thanks So Much!
|
|
|
 |
frustrated
|
| Posted: 10/12/2005, 9:52 AM |
|
Figured it out! 
In researching for an unrelated project, I found code that was easier for me to figure out in the Master Detail Example. For anyone who was in the same frustrating position that I was, this is what I added in the AfterInsert Event:
//Custom Code @30-2A29BDB7
global $Redirect;
//Below is the name of the DB CONNECTION. Not the name of the DB itself.
global $DBShowSched;
//Retrieve the last inserted key
$LastID = CCDLookUp("max(performer_id)","performer","", $DBShowSched);
//Append the last inserted key to the URL by modifying the 'Redirect' variable
$Redirect = "show_maint.php?".CCAddParam(CCRemoveParam(CCGetQueryString("QueryString", "ccsForm"),"ccsForm"), "performer_id", $LastID);
//End Custom Code
|
|
|
|
Walter Kempees
|
| Posted: 10/12/2005, 12:04 PM |
|
Read your post, compliments for solving it. BUT:
Although this is a working solution it is not save. Please let me explain.
You correctly took part of the solution from the MultiForm registration
example. If you look closely in the AfterInsert embed you'll see a note
saying that the method is save because [explanation], well yours isn't.
You are in fact inserting a record into a table and then after the insert
retreiving the id by selecting max(id).
In between the two sql statements many more records might have been added by
other users.
MySQL (I presume you are using MySQL) has last_inserted_id() which will
return the real id added within your session.
So your statement would be saver like so:
$LastID = CCDLookUp("last_inserted_id()","performer","", $DBShowSched);
Greetz,
Walter
"frustrated" <frustrated@forum.codecharge> schreef in bericht
news:5434d1f60c0491@news.codecharge.com...
>I have researched all the previous posts about this topic and tried to
> interperate the lingo as best I can. With zero luck. Complete PHP/CCS
> newbie!
> I have a record form that when submited creates a new show (its a show
> schedule) in the DB. I need that new show's ID to appear in the URL
> (.php?performer_id=XX) that when brought to the next page to complete the
> entry
> info all of the pertinent info is there. You guys have helped many with
> this
> problem and I have tried my best to follow the proceedures that were
> suggested
> with no luck. Being so new to PHP I'm sure that the custom code must be my
> issue.
>
> I used the multi-registration example and pasted/altered it into the
> events.php.
>
> Here it is:
> /show_contact_AfterInsert @205-63B43B90
> function show_contact_AfterInsert()
> {
> $show_contact_AfterInsert = true;
> //End show_contact_AfterInsert
>
> //Custom Code @259-35B0037F
> // -------------------------
> global $show_contact;
> global $Redirect;
> // Write your own code here.
> if(!CCGetFromGet("performer_id",0)) {
> $LastID =
> CCDLookup("max(performer_id)","performer","performer=".$DBshowsched->ToSQL(CCGetFromPost("performer",""),ccsText),$DBshowsched);
> if (strpos($Redirect,"?") == false ) {
> $Redirect = $Redirect."?performer_id=".$LastID;
> } else if (substr($Redirect,-1) == "?" ) {
> $Redirect = $Redirect."performer_id=".$LastID;
> } else {
> $Redirect = $Redirect."&performer_id=".$LastID;
> }
> }
> // -------------------------
> //End Custom Code
>
> To give you a bit more insight:
> Server Info:
> Unix/Mysql/PHP
>
> Database Info:
> DB:showsched
> table:performer
> PK Field:performer_id
>
> a show name field is entered here too. It would be a unique entry and if
> needed
> the field name for that entry is "performer"
>
> Thanks So Much!
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
frustrated
|
| Posted: 10/13/2005, 6:30 AM |
|
Good Morning Walter!
Thanks for the added insight. I replaced the code as you suggested. (Thanks for the great explaination of the benefits, makes sence). When I tested it out I got the following error:
Database error: Invalid SQL: SELECT last_inserted_id() FROM performer
MySQL Error: 1064 (You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '() FROM performer' at line 1)
Session halted.
I went to research the last_insert_id function in Mysql to get syntax correct, but couldn't find documentation. 
|
|
|
|
DonB
|
| Posted: 10/13/2005, 6:37 AM |
|
mysql_last_id() is a PHP function, despite the misleading name. You don't
use it in a SQL query, just call it directly from PHP code.
--
DonB
http://www.gotodon.com/ccbth
"frustrated" <frustrated@forum.codecharge> wrote in message
news:5434e61656c6e6@news.codecharge.com...
> Good Morning Walter!
> Thanks for the added insight. I replaced the code as you suggested.
(Thanks for
> the great explaination of the benefits, makes sence). When I tested it out
I got
> the following error:
>
> Database error: Invalid SQL: SELECT last_inserted_id() FROM
performer
> MySQL Error: 1064 (You have an error in your SQL syntax. Check the manual
that
> corresponds to your MySQL server version for the right syntax to use near
'()
> FROM performer' at line 1)
> Session halted.
>
> I went to research the last_insert_id function in Mysql to get syntax
correct,
> but couldn't find documentation.
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
datadoit.com
|
| Posted: 10/13/2005, 6:42 AM |
|
> When I tested it out I got
> the following error:
>
> Database error: Invalid SQL: SELECT last_inserted_id() FROM
> performer
> MySQL Error: 1064 (You have an error in your SQL syntax. Check the manual
> that
> corresponds to your MySQL server version for the right syntax to use near
> '()
> FROM performer' at line 1)
> Session halted.
>
> I went to research the last_insert_id function in Mysql to get syntax
> correct,
> but couldn't find documentation.
> ---------------------------------------
LAST_INSERT_ID() or mysql_insert_id will only return the last inserted or
updated value of an AUTO-INCREMENT field.
Ref: http://dev.mysql.com/doc/refman/5.0/en/getting-unique-id.html
-MikeR
|
|
|
|
frustrated
|
| Posted: 10/13/2005, 6:49 AM |
|
I resorted to my BIG MySQL reference binder (just about broke my arm). Found documentation! It was indeed a syntax issue:
Correct usage:
$LastID = CCDLookUp("last_insert_id()","performer","", $DBShowSched);
NO "ed"
It worked when I published!
DonB- forgive me, but you are over my head. Complete PHP/MYSQL newbie.
|
|
|
|
Walter Kempees
|
| Posted: 10/13/2005, 6:57 AM |
|
Datadoit, you are correct only for autoincremented value, but I took that
from the original post as being te case.
Not to be cheecky but if he know the value of the id when adding a row, he
wouldnot have to ask MySql to retreive it , (grin)
DonB, Sorry but I disagree it is a MySQL statement Select last_insert_id
from <table>.
Frustrated, glad it work(ed) out sorry for the additional "ed"
Happy Charging all.
Do we all agree that the example is great but this is the one we need?
Walter
"frustrated" <frustrated@forum.codecharge> schreef in bericht
news:5434e65df3e07c@news.codecharge.com...
>I resorted to my BIG MySQL reference binder (just about broke my
>arm).
> Found documentation! It was indeed a syntax issue:
> Correct usage:
> $LastID = CCDLookUp("last_insert_id()","performer","",
> $DBShowSched);
>
>
> NO "ed"
>
> It worked when I published!
>
> DonB- forgive me, but you are over my head. Complete PHP/MYSQL newbie.
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
donsafar
Posts: 90
|
| Posted: 10/13/2005, 8:33 AM |
|
The php function to accomplish the same is mysql_insert_id(). According to the php documentation; If your AUTO_INCREMENT column has a column type of BIGINT, the value returned by mysql_insert_id() will be incorrect. Instead, use the internal MySQL SQL function LAST_INSERT_ID() in an SQL query.
_________________
Don Safar
|
 |
|
DonB
|
| Posted: 10/13/2005, 11:25 AM |
|
Once again, I see I screwed up the names of the functions (in an earlier
post). I typed in an incorrect name by combining half from one and half
from the other. Duh... I keep doing that.
It's interesting a BIGINT breaks one and not the other.
--
DonB
http://www.gotodon.com/ccbth
"donsafar" <donsafar@forum.codecharge> wrote in message
news:5434e7e381486b@news.codecharge.com...
> The php function to accomplish the same is mysql_insert_id(). According
to the
> php documentation; If your AUTO_INCREMENT column has a column type of
BIGINT,
> the value returned by mysql_insert_id() will be incorrect. Instead, use
the
> internal MySQL SQL function LAST_INSERT_ID() in an SQL query.
>
> _________________
> Don Safar
> http://www.insights-web.com
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
|
