William Jiang

JavaScript,PHP,Node,Perl,LAMP Web Developer – http://williamjxj.com; https://github.com/williamjxj?tab=repositories

MySQL trigger and jQuery selector

MySQL: Trigger

Suppose we have 3 tables, 1 master table t1 and 2 slave tables: t2, t3. They are associated with column ‘name’. When record in master table t1 is deleted, the assciated records in t2 and t3 should be deleted either. This is where the trigger used.

The following is my cascade delete trigger example:

delimiter //
create trigger delete_3cascade_tables 
after 
delete on t1 for each row
begin
  delete from t2 where name=OLD.name;
  delete from t3 where name=OLD.name;
end;//
delimiter ;

It works fine. By the way, if want to check this trigger, use the following sql:

select * from information_schema.triggers 
where trigger_schema='Database_Where_Triggers_In';

jQuery: Auto refresh table’s td column

I have the following html table to list data from MySQL DB.

<table>
<tr>
<td>...</td>
<td><form method="post" action="<?=$this->url;?>" id="f_<?=$row['id'];?>">
  <textarea name="comments" 
    onchange="MyFakeClass.comment('f_<?=$row['id'];?>')">
  <?=$row['comment'];?></textarea>
  <input type="hidden" name="id" value="<?=$row['id'];?>" />
  </form>
</td>
<td><label><?=$row['username']?$row['username']:'N/A';?></label></td>
</tr>
</table>

When user edits the textarea column, the editing comment is auto stored in MySQL table, also refreshes the next <td> that marks which users does the editing.

Ajax is good. By using ‘onchange=”MyFakeClass.comment(‘f_<?=$row[‘id’];?>’)”>’, when comment changes, some magic steps are done to control screen and dynamic data in Ajax Style.
The following is my simple snippet; it implements the above 2 functions: submit form and refresh screen.

MyFakeClass = {
...
  comment : function(fid) {
    $.post(MyFakeClass.url, $('#'+fid).serialize(), function(data) {
        $('#'+fid).parent().parent().find('td:last label').text(data);
    });
    return false;
  }
}
  • Here use form for 2 things: uses it’s post method; adds a hidden field to pass id. An alternative way to do so is manually processing textarea by using $.trigger() without form.
  • MyFakeClass.url = $(‘#’+fid).attr(‘action’); This makes it standard to make the form readable.
  • Function comment() returns false is a ncessary: So the <form> will never submit, this is exactly what it should.
  • When form submit, the value in <td><label><?=$row[‘username’]?$row[‘username’]:’N/A’;?></label></td> will be auto updated based on Ajax Response; same as the <textarea> field.
  • By using powerful jQuery selector, it is easy to locate where to dynamically add meesage in the html table.

The codes work pretty good.

Advertisements

One response to “MySQL trigger and jQuery selector

  1. business daily 03/22/2011 at 9:52 pm

    This short tutorial will summarizes the best practices of using MySQL real escape string. One of the primary reasons are that this function is know to cause problems in PHP web applications in the long run particularly if you use this along with mysql real escape string in preventing MySQL injection attacks…As an overview if you do not know this function very well. It is best to look at your PHP configuration file php.ini and make sure it is disabled …magic quotes gpc Off…if you turn it on and you use mysql real escape string then you end up double escaping your quotes which is NOT good…Second mysql real escape string function does not prevent injection in non-string inputs..Reason Supposing you have this string input with quotes ..hello..To prevent any possibility of MySQL injection attacks it will escape the quote so it will become ..hello..So with quoted strings the function actually prevents the injection.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s