how do I add a column with type array using phpMyAdmin or sql-CodePudding

how do I add a column with type array using phpMyAdmin knowing that alter table name add column listid integer array; alter table name add column listid integer []; not working

CodePudding user response：

As mentioned in the comments, an array is not a type. You could opt to instead have a separate table which keeps the elements in the array, and let them have a foreign key referring to the original table, or parse the array into a String each time and store it as text, depending on your needs.

CREATE TABLE orders (
  id INT NOT NULL PRIMARY KEY,
  description TEXT,
  reference TEXT
  -- This is where you'd want to add your list of order lines
);

-- Instead, we'll create an orderline table referring back to the orders
CREATE TABLE orderlines (
  id INT NOT NULL PRIMARY KEY,
  description TEXT,
  order_id INT REFERENCES orders(id)
);

Now you can put your array values (which I now assume to be the order lines) in their own separate table. To query them, you could do

SELECT * FROM orders
LEFT JOIN orderlines ON orderlines.order_id = orders.id;

Which you could probably make smart enough using sub-queries to return you an array, especially within your application.