posting data to different mysql tables

Easyb · August 2, 2011, 1:45pm

Hi there,
I have 3 mysql tables… one is just a daily registry, logging in details of a particular stock from a certain supplier. The other is to store sum of the each stock’s quantity and the other to store details of an order. The one that stores details of an order should interact with the one that stores the sum of each stock’s quantity.
Below is my code snippet:
[php]
//Part 1: Retrieves from bridge table
$q1 = “SELECT quantity FROM bridge WHERE Category=’$cat’ AND type = ‘$typ’ AND family = ‘$fam’”;
$r1 = mysqli_query($dbc, $q1) or die (mysqli_error($dbc));

       $q2 = "SELECT sacks FROM bridge WHERE Category = '$cat' AND type = '$typ' AND family= '$fam'";
       $r2 = mysqli_query($dbc, $q2) or die (mysqli_error($dbc));

        $r11 = mysqli_fetch_array($r1);
            $r22 = mysql_fetch_array($r2); 
     
        $r4 = $r11 + $qty;
        $r5 = $r22 + $sck;
        
        $q3 = "UPDATE bridge SET quantity='$r4' AND sacks='$r5' WHERE type = '$typ'";
        $stmt2 = mysqli_query($dbc, $q3) or die (mysqli_error($dbc));

// Part 2: Add the order to orders table:
$q = “INSERT INTO orders (entry_date, customer, category, family, type, quantity, unit_price, tot_price, rate, sacks )
VALUES (’$ed’, ‘$cust’, ‘$cat’, ‘$fam’, ‘$typ’, ‘$qty’, ‘$up’, ‘$tp’, ‘$rte’, ‘$sck’)”;
$stmt = mysqli_query($dbc, $q) or die (mysqli_error($dbc));
// Check the results…
if (($stmt) && ($stmt2)) {

        // Print a message:
        echo 'Thank you. Order is Submitted';

             } else { // Error!
        echo 'Your submission could not be processed due to a system error.';
    }

    mysqli_close($dbc);[/php]

Brief Explanation:
Part 1 should pull off some information (quantity and sacks) from the table that stores the sum of the stock’s quantity, assign that information to a variable and then get the new value being ordered and add to it the one pulled from the table and later post it back to that same table.

Part 2 just gets the order and posts it to the orders table.

I am stuck trying to achieve this… Any thoughts are welcome…

Regards,

sergo_bero · August 2, 2011, 2:53pm

first of all , you can user JOIN with that tables , but i cant get what you want to achieve , can you describe or give us some example ?

Easyb · August 2, 2011, 4:09pm

Thanks for reply… below are my 3 tables:

--Table
CREATE TABLE IF NOT EXISTS `products` (
  `pid` int(8) unsigned NOT NULL AUTO_INCREMENT,
  `entry_date` date NOT NULL,
  `category` varchar(60) NOT NULL,
  `family` varchar(60) NOT NULL,
  `type` varchar(60) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `quantity` decimal(6,2) unsigned NOT NULL,
  `unit_price` decimal(6,2) NOT NULL,
  `tot_price` decimal(11,2) NOT NULL,
  `rate` decimal(6,2) unsigned NOT NULL,
  `sacks` decimal(6,2) unsigned NOT NULL,
  `supplier` varchar(255) NOT NULL,
  `harvest_date` date NOT NULL,
  PRIMARY KEY (`pid`),
  KEY `category` (`category`),
  KEY `quantity` (`quantity`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;

--
-- Dumping data for table `products`
--

INSERT INTO `products` (`pid`, `entry_date`, `category`, `family`, `type`, `description`, `quantity`, `unit_price`, `tot_price`, `rate`, `sacks`, `supplier`, `harvest_date`) VALUES
(8, '2011-07-29', 'Pulses', 'Peas', 'Pigeon Peas', 'Fresh', 200.00, 2300.00, 460000.00, 100.00, 2.00, 'Farmers Group', '2011-07-11');

--Table 2:
CREATE TABLE IF NOT EXISTS `orders` (
  `order_id` int(8) unsigned NOT NULL AUTO_INCREMENT,
  `entry_date` date NOT NULL,
  `customer` varchar(255) NOT NULL,
  `category` varchar(60) NOT NULL,
  `family` varchar(60) NOT NULL,
  `type` varchar(60) NOT NULL,
  `quantity` decimal(6,2) unsigned NOT NULL,
  `unit_price` decimal(6,2) NOT NULL,
  `tot_price` decimal(11,2) NOT NULL,
  `rate` decimal(6,2) unsigned NOT NULL,
  `sacks` decimal(6,2) unsigned NOT NULL,
  PRIMARY KEY (`order_id`),
  KEY `category` (`category`),
  KEY `quantity` (`quantity`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

--
-- Dumping data for table `orders`
--

INSERT INTO `orders` (`order_id`, `entry_date`, `customer`, `category`, `family`, `type`, `quantity`, `unit_price`, `tot_price`, `rate`, `sacks`) VALUES
(1, '2011-08-02', 'Mr. X', 'Cereals', 'Sorghum', 'Sorghum', 111.00, 250.00, 27750.00, 9999.99, 1.11);

--Table 3
CREATE TABLE IF NOT EXISTS `bridge` (
  `bid` int(8) unsigned NOT NULL AUTO_INCREMENT,
  `category` varchar(60) NOT NULL,
  `family` varchar(60) NOT NULL,
  `type` varchar(60) NOT NULL,
  `quantity` decimal(6,2) unsigned NOT NULL,
  `tot_price` decimal(11,2) NOT NULL,
  `sacks` decimal(6,2) unsigned NOT NULL,
  PRIMARY KEY (`bid`),
  KEY `category` (`category`),
  KEY `quantity` (`quantity`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=12 ;

--
-- Dumping data for table `bridge`
--

INSERT INTO `bridge` (`bid`, `category`, `family`, `type`, `quantity`, `tot_price`, `sacks`) VALUES
(11, 'Cereals', 'Maize', 'Longe 10H', 0.00, 200000.00, 3.00);

Problem:
Table 1 is updated when a new product is being registered.
Table 2 is as well updated when a new order is being made.
Table 3 is to act as a holder for the total of a particular product’s quantity… sort of sumation of all products of type X.
The respective php scripts that update (and view contents of) tables 1 and 2 are functioning well but the trouble is updating table 3 when a product of type x is registered and then performing the transaction such that when product x is ordered, that quantity ordered is deducted automatically from table 3.
Literally table 3 should contain totals of all the types of my products whenever updated or ordered.

Grateful for any further insight…

Cheers…

sergo_bero · August 2, 2011, 6:14pm

okay , first of all you dont need 2 query to get info,then use variable to add quantity and third query to update table , just run this query

$sql = "UPDATE bridge SET quantity = (quantity + " . $quant . ") , sacks = (sacks + " . $sack . ") WHERE type = '" . $typ . "';";

then just insert a new row into orders …

sergo_bero · August 2, 2011, 6:19pm

that query should be used when registering/adding a new product ,

use this when inserting new order ,

[php]$sql = "UPDATE bridge SET quantity = (quantity - " . $quant . ") , sacks = (sacks - " . $sack . “) WHERE type = '” . $typ . “’;”;[/php]
so , you will update your products and then in bridge quantity / sacks will be increased,
when user orders , the quantity/sack of order is decreased from the table bridge ,

or did i get something wrong ?

Easyb · August 2, 2011, 11:30pm

Thanks for tip… You got it exactly as I explained…

The following occur:

–> Script to update products and bridge tables runs but products table is updated and bridge table is updated only if there is an existing match of the type of product being registered. This means nothing is put into bridge table if product being added is new. Snippet is below:
[php]
//Update summary in bridge table if existing
$sql = "UPDATE bridge SET quantity = (quantity + " . $qty . ") , sacks = (sacks + " . $sck . “) WHERE type = '” . $typ . “’;”;
$stmt1 = mysqli_query($dbc, $sql) or die (mysqli_error($dbc));

// Add the product to the database:
$q = “INSERT INTO products (entry_date, category, family, type, description, quantity, unit_price, tot_price, rate, sacks, supplier, harvest_date ) VALUES (’$ed’, ‘$cat’, ‘$fam’, ‘$typ’, ‘$desc’, ‘$qty’, ‘$up’, ‘$tp’, ‘$rte’, ‘$sck’, ‘$sup’, ‘$hd’)”;

$stmt = mysqli_query($dbc, $q) or die (mysqli_error($dbc));

    // Check the results...
    if (($stmt) && ($stmt1)) {

	// Print a message:
        echo 'Success, product is added';

    } else { // Error!
        echo 'Product could not be added due to a system error.';
    }
mysqli_close($dbc);
}[/php]

–> Script to record orders and bridge tables runs as well but changes in the bridge only take effect if there is an existing match. This is perfect because we can’t order what is not there in stock unless it is pre-ordering. I will figure out to let the users first see the summary from the bridge table before proceeding to place an order.

Main Question:
How would some details (according to structure of bridge table) be posted to the bridge table if product is new and at the same time posted to the products table?
Subsequent updates should then be successful if new or existing.

More insight is appreciated…

Cheers…