Link and display values from 3 normalized tables using PHP and MYSQL in an HTML table

Question

0.00/5 (No votes)

See more:

I want to link and display values from 3 normalized tables using PHP and MYSQL in an HTML table. 1st table is called uom:

id  |  uom
1   |  piece
2   |  dozen
3   |  set

2nd table called products:

id   |  itemNo   |  title   |   quantity
84   |  19421      bags         10
88   |  7878       chests       20
89   |  123456     test         30

3rd table is called products_uom:

id  |  product_id  |  uom_id  |  quantity_package  |  price
1   |   84         |  1       |         1          |   10.00
2   |   84         |  2       |         12         |   20.00
3   |   84         |  3       |         1          |   30.00
4   |   88         |  1       |         1          |   60.00

and here is my HTML table:

<table border='1'>
    <tr>
        <th>item No.</th>
        <th>title</th>
        <th>uom and price</th>
        <th>quantity</th>
    </tr>

Now in uom and price I need to display all uom of a product with its price + display the uom as text not id (uom column) like this (piece:10 dozen:20) for bags.

What I have tried:

I made this code:

SQL

SELECT products.id, products.itemNo, products.title,
GROUP_CONCAT(uom.uom) 
AS uom_price, 
GROUP_CONCAT(products_uom.price) 
AS price, products.quantity
FROM ((products
INNER JOIN products_uom
ON products_uom.product_id = products.id)
INNER JOIN uom
ON products_uom.uom_id = uom.id)
GROUP BY products.id

and then I did a while loop afterwards, and then when I reached to uom and price, I did that,

$uom_price = explode(",", $product['uom_price']);
$price_price = explode(",", $product['price']);
echo $uom_price[0].':'.$price_price[0].' ';
echo $uom_price[1].':'.$price_price[1].' ';
echo $uom_price[2].':'.$price_price[2].' ';

and that's the result https://jsfiddle.net/vapfchr6/ and this what I want to achieve https://jsfiddle.net/aczshequ/ it is almost exactly what I wanted but there is an issue. In uom and price for chests, it displays piece:60.00 :: with 2 colons, how to fix this issue? I think I need to do if statement if there are more uoms display the colon, but I don't know how to do it in my code above... Also there is an optional issue, I need to display the prices without the double zeros at the end 10.00 > 10 unless there are numbers...

Posted 4-Mar-22 21:41pm

FRS4002

Updated 5-Mar-22 22:28pm

v18

Add a Solution

2 solutions

Solution 2

SELECT products.id, products.itemNo, products.title,
GROUP_CONCAT(uom.uom) 
AS uom_price, 
GROUP_CONCAT(products_uom.price) 
AS price, products.quantity
FROM ((products
INNER JOIN products_uom
ON products_uom.product_id = products.id)
INNER JOIN uom
ON products_uom.uom_id = uom.id)
GROUP BY products.id

and then while loop. Afterwards,

$uom_price = explode(",", $product['uom_price']);
$price_price = explode(",", $product['price']);
echo $uom_price[0].':'.$price_price[0].' ';
if(!empty($uom_price[1])){
   echo $uom_price[1].':'.$price_price[1].' ';
    }
if(!empty($uom_price[2])){
   echo $uom_price[2].':'.$price_price[2].' ';
  }

Posted 5-Mar-22 22:26pm

FRS4002

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard MacCutchan · Accepted Answer · 2022-03-04T22:00:00

Solution 1

You need to use the JOIN clause to select items from multiple tables at the same time. See the description and examples at MySQL Joins[^].

Posted 4-Mar-22 22:00pm

Richard MacCutchan

Comments

FRS4002 5-Mar-22 4:09am

@Richard MacCutchan Could you please provide me for the right MYSQL code for my situation?

Richard MacCutchan 5-Mar-22 4:52am

Something like:

SELECT products.itemNo, products.title, uom.uom, products_uom.price, products.quantity
FROM ((products
INNER JOIN uom ON products_uom.uom_id = uom.id)
INNER JOIN products_uom ON products_uom.product_id = products.id);

However, I do not have any way to test this so you may need to modify it. Also, I would suggest you spend time studying SQL syntax in general, and MySQL in particular, if you intend to use this in your career.

FRS4002 5-Mar-22 7:22am

Thanks, but weird, it says "Unknown column 'products_uom.uom_id' in 'on clause'". Also there are 4 kinds of joins, which to use in my case? Is it only inner join?

Richard MacCutchan 5-Mar-22 6:58am

Sorry, I had the JOINS out of order, try:

SELECT products.itemNo, products.title, uom.uom, products_uom.price, products.quantity
FROM ((products
INNER JOIN products_uom ON products_uom.product_id = products.id)
INNER JOIN uom ON products_uom.uom_id = uom.id);

FRS4002 5-Mar-22 7:27am

Thanks, now I tried the code in phpmyadmin and I didn't get any issues. Now pardon me, you didn't answer my question above, there are 4 kinds of joins, which to use in my case? Is it only inner join? Also, now if I want to implement this in PHP I am thinking to do a while loop correct me if I am wrong

while($product = mysqli_fetch_assoc($presults)){-----}

Now how can I display a specific value from joins? like this?

$product['uom.uom'];

Does this works or there is a right way?

Richard MacCutchan 5-Mar-22 7:48am

1. I gave you the link to the documentation on JOINS above, it explains everything.
2. You need to run some tests. However, as far as I can guess the field names returned by the query will be: "itemNo", "title", "uom", "price" and "quantity".

FRS4002 5-Mar-22 8:00am

Thanks, I have tried the above MYSQL code that you wrote but I got almost everything repeated...

Richard MacCutchan 5-Mar-22 8:55am

What does that mean?

FRS4002 5-Mar-22 9:02am

This is what I got https://jsfiddle.net/mz9aoxyb/
and this what I want to achieve https://jsfiddle.net/aczshequ/

Richard MacCutchan 5-Mar-22 9:06am

Then you will need to write some code (SQL or Javascript) to combine items that have the same products.itemNo.

FRS4002 5-Mar-22 9:25am

I am thinking of doing GROUP BY(title) for example, but I don't know if it can help. Also, if it helps I don't know how to implement it in MYSQL code above...

Richard MacCutchan 5-Mar-22 9:29am

Sorry, but my SQL skills are not up to writing more complicated statements. I would suggest you use the Improve question link above, and add the additional requirements to your question. With luck one of the experts will offer the answer.

Richard MacCutchan 5-Mar-22 9:35am

On second thoughts try:

SELECT products.itemNo, products.title, uom.uom, products_uom.price, SUM(products.quantity) FROM ((productsINNER JOIN products_uom ON products_uom.product_id = products.id)INNER JOIN uom ON products_uom.uom_id = uom.id)GROUP BY products.id, uom.id;

FRS4002 6-Mar-22 0:57am

It gives almost same result as the previous MYSQL code... I made this code:

SELECT products.id, products.itemNo, products.title, GROUP_CONCAT(uom.uom) AS uom_price, GROUP_CONCAT(products_uom.price) AS price, products.quantity FROM ((products INNER JOIN products_uomON products_uom.product_id = products.id)INNER JOIN uomON products_uom.uom_id = uom.id) GROUP BY products.id

and did a while loop afterwards but this is what I got https://jsfiddle.net/b3dv4ckf/
and this what I want to achieve https://jsfiddle.net/aczshequ/
A little bit similar to my goal. Now I need to print the prices against each uom like so piece:10 dozen:20. How can I do that in PHP?

Richard MacCutchan 6-Mar-22 3:11am

You probably need to split the requests so that you get the piece and bag entries separately. You can then use PHP functions to combine the details.

FRS4002 6-Mar-22 3:18am

What do you mean by split requests? You mean I should do another MYSQL code and add it to a variable?

Richard MacCutchan 6-Mar-22 3:39am

Yes, use two separate SELECT statements. It may be possible to achieve what you want with a single request but I am afraid my skills are not good enough to find the answer.

FRS4002 6-Mar-22 4:06am

I just did that

$uom_price = explode(",", $product['uom_price']);$price_price = explode(",", $product['price']);echo $uom_price[0].':'.$price_price[0].' ';echo $uom_price[1].':'.$price_price[1].' ';echo $uom_price[2].':'.$price_price[2].' ';

and that's the result https://jsfiddle.net/vapfchr6/ it is almost exactly what I wanted but as you can see there is an issue. In uom and price for chests, it displays piece:60.00 :: with 2 colons, how to fix this issue? I think I need to do if statement if there are more uoms display the colon, but I don't know how to do it in my code above... Also there is an optional issue, I need to display the prices without the double zeros at the end 10.00 > 10 unless there are numbers...

Richard MacCutchan 6-Mar-22 4:13am

You just need to check if any of the fields are null, and ignore them if so.

FRS4002 6-Mar-22 4:24am

Yep! I edited the code to:

$uom_price = explode(",", $product['uom_price']);
$price_price = explode(",", $product['price']);
echo $uom_price[0].':'.$price_price[0].' ';
if(!empty($uom_price[1])){
   echo $uom_price[1].':'.$price_price[1].' ';
    }
if(!empty($uom_price[2])){
   echo $uom_price[2].':'.$price_price[2].' ';
  }

and it worked! But I have 2 questions, in my situation which is better to use !is_null or !empty? Also, regarding my previous question there is an optional issue, I need to display the prices without the double zeros at the end 10.00 > 10 unless there are numbers...

Richard MacCutchan 6-Mar-22 7:02am

You should make use of the documentation: See PHP number_format() Function[^] and PHP: is_null - Manual[^].

FRS4002 6-Mar-22 7:09am

Thanks a lot!