Chapter 5.docx.pdf

Chapter 5 Connecting to Databases What is Database? A database is a separate application that stores a collection of data. Each database has one or more distinct APIs for creating, accessing, managing, searching and replicating the data it holds. MySQL Database: MySQ...

Chapter 5 Connecting to Databases What is Database? A database is a separate application that stores a collection of data. Each database has one or more distinct APIs for creating, accessing, managing, searching and replicating the data it holds. MySQL Database: MySQL is a fast, easy-to-use RDBMS being used for many small and big businesses. MySQL is developed, marketed, and supported by MySQL AB, which is a Swedish company. MySQL is becoming so popular because of many good reasons: MYSQL Exercise Use shell provided by XAMPP And enter as a root user> mysql –u root; 1) mysql > SHOW DATABASES; Creating a database 2) mysql > CREATE DATABASE database name; Deleting a database 3) mysql > DROP DATABASE database name; To select a particular database 4) mysql > USE database_name; TO see tables in a database 5) mysql >SHOW TABLES; To create a new table in mysql 6) mysql > CREATE TABLE stuTab (id INT(3) NOT NULL PRIMARY KEY , name VARCHAR(20), address VARCHAR(30), mobile NUMBER(1)); To see structure of Table in a mysql database 7) mysql > DESCRIBE table_name; 1 To See contents inside a table 8) mysql > SELECT * FROM table_name; To insert data into a table 9) mysql > INSERT INTO `stuTab` (`id`,`name`,`address`,`mobile`) VALUES (01, "John", "America","098765431”); To Update a record in a table 10) mysql > UPDATE s tuTab SET name = “John Mc” WHERE id ='01'; 11) mysql > UPDATE table_name SET column1=value, column2=value2,... WHERE some_column=some_value To Delete a record from a table 12) mysql > DELETE from [table_name] where [column name]=[field text]; To Add a column in a table 13) mysql > ALTER TABLE stuTab ADD email VARCHAR(40) AFTER mobile; To modify a column in a table 14) mysql> ALTER TABLE table_name MODIFY Column_name Column Definition To drop a column in a table 15) mysql > ALTER TABLE table_name DROP column_name Q. Write SQL query for each question exercise given below: (Mysql Reference for help) Self Exercise (refer to mysql lab1-lab4) 1) Create a table called Mytable in database called Mydatabase (given below is definition of table) Field Type Null Key Default Extra Stu_ID int (4) NO PRIM 0 Firstname char(15) NO NO Lastname char(15) YES YES Address varchar(50) YES YES Email varchar(30) YES YES mobile int(10) YES YES 2  2) Enter four rows of data into the tables 3) Delete column from above table 4) Drop primary key from above table 5) Modify column name firstname to fname and lastname to lname 6) Add new column grade after column mobile Connect to Mysql using PHP Please go through all the Steps and codes given below use XAMPP software for exercise given below 1. Connecting to MySQL database Opening Database Connection: PHP provides mysql_connect function to open a database connection. This function takes five parameters and returns a MySQL link identifier on success, or FALSE on failure. Closing Database Connection: Its simplest function mysql_close PHP provides to close a database connection. This function takes connection resource returned by mysql_connect function. It returns TRUE on success or FALSE on failure. Example: Try out following example to open and close a database connection: 2. Create MySQL Database Using PHP 3 Creating a Database: To create and delete a database you should have admin priviledge. Its very easy to createa new MySQL database. PHP uses mysql_query function to create a MySQL database. This function takes two parameters and returns TRUE on success or FALSE on failure. Example: Try out following example to create a database: Selecting a Database: Once you estblish a connection with a database server then it is required to select a particular database where your all the tables are associated. 4 This is required because there may be multiple databases residing on a single server and you can do work with a single database at a time. PHP provides function mysql_select_db to select a database.It returns TRUE on successor FALSE on failure. Example: Here is the example showing you how to select a database. Creating Database Tables: To create tables in the new database you need to do the same thing as creating the database. First create the SQL query to create the tables then execute the query using mysql_query() function. Example: Try out following example to create a table: 3. Delete MySQL Database Using PHP Deleting a Database: If a database is no longer required then it can be deleted forever. You can use pass anSQL command to mysql_query to delete a database. Example: Try out following example to drop a database. Deleting a Table: Its again a matter of issuing one SQL command through mysql_query function to deleteany database table. But be very careful while using this command because by doing so you can delete some important information you have in your table. Example: Try out following example to drop a table: 4. Insert Data To MySQL Database Data can be entered into MySQL tables by executing SQL INSERT statement throughPHP function mysql_query. Below a simle example to insert a record into employee table. 7  Example: Try out following example to insert record into employee table.