A Mountain Top
  • Home
  • |
  • Archive
  • |
  • Friends
  • |
  • Portfolio
  • |
  • Resume
  • |
  • Why I Blog
Get an Estimate
Home

mysql

MySQL 5 Stored Procedure Examples with PHP

Thu, 04/26/2007 - 14:25 — mikehostetler

I've received a lot of interest from my previous post about MySQL Stored Procedures and PHP 5. So, I decided to set up an example archive of the stored procedures I've written for a Inventory Management project I'm currently working on. I've got 13 examples up so far, with more to come. Check it out.

  • mikehostetler's blog
  • 1 comment
  • mysql
  • mysqli
  • PHP
  • stored procedure

Inventory Allocation Count Total Served Stored Procedure

After an Inventory Allocation has been created, Partners need to be chosen. Thus, we cannot fill in the value of Total Served in the Inventory Allocation table until we've chosen all our partners. The value is calculated and stored because it is used frequently in the distribution algorithm.

<?php---- Inventory Allocation Count Total Served Stored Procedure--USE `cics_app`;DROP PROCEDURE IF EXISTS Ia_CountTotalServed;DELIMITER |CREATE PROCEDURE Ia_CountTotalServed (IN ia_id INT)BEGIN    DECLARE total_served INT;    SELECT         SUM(`net_served`) INTO total_served    FROM         `ia_partner`    WHERE        `ia_partner`.`ia_id` = ia_id;    SELECT total_served;END;|DELIMITER ;?>

  • Add new comment
  • cics
  • mysql
  • mysqli
  • stored procedure

Inventory Allocation Sub-System

The Inventory Allocation functionality in my Inventory Management system is designed to distribute units from inventory to a number of partners. There's way to many business rules associated with this piece to describe here, but I wanted to at least give a high-level picture.

<?php
--
-- Table structure for table `ia`
--

CREATE TABLE `ia` (
`ia_id` int(10) unsigned NOT NULL auto_increment,
`date` date NOT NULL default '0000-00-00',
`closed` tinyint(1) NOT NULL default '0',
`locked` tinyint(1) NOT NULL default '0',
`total_weight` int(10) default '0',
`total_served` int(10) default '0',
PRIMARY KEY (`ia_id`),
KEY `IDX_ia_date` (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `ia_unit`
--

CREATE TABLE `ia_unit` (
`ia_unit_id` int(10) unsigned NOT NULL auto_increment,
`ia_id` int(10) unsigned NOT NULL default '0',
`category_id` int(10) unsigned NOT NULL default '0',Read more

  • Add new comment
  • cics
  • mysql
  • mysqli
  • stored procedure

Transaction Max Batch Number Stored Procedure

This is somewhat simplistic. The reason I implemented a simple MAX() query is to move towards centralizing all of my custom SQL into stored procedures.

<?php---- Transaction Maximum Batch Number Stored Procedure--USE `cics_app`;DROP PROCEDURE IF EXISTS Transaction_MaxBatchNumber;DELIMITER |CREATE PROCEDURE Transaction_MaxBatchNumber ()BEGIN    DECLARE batch_number INT;    SELECT         MAX(`transaction`.`batch_number`) INTO batch_number     FROM        `transaction`;    SELECT batch_number;END;|DELIMITER ;?>

  • Add new comment
  • cics
  • mysql
  • mysqli
  • stored procedure

Transaction Unit Balance Stored Procedure

<?php---- Transaction Unit Balance Stored Procedure--USE `cics_app`;DROP PROCEDURE IF EXISTS Transaction_UnitBalance;DELIMITER |CREATE PROCEDURE Transaction_UnitBalance (IN unit_number INT, IN unit_account_id INT)BEGIN    DECLARE unit_balance, unit_credit, unit_debit INT;    SELECT         SUM(t.amount) INTO unit_credit    FROM         transaction AS t    WHERE        t.unit_number = unit_number      AND        t.credit_account_id = unit_account_id;    SELECT         SUM(t.amount) INTO unit_debit    FROM         transaction AS t    WHERE        t.unit_number = unit_number      AND        t.debit_account_id = unit_account_id;    SET unit_debit = IFNULL(unit_debit,0);    SET unit_credit = IFNULL(unit_credit,0);    SET unit_balance = unit_debit - unit_credit;    SELECT unit_balance;END;|DELIMITER ;?>

  • Add new comment
  • cics
  • mysql
  • mysqli
  • stored procedure

Transaction Open Units Stored Procedure

This function was easy to write after writing the Transaction Open Batches Stored Procedure.
<?php---- Transaction Open Units Stored Procedure--USE `cics_app`;DROP PROCEDURE IF EXISTS Transaction_OpenUnits;DELIMITER |CREATE PROCEDURE Transaction_OpenUnits (IN unit_account_id INT)BEGIN    SELECT         `transaction`.`unit_number` AS unit_no,        `category`.`category_id` AS category_id,        `category`.`calc_name` AS category,        IFNULL(            IFNULL((                SELECT SUM(`transaction`.`amount`) FROM `transaction`                WHERE `unit_number` = `unit_no` AND `credit_account_id` = unit_account_id            ),0)        -            IFNULL((                SELECT SUM(`transaction`.`amount`) FROM `transaction`                WHERE `unit_number` = `unit_no` AND `debit_account_id` = unit_account_id            ),0)        ,0) AS unit_balance    FROM        `transaction`    LEFT JOIN `category` ON `transaction`.`category_id` = `category`.`category_id`    WHERE         `transaction`.`unit_number` > 0    GROUP BY        `transaction`.`unit_number`    HAVING        unit_balance > 0;END|DELIMITER ;?>

  • Add new comment
  • cics
  • mysql
  • mysqli
  • stored procedures

Transaction Open Batches Stored Procedure

This was a fun procedure to write. I originally found the IFNULL function for my Account Balance Store Procedure. This query, when directly implemented in PHP was MUCH more complicated because of the possibilities of returning a NULL value. Now, it's all done in one statement.

<?php
--
-- Transaction Open Batches Stored Procedure
--

USE `cics_app`;

DROP PROCEDURE IF EXISTS Transaction_OpenBatches;

DELIMITER |
CREATE PROCEDURE Transaction_OpenBatches (IN batch_account_id INT)
BEGIN
SELECT
`transaction`.`batch_number` AS batch_no,
IFNULL(
IFNULL((
SELECT SUM(`transaction`.`amount`) FROM `transaction`
WHERE `batch_number` = `batch_no` AND `credit_account_id` = batch_account_id
),0)
-
IFNULL((
SELECT SUM(`transaction`.`amount`) FROM `transaction`Read more

  • Add new comment
  • cics
  • mysql
  • mysqli
  • stored procedure

Transactioon Max Unit Number Stored Procedure

<?php---- Transaction Maximum Unit Number Stored Procedure--USE `cics_app`;DROP PROCEDURE IF EXISTS Transaction_MaxUnitNumber;DELIMITER |CREATE PROCEDURE Transaction_MaxUnitNumber ()BEGIN    DECLARE unit_number INT;    SELECT         MAX(`transaction`.`unit_number`) INTO unit_number     FROM        `transaction`;    SELECT unit_number;END;|DELIMITER ;?>

  • Add new comment
  • cics
  • mysql
  • mysqli
  • stored procedure

Transaction Count Batch Stored Procedure

<?php---- Transaction Count Batch Number Stored Procedure--USE `cics_app`;DROP PROCEDURE IF EXISTS Transaction_CountBatchNumber;DELIMITER |CREATE PROCEDURE Transaction_CountBatchNumber (IN batch_number INT)BEGIN    DECLARE batch_count INT;    SELECT         COUNT(`transaction`.`batch_number`) INTO batch_count    FROM        `transaction`    WHERE        `transaction`.`batch_number` = batch_number;    SELECT batch_count;END;|DELIMITER ;?>

  • Add new comment
  • cics
  • mysql
  • mysqli
  • stored procedure

Transaction Batch Balance Stored Procedure

This is a procedure that simply gets the balance of a batch. A batch is a business level object that is reflected at this level. This procedure is a direct descendant of the Account Balance Stored Procedure.

<?php
--
-- Transaction Batch Balance Stored Procedure
--

USE `cics_app`;

DROP PROCEDURE IF EXISTS Transaction_BatchBalance;

DELIMITER |
CREATE PROCEDURE Transaction_BatchBalance (IN batch_number INT, IN batch_account_id INT)
BEGIN
DECLARE batch_balance, batch_credit, batch_debit INT;

SELECT
SUM(t.amount) INTO batch_credit
FROM
transaction AS t
WHERE
t.batch_number = batch_number
AND
t.credit_account_id = batch_account_id;

SELECT
SUM(t.amount) INTO batch_debit
FROM
transaction AS t
WHERE
t.batch_number = batch_number
AND
t.debit_account_id = batch_account_id;

SET batch_debit = IFNULL(batch_debit,0);
SET batch_credit = IFNULL(batch_credit,0);Read more

  • cics
  • mysql
  • mysqli
  • stored procedure
  • 1
  • 2
  • next ›
  • last »

WHAT WE DO

  • Programming


Navigation

  • blogs
  • books
  • compose tips
  • content
  • Recent posts

‹ Login / Register ›

NEW FROM THE BLOG

Date of Entry
Blog Entry 1

Date of Entry
Blog Entry 2

Date of Entry
Blog Entry 3

Date of Entry
Blog Entry 4

Advertisments

ReputationDefender
Moeller Electric
toner
Cheap Web Hosting

GET AN ESTIMATE

Complete the form below.



© 2006 - 2010 A Mountain Top, LLC, All rights reserved.