How to alter a table (add a column) simultaneously when a row is added (column name = row id) in another table?

Question

How to write code in MySQL such that when ever a row with a primary key id is added to a table, a corresponding column is added in another table such that the name of the column is equal to the id of the row just added.

I tried the following but to no use.

sqlQuery("INSERT INTO table1(name) VALUES('$name')");
$id = sqlQuery("SELECT id FROM table1 WHERE id = LAST_INSERT_ID()");
$id = mysqli_fetch_array($id);
$id = $id['id'];
sqlQuery("ALTER TABLE table2 ADD '$id' INT(2) NOT NULL");

sqlQuery - user defined function that return mysqli_query result.

Any help would be great. Also, I'm a newbie. Sorry if this is a silly question to ask.

This stinks of bad design, and a complete lack of understanding of databases — Mark Baker, Jan 13 '15 at 09:15
Better read a bit about MySQL, and the difference between `'` and `\`` — Mark Baker, Jan 13 '15 at 09:16
@MarkBaker Sorry, but I'm new to MySQL and databases. Any help? — Faizuddin Mohammed, Jan 13 '15 at 09:18
@Daan I'm making a website that takes allows people to make orders. Orders involve name of the product and quantity. First table lists all the products and their details like price. Second table has the actual orders. Row with quantity. Any better design? — Faizuddin Mohammed, Jan 13 '15 at 09:19
Without knowing why you're doing it (what business requirement you're trying to solve) it's very difficult for anybody to advise you on how you should approach the problem — Mark Baker, Jan 13 '15 at 09:23
Why don't you just use mysql relationships? http://code.tutsplus.com/articles/sql-for-beginners-part-3-database-relationships--net-8561 What exactly is your goal and what are you trying to create? There are probably some better cleaner ways of getting your problem solved. — Koen van den Heuvel, Jan 13 '15 at 09:18
Thanks.. My actual goal is to have a table that can list product and quantity ordered by the customers along with the details of the customer. I have separate table listing details of all the products. — Faizuddin Mohammed, Jan 13 '15 at 09:26
Happy to help. There are a lot of examples on the internet for what you are trying to make. Once you have learned how relational database work in MySQL you will be able to come up with the answers on your own. — Koen van den Heuvel, Jan 13 '15 at 10:15
okay... Thanks! Also, would I need to learn how to update two tables simultaneously for this design? Are transactions necessary? — Faizuddin Mohammed, Jan 13 '15 at 10:37
Take a look at this question it already has some great answers. http://stackoverflow.com/questions/2044467/how-to-update-two-tables-in-one-statement-in-sql-server-2005 — Koen van den Heuvel, Jan 13 '15 at 10:51

score 0 · Answer 1 · answered Jan 13 '15 at 17:35

Make it OOP style and there is a var in the class that automatically returns the last updated item.

$con = new mysqli(SQL_HOST, SQL_USER, SQL_PASSWORD, SQL_DATABASE); //do normal error checking with database connection
$sql = "INSERT INTO table1(name) VALUES('$name')";
$con->query($sql);
$sql2 = "ALTER TABLE table2 ADD '$con->insert_id' INT(2) NOT NULL"  //$con->insert_id is the parm you are looking for.
$con->query($sql2);

How to alter a table (add a column) simultaneously when a row is added (column name = row id) in another table?

1 Answers1