Calling SQL foreign key data with PHP

Question

I need to get a better grasp on the process of manipulating and utilizing the SQL tables I need to make so I can continue figuring out exactly how I should make them and structure them to work.

If I have a table for shirts and another table for sizes and I use a foreign key on the shirts table to link to the sizes table to represent multiple options for that column. Do I only need to call on the shirts table in the PHP coding? If so how do I tell the PHP to gather whatever options are available for each row on the sizes table?

If in the table it has

vneck sizes,

scoop neck sizes

and I have it set where the vnecks only have s,m,l,1x and the scoop necks have xs,s,m,l,1x,2x,3x. How can I code the PHP to recognize the difference I have logically set in each row for that column?

Answer 1

With the following table structure:

CREATE TABLE `shirt` (
    `id` INTEGER NOT NULL,
    `name` VARCHAR(32),
    PRIMARY KEY( `id` )
);

CREATE TABLE `size` (
    `id` INTEGER NOT NULL,
    `name` VARCHAR(4),
    PRIMARY KEY( `id` )
);

CREATE TABLE `shirt_size` (
    `shirtId` INTEGER NOT NULL,
    `sizeId` INTEGER NOT NULL,
    PRIMARY KEY( `shirtId`, `sizeId` ),
    FOREIGN KEY( `shirtId` ) REFERENCES `shirt`( `id` ),
    FOREIGN KEY( `sizeId` ) REFERENCES `size`( `id` )
);

And this data:

INSERT INTO
    `shirt` ( `id`, `name` )
VALUES
    ( 1, "vneck" ),
    ( 2, "scoop neck" );

INSERT INTO
    `size` ( `id`, `name` )
VALUES
    ( 1, "xs" ), ( 2, "s" ), ( 3, "m" ),
    ( 4, "l" ), ( 5, "1x" ), ( 6, "2x" ), ( 7, "3x" );

INSERT INTO
    `shirt_size` ( `shirtId`, `sizeId` )
VALUES
    ( 1, 2 ), ( 1, 3 ), ( 1, 4 ), ( 1, 5 ),
    ( 2, 1 ), ( 2, 2 ), ( 2, 3 ), ( 2, 4 ), ( 2, 5 ), ( 2, 6 ), ( 2, 7 );

In MySQL you could do:

SELECT
    `shirt`.`id`,
    `shirt`.`name`,
    GROUP_CONCAT( `size`.`name` ) as `sizes`
FROM
    `shirt`
JOIN
    `shirt_size`
    ON `shirt_size`.`shirtId` = `shirt`.`id`
JOIN
    `size`
    ON `size`.`id` = `shirt_size`.`sizeId`
GROUP BY `shirt`.`id`;

Which would result in something like:

+----+------------+-------------------+
| id | name       | sizes             |
+----+------------+-------------------+
|  1 | vneck      | s,m,l,1x          |
+----+------------+-------------------+
|  2 | snoop neck | xs,s,m,l,1x,2x,3x |
+----+------------+-------------------+

Not sure if other RDBMS's have aggregate functions similar to MySQL's GROUP_CONCAT(). If not, then use something like:

SELECT
    `shirt`.`id`,
    `shirt`.`name` as `shirtName`,
    `size`.`name` as `sizeName`
FROM
    `shirt`
JOIN
    `shirt_size`
    ON `shirt_size`.`shirtId` = `shirt`.`id`
JOIN
    `size`
    ON `size`.`id` = `shirt_size`.`sizeId`;

Which will give you multiple rows for every size, with each shirt.