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?

score 4 · Answer 1 · answered Dec 29 '12 at 01:16

It sounds like you actually need to have at least three tables, one for shirts, one for sizes , and one to relate shirts to sizes. There are any number of way you can use PHP to query the data, but most likely you would want to simply query using a JOIN to get data from all tables at the same time.

So perhaps something like this:

shirts table:
shirt_id (auto-incrementing primary key)
...other shirt-related fields

sizes table:
size_id (auto-incrementing primary key)
size_value (i.e. S, M, L)
...other size-related fields

shirt_sizes table:
shirt_id (foreign key to shirts table)
size_id (foreign key to sizes table)
(you have compound primary key across these two fields)

An you would query it like

SELECT * (or whatever fields you need)
FROM shirts
INNER JOIN shirt_sizes ON shirts.shirt_id = shirt_sizes.shirt_id
INNER JOIN size ON shirt_sizes.size_id = sizes.size_id

Decent Dabbler · Accepted Answer · 2012-12-29T11:10:24.700

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.

I see how this can work. I copy and pasted the scripts you gave and on the last one I get this error (Unknown column 'shirt_size.id' in 'on clause') I looked over the code and tried to figure out what was wrong myself but couldn't find it. This happened with both SELECT options you gave me, so idk. I'll keep looking it over to see if I can find what's wrong, but if you can find it please share, I think this will work. :) — Optiq, Dec 29 '12 at 09:41
I THINK I FIGURED IT OUT!!!!... shouldn't it be (shirt_size.size_id)?instead of (shirt_size.id)? I'll try that — Optiq, Dec 29 '12 at 09:45
sizeId worke. but there were no results in the table that popped up. — Optiq, Dec 29 '12 at 09:53
@DaMightyOptiq I'm sorry, I made a few errors: the last foreign key in table `shirt_size` was pointing to table `shirt` in stead of `size` and in the queries, I joined on `shirt_size.id`, which should have been `shirt_size.sizeId`. And in the first query I forgot to add a `GROUP BY` clause. I've fixed this all now. So, please remove the old three tables, and try recreating them again and insert the data again as well. It should work now. — Decent Dabbler, Dec 29 '12 at 11:00
YES!!!! THANK YOU!!!.. one thing though, the second select option only shows the shirt name and id columns, they populate the right amount of rows, but they don't have the name column from the size table present, how can we make that show up? — Optiq, Dec 29 '12 at 11:08
Ah yes, that's because two of the selected columns where both named `name`. I gave them unique names now: `shirtName` and `sizeName`. You should be good now. — Decent Dabbler, Dec 29 '12 at 11:12

Calling SQL foreign key data with PHP

2 Answers2