Mysql php create column

PHP MySQL
Create & Test MySQL Database

Existing tables can be altered using the mysql_query() function.

You can add or modify fields or columns in an existing table.

You can even delete an entire table and start over.

Note: If you have accessed this web page via a search engine, you should go back and start at the Beginning.
This tutorial is designed to be viewed and executed in sequence.
Learn to build your database right on your PC and Export it to your website.

Add a Column or Field to a Table

To add a column to an existing table the syntax would be:
mysql_query(«ALTER TABLE birthdays ADD street CHAR(30)»);

You can also specify where you want to add the field.
mysql_query(«ALTER TABLE birthdays ADD street CHAR(30) AFTER birthday»);

The simple bit of code shown above would add a new column to the birthdays table named street after birthday. Type and size are also set in the statement.

You can also add multiple fields:

mysql_query("ALTER TABLE birthdays ADD street CHAR(30) AFTER birthday, Add city CHAR(30) AFTER street, ADD state CHAR(4) AFTER city, ADD zipcode CHAR(20) AFTER state, ADD phone CHAR(20) AFTER zipcode");

Modify a Column or Field

Column definitions can be modified using the ALTER method. The following code would change the existing birthday column from 7 to 15 characters.

mysql_query(«ALTER TABLE birthdays CHANGE birthday birthday VARCHAR(15)»);

In the example the column to alter is first named and then the new definition is supplied which includes the column name

Remove a Column or Field

Columns can be removed from an existing table. The next example of code would remove the lastname column.
mysql_query(«ALTER TABLE birthdays DROP lastname»);

Remove a Table

Be careful with this code. It will remove an entire table and all of its contents from your database.
mysql_query(«DROP TABLE table_name»);

This script is included in the download zip file. If you run it, you will need to add the new fields to all of the existing scripts.

Copy & Save as: birthdays_add_fields.php

You should only run this script if you want to increase your knowledge by getting a little practice in updating the existing scripts.

Note: All ofhe operations covered above can be performed from the phpMyAdmin panel.

MySQL Tutorial

To extend your knowledge of MySQL study the Docs and Tutorials at the official MySQL website. MYSQL.com

Источник

Tutorial: How to Add Column in MySQL Database using PHP with Source Code

About How to Add Column in MySQL Database using PHP

In this tutorial, I’m going to show you How to Add Column in MySQL Database using PHP. I’ve also included in this tutorial the use of GROUP BY in MySQL query and the two mysqli methods which I have included in the comments. This tutorial does not include a good design but will give you an idea on the topic.

Creating a Database

First, we’re going to create our database.

2. Click databases, create a database and name it as “sum”.

3. After creating a database, click the SQL and paste the below codes. See image below for detailed instruction.

CREATE TABLE `product` ( `productid` INT(11) NOT NULL AUTO_INCREMENT, `product_name` VARCHAR(30) NOT NULL, PRIMARY KEY(`productid`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `sales` ( `salesid` INT(11) NOT NULL AUTO_INCREMENT, `productid` INT(11) NOT NULL, `sales_qty` DOUBLE NOT NULL, PRIMARY KEY(`salesid`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

How to Add Column in MySQL Database using PHP

INSERT INTO `product` (`product_name`) VALUES ('Apple'), ('Orange'), ('Strawberry');

Creating a Connection

Next step is to create a database connection and save it as “conn.php“. This file will serve as our bridge between our form and our database. To create the file, open your HTML code editor and paste the code below after the tag.

 //MySQLi OOP $conn = new mysqli("localhost","root","","sum"); if ($conn->connect_error) < die("Connection failed: " . $conn->connect_error); > ?>

Creating a Table and a Form

Next is to create out table and our add form. In this case, we will create a sample sales table and add sale form. We name this as “index.php”.

Creating a Add Code

Lastly, we create our add sale code. We name this code as “add_sale.php”.

 else< //MySQLi Procedural //mysqli_query($conn,"insert into sales (productid,sales_qty) values ('$product','$qty')"); //MySQLi OOP $conn->query("insert into sales (productid,sales_qty) values ('$product','$qty')"); header('location:index.php'); > ?>

Happy Coding! How to Add Column in MySQL Database using PHP.

Источник

How to Add a Column to a MySQL Table Using PHP

In this article, we show how to add a new column to a MySQL table using PHP.

We add a new column to a MySQL table using PHP to write the MySQL query to add the column the table.

So the above is the PHP code to add a column to a MySQL table at the end of table, meaning it will be the last column.

So a column named email is added to the last column of the table. So if the table had 2 columns named first name and last name. It will now contain first name, last name, and email, in that order.

We’ll now show how to tie this in completely with the full PHP code to add a column to a MySQL table.

So, first, we must connect to the database.

After we do this, we create a variable named $add. This $add variable is set equal to a mysql_query() function that has the parameter «ALTER TABLE customers ADD email VARCHAR(50) NOT NULL». This adds the column, email, to the customers table. PHP.

The email column is of type VARCHAR(50) NOT NULL. This makes it a variable string up to 50 characters in length.

How to Add a Column After a Specified Column

So the code above shows how to add a column to the end of a table. The column you add will be the last column of the table.

However, you may not want to add the column to the end of a table. You may want to add it after a specific column.

The MySQL query below is the general format to add a column after a specified column.

So the code above adds a column named email of type VARCHAR(50) after the column firstname to the table customers.

We’ll now show how to tie this in completely with the full PHP code to add a column to a MySQL table after a specified column.

So the code above adds a column named email of type VARCHAR(50) after the column named firstname.

So the MySQL keyword AFTER can enable us to add columns after a specified column in a table.

How to Add a Column to the Beginning of a Table

Now we show how to add a column to the beginning of a MySQL table.

The MySQL query below is the general format to add a column to the beginning of a table is shown below.

So the code above adds a column named email of type VARCHAR(50) after the column firstname to the table customers.

We’ll now show how to tie this in completely with the full PHP code to add a column to a MySQL table after a specified column.

So this code shows how to add a column to the beginning of a MySQL table using PHP.

Источник

How to Add Columns to a Table Using MySQL ADD COLUMN Statement

Summary: in this tutorial, we will show you how to add a column to a table using MySQL ADD COLUMN statement.

Introduction to MySQL ADD COLUMN statement

To add a new column to an existing table, you use the ALTER TABLE ADD COLUMN statement as follows:

ALTER TABLE table ADD [COLUMN] column_name column_definition [FIRST|AFTER existing_column];Code language: SQL (Structured Query Language) (sql)

Let’s examine the statement in more detail.

• First, you specify the table name after the ALTER TABLE clause.
• Second, you put the new column and its definition after the ADD COLUMN clause. Note that COLUMN keyword is optional so you can omit it.
• Third, MySQL allows you to add the new column as the first column of the table by specifying the FIRST keyword. It also allows you to add the new column after an existing column using the AFTER existing_column clause. If you don’t explicitly specify the position of the new column, MySQL will add it as the last column.

To add two or more columns to a table at the same time, you use the following syntax:

ALTER TABLE table ADD [COLUMN] column_name_1 column_1_definition [FIRST|AFTER existing_column], ADD [COLUMN] column_name_2 column_2_definition [FIRST|AFTER existing_column], . ; Code language: SQL (Structured Query Language) (sql)

Let’s take a look some examples of adding a new column to an existing table.

MySQL ADD COLUMN examples

First, we create a table named vendors for the demonstration purpose using the following statement:

CREATE TABLE IF NOT EXISTS vendors ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) );Code language: SQL (Structured Query Language) (sql)

Second, we add a new column named phone to the vendors table. Because we specify the position of the phone column explicitly after the name column, MySQL will obey this.

ALTER TABLE vendors ADD COLUMN phone VARCHAR(15) AFTER name; Code language: SQL (Structured Query Language) (sql)

Third, we add a new column named vendor_group to the vendors table. At this time, we don’t specify the new column’s position so MySQL adds the vendor_group column as the last column of the vendors table.

ALTER TABLE vendors ADD COLUMN vendor_group INT NOT NULL;Code language: SQL (Structured Query Language) (sql)

Let’s insert some rows into the vendors table.

INSERT INTO vendors(name,phone,vendor_group) VALUES('IBM','(408)-298-2987',1); INSERT INTO vendors(name,phone,vendor_group) VALUES('Microsoft','(408)-298-2988',1);Code language: SQL (Structured Query Language) (sql)

We can query the data of the vendors table to see the changes.

SELECT id, name, phone,vendor_group FROM vendors; Code language: SQL (Structured Query Language) (sql)

Fourth, add two more columns email and hourly_rate to the vendors table at the same time.

ALTER TABLE vendors ADD COLUMN email VARCHAR(100) NOT NULL, ADD COLUMN hourly_rate decimal(10,2) NOT NULL;Code language: SQL (Structured Query Language) (sql)

Note that both email and hourly_rate columns are assigned to NOT NULL values However, the vendors table already has data. In such cases, MySQL will use default values for those new columns.

Let’s check the data in the vendors table.

SELECT id, name, phone, vendor_group, email, hourly_rate FROM vendors;Code language: SQL (Structured Query Language) (sql)

The email column is populated with blank values, not the NULL values. And the hourly_rate column is populated with 0.00 values.

If you accidentally add a column that already exists in the table, MySQL will issue an error. For example, if you execute the following statement:

ALTER TABLE vendors ADD COLUMN vendor_group INT NOT NULL;Code language: SQL (Structured Query Language) (sql)

MySQL issued an error message:

Error Code: 1060. Duplicate column name 'vendor_group'Code language: SQL (Structured Query Language) (sql)

For the table with a few columns, it is easy to see which columns are already there. However, with a big table with hundred of columns, it is more difficult.

In some situations, you want to check whether a column already exists in a table before adding it. However, there is no statement like ADD COLUMN IF NOT EXISTS available. Fortunately, you can get this information from the columns table of the information_schema database as the following query:

SELECT IF(count(*) = 1, 'Exist','Not Exist') AS result FROM information_schema.columns WHERE table_schema = 'classicmodels' AND table_name = 'vendors' AND column_name = 'phone';Code language: SQL (Structured Query Language) (sql)

In the WHERE clause, we passed three arguments: table schema or database, table name, and column name. We used IF function to return whether the column exists or not.

In this tutorial, you have learned how to add one or more columns to a table using MySQL ADD COLUMN statement.

Источник

MySQL добавить столбец в таблицу

В файл index.php вставим следующий php код:

query($sql) === TRUE) < echo "Столбец успешно создан"; >else < echo "Ошибка создание столбца" . $link->error; > $link->close(); ?>

Было

стало

Если нужно модифицировать тип данных столбца MODIFY COLUMN, то

query($sql) === TRUE) < echo "Тип данных столбца успешно модифицирован"; >else < echo "Ошибка модификации типы данных столбца" . $link->error; > $link->close(); ?>

Было

стало

Переместить столбец MODIFY COLUMN, код:

query($sql) === TRUE) < echo "Столбцы поменяны местами"; >else < echo "Ошибка перемещения столбца" . $link->error; > $link->close(); ?>

Было

стало

Изменить имя и тип данных столбца CHANGE COLUMN, php код:

query($sql) === TRUE) < echo "Столбец успешно переименован"; >else < echo "Ошибка переименования столбца" . $link->error; > $link->close(); ?>

Было

стало

Переименование таблицы product на products RENAME TO:

query($sql) === TRUE) < echo "Таблица успешно переименована"; >else < echo "Ошибка переименования таблицы" . $link->error; > $link->close(); ?>

Удаление столбца из таблицы DROP:

query($sql) === TRUE) < echo "Столбец успешно удален"; >else < echo "Ошибка удаления столбца" . $link->error; > $link->close(); ?>

Было

стало

Обновление данных в таблице с помощью оператора UPDATE

query($sql); if ($link->query($sql) === TRUE) < echo "Цена успешно изменена"; >else < echo "Ошибка изменения цены" . $link->error; > $link->close(); ?>

Было

стало

Сделать скидку на товар можно следующим запросом, используя SET и CONCAT SQL:

$sql = «UPDATE products SET cost=90, name=CONCAT(name,’ (Скидка)’) WHERE cost>120;»;

2145

Источник