SQL database design issue (ecommerce)

PHP
1

Here is an abbreviated example of my database tables:

products table

id
title
size_id (foreign key from sizes table)
price

Sample rows/values for products table:
1, product1, 1, 2000
2, product1, 2, 3000
3, product1, 3, 4000
4, product2, 1, 1000
5, product2, 2, 1500
6, product2, 3, 2000

sizes table

id
size

Sample rows/values for sizes table:
1, small
2, medium
3, large

Ok, so here are my issues:

  1. I need to be able to loop through a returned row and display each product, BUT I have three of each product because of the size selection. I need to display the title of just one of those three for the customer to view.
  2. I need to be able to generate and display a price range. I need to get the lowest price for the products smallest size and the highest price for the products largest size in order to display the Title ($lowprice - $highprice).

I don’t even know if this is possible with SQL commands or php/sql techniques, but I am desperate. I have been trying to find a way to do this for over a month now and am absolutely stumped. If there are any SQL wizards out there I would greatly appreciate help.

2

This is not a hard task if build an array with your results.

<?php
$host = "localhost";
//Database user name.	
$login = "";
//Database Password.
$dbpass = "";
//Database name.
$dbname = "";
$PDO = new PDO("mysql:host=localhost;dbname=$dbname", "$login", "$dbpass");

try{
	$sql = "SELECT
	p.id,
	p.title,
	s.size,
	p.price
	FROM products AS p
	LEFT JOIN sizes AS s
	ON s.id = p.size_id";
	
	$stmt = $PDO->prepare($sql);
	$stmt->execute();
	$result = array();
    while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
		$result[$row['title']]['id'][]    = $row['id'];
		$result[$row['title']]['size'][]  = $row['size'];
		$result[$row['title']]['price'][] = $row['price'];
	}
	
}catch (PDOException $e){
	echo "Database error: ".$e->getMessage();
}

	//Start Display Section
	$display = "";
	foreach($result as $title => $arry){
	
		//Get min/max values for product
		$min = min($result[$title]['price']);
		$max = max($result[$title]['price']);
		
		//Display title section
		$display .= "<b>" . $title . "</b> (" . $min . "-" . $max . ")<br />";
		
		/*
		All product values have matching array keys, so we loop through one
		array i.e. $arry['id'] and use the key $k to get the value of each item.
		*/
		foreach($arry['id'] as $k => $v){		
			$id    = $result[$title]['id'][$k];
			$size  = $result[$title]['size'][$k];
			$price = $result[$title]['price'][$k];
			$display .= "ID # " . $id . " Size: " . $size . " Price: " . $price . "<br /><br />";
		}
	}  	
?>
<html>
<body>
<?php
if(isset($display)){ echo $display;}
?>
</body>
</html>

The result is as shown.

product1 (2000-4000)
ID # 1 Size: small Price: 2000

ID # 2 Size: medium Price: 3000

ID # 3 Size: large Price: 4000

product2 (1500-2500)
ID # 4 Size: small Price: 1500

ID # 5 Size: medium Price: 2000

ID # 6 Size: large Price: 2500

3

Thank you! This is exactly what I needed. Thanks so much for taking the time to write that out I greatly appreciate it.