CODEWITHSUNDEEP
phpsqldatabase
David
David

Reputation: 23

How do I display the name of a table on SQL

First of all, I'm from Spain so I'm sorry if I made some mistakes writing. So, I have two problems. It will be better if I give context before. I am not even junior, still learning code, and I thought that it will be a good proyect to create a web page where you can add ingredients, foods with that ingredients, etc. So I decided to start learning PHP and SQL. Now I'm trying to create a database, starting with some ingredients and two kinds of rices. My 1st problem is that I don't know if I need to create a data base for that. The second and main one is that I don't have any idea about how to get this working as I want.

See, First of all I created the table for ingredients´

CREATE TABLE ingredientes(
id       int(255) auto_increment not null,
ingrediente    varchar(255) not null,
CONSTRAINT pk_ingredientes PRIMARY KEY(id) )ENGINE=InnoDb;

Sorry 'cause it's on spanish :/, but nothing to hard to understand. So I add some ingredients. Here the pic showing them

After that I created two tables, and add ingredients to them.

CREATE TABLE arroz_con_pollo(
    id          int(255) auto_increment not null,
    ingrediente    int(255) not null,
    CONSTRAINT pk_arroz_con_pollo PRIMARY KEY(id),
    CONSTRAINT fk_pollo_ingredientes FOREIGN KEY(ingrediente) REFERENCES ingredientes(id) )ENGINE=InnoDb;

CREATE TABLE arroz_cubana(
    id             int(255) auto_increment not null,
    ingrediente    int(255) not null,
    CONSTRAINT pk_arroz_cubana PRIMARY KEY(id),
    CONSTRAINT fk_cubana_ingredientes FOREIGN KEY(ingrediente) REFERENCES ingredientes(id))ENGINE=InnoDb;

Here the picture showing the ID's.

Here

So now I spend a lot of time researching and find out that I can show the names by using this command

SELECT a.id,i.ingrediente
    FROM ingredientes i, arroz_cubana a
        WHERE i.id = a.id;

And have something like this

At this point, everything is, more or less, working. My issue came when I want to create a data base that keep all the names (arroz con pollo, arroz cubana...) in an only table named as 'rices' to be able to choose a name, and automatically have the ingredients there, without any complication for the user. But, I literally have no idea. I've been coding for hours without any victory on that. And I haven't see anything similar on the web so, if someone tell me how to fix that issue or how to make that idea of a web to keep ingredients and foods, I'll be very greatful.

Upvotes: 2

Views: 48

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

Your data structure is messed up. SQL is not designed to have a separate table for each ingredient. Instead, you want two other tables.

The first is for dishes:

CREATE TABLE dishes (
    dish_id int auto_increment not null,
    name varchar(255)
);

You would then insert appropriate rows into this:

INSERT INTO dishes (name)
    VALUES ('arroz_on_pollo');

Then you have another table for the ingredients:

CREATE TABLE dishes_ingredients (
    dish_ingredient_id  int auto_increment primary key,
    dish_id int not null
    ingredient_id int not null,
    CONSTRAINT fk_dish_ingredientes_dish FOREIGN KEY(dish_id) REFERENCES dishes(dish_id)
    CONSTRAINT fk_dish_ingredientes_dish FOREIGN KEY(ingredient_id) REFERENCES ingredientes(ingredient_id)
);

Voila! New dishes are just rows in a table, so you can get the names using a SELECT.

Notes on structure:

  • int(255) really makes no sense. Just use int. The number in parentheses is a width for the value when printing it and 255 is a ridiculous width.
  • I am a fan of naming primary keys with the table name. That way, the primary key and foreign key typically have the same name.

Upvotes: 2

alexis
alexis

Reputation: 50190

You should not have a table per dish. Create one table "dish", that includes a column "name". Each row represents a dish. Then create a supporting table where you list the (multiple) ingredients for each dish. Look around for a tutorial on databases, this topic is too large to explain in a stackoverflow question (or several).

And so you do not need to be able to list the table names, the way you were considering. (Which is not something SQL supports directly; different databases provide non-standard ways to do it, but as explained you do not actually need such a feature.)

Upvotes: 1

Related Questions