Page 246 - Open Soource Technologies 304.indd

Unit 14: Connecting to MySQL with PHP

Suppose you want to drop field marks, then you can give the following query: Notes
mysql>ALTER TABLE student DROP COLUMN marks;

14.2.11 Dropping a Table

The difference between DROP and DELETE table is that, after executing DELETE statement the
contents of table are removed but the structure remains same, but in case of DROP statement
both the contents and structure are removed.

Syntax for DROP statement is:
mysql>DROP TABLE table_name;

During issuing query if you put a single quote( ‘ ) or double quote( “ ) inside a query you must
have to end somewhere with single quote or double quote otherwise an error will be thrown (as
shown below) because mysql will think as receiving a string until the quote ends with another
quote. Anything inside that two quote is treated as string.

14.2.12 Working with NULL Value
When the value of a field is NULL you cannot compare in the same way as doing for NOT
NULL value, if you do so you will not get the desired result.

For NULL value comparison you may follow the following procedure:

mysql>SELECT * FROM table_name WHERE field_name is NULL;
14.2.13 Backing up a Database

You can take a backup of your database in a text file by using the mysqldump command from
shell prompt as given below

[anand soft@localhost anandsoft]$mysqldump -u subu -psubu sample_db>sample.sql

After the execution of the above command sample.sql file will contain the structure as well as
the data insertion statements done on sample_db database.

Sample.sql file is stored in the user home directory, i.e. the user name under
which you logged in to the server(not MySQL server). For example if your
username is anandsoft, in Linux system the file sample.sql will be stored in the
directory/home/anandsoft/

you can only take the structure of the tables by giving the following command:

[anand soft@localhost anandsoft]$mysqldump -d -u subu -psubu sample_db>sample1.sql
You can take back up of any specific table from a database by following way:

LOVELY PROFESSIONAL UNIVERSITY 241