[Sum and join 2 tables]

23 hours ago, Barand said:

Have you considered using ifnull() on that too?

SELECT ..., ifnull(b.qty_bot, 0) as qty_bot, ...

Or an aternative to isnull() is coalesce()

SELECT ..., coalesce(b.qty_bot, 0) as qty_bot, ...

 

Thanks

[Sum and join 2 tables]

On 1/31/2023 at 2:20 PM, Barand said:

Your query joins each record from 'a' to many records from 'b' thus multiplying your totals.

Try

SELECT a.prod_id, a.prod_name
     , a.qty_received
     , b.qty_bot
     , a.qty_received - b.qty_bot as qty_remain
FROM (
        SELECT prod_id
             , prod_name
             , sum(prod_qty) as qty_received
        FROM tbl_distribution
        WHERE staff_id = 2962
        GROUP BY prod_id
      ) a 
      LEFT JOIN 
      (
        SELECT prod_id
             , sum(qty_bought) as qty_bot
        FROM tbl_sales_bar
        WHERE staff_id = 2962
        GROUP BY prod_id
      ) b USING (prod_id);

Your tables are in dire need of normalization. For example, "prod_name" should occur once in your database (in a product table) and not be repeated in several tables.

PS

In future, don't post pictures of your data, post the data. It was not a great deal of fun building the test tables from those images so that I could test your  query.

like i thought, the ifnull did the magic. i was able to get the qty_remaining but i couldnt get the qty_bot to be 0. still showing null. i guess i just have to stick with the null of is there a way i could get 0 and not null?

SELECT 
a.prod_id, a.prod_name , a.qty_received , 
b.qty_bot , a.qty_received - ifnull(b.qty_bot, 0) as qty_remain 
FROM ( SELECT prod_id , prod_name , sum(prod_qty) as qty_received 
FROM tbl_distribution 
WHERE staff_id = 2962 GROUP BY prod_id ) a 
LEFT JOIN ( SELECT prod_id , sum(qty_bought) as qty_bot 
FROM tbl_sales_bar 
WHERE staff_id = 2962 GROUP BY prod_id ) b 
USING (prod_id);

 

[Sum and join 2 tables]

Thanks for help and pointers. your method seems easier but for the null value on goldberg.

i am going to work on the normalization of the tables.

i will like to adapt to your method. how can the null issue be resolved?
i am thinking might ifnull do the magic? lemme give it a shot though

[Sum and join 2 tables]

17 hours ago, Barand said:

You are referencing aliases before they have been defined. For example, you dont define the table alias "b" until the last line of the query but you are referencing it several time earlier subqueries in the query.

Why output identical values twice? ...

 

SELECT a.prod_name
     , a.prod_size 
     , ifnull(a.qty_received, 0) qty_received
     , ifnull(b.qty_bot, 0) qty_bot
     , ifnull(a.qty_received, 0) - ifnull(b.qty_bot, 0) qty_remain
  FROM (
     SELECT prod_id
     FROM tbl_distribution
     UNION
     SELECT prod_id
     FROM tbl_sales_bar
  ) t
  LEFT JOIN (
        SELECT
        prod_id, prod_name, prod_size,
        SUM(prod_qty) qty_received
        FROM tbl_distribution
        WHERE staff_id = 2962
        GROUP BY  prod_id
  ) a on a.prod_id = t.prod_id
  LEFT JOIN (
         SELECT prod_id,
         SUM(qty_bought) qty_bot
         FROM tbl_sales_bar
         WHERE staff_id = 2962
         GROUP BY prod_id
  ) b on b.prod_id = t.prod_id;
  

results

+---------------+-----------+--------------+---------+------------+
| prod_name     | prod_size | qty_received | qty_bot | qty_remain |
+---------------+-----------+--------------+---------+------------+
| 33            |           |           13 |       8 |          5 |
| Star Wrangler |           |            7 |       6 |          1 |
| Star          |           |           19 |       7 |         12 |
| Goldberg      |           |           10 |       0 |         10 |
+---------------+-----------+--------------+---------+------------+

 

woah! thanks a lot. this one accounts for the 10 goldberg that was not sold

[Sum and join 2 tables]

On 1/31/2023 at 2:20 PM, Barand said:

Your query joins each record from 'a' to many records from 'b' thus multiplying your totals.

Try

SELECT a.prod_id, a.prod_name
     , a.qty_received
     , b.qty_bot
     , a.qty_received - b.qty_bot as qty_remain
FROM (
        SELECT prod_id
             , prod_name
             , sum(prod_qty) as qty_received
        FROM tbl_distribution
        WHERE staff_id = 2962
        GROUP BY prod_id
      ) a 
      LEFT JOIN 
      (
        SELECT prod_id
             , sum(qty_bought) as qty_bot
        FROM tbl_sales_bar
        WHERE staff_id = 2962
        GROUP BY prod_id
      ) b USING (prod_id);

Your tables are in dire need of normalization. For example, "prod_name" should occur once in your database (in a product table) and not be repeated in several tables.

PS

In future, don't post pictures of your data, post the data. It was not a great deal of fun building the test tables from those images so that I could test your  query.

I just noticed something on this. the goldberg ought to remain 10 but its null. since nothing is sold from the quantity received, the quantity remaining should be be quantity received

[Sum and join 2 tables]

12 hours ago, Barand said:

OK, post it and I'll have look.

 

SELECT a.prod_name, a.prod_size, b.prod_name, b.prod_size,
  ifnull(b.qty_bot,0), ifnull(a.qty_received ,0)
  from (
     SELECT a.prod_id prod_id
     FROM tbl_distribution a
     UNION
     SELECT b.prod_id
     FROM tbl_sales_bar b
  ) t
  LEFT JOIN (
        SELECT
        a.prod_id, a.prod_name, a.prod_size,
        SUM(a.prod_qty) qty_received
        FROM tbl_distribution a
        WHERE a.staff_id = 2962
        GROUP BY  a.prod_id
  ) a on a.prod_id = b.prod_id
  LEFT JOIN (
         SELECT b.prod_id, b.prod_name, b.prod_size,
         SUM(b.qty_bought) qty_bot
         FROM tbl_sales_bar b
         WHERE b.staff_id = 2962
         GROUP BY  b.prod_id
  ) b on b.prod_id = t.prod_id"

Gave an error

[Sum and join 2 tables]

42 minutes ago, Barand said:

If you have a different problem, post it in a new topic.

OK. But it's the same issue I am trying to solve but with a different approach. I am just wondering why it did not work. Maybe I should just let go and stick with what you gave. Just wanted to learn more and feed my curiosity 

[Sum and join 2 tables]

3 hours ago, Barand said:

Your query joins each record from 'a' to many records from 'b' thus multiplying your totals.

Try

SELECT a.prod_id, a.prod_name
     , a.qty_received
     , b.qty_bot
     , a.qty_received - b.qty_bot as qty_remain
FROM (
        SELECT prod_id
             , prod_name
             , sum(prod_qty) as qty_received
        FROM tbl_distribution
        WHERE staff_id = 2962
        GROUP BY prod_id
      ) a 
      LEFT JOIN 
      (
        SELECT prod_id
             , sum(qty_bought) as qty_bot
        FROM tbl_sales_bar
        WHERE staff_id = 2962
        GROUP BY prod_id
      ) b USING (prod_id);

Your tables are in dire need of normalization. For example, "prod_name" should occur once in your database (in a product table) and not be repeated in several tables.

PS

In future, don't post pictures of your data, post the data. It was not a great deal of fun building the test tables from those images so that I could test your  query.

Sorry for the images used. just wanted to drive home my intentions. Noted though.

I will try and normalize the table more. I am just trying to avoid too many JOINs.

Thank you so very much cos this solved my question.

There is something else i was trying but was getting error though. I was thinking a UNION could achieve same result?

Dont want to start another discussion since the issue is solved else i would have posted my code so you could see and maybe tell what is wrong with it?

Can i post the code?

 

Thanks anyways

[Sum and join 2 tables]

I want to sum and join 2 tables. I am getting a wrong answer. What am i doing wrong please.

what i want is to get the sum of quantity bought and the sum of product quantity so that when i minus the product quantity for the quantity bought, i will get the balance which is the difference between the product quantity - quantity bought = available quantity

$stmt = $pdo->query("
   SELECT 
   a.prod_name, a.prod_size,
   b.prod_name, b.prod_size,
   SUM(b.qty_bought) qty_bot,
   SUM(a.prod_qty) qty_received
   FROM tbl_distribution a
   JOIN tbl_sales_bar b
   ON a.staff_id = b.staff_id
   WHERE a.staff_id = '$_GET[id]'
   GROUP BY  b.prod_id
");
WHILE($row = $stmt->fetch(PDO::FETCH_ASSOC)){
  echo '<tr>
           <td>'.$row["prod_name"].'</td>           
           <td>'.$row["prod_size"].'</td>
           <td>'.$row["qty_received"].'</td>
           <td>'.$row["qty_bot"].'</td>
           <td></td>
           </tr>';
}

Image shows what i want to achieve

tbl_distribution - the table for distribution
tbl_distribution_user - the table for a single user after sum and group
tbl_sales_bar - table for sales
tbl_sales_bar_user - table for a single user in bar after sum and group
result - what i intent to achieve as my end result after summing and grouping

thanks

[UPDATE VALUES]

Sorry. I've been away for a while. Thanks all. I will do as suggested.

ps
btw: i have been able to do the reset and delete as i planned initially but wont be implementing it. i will go with your suggestion.

Thanks

[UPDATE VALUES]

5 hours ago, mac_gyver said:

no. do not update quantities or delete data to accomplish this. databases are for recording information. by updating/deleting data, you lose an audit trail that would let you know if a programming mistake, an accidental key was pressed, or nefarious activity changed a value.

you would INSERT data for every order/transaction that affects a value. a sale would insert a row into an order/transaction table with a 'type' indicating it is for a sale, then insert row(s) into an order_item table for each item that was sold with the order_id, item_id, and quantity. to void a sale, you would insert another row into the order/transaction table with a 'type' indicating it is for a void/return, with a reference to the original order_id, then insert row(s) into the order_item table, with a negative quantity for the items that are returned and will be restocked (some of the items might have been kept, some might have been damaged, and won't be restocked.)

to get the total quantity you would just SUM() the quantities per item_id.

Thanks. But I am not totally deleting the data, i am moving them to a different table. I am still trying to comprehend the issue of not updating the records since the goods are returned. if we don't update the record, it means the record we have is not correct as the returned goods will be taken to the store for reselling. so we will have an excess that is not reflecting on the record. how do you deal with that? unless of course we do a different record and collation and i feel is one-to-many exercise. what do you think? 

[UPDATE VALUES]

Hello all,

In my shopping app. I want add a delete feature from the admin area. In case there is a dispute for a transaction, the admin can delete the transaction. The problem I am having is that when the transaction is deleted, i want the items bought to be added to their respective total. i.e if i have 200 pepsi, 30 cocacola, 20 fruit juice in stock and a customer bought 2 pepsi, 2 cocacola, 3 juice. definately the total in stock will reduce. I want a situation where after i delete the transaction the items bought will be added back to its remaining quantity.

previous quantity
pepsi 200
coke 30
fruit 20

after purchase
pepsi 180
coke 28
fruit 17

after delete
pepsi 200
coke 30
fruit 20

 

thanks

[display values based on selected]

In my database table, i have a field for price and quantity. Is there a way I could use a select to get 2 values from the database? What I mean is I will create a select field where when I select wholesale it will bring out the price and quantity and if I select retail, it will bring the price and the quantity. 

[idea and help needed]

I'm thinking aloud. Is there a way I could use a select to get 2 values from the database? What I mean is I will create a select field where when I select wholesale it will bring out the price per carton and the quantity available and if I select retail, it will bring the price per unit and the quantity available for the item. The values will be from the database of course

I'm thinking jquery will be able to do such.

[idea and help needed]

On 11/1/2021 at 9:07 AM, mac_gyver said:

to define what quantities are/are-not available, for each item, you need an item_carton table - id, item_id, quantity, any other useful status/flag columns. there can be cases where there is more than one carton size for an item, i.e. 15, 25, either concurrently or as time passes. there can also be cases where a unit item cannot be bought, only a full carton. you would assume that unit items can be bought, unless overridden by an entry in this table. you would also need a status flag, somewhere, indicating if the available quantity is limited to the stock on hand, i.e. more of the item cannot be obtained. if an item has an entry with this flag set, you could query to get the available quantity and display it on the item add to cart page as the maximum quantity available. for your example, there would be a row with the noodle item_id and quantity 15 to indicate that a carton quantity of 15 is available for that item. the lack of a row indicating that unit items cannot be bought would indicate that unit items are available.

when you search and list items on the add to cart page, you would left join the item table with the item_carton table to determine what can/can-not be bought for each item. for your example, you would list that single items and cartons with quantity 15 can be bought.

you would need an inventory table that would have a row inserted for every transaction that affects the inventory of an item. to handle both carton and unit items, in addition to item_id and quantity columns, you would have an item_carton_id column (an id from the above item_carton table.) if this column contains a value, it indicates the row is for a carton and the quantity is the number of cartons. if this column does not contain a value, the quantity is the number of unit items. for your example, when the 30 cartons were received, a row with the noodle item_id, 30 for the quantity, and the corresponding item_carton_id from the item_carton table would be inserted.

when the cart is finalized and converted to an order, you would insert a row into an order table, with the unique/one time order information,  establishing an order id, and insert row(s) for the carton/unit items into an order_item table. the order_item table would have an item_carton_id column, the same as defined for the inventory table (you will end up doing a UNION query between the two tables to determine the current carton/unit quantity of item(s)).

to determine if a full case needs to be opened and broken into unit items, you would then query to find the current unit quantity for the item id that was just inserted. if it is a negative value, you would then insert a row into the inventory table that deducts enough full case(s) and insert a row into the inventory table that adds that many case quantity of unit items.

for your example, when the order is submitted, you would insert a row for one carton and two unit items into the order_item table. since there are initially no unit items in the inventory, the above logic would get a negative 2, determine that this requires one carton to be broken into unit items, insert a row to deduct one full carton from the inventory, and insert a row with 15 unit items into the inventory table.

WOAH! I'm lost here! Will need to read and reread. Thanks

[idea and help needed]

15 hours ago, ginerjm said:

It seems from this discussion that the problem is not the coding of the app, but the handling of the inventory.  You need to address that issur with those in control of that facet. Without that your app would appear to be a terrible expense despite the best effort you put into it.  If those individuals cannot promise you a sound inventory number for your app to rely on, I'd back away rather than be blamed for the future entanglements you will appear to be responsible for.

Exactly! Inventory is the main headache

[idea and help needed]

2 hours ago, mac_gyver said:

to define what quantities are/are-not available, for each item, you need an item_carton table - id, item_id, quantity, any other useful status/flag columns. there can be cases where there is more than one carton size for an item, i.e. 15, 25, either concurrently or as time passes. there can also be cases where a unit item cannot be bought, only a full carton. you would assume that unit items can be bought, unless overridden by an entry in this table. you would also need a status flag, somewhere, indicating if the available quantity is limited to the stock on hand, i.e. more of the item cannot be obtained. if an item has an entry with this flag set, you could query to get the available quantity and display it on the item add to cart page as the maximum quantity available. for your example, there would be a row with the noodle item_id and quantity 15 to indicate that a carton quantity of 15 is available for that item. the lack of a row indicating that unit items cannot be bought would indicate that unit items are available.

when you search and list items on the add to cart page, you would left join the item table with the item_carton table to determine what can/can-not be bought for each item. for your example, you would list that single items and cartons with quantity 15 can be bought.

you would need an inventory table that would have a row inserted for every transaction that affects the inventory of an item. to handle both carton and unit items, in addition to item_id and quantity columns, you would have an item_carton_id column (an id from the above item_carton table.) if this column contains a value, it indicates the row is for a carton and the quantity is the number of cartons. if this column does not contain a value, the quantity is the number of unit items. for your example, when the 30 cartons were received, a row with the noodle item_id, 30 for the quantity, and the corresponding item_carton_id from the item_carton table would be inserted.

when the cart is finalized and converted to an order, you would insert a row into an order table, with the unique/one time order information,  establishing an order id, and insert row(s) for the carton/unit items into an order_item table. the order_item table would have an item_carton_id column, the same as defined for the inventory table (you will end up doing a UNION query between the two tables to determine the current carton/unit quantity of item(s)).

to determine if a full case needs to be opened and broken into unit items, you would then query to find the current unit quantity for the item id that was just inserted. if it is a negative value, you would then insert a row into the inventory table that deducts enough full case(s) and insert a row into the inventory table that adds that many case quantity of unit items.

for your example, when the order is submitted, you would insert a row for one carton and two unit items into the order_item table. since there are initially no unit items in the inventory, the above logic would get a negative 2, determine that this requires one carton to be broken into unit items, insert a row to deduct one full carton from the inventory, and insert a row with 15 unit items into the inventory table.

WOAH! I'm lost here! Will need to read and reread. Thanks

[idea and help needed]

21 hours ago, requinix said:

"Best" is extremely subjective.

What ideas have you come up with so far?

What I have in mind is to convert all the cartons to pieces. But I envisage a problem of conflict somewhere. I have not been able to put the problem to perspective though. I want to do the thinking and computation first before I start codifying it.

[idea and help needed]

46 minutes ago, Psycho said:

A potential problem is that such a system could never really "know" how many cartons you have vs. loose items unless the person fulfilling the order explicitly enters the number of cartons vs/ loose items. If that is not important, than I would define each product with a column to identify the "bulk quantity". Store the inventory in the total number of items (regardless of cartons). Then, whenever calculating a transaction just divide by the bulk quantity. The whole divisor would be the number of whole cartons and the remainder would be the individual units.

My thoughts are almost like yours.

[idea and help needed]

pls i am looking for the best way to get this done: i want to be able to deal with both retail and wholesales from one spot. in my frontend store, a customer can decide to chose buying just an item or the whole pack. e.g i want to buy noodles, when i type noodles, it will give me an option to buy either 1 or 2 or the whole carton or also i can buy a carton and some pieces. meanwhile any item bought from the front end should be deducted from the quantity in stock (e.g a cartoon of noodle contains 15 pieces and i have 30 cartons of noodle in stock. if a carton and 2 pieces are bought, it should deduct the item i.e 30 cartons minus 1 carton and 2 pieces = 28 cartons and 13 pieces as the remain stock on noodles)

thanks

[Update multiple row at once]

43 minutes ago, Barand said:

A javascript/AJAX process is a common alternative

ok sir. how can that be done with the above mentioned?

[Update multiple row at once]

thanks all.

i got it up and running now. but Mr Barand, is there a way i could have stored the items into the database without the use of form? meanwhile like i tried explaining, the values are derived from session so the page i had problem is the checkout page. 

 

thanks again

[Update multiple row at once]

5 minutes ago, requinix said:

Then what are you "updating"?

Also,

number_format($values["product_quantity"] * $values["product_price"], 2)

don't do that. I know you think you won't deal with figures above 1000 but number_format will add commas ("1,000") which PHP will not be able to reverse into a regular number. If you want to show a formatted number then go ahead and do that, but the value itself has to be unformatted.
Which brings us back to the "what are you updating" question.

I really want to update the record. I mean to subtract the quantity of items bought to that in stock. that is if a customer buys 12 coke and 15 milk, they will be deducted from the total in stock. hope you get it.

thanks

[Update multiple row at once]

14 hours ago, requinix said:

Next time, please post all of your code intact instead of extracting the pieces you think are relevant. That said, with some educated guesses to fill in the blanks,

If your HTML form is including lots of rows to update, you can't give every "product name" field the same "prod_name" field. It's like you wrote code

$prod_name = "First product";
$prod_name = "Second product";
$prod_name = "Third product";

Think about it: what's $prod_name going to be?

Use [ ]s in your form field names to create arrays, similar to the code

$prod_name[] = "First product";
$prod_name[] = "Second product";
$prod_name[] = "Third product";

In this case, I would suggest using the product_ids (is it just one ID, right?) as one array key, then "product_name" and such as secondary array keys. Means you can remove the hidden prod_id field too.

'<td><input type="text" class="form-control" readonly name="products['.$values["product_ids"].'][prod_name]"  value="'.$values["product_name"].'" /></td>'

But... the fields are readonly?

Sorry but i don't quite get you. the files are read only cos user is not expected to do any form of input on the page. the values are from another page which is the front end. this page is the checkout page.

[Update multiple row at once]

12 hours ago, Barand said:

When it's finished, send us a link to the site. I love buying from sites where the user can specify their own prices (0.01) for each item purchased 😀

All you should be sending from the form is product ID and qty. Price will be stored in your product table.

Hahahahahahaha. Users don't get to input their own price. this page is actually to send the order to the database and print. meanwhile its for online, I'm developing it for my wife to use in her shop. so everything is ran on localhost.