MySQL CREATE TABLE

Nickosssss

Hello there,

lets say i have a table

create table customers (cust_id, cust_f_name, ......)

how can i create a table from that one where the each customer will appear as a column

for example

thank you

AstroTeg

Is this a database table you wish to create or an HTML table?

I ask because you really don't want to store like data "horizontally." Think if you had to search that data! You'd have to do something like: WHERE Cust_f_name[1] like '%name%' or Cust_f_name[2] like '%name%' or Cust_f_name[3] like '%name%' (this would suck!) Plus, how many customers could you have? Databases will only be able to store so much data per row or have so many fields per table and you'll be bound to run out of room at some point.

But if you store it like in your first example with all the customers going "vertically." You can do some PHP tricks to present the data in an HTML way similar to your 2nd example. One technique is called cross-tab reports which is something Excel likes doing.

Nickosssss

well it is a database table from which i need to put the customers like u saw before because for each product they will be assigned a specific value....In this case the customers will be only 10 to max 15. Its a special case which i dont want to explain because u will be comfused. 🙂

Because i am using the prod_id as a reference key....
so i tried to do insert into table select prod_id from products....this populated the prod_id with the keys and then the only way i found to do it is ALTER TABLE table ADD customer , ADD customer2, ADD customer3,.....

thank u mate

AstroTeg

Why not use a 2nd table to associate the product to the customers? Then, if only 10 customers need be assigned to product X and 15 to product Y and 100 to product Z, you don't have to modify any code or table structure. If you need them in a particular order, then include a rank value that you can use to keep track of the order of the customers for that particular product.

Nickosssss

there are lets say 50 products and only 15 customers ok ?
so this new table needs to be

prod_id Cust1 Cust2 Cust 3..........Cust 10
001
002
003
....
....
050

i can insert values to the Customers with UPDATE because i have assigned the default value to be INT 0

the problem is that i need to automate this procedure and to have the customers linked with the products they have already chosen.

because i have only the prod_id as reference key, this causes me trouble to find a way to do the right associations.....

the customers already exist on the customers table...

here are the commands i am using so u can have an idea of what i am trying to do

drop table staff_stud_alloc;
CREATE TABLE allocation
(
prod_id INT UNSIGNED ,
PRIMARY KEY (prod_id),
FOREIGN KEY (prod_id) REFERENCES products (prod_id)

	ON DELETE CASCADE
	ON UPDATE CASCADE

)ENGINE = InnoDB;

then:

insert into allocation select prod_id from products;

then:

ALTER table allocation ADD cust1 INT DEFAULT 0, ADD cust2 INT DEFAULT 0, ADD cust3 INT DEFAULT 0, .........

AstroTeg

Silly question, because its not clear from the post, but are you aware of inner join and left joins? If not, that might be why this is a struggle. If you are aware of them, you can just join a lookup table to the product and customer tables.

Nickosssss

well i know they exist...and had tried them in past....but thanks mate...i ll look at them again ....🙂

dnast

I very much agree with AstroTeg. Designing your database his way will save you a lot of trouble in the future. Take a look at the Database Normalization article on this site. You'll only have to get to the third page (the pages aren't long) to see how to apply it to your project.