bill
|
| Posted: 12/29/2009, 1:09 PM |
|
I'm using MySql 5
When inserting, updating or deleting entries into a table I need to write to
a log file.
This will simply be a MySQL table called LOG with a simple format Record ID,
several key column values, UserID, DateTime
I assume I will need to do this as custom SQL code on a event on each of the
Insert, Change, Delete buttons
I think I need to do something like...
$db = new clsDBmyDb();
$SQL = "INSERT INTO log (Record ID, several key column values, UserID,
DateTime) ".
"VALUES (".
This is where I'm stuck - how do I get the values from the current entry
form ??
Any ideas on how this can be done would be helpful.
|
|
|
 |
damian
Posts: 838
|
| Posted: 12/29/2009, 1:40 PM |
|
probably you would do this on
after insert
after update
after delete
events.....
_________________
if you found this post useful take the time to help someone else.... :)
|
 |
|
Aleister
Posts: 73
|
| Posted: 12/29/2009, 2:23 PM |
|
If your form has a textbox named e.g. RecordID and another named DayOfWeek you can get their values in after insert/update/delete like this:
// After insert / after / update / after delete...
$vID = $db->ToSQL($Component->RecordID->GetValue(), ccsInteger);
$vDay = $db->ToSQL($Component->DayOfWeek->GetValue(), ccsDate);
So after your code you add your variables:
$SQL = "INSERT INTO log (Record ID, several key column values, UserID,
DateTime) ".
"VALUES (".$vID." , ".$vDay ...e.t.c
...
...
$db->close();
Have in mind that if you have an auto-increment Record ID, you cannot get it's value in after insert from the record, you need to get it's value from the database.
|
|
|
|
datadoit
|
| Posted: 12/29/2009, 3:56 PM |
|
Aleister wrote:
> Have in mind that if you have an auto-increment Record ID, you cannot get it's
> value in after insert from the record, you need to get it's value from the
> database.
> ---------------------------------------
You should be able to grab that auto-incremented RecordID in the
AfterInsert event via:
$RecordID = mysql_insert_id();
|
|
|
|
bill
|
| Posted: 12/30/2009, 7:07 AM |
|
didn't work - so I simplified it with simple table
custom code in After Insert
$db = new clsDBmydb();
$SQL = "INSERT INTO users (userid, name) Values(2, user2)";
$db->query($SQL);
$db->close();
what am I doing wrong?
"Aleister" <Aleister@forum.codecharge> wrote in message
news:54b3a814aa1bc4@news.codecharge.com...
> If your form has a textbox named e.g. RecordID and another named DayOfWeek
> you
> can get their values in after insert/update/delete like this:
>
> // After insert / after / update / after delete...
> $vID = $db->ToSQL($Component->RecordID->GetValue(), ccsInteger);
> $vDay = $db->ToSQL($Component->DayOfWeek->GetValue(), ccsDate);
>
> So after your code you add your variables:
>
> $SQL = "INSERT INTO log (Record ID, several key column values, UserID,
> DateTime) ".
> "VALUES (".$vID." , ".$vDay ...e.t.c
> ..
> ..
> $db->close();
>
>
> Have in mind that if you have an auto-increment Record ID, you cannot get
> it's
> value in after insert from the record, you need to get it's value from the
> database.
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.yessoftware.com/
>
|
|
|
|
bill
|
| Posted: 12/30/2009, 8:05 AM |
|
$userid="2";
$name="user2";
$db = new clsDBmydb();
$SQL = "INSERT INTO users (userid, name) Values($userid, $name)";
$db->query($SQL);
$db->close();
No error and not entry iun the users table!
"bill" <bill@nospamplease.com> wrote in message
news:hhfqb2$9oj$1@news.codecharge.com...
> didn't work - so I simplified it with simple table
>
> custom code in After Insert
>
> $db = new clsDBmydb();
> $SQL = "INSERT INTO users (userid, name) Values(2, user2)";
> $db->query($SQL);
> $db->close();
>
> what am I doing wrong?
>
>
> "Aleister" <Aleister@forum.codecharge> wrote in message
>news:54b3a814aa1bc4@news.codecharge.com...
>> If your form has a textbox named e.g. RecordID and another named
>> DayOfWeek you
>> can get their values in after insert/update/delete like this:
>>
>> // After insert / after / update / after delete...
>> $vID = $db->ToSQL($Component->RecordID->GetValue(), ccsInteger);
>> $vDay = $db->ToSQL($Component->DayOfWeek->GetValue(), ccsDate);
>>
>> So after your code you add your variables:
>>
>> $SQL = "INSERT INTO log (Record ID, several key column values, UserID,
>> DateTime) ".
>> "VALUES (".$vID." , ".$vDay ...e.t.c
>> ..
>> ..
>> $db->close();
>>
>>
>> Have in mind that if you have an auto-increment Record ID, you cannot get
>> it's
>> value in after insert from the record, you need to get it's value from
>> the
>> database.
>> ---------------------------------------
>> Sent from YesSoftware forum
>> http://forums.yessoftware.com/
>>
>
>
|
|
|
|
Aleister
Posts: 73
|
| Posted: 12/30/2009, 11:37 AM |
|
Quote :
$SQL = "INSERT INTO users (userid, name) Values($userid, $name)";
You are sending $name without single quotes. Change it to this:
$SQL = "INSERT INTO users (userid, name) Values(".$userid.", '".$name."')";
or to this:
$SQL = "INSERT INTO users (userid, name) Values(".$userid.", ".$db->ToSQL($name, ccsString).")";
|
|
|
|
bill
|
| Posted: 12/30/2009, 3:00 PM |
|
Tried both you suggestions
Replacing ccsString with ccsText)
Still nothing entered into the table! and no error!
"Aleister" <Aleister@forum.codecharge> wrote in message
news:54b3babf4c3eaf@news.codecharge.com...
> Quote :
> $SQL = "INSERT INTO users (userid, name) Values($userid, $name)";
>
> You are sending $name without single quotes. Change it to this:
>
> $SQL = "INSERT INTO users (userid, name) Values(".$userid.",
> '".$name."')";
>
> or to this:
>
> $SQL = "INSERT INTO users (userid, name) Values(".$userid.",
> ".$db->ToSQL($name, ccsString).")";
>
>
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.yessoftware.com/
>
|
|
|
|
Aleister
Posts: 73
|
| Posted: 12/30/2009, 3:48 PM |
|
Try to echo $SQL and copy / paste it to phpMyAdmin SQL window and see what happens.
|
|
|
|
bill
|
| Posted: 12/30/2009, 4:22 PM |
|
Ok - still not writing to file AfterInsert custom code
No echo either
So I created a Blank Page and put a button one it then in the OnClick
custome code I put the same code
$userid="12";
$name="Fred Flitstone";
$db = new clsDBrefdb();
$SQL = "INSERT INTO uid (userid, name) Values(".$userid.",
".$db->ToSQL($name, ccsText).")";
$db->query($SQL);
$db->close();
echo $SQL;
STILL NOTHING
So I put the code in the BEFORE SHOW of the Button and it works.
Even shows echo of the SQL - best of all Record is inserted!
It looks like I'm putting the code in the WRONG place!
But, BEFORE SHOW is the WRONG place
Any ideas?
"Aleister" <Aleister@forum.codecharge> wrote in message
news:54b3be6c58e37c@news.codecharge.com...
> Try to echo $SQL and copy / paste it to phpMyAdmin SQL window and see what
> happens.
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.yessoftware.com/
>
|
|
|
|
Aleister
Posts: 73
|
| Posted: 12/30/2009, 6:04 PM |
|
After Insert / After Update / After Delete is the correct place to put custom code for logging.
For some reason the event custom code isn't fired. Make sure that you haven't any errors in _events page. A forgotten semicolon, unclosed quotes, a /* without a matching */ etc.
Copy paste all _events page code to a php editor with syntax check. If there are no errors, do the same for the main .php file as you might have some error in a property of a component.
|
|
|
|
bill
|
| Posted: 12/31/2009, 2:24 AM |
|
Tried it on another page - it worked.
Deleted all custom code on the page where I was having a problem, Published
Then re-added the code and published
This time it worked.
Now I still can't get one of the values I need from the form.
$name=$db->ToSQL($questions->type->GetValue(), ccsText);
Where the Page is called questions_maint, Form is called questions and the
column I need value from is type
Fatal error: Call to a member function ToSQL() on a non-object in
C:\wamp\www\mydb\questions_events.php on line 73
This referes to the above line
"Aleister" <Aleister@forum.codecharge> wrote in message
news:54b3c06c847681@news.codecharge.com...
> After Insert / After Update / After Delete is the correct place to put
> custom
> code for logging.
>
> For some reason the event custom code isn't fired. Make sure that you
> haven't
> any errors in _events page. A forgotten semicolon, unclosed quotes, a /*
> without
> a matching */ etc.
>
> Copy paste all _events page code to a php editor with syntax check. If
> there
> are no errors, do the same for the main .php file as you might have some
> error
> in a property of a component.
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.yessoftware.com/
>
|
|
|
|
Aleister
Posts: 73
|
| Posted: 12/31/2009, 6:15 AM |
|
Why don't you use:
Quote :
$name=$db->ToSQL($Component->type->GetValue(), ccsText);
$Component is your record and "type" is your control. Also, try to rename your control from "type" to something else.
|
|
|
|
bill
|
| Posted: 12/31/2009, 6:34 AM |
|
Did try that before
Now just tried one of the other controls from the record
$name=$db->ToSQL($Component->publisher->GetValue(), ccsText);
Same error
// Write your own code here.
$userid=mysql_insert_id();
$name=$db->ToSQL($Component->publisher->GetValue(), ccsText);
$db = new clsDBmydb();
$SQL = "INSERT INTO users (userid, name) Values(".$userid.",
".$db->ToSQL($name, ccsText).")";
$db->query($SQL);
$db->close();
echo $SQL;
// -------------------------
//End Custom Code
"Aleister" <Aleister@forum.codecharge> wrote in message
news:54b3cb1f14c217@news.codecharge.com...
> Why don't you use:
> Quote :
> $name=$db->ToSQL($Component->type->GetValue(), ccsText);
>
> $Component is your record and "type" is your control. Also, try to rename
> your
> control from "type" to something else.
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.yessoftware.com/
>
|
|
|
|
Aleister
Posts: 73
|
| Posted: 12/31/2009, 6:55 AM |
|
I just saw that the error is on ToSQL, not on the control.
You can use $db->ToSQL() only after you create $db using $db = new clsDBmydb();
You try to use $db just before you define it, change it like this:
// Write your own code here.
$db = new clsDBmydb();
$userid=mysql_insert_id();
$name=$db->ToSQL($Component->publisher->GetValue(), ccsText);
$SQL = "INSERT INTO users (userid, name) Values(".$userid.",
".$db->ToSQL($name, ccsText).")";
$db->query($SQL);
$db->close();
echo $SQL;
// -------------------------
//End Custom Code
|
|
|
|
bill
|
| Posted: 12/31/2009, 7:14 AM |
|
Thanks
that sort of did it - my flawed thinking was that I needed to get value from
Current form BEFORE a new connection is made!
Value received is enclosed in speach marks ie. "Test" but I can do string
manipulation to remove the "" marks
"Aleister" <Aleister@forum.codecharge> wrote in message
news:54b3cbb747331e@news.codecharge.com...
>I just saw that the error is on ToSQL, not on the control.
>
> You can use $db->ToSQL() only after you create $db using $db = new
> clsDBmydb();
>
> You try to use $db just before you define it, change it like this:
>
> // Write your own code here.
> $db = new clsDBmydb();
> $userid=mysql_insert_id();
> $name=$db->ToSQL($Component->publisher->GetValue(), ccsText);
> $SQL = "INSERT INTO users (userid, name) Values(".$userid.",
> ".$db->ToSQL($name, ccsText).")";
> $db->query($SQL);
> $db->close();
> echo $SQL;
> // -------------------------
> //End Custom Code
>
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.yessoftware.com/
>
|
|
|
|
bill
|
| Posted: 12/31/2009, 7:56 AM |
|
In case it's use to anyone.
To removing the single quotes
I replaced $name=$db->ToSQL($Component->publisher->GetValue(), ccsText);
with
$name=str_replace("'","",$db->ToSQL($Component->publisher->GetValue(),
ccsText));
"bill" <bill@nospamplease.com> wrote in message
news:hhif3v$su6$1@news.codecharge.com...
> Thanks
>
> that sort of did it - my flawed thinking was that I needed to get value
> from Current form BEFORE a new connection is made!
>
> Value received is enclosed in speach marks ie. "Test" but I can do string
> manipulation to remove the "" marks
>
>
>
> "Aleister" <Aleister@forum.codecharge> wrote in message
>news:54b3cbb747331e@news.codecharge.com...
>>I just saw that the error is on ToSQL, not on the control.
>>
>> You can use $db->ToSQL() only after you create $db using $db = new
>> clsDBmydb();
>>
>> You try to use $db just before you define it, change it like this:
>>
>> // Write your own code here.
>> $db = new clsDBmydb();
>> $userid=mysql_insert_id();
>> $name=$db->ToSQL($Component->publisher->GetValue(), ccsText);
>> $SQL = "INSERT INTO users (userid, name) Values(".$userid.",
>> ".$db->ToSQL($name, ccsText).")";
>> $db->query($SQL);
>> $db->close();
>> echo $SQL;
>> // -------------------------
>> //End Custom Code
>>
>> ---------------------------------------
>> Sent from YesSoftware forum
>> http://forums.yessoftware.com/
>>
>
>
|
|
|
|
Aleister
Posts: 73
|
| Posted: 12/31/2009, 8:34 AM |
|
By doing this:
$name=str_replace("'","",$db->ToSQL($Component->publisher->GetValue(),
ccsText));
you first add quotes and then you remove them... Why do that? $db->ToSQL() converts the value of "publisher" to correct format (ccsInteger, ccsFloat, ccsText etc) only for using it for SQL queries. If you want, you can do this yourself without ever using it. E.g. for a string you can do this as an alternative:
$name="'".$Component->publisher->GetValue()."'";
for a float you can use (float)$varname, e.t.c.
|
|
|
|
datadoit
|
| Posted: 12/31/2009, 10:50 AM |
|
If you suspect that the fielded data you're attempting to write to the
database has quotes in it's value, you're going to need to handle that
accordingly by either removing them or replacing them. You're on the
right track with the str_replace() function, but as stated you're adding
in the delimeter then removing it. That may actually create a database
error in some situations.
Clean the fielded data first:
$quotes = array(chr(34), chr(39), chr(44)); //Use the ASCII table.
$data = str_replace($quotes, chr(0), $Container->YourField->GetValue());
Now write that info into the database:
$SQL = "INSERT INTO log SET YourField = " . CCToSQL($data, ccsText);
//Be ANSI standards friendly with your SQL.
Recognize that you can use CCToSQL() at any time, any place, any where
without having to establish a new connection.
|
|
|
|
|
