Mysql, mysqli, pdo
There are (more
than) three popular ways to use MySQL from PHP.
1.
The mysql functions are
procedural and use manual escaping.
2.
mysqli is a
replacement for the mysql functions, with object-oriented and procedural
versions. It has support for prepared statements.
3.
PDO (PHP Data Objects) is a
general database abstraction layer with support for MySQL among many other
databases. It provides prepared statements, and significant flexibility in how
data is returned.
I would recommend using PDO with
prepared statements. It is a well-designed API and will let you more easily
move to another database (including any that supports ODBC) if necessary.
Store procedure:
Stored
Procedures Advantages
§ Stored
procedures help increase the performance of application using them. Once
created, stored procedures are compiled and stored in the database catalog. A
stored procedure typically runs faster than uncompiled SQL statements that are
sent from external applications.
§ Stored
procedures reduce the traffic between application and database server because
instead of sending multiple uncompiled lengthy SQL statements, the application
only has to send the stored procedure’s name with parameters.
§ Stored
procedures are reusable and transparent to any applications. Stored procedures
expose the database interface to all applications so developers don’t have to
develop functions that are already supported in stored procedure in those
applications.
§ Stored
procedures are secured. Database administrator can grant appropriate permission
to application that access stored procedures in database catalog without giving
any permission on the underlying database tables.
Stored
Procedures Disadvantages
§ Stored
procedures make the database server high load in both memory and processors.
Instead of being focused on the storing and retrieving data, you could be
asking the database server to perform a number of logical operations which is
not well-designed for database server.
Creation: Update user
DELIMITER
$$
DROP PROCEDURE IF EXISTS `test`.`update_user`$$
CREATE PROCEDURE `test`.`update_user`
(
IN userId INT,
IN firstName VARCHAR(100),
IN lastName VARCHAR(100)
)
BEGIN
update users set first_name=firstName,last_name=lastName where users_id=userId;
END $$
DELIMITER ;
DROP PROCEDURE IF EXISTS `test`.`update_user`$$
CREATE PROCEDURE `test`.`update_user`
(
IN userId INT,
IN firstName VARCHAR(100),
IN lastName VARCHAR(100)
)
BEGIN
update users set first_name=firstName,last_name=lastName where users_id=userId;
END $$
DELIMITER ;
Call
the store procedure with php:
$rs
= $mysqli->query( 'CALL
update_user(4,"Rakesh","mumtaz")' );
MySQL
triggers
In
MySQL, a trigger is a set of SQL statements that is invoked automatically when
a change is made to the data on the associated table. A trigger can be defined
to be invoked either BEFORE or AFTER data change by INSERT, UPDATE and DELETE statements.
MySQL allows you to define maximum six triggers for each table.
§ BEFORE
INSERT
§ AFTER
INSERT
§ BEFORE
UPDATE
§ AFTER
UPDATE
§ BEFORE
DELETE
§ AFTER
DELETE
Creating a
Trigger
We now require two triggers:- When a record is INSERTed into the blog table, we want
to add a new entry into the audit table containing the blog ID and a type
of ‘NEW’ (or ‘DELETE’ if it was deleted immediately).
- When a record is UPDATEd in the blog table, we want to
add a new entry into the audit table containing the blog ID and a type of
‘EDIT’ or ‘DELETE’ if the deleted flag is set.
Each trigger requires:
- A unique
name. I prefer to use a name which describes the table and
action, e.g. blog_before_insert or blog_after_update.
- The table
which triggers the event. A single trigger can only monitor a single
table.
- When
the trigger occurs. This can either be BEFORE or AFTER an INSERT, UPDATE
or DELETE. A BEFORE trigger must be used if you need to modify incoming
data. An AFTER trigger must be used if you want to reference the
new/changed record as a foreign key for a record in another table.
- The trigger
body; a set of SQL commands to run. Note that you can
refer to columns in the subject table using OLD.col_name (the previous
value) or NEW.col_name (the new value). The value for NEW.col_name can be
changed in BEFORE INSERT and UPDATE triggers.
CREATE
TRIGGER `event_name` BEFORE/AFTER INSERT/UPDATE/DELETE
ON `database`.`table`
FOR EACH ROW BEGIN
-- trigger body
-- this code is applied to every
-- inserted/updated/deleted row
END;
We require two triggers — AFTER INSERT and AFTER UPDATE on the blog
table. It’s not necessary to define a DELETE trigger since a post is marked as
deleted by setting it’s deleted field to true.The first MySQL command we’ll issue is a little unusual:
DELIMITER $$
Our trigger body requires a number of SQL commands separated by a
semi-colon (;). To create the full trigger code we must change delimiter to
something else — such as $$.Our AFTER INSERT trigger can now be defined. It determines whether the deleted flag is set, sets the @changetype variable accordingly, and inserts a new record into the audit table:
CREATE
TRIGGER `blog_after_insert` AFTER INSERT
ON `blog`
FOR EACH ROW BEGIN
IF NEW.deleted THEN
SET @changetype = 'DELETE';
ELSE
SET @changetype = 'NEW';
END IF;
INSERT INTO audit (blog_id, changetype) VALUES (NEW.id, @changetype);
END$$
Finally, we set the delimiter back to a semi-colon:
DELIMITER;
The AFTER UPDATE trigger is almost identical:
DELIMITER $$
CREATE
TRIGGER `blog_after_update` AFTER UPDATE
ON `blog`
FOR EACH ROW BEGIN
IF NEW.deleted THEN
SET @changetype = 'DELETE';
ELSE
SET @changetype = 'EDIT';
END IF;
INSERT INTO audit (blog_id, changetype) VALUES (NEW.id, @changetype);
END$$
DELIMITER ;
It’s beyond the scope of this article, but you could consider calling a
single stored procedure which handles both triggers.
Trigger Happy?
Let’s see what happens when we insert a new post into our blog table:
INSERT INTO blog (title, content) VALUES ('Article One', 'Initial text.');
A new entry appears in the `blog` table as you’d expect:
id
|
title
|
content
|
deleted
|
1
|
Article One
|
Initial text
|
0
|
id
|
blog_id
|
changetype
|
changetime
|
1
|
1
|
NEW
|
2011-05-20 09:00:00
|
UPDATE blog SET content = 'Edited text' WHERE id = 1;
As well as changing the post, a new entry appears in the `audit` table:
id
|
blog_id
|
changetype
|
changetime
|
1
|
1
|
NEW
|
2011-05-20 09:00:00
|
2
|
1
|
EDIT
|
2011-05-20 09:01:00
|
UPDATE blog SET deleted = 1 WHERE id = 1;
The `audit` table is updated accordingly and we have a record of when
changes occurred:
id
|
blog_id
|
changetype
|
changetime
|
1
|
1
|
NEW
|
2011-05-20 09:00:00
|
2
|
1
|
EDIT
|
2011-05-20 09:01:00
|
3
|
1
|
DELETE
|
2011-05-20 09:03:00
|
Trigger query sql
USE [doc0nedb]
GO
/****** Object: Trigger
[dbo].[manualtbbom] Script Date: 03/15/2013 07:50:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[manualtbbom]
ON
[dbo].[tbBOM]
AFTER
INSERT
AS
BEGIN
DECLARE @cdpkid int
DECLARE @bomdwgnumber varchar(255)
SELECT @cdpkid =
[ChildDocumentPKId],@bomdwgnumber = [BOMDwgNum] FROM
INSERTED
IF(@cdpkid IS NULL OR @cdpkid = '')
BEGIN
DECLARE @childdocpkid int
DECLARE @picnumber int
DECLARE @childissue int
DECLARE @childsizes varchar(2)
DECLARE @childtypes varchar(10)
SET @childdocpkid = (SELECT PKId FROM dbo.tbstorlib WHERE
DrawingID = @bomdwgnumber)
SET @picnumber = (SELECT MaterialNumber FROM
dbo.tbstorlib where
DrawingID = @bomdwgnumber)
SET @childissue = (SELECT Issue FROM dbo.tbstorlib where
DrawingID = @bomdwgnumber)
SET @childsizes = (SELECT Size FROM dbo.tbstorlib where
DrawingID = @bomdwgnumber)
SET @childtypes = (SELECT DwgType FROM
dbo.tbstorlib where
DrawingID = @bomdwgnumber)
UPDATE dbo.tbBOM SET ChildDocumentPKId =
@childdocpkid, PicNum =
@picnumber, ChildIssue =
@childissue, ChildSize =
@childsizes, ChildType =
@childtypes WHERE BOMDwgNum = @bomdwgnumber
END
END
No comments:
Post a Comment