PHP unit5

Q) Explain the importance of good database design.

A good database design is crucial for a high-performance application. Without optimized relationships, database will not perform as efficiently as possible.

The two important pieces for good database design are

1) Relationships and

2) Normalization

1. Relationship: For ease of maintenance, minimizing duplications, and avoiding inconsistencies we use relationship. Table relationships come in several forms:

A. One-to-one relationships

B. One-to-many relationships

C. Many-to-many relationships

For example:

Let us consider a table called EMPLOYEES that contains each person’s Social Security number, name, and the department. Suppose that either is another table called DEPARTMENTS, containing Department ID and a name.

In the EMPLOYEES table, the Department ID field matches an ID found in the DEPARTMENTS table.

This type of relationship is shown in Figure. The PK next to the field name indicates the primary key for the table.

A. One-to-One Relationships: In a one-to-one relationship, a key appears only once in a related table.A one-to-one relationship exists, if each employee is assigned one computer within a company.

B. One-to-Many Relationships: In a one-to-many relationship, keys from one table appear multiple times in a related table.

The example shown in Figure, indicating a connection between employees and departments, illustrates a one-to-many relationship. A real-world example is an organizational chart of the department, as shown in Figure

C. Many-to-Many Relationships: In a many-to-many relationship, the key value of one table can appear many times in a related table. So far, it sounds like a one-to-many relationship, but here’s the curveball: The opposite is also true, meaning that the primary key from that second table can also appear many times in the first table.

Example of STUDENTS and CLASSES:

A student has an ID and a name. A class has an ID and a name. A student usually takes more than one class at a time, and a class always contains more than one student, as you can see in Figure

2. Normalization

Normalization is simply a set of rules that will ultimately make easier when you are acting as a database administrator. It is the art of organizing database in such a way that tables relate where appropriate and are flexible for future growth. The sets of rules used in normalization are called normal forms.

Problems with the Flat Table

In the case of a database, it’s the flat table. A flat table is like a spreadsheet—it has many, many columns. There are no relationships between multiple tables; all the data could possibly want is right there in that flat table.

This scenario is inefficient and consumes more physical space on your hard drive than a normalized database.

Consider students-and-courses database, with the following fields in flat table:

· StudentName—The name of the student.

· CourseID1—The ID of the first course taken by the student.

· CourseDescription1—The description of the first course taken by the student.

· CourseInstructor1—The instructor of the first course taken by the student.

· CourseID2—The ID of the second course taken by the student.

· CourseDescription2—The description of the second course taken by the Student

· CourseInstructor2—The instructor of the second course taken by the student.

Repeat CourseID, CourseDescription, and CourseInstructor columns many more times to account for all the classes students can take during their academic career.

With this we are able to identify the first problem area: CourseID, CourseDescription, and CourseInstructor columns are repeated groups.

Eliminating redundancy is the first step in normalization, so next you take this flat

table to first normal form. If your table remained in its flat format, you could have

a lot of unclaimed space and a lot of space being used unnecessarily—not an efficient

table design.

First Normal Form:

The rules for the first normal form are as follows:

· Eliminate repeating information.

· Create separate tables for related data.

Taking students-and-courses database to the first normal form means that create two tables: one for students and one for courses, as shown in Figure.

Now tables represent a one-to-many relationship of one student to many courses. Students can take as many courses as they want and are not limited to the number of CourseID/CourseDescription/CourseInstructor groupings that existed in the flat table

Second Normal Form:

The rule for the second normal form is as follows:

· No nonkey attributes depend on a portion of the primary key.

In plain English, this means that if fields in table are not entirely related to a primary key, we have more work to do. In the students-and-courses example, you need to break out the courses into their own table and modify the students_courses table.

CourseID, CourseDescription, and CourseInstructor can become a table called courses with a primary key of CourseID. The students_courses table should then just contain two fields: StudentID and CourseID. You can see this new design in Figure.

This structure should look familiar as a many-to-many relationship using an intermediary mapping table.

Third Normal Form:

The rule for the third normal form is as follows:

· No attributes depend on other nonkey attributes.

This rule simply means that you need to look at tables and see whether we have more fields that can be broken down further and that are not dependent on a key.

An instructor teaches more than one class. However, CourseInstructor is not a key of any sort. So, if we break out this information and create a separate table purely for the sake of efficiency and maintenance (as shown in Figure.), that is the third normal form.

Third normal form is usually adequate for removing redundancy and allowing for flexibility and growth.

Q) Explain briefly about MySQL Data Types

MySQL uses many different data types, broken into three categories:

1. Numeric

2. Date and time, and

3. String types

1. Numeric Data Types: MySQL uses all the standard ANSI SQL numeric data types.

· INT—A normal-sized integer that can be signed or unsigned. If signed, the allowable range is from –2147483648 to 2147483647. If unsigned, the allowable range is from 0 to 4294967295. We can specify a width of up to 11 digits.

· TINYINT—A small integer that can be signed or unsigned. If signed, the allowable range is from –128 to 127. If unsigned, the allowable range is from 0 to 255. We can specify a width of up to 4 digits.

· SMALLINT—A small integer that can be signed or unsigned. If signed, the allowable range is from –32768 to 32767. If unsigned, the allowable range is from 0 to 65535. We can specify a width of up to 5 digits.

· MEDIUMINT—A medium-sized integer that can be signed or unsigned. If signed, the allowable range is from –8388608 to 8388607. If unsigned, the allowable range is from 0 to 16777215. You can specify a width of up to 9 digits.

· BIGINT—A large integer that can be signed or unsigned. If signed, the allowable range is from –9223372036854775808 to 9223372036854775807. If unsigned, the allowable range is from 0 to 18446744073709551615. We can specify a width of up to 11 digits.

· FLOAT(M,D)—A floating-point number that cannot be unsigned. We can define the display length (M) and the number of decimals (D). This is not required and defaults to 10,2, where 2 is the number of decimals and 10 is the total number of digits (including decimals). Decimal precision can go to 24 places for a FLOAT.

· DOUBLE(M,D)—A double-precision floating-point number that cannot be unsigned. We can define the display length (M) and the number of decimals (D). This is not required and will default to 16,4, where 4 is the number of decimals. Decimal precision can go to 53 places for a DOUBLE. REAL is a synonym for DOUBLE.

· DECIMAL(M,D)—An unpacked floating-point number that cannot be unsigned. In unpacked decimals, each decimal corresponds to 1 byte. Defining the display length (M) and the number of decimals (D) is required. NUMERIC is a synonym for DECIMAL.

2. Date and Time Types: The MySQL date and time data types are as follows:

· DATE—A date in YYYY-MM-DD format, between 1000-01-01 and 9999-12-31.

· DATETIME—A date and time combination in YYYY-MM-DD HH:MM:SS format, between 1000-01-01 00:00:00 and 9999-12-31 23:59:59.

· TIMESTAMP—A timestamp between midnight, January 1, 1970, and sometime in 2037. We can define multiple lengths to the TIMESTAMP field, which directly correlates to what is stored in it. The default length for TIMESTAMP is 14, which stores YYYYMMDDHHMMSS. This looks like the previous DATETIME format, only without the hyphens between numbers; 3:30 in the afternoon on December 30, 1973, is stored as 19731230153000. Other definitions of TIMESTAMP are 12 (YYMMDDHHMMSS), 8 (YYYYMMDD), and 6 (YYMMDD).

· TIME—Stores the time in HH:MM:SS format.

· YEAR(M)—Stores a year in two-digit or four-digit format. If the length is specified as 2 (for example, YEAR(2)), YEAR can be 1970 to 2069 (70 to 69). If the length is specified as 4, YEAR can be 1901 to 2155. The default length is 4.

3. String Types: This list describes the common string data types in MySQL:

· CHAR(M)—A fixed-length string between 1 and 255 characters in length (for example, CHAR(5)), right-padded with spaces to the specified length when stored. Defining a length is not required, but the default is 1.

· VARCHAR(M)—A variable-length string between 1 and 255 characters in length; for example, VARCHAR(25). You must define a length when creating a VARCHAR field.

· BLOB or TEXT—A field with a maximum length of 65,535 characters. BLOBs are Binary Large Objects and are used to store large amounts of binary data, such as images or other types of files. Fields defined as TEXT also hold large amounts of data; the difference between the two is that sorts and comparisons on stored data are case sensitive on BLOBs and are not case sensitive in TEXT fields. You do not specify a length with BLOB or TEXT.

· TINYBLOB or TINYTEXT—A BLOB or TEXT column with a maximum length of 255 characters.

· MEDIUMBLOB or MEDIUMTEXT—A BLOB or TEXT column with a maximum length of 16,777,215 characters. You do not specify a length with MEDIUMBLOB or MEDIUMTEXT.

· LONGBLOB or LONGTEXT—A BLOB or TEXT column with a maximum length of 4,294,967,295 characters. You do not specify a length with LONGBLOB or LONGTEXT.

· ENUM—An enumeration, which is a fancy term for list. When defining an ENUM, you are creating a list of items from which the value must be selected or it can be NULL). For example, if you want your field to contain A or B or C, you would define your ENUM as ENUM (‘A’, ‘B’, ‘C’), and only those values (or NULL) could ever populate that field. ENUMs can have 65,535 different values. ENUMs use an index for storing items.

Q) Write a note on creation of table in MYSQL.

The table-creation command requires

· Name of the table

· Names of fields

· Definitions for each field

Syntax:

CREATE TABLE table_name (column_name column_type);

Example:

CREATE TABLE grocery_inventory (

id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

item_name VARCHAR (50) NOT NULL,

item_desc TEXT,

item_price FLOAT NOT NULL,

curr_qty INT NOT NULL

);

Q) Write a note on INSERT Command in MYSQL.

The SQL command INSERT is used for adding new records to tables.

Syntax :

INSERT INTO table_name (column list) VALUES (column values);

The INSERT statement consists of two main parts: the column list and the value list.

Only the value list is actually required, but if we can omit the column list, you must specifically provide for each column in your value list—in the exact order.

Example1:

INSERT INTO grocery_inventory (id, item_name, item_desc, item_price, curr_qty) VALUES (‘1’, ‘Apples’, ‘Beautiful, ripe apples.’, ‘0.25’, 1000);

A statement that uses all columns but does not explicitly name them:

Example2 :

INSERT INTO grocery_inventory VALUES (‘2’, ‘Bunches of Grapes’, ‘Seedless grapes.’, ‘2.99’, 500);

Q) Describe briefly about SELECT Command in MYSQL.

SELECT is the SQL command used to retrieve records from tables.

Syntax

SELECT expressions_and_columns FROM table_name

[WHERE some_condition_is_true]

[ORDER BY some_column [ASC | DESC]]

[LIMIT offset, rows]

1: To select everything (all rows, all columns) from the table.

Example:

SELECT * FROM grocery_inventory;

2: To select specific columns only, replace the * with the names of the columns, separated by commas.

Example:

SELECT id, item_name, curr_qty FROM grocery_inventory;

3: To order results in a specific way, we use the ORDER BY clause.

Example:

SELECT id, item_name, curr_qty FROM grocery_inventory ORDER BY item_name;

The default sorting of ORDER BY results is ascending (ASC); strings sort from A to integers start at 0, and dates sort from oldest to newest. we can also specify a descending sort, using DESC:

Example:

SELECT id, item_name, curr_qty FROM grocery_inventory ORDER BY item_name DESC;

4. To Limit the Results, we use the LIMIT clause to return only a certain number of records. Two requirements apply when using the LIMIT clause: the offset and the number of rows. The offset is the starting position, and the number of rows should be self-explanatory.

Example:

SELECT id, item_name, curr_qty FROM grocery_inventory ORDER BY curr_qty LIMIT 2;

Example:

The LIMIT clause can prove useful in an actual application. For example, you can use the LIMIT clause within a series of SELECT statements to travel through results in steps (first two items, next two items, next two items after that):

1. SELECT * FROM grocery_inventory ORDER BY curr_qty LIMIT 0, 2;

2. SELECT * FROM grocery_inventory ORDER BY curr_qty LIMIT 2, 2;

3. SELECT * FROM grocery_inventory ORDER BY curr_qty LIMIT 4, 2;

Q) Write a note on Where clause in MYSQL.

To see the result depend on condition, use WHERE clause. the equal sign (=) in WHERE clauses to determine the truth of a condition—that is, whether one thing is equal to another.

In where clause we can use many types of operators, with comparison operators and logical operators.

Syntax:

SELECT expressions_and_columns FROM table_name

[WHERE some_condition_is_true]

An example is to retrieve all the records for items with a quantity of 500:

SELECT * FROM grocery_inventory WHERE curr_qty = 500;

The lists of Basic Comparison Operators and Their Meanings are as follows

Operator Meaning

= Equal to

!= Not equal to

<= Less than or equal to

< Less than

>= Greater than or equal to

> Greater than

There’s also a handy operator called BETWEEN, which is useful with integer or date comparisons because it searches for results between a minimum and maximum

value.

For example:

SELECT * FROM grocery_inventory WHERE item_price BETWEEN 1.50 AND 3.00;

Q) Write a note on Wildcards in string pattern matching.

The LIKE operator is used for comparing strings in Where clause. This operator uses two characters as wildcards in pattern matching:

· %—Matches multiple characters

· _—Matches exactly one character

For example,

SELECT * FROM grocery_inventory WHERE item_name LIKE ‘A%’;

Q) Describe the Selecting from Multiple Tables.

When you select from more than one table in one SELECT statement, it means we are joining the tables together.

Suppose that you have two tables: fruit and color. You can select all rows from each of the two tables by using two separate SELECT statements:

SELECT * FROM fruit;

+----+-----------+

| id | fruitname |

+----+-----------+

| 1 | apple |

| 2 | orange |

| 3 | grape |

| 4 | banana |

+----+-----------+

SELECT * FROM color;

+----+-----------+

| id | colorname |

+----+-----------+

| 1 | red |

| 2 | orange |

| 3 | purple |

| 4 | yellow |

+----+-----------+

SELECT * FROM fruit, color;

With this query, you get results like this:

+----+-----------+----+-----------+

| id | fruitname | id | colorname |

+----+-----------+----+-----------+

| 1 | apple | 1 | red |

| 2 | orange | 1 | red |

| 3 | grape | 1 | red |

| 4 | banana | 1 | red |

| 1 | apple | 2 | orange |

| 2 | orange | 2 | orange |

| 3 | grape | 2 | orange |

| 4 | banana | 2 | orange |

| 1 | apple | 3 | purple |

| 2 | orange | 3 | purple |

| 3 | grape | 3 | purple |

| 4 | banana | 3 | purple |

| 1 | apple | 4 | yellow |

| 2 | orange | 4 | yellow |

| 3 | grape | 4 | yellow |

| 4 | banana | 4 | yellow |

+----+-----------+----+-----------+

SELECT fruit.id, fruitname, colorname FROM fruit, color WHERE fruit.id = color.id;

This query produces these results:

+------+-----------+-----------+

| id | fruitname | colorname |

+------+-----------+-----------+

| 1 | apple | red |

| 2 | orange | orange |

| 3 | grape | purple |

| 4 | banana | yellow |

+------+-----------+-----------+

Q) Explain briefly about JOIN.

MySQL JOINS are used to retrieve data from multiple tables. A MySQL JOIN is performed whenever two or more tables are joined in a SQL statement.

There are different types of MySQL joins:

1. MySQL INNER JOIN (or sometimes called simple join)

2. MySQL LEFT OUTER JOIN (or sometimes called LEFT JOIN)

3. MySQL RIGHT OUTER JOIN (or sometimes called RIGHT JOIN)

1. MySQL INNER JOINS return all rows from multiple tables where the join condition is met.

Syntax :

SELECT columns FROM table1

INNER JOIN table2 ON table1.column = table2.column;

Example:

SELECT fruitname, colorname FROM fruit

INNER JOIN color ON fruit.id = color.id;

+-----------+-----------+

| fruitname | colorname |

+-----------+-----------+

| apple | red |

| orange | orange |

| grape | purple |

| banana | yellow |

+-----------+-----------+

2. LEFT OUTER JOIN

Another common type of JOIN is the LEFT JOIN. When joining two tables with LEFT

JOIN, all rows from the first table are returned, no matter whether there are matches in the second table.

Syntax:

SELECT columns

FROM table1

LEFT [OUTER] JOIN table2

ON table1.column = table2.column;

Consider two tables as shown below

master_name Table

+---------+-----------+----------+

| name_id | firstname | lastname |

+---------+-----------+----------+

| 1 | John | Smith |

| 2 | Jane | Smith |

| 3 | Jimbo | Jones |

| 4 | Andy | Smith |

| 5 | Chris | Jones |

| 6 | Anna | Bell |

| 7 | Jimmy | Carr |

| 8 | Albert | Smith |

| 9 | John | Doe |

+---------+-----------+----------+

email table

+---------+-------------------+

| name_id | email |

+---------+-------------------+

| 2 | jsmith@jsmith.com |

| 6 | annabell@aol.com |

| 9 | jdoe@yahoo.com |

+---------+-------------------+

SELECT firstname, lastname, email FROM master_name

LEFT JOIN email ON master_name.name_id = email.name_id;

The LEFT JOIN query here produces these results:

+-----------+----------+-------------------+

| firstname | lastname | email |

+-----------+----------+-------------------+

| John | Smith | |

| Jane | Smith | jsmith@jsmith.com |

| Jimbo | Jones | |

| Andy | Smith | |

| Chris | Jones | |

| Anna | Bell | annabell@aol.com |

| Jimmy | Carr | |

| Albert | Smith | |

| John | Doe | jdoe@yahoo.com |

+-----------+----------+-------------------+

3. RIGHT JOIN: A RIGHT JOIN works like LEFT JOIN but with the table order reversed. In other words, when using a RIGHT JOIN, all rows from the second table are returned, no matter whether matches exist in the first table.

SELECT firstname, lastname, email FROM master_name

RIGHT JOIN email ON master_name.name_id = email.name_id;

+-----------+----------+-------------------+

| firstname | lastname | email |

+-----------+----------+-------------------+

| Jane | Smith | jsmith@jsmith.com |

| Anna | Bell | annabell@aol.com |

| John | Doe | jdoe@yahoo.com |

+-----------+----------+-------------------+

Q) Explain Using the UPDATE Command to Modify Records.

UPDATE is the SQL command used to modify the contents of one or more columns in an existing record or set of records.

Syntax :

UPDATE table_name

SET column1=’new value’,

column2=’new value2’

[WHERE some_condition_is_true]

For example, assume that you have a table called fruit containing an ID, a fruit

name, and the status of the fruit (ripe or rotten):

+----+------------+--------+

| id | fruit_name | status |

+----+------------+--------+

| 1 | apple | ripe |

| 2 | orange | rotten |

| 3 | grape | ripe |

| 4 | banana | rotten |

+----+------------+--------+

Example1: To update the status of the fruit to ripe, use

UPDATE fruit SET status = ‘ripe’;

Q) Explain Using the REPLACE Command in PHP

To modifying the records we use the REPLACE command, which is similar to the INSERT command.

Syntax:

REPLACE INTO table_name (column list) VALUES (column values);

If the record you are inserting into the table contains a primary key value that matches a record already in the table, the record in the table is deleted and the new record inserted in its place.

Example:

REPLACE INTO grocery_inventory VALUES (1, ‘Granny Smith Apples’, ‘Sweet!’, ‘0.50’, 1000);

Q) Write a note on Using the DELETE Command

The delete command is used to remove all/some records in the table.

Syntax:

DELETE FROM table_name

[WHERE some_condition_is_true]

[LIMIT rows]

1. The following statement removes all records in the table:

DELETE FROM fruit;

2. A conditional DELETE statement is used to remove all records depend on condition.

DELETE FROM fruit WHERE status = ‘rotten’;

3. ORDER BY clauses can also be used in DELETE statements.

Syntax:

DELETE FROM table_name

[WHERE some_condition_is_true]

[ORDER BY some_column [ASC | DESC]]

[LIMIT rows]

Example:DELETE FROM access_log ORDER BY date_accessed DESC LIMIT 1;

Q) Explain String Functions in MySQL

MySQL’s built-in string-related functions can be used several ways.

1. Length and Concatenation Functions: The group of length and concatenation functions focuses on the length of strings and concatenating strings together.

Length function:

Length-related functions include LENGTH(), OCTET_LENGTH(), CHAR_LENGTH(), and CHARACTER_LENGTH(), which do almost the same thing: count characters in a string.

Example : SELECT LENGTH(‘This is cool!’);

Concatenation Function:

a) CONCAT() function, which concatenates two or more strings:

Example1: SELECT CONCAT(‘My’, ‘S’, ‘QL’);

Example2: SELECT CONCAT(firstname, lastname) FROM master_name;

b) CONTACT_WS() stands for concatenate with separator.

Example : SELECT CONCAT_WS(‘ ‘, firstname, lastname) FROM master_name;

2. Trimming and Padding Functions: MySQL provides several functions for adding and removing extra characters (including whitespace) from strings.

Trimming :

a) RTRIM( ) and LTRIM( ): The RTRIM() and LTRIM() functions remove whitespace from either the right or left side of a string.

Example1: SELECT RTRIM(‘stringstring ‘);

Example2: SELECT LTRIM(‘ stringstring’);

b) LEADING: To remove the leading X characters from the string XXXneedleXXX, use SELECT TRIM(LEADING ‘X’ FROM ‘XXXneedleXXX’);

c) TRAILING: To remove the characters from the end of the string:

SELECT TRIM(TRAILING ‘X’ FROM ‘XXXneedleXXX’);

Padding :

a) RPAD( ) and LPAD( ) add characters from either the right or left side to a string.

Example: SELECT RPAD(‘needle’, 10, ‘X’);

3. Location and Position Functions: The group of location and position functions is useful for finding parts of strings within other strings.

a) The LOCATE() function returns the position of the first occurrence of a given substring within the target string.

Example: SELECT LOCATE(‘needle’, ‘haystackneedlehaystack’);

4. Substring Functions: To extract a substring from a target string

Example: SELECT SUBSTRING(“MySQL”, 2, 3);

To geta few characters from the left or right ends of a string, use the LEFT() and RIGHT() functions.

Example1: SELECT LEFT(“MySQL”, 2);

Example2: SELECT RIGHT(“MySQL”, 3);

Q) Briefly explain String Modification Functions in MYSQL.

PHP has numerous functions to modify the appearance of strings.

1. The MySQL LCASE() and UCASE() functions transform a string into lowercase or uppercase:

Example1: SELECT LCASE (‘MYSQL’);

Example2: SELECT UCASE (‘mysql’);

2. REPEAT( ) function used to repeats a string for a given number of times:

Example1: SELECT REPEAT (“bowwow”, 4);

3. The REPLACE() function replaces all occurrences of a given string with another string:

Example: SELECT REPLACE (‘bowwowbowwowbowwowbowwow’, ‘wow’, ‘WOW’);

Q) Explain Using Date and Time Functions in MySQL

MySQL’s built-in date-related functions in SELECT statements.

Working with Days

1. The DAYOFWEEK() and WEEKDAY() functions do similar things with slightly different results. Both functions find the weekday index of a date, but the difference lies in the starting day and position. If you use DAYOFWEEK(), the first day of the week is Sunday, at position 1, and the last day of the week is Saturday, at position 7.

Example: SELECT DAYOFWEEK(‘2012-01-09’);

Example: SELECT WEEKDAY (‘2012-01-09’);

2. The DAYOFMONTH() and DAYOFYEAR() functions are more straightforward, with only one result and a range that starts at 1 and ends at 31 for DAYOFMONTH() and 366 for DAYOFYEAR().

Example: SELECT DAYOFMONTH(‘2012-01-09’);

Example: SELECT DAYOFYEAR(‘2012-01-09’);

3. The DAYNAME() function is used returns the name of the weekday for any given date.

Example: SELECT DAYNAME(date_ordered) FROM orders;

Working with Months and Years: Days of the week are not the only parts of the calendar, and MySQL has functions specifically for months and years as well.

1. The MONTH() and MONTHNAME() return the number of the month in a year and the name of the month for a given date.

For example: SELECT MONTH(‘2012-01-09’), MONTHNAME(‘2012-01-09’);

To get nonrepetitive results use DISTINCT :

Example: SELECT DISTINCT MONTHNAME(date_ordered) FROM orders;

2. The YEAR() function returns the year of a given date:

Example: SELECT DISTINCT YEAR(date_ordered) FROM orders;

Working with Weeks: Weeks can be tricky things—there can be 53 weeks in a year if Sunday is the first day of the week and December hasn’t ended.

Example: SELECT DAYNAME(‘2001-12-30’);

The Week function is used to display week of the year (i.e in number 53 for December)

Example: SELECT WEEK(‘2001-12-30’);

Working with Hours, Minutes, and Seconds: Using a date that includes the exact time, such as datetime or timestamp, or even just a time field, there are functions to find the hours, minutes, and seconds from that string. Not surprisingly, these functions are called HOUR(), MINUTE(), and SECOND(). HOUR() returns the hour in a given time, which is between 0 and 23. The range for MINUTE() and SECOND() is 0 to 59.

Here is an example:

SELECT HOUR(‘2012-01-09 07:27:49’) AS hour,

MINUTE(‘2012-01-09 07:27:49’) AS minute,

SECOND(‘2012-01-09 07:27:49’) AS second;

Q) Explain the Formatting Dates and Times with MySQL.

The DATE_FORMAT() function formats a date, datetime, or timestamp field into a string by using options that tell it exactly how to display the results.

Syntax :

DATE_FORMAT(date,format)

DATE_FORMAT() Format String Options

Option Result

%M Month name (January through December)

%b Abbreviated month name (Jan through Dec)

%m Month, padded digits (01 through 12)

%c Month (1 through 12)

%W Weekday name (Sunday through Saturday)

%a Abbreviated weekday name (Sun through Sat)

%D Day of the month using the English suffix, such as first, second,third, and so on

%d Day of the month, padded digits (00 through 31)

%e Day of the month (0 through 31)

%j Day of the year, padded digits (001 through 366)

%Y Year, four digits

%y Year, two digits

%X Four-digit year for the week where Sunday is the first day; used with %V

%x Four-digit year for the week where Monday is the first day; used with %v

%w Day of the week (0=Sunday…6=Saturday)

%U Week (0 through 53) where Sunday is the first day of the week

%u Week (0 through 53) where Monday is the first day of the week

%V Week (1 through 53) where Sunday is the first day of the week;

%H Hour, padded digits (00 through 23)

%k Hour (0 through 23)

%h Hour, padded digits (01 through 12)

%l Hour (1 through 12)

%i Minutes, padded digits (00 through 59)

%S Seconds, padded digits (00 through 59)

%s Seconds, padded digits (00 through 59)

%r Time, 12-hour clock (hh:mm:ss [AP]M)

%T Time, 24-hour clock (hh:mm:ss)

%p AM or PM

Example1: SELECT DATE_FORMAT(‘2012-01-09 02:02:00’, ‘%h:%i’) AS sample_time;

Example2: SELECT DATE_FORMAT(‘2012-01-09’, ‘%W, %M %D, %Y’) AS sample_time;

Example3: SELECT DATE_FORMAT(NOW(),’%W the %D of %M, %Y

around %l o\’clock %p’) AS sample_time;

Q) Briefly explain Performing Date Arithmetic with MySQL

MySQL has several functions to help perform date arithmetic. The DATE_ADD() and DATE_SUB() functions return a result given a starting date and an interval.

The syntax for both functions is as follows:

DATE_ADD(date,INTERVAL value type)

DATE_SUB(date,INTERVAL value type)

The following table shows the possible types and their expected value format.

Value Type

Number of seconds SECOND

Number of minutes MINUTE

Number of hours HOUR

Number of days DAY

Number of months MONTH

Number of years YEAR

“minutes:seconds” MINUTE_SECOND

“hours:minutes” HOUR_MINUTE

“days hours” DAY_HOUR

“years-months” YEAR_MONTH

“hours:minutes:seconds” HOUR_SECOND

“days hours:minutes” DAY_MINUTE

“days hours:minutes:seconds” DAY_SECOND

Example1: SELECT DATE_ADD(NOW(), INTERVAL 21 DAY);

Example2: SELECT DATE_SUB(NOW(), INTERVAL 21 DAY) ;

Q) Explain Database Transactions in MYSQL?

Database transactions are sets of queries that must execute in such a way so that if one query fails to execute completely they all fail.

For instance, suppose that you have a set of three queries, the second dependent on the results of the first, and the third dependent on the results of the second. If the second query fails, you need to have a way to negate the results of the first query; similarly, if the third query fails, you need to negate the results of the first and second queries, as well.

The key terms used in transactions with MySQL are COMMIT and ROLLBACK.

· COMMIT—this command occurs at the end of the series of queries in transaction and is issued only if all the required queries have executed successfully.

· ROLLBACK—This command is used when one or more of the series of queries in transaction fails and resets the affected tables to their pre transaction state.

Example:

CREATE TABLE store_inventory (

id int not null primary key auto_increment, item_name varchar(50),

item_price float(6,2), item_qty int ) ENGINE=InnoDB;

CREATE TABLE store_orders (

id int not null primary key auto_increment, purchaser_name varchar(50),

purchase_date datetime ) ENGINE=InnoDB;

CREATE TABLE store_orders_items ( id int not null primary key auto_increment,

order_id int, inventory_id int, item_qty int ) ENGINE=InnoDB;

+----+------------+------------+----------+

+----+------------+------------+----------+

| 1 | Great Book | 19.99 | 10 |

| 2 | Awesome CD | 9.99 | 20 |

+----+------------+------------+----------+

If a shopper wants to purchase two Great Books and one Awesome CD through your online store, the process would go something like this:

1. The user completes an online form and attempts to pay for the purchases, so issue a BEGIN command for the transaction that would be part of the checkout script:

BEGIN;

2. Decrement the quantity of items in the store_inventory table:

UPDATE store_inventory SET item_qty = item_qty - 2 WHERE id = 1;

UPDATE store_inventory SET item_qty = item_qty - 1 WHERE id = 2;

3. Add a record to the store_orders table:

INSERT INTO store_orders (purchaser_name, purchase_date) VALUES (‘John Smith’, now());

4. If adding the record fails, issue a ROLLBACK command to reset the available

quantity of the items:

ROLLBACK;

If adding the record succeeds, get the ID of the record just added and use it in your query to add line items to the sales record by inserting records in the store_orders_items table:

INSERT INTO store_orders_items (order_id, inventory_id, item_qty) VALUES (‘1’, ‘1’, ‘2’);

INSERT INTO store_orders_items (order_id, inventory_id, item_qty) VALUES (‘1’, ‘2’, ‘1’);

6. If adding the records fails, issue a ROLLBACK command to reset the available quantity of the items and remove the record in store_orders:

ROLLBACK;

7. If adding the records succeeds but the subsequent charging of a credit card or other payment method fails, issue a ROLLBACK command to reset the available quantity of the items, remove the record in store_orders, and remove the records in store_orders_items:

ROLLBACK;

8. If adding the records succeeds and the subsequent charging of a credit card or other payment method succeeds, issue a COMMIT command to ensure that all the changes are stored and the transaction ends:

COMMIT;

Q) What Are Stored Procedures?

A stored procedure is a procedure in SQL that is stored in the database server rather than the web server. Proponents of using stored procedures in code point to performance and maintenance as key reasons for doing so:

. Better performance—Stored procedures exist as precompiled SQL in the database, so a typical two-step process (compile and execute) becomes a single-step process (execute).

. Ease of maintenance—Maintaining one statement in one place (the database) is significantly less time-consuming than maintaining one statement in numerous places, such as all through scripts on your web server.

This stored procedure example uses the following table:

CREATE TABLE testSP ( id int not null primary key auto_increment,

field_name varchar(25), date_added datetime ) ENGINE=InnoDB;

The values of this table for testing purposes are as follows:

+----+------------+---------------------+

| id | field_name | date_added |

+----+------------+---------------------+

| 1 | Value 1 | 2012-01-23 09:40:24 |

| 2 | Value 2 | 2012-01-24 09:40:24 |

| 3 | Value 3 | 2012-01-25 09:40:24 |

| 4 | Value 4 | 2012-01-26 09:40:24 |

| 5 | Value 5 | 2012-01-27 09:40:24 |

| 6 | Value 6 | 2012-01-30 09:40:24 |

| 7 | Value 7 | 2012-01-31 09:40:24 |

| 8 | Value 8 | 2012-02-01 09:40:24 |

| 9 | Value 9 | 2012-02-02 09:40:24 |

| 10 | Value 10 | 2012-02-14 09:40:24 |

Syntax:

CREATE PROCEDURE procedure_name () query

For this example, the stored procedure simply selects all data from the testSP table

that has been added in the past 7 days. The name of the stored procedure is sp1:

CREATE PROCEDURE sp1 () SELECT * FROM testSP WHERE date_added BETWEEN DATE_SUB(NOW(), INTERVAL 7 DAY) AND NOW()

To call the stored procedure, use the CALL command:

CALL sp1 ( )

The results of the stored procedure (the SELECT query) are returned to you:

+----+------------+---------------------+

| id | field_name | date_added |

+----+------------+---------------------+

| 2 | Value 2 | 2012-01-24 09:40:24 |

| 3 | Value 3 | 2012-01-25 09:40:24 |

| 4 | Value 4 | 2012-01-26 09:40:24 |

| 5 | Value 5 | 2012-01-27 09:40:24 |

| 6 | Value 6 | 2012-01-30 09:40:24 |

Q) What are MySQL or MySQLi Functions?

MySQL Improved Extension (MySQLi) provides a Procedural Interface as well as an Object Oriented Interface.

Let us look into some of the common MySQLi Procedural functions.

1. mysqli_connect(): This function is used for connecting to MySQL. Before doing any database operation, you need to connect to MySQL. On success, this function returns a link identifier that you can use in other MySQLi functions. On failure, it will throw an error.

Example: $link = mysqli_connect('localhost', 'robin', 'robin123', 'company_db');

2. mysqli_connect_error(): mysqli_connect() throws an error at failure, and mysqli_connect_error() stores the error of the last call to mysqli_connect(). If there is no error, it returns NULL.

Example:

<?php

$link = @mysqli_connect('localhost', 'robin', 'robin123', 'company_db');

if (mysqli_connect_error()) {

$logMessage = 'MySQL Error: ' . mysqli_connect_error();

// Call your logger here.

die('Could not connect to the database');

}

// Rest of the code goes here

3. mysqli_select_db(): To change the database in use, you can use mysqli_select_db().

For example assume that user robin also has privileges for a database called company_new_db; then you can change the database as

below.

$link = @mysqli_connect('localhost', 'robin', 'robin123', 'company_db');

// Operations on 'company_db'

mysqli_select_db($link, 'company_new_db');

// Operations on 'company_new_db'

You will only need this function if your PHP application deals with more than one database.

4. nmysqli_close(): You can use this function to close a MySQL connection. It returns TRUE on success and FALSE on failure.

$link = @mysqli_connect('localhost', 'robin', 'robin123', 'company_db');

// MySQL operations goes here.

mysqli_close($link);

5. mysqli_query(): This is the function used for executing MySQL queries. It returns FALSE on failure. For SELECT, SHOW, DESCRIBE, and EXPLAIN queries (where there is an output), it returns a MySQL result set (resource) which can be used in functions like mysqli_fetch_array().

For other queries, like INSERT, UPDATE, and DELETE, it returns TRUE on success.

$link = @mysqli_connect('localhost', 'robin', 'robin123', 'company_db');

$query = "SELECT * FROM employee";

if (mysqli_query($link, $query)) {

// Iterate and display result

} else {

// Show error

}

mysqli_close($link);

6. mysqli_fetch_array(): This function is used for reading data from a MySQL result set (returned by a mysqli_query()). It reads and returns one row of data as an array and then moves the pointer to the next row. When there are no more rows to return, it returns NULL. Because of this behavior, it’s often used with a While Loop as below.

while ($row = mysqli_fetch_array($result)) {

/* Till there is data, $row will be an array.

* At the end, $row becomes NULL ending the loop.

}

Let’s assume the following employee table is available in our company_db database.

Below is how we would fetch ID, First Name, and Last Name from this table.

<?php

$link = @mysqli_connect('localhost', 'robin', 'robin123', 'company_db');

$query = "SELECT `id`, `first_name`, `last_name` FROM `employee`";

$result = mysqli_query($link, $query);

while ($row = mysqli_fetch_array($result)) {

echo $row[0] . ': ' . $row[1] . ' ' . $row[2];

echo '<br />';

}

mysqli_free_result($result);

mysqli_close($link);

When you run it, the code above will output the following content in the web browser.

1: Robin Jackman

2: Taylor Edward

7. mysqli_num_rows(): mysqli_num_rows() returns the number of rows in a result set. Using it, you can take a different action when the result set is empty.

if (mysqli_num_rows($result) > 0) {

// Proceed with the $result

} else {

// Show an error message

}

Q) How to Connect to MySQL with PHP

1. To connect to MYSQL we use mysqli_connect ( ) function.

Syntax:

$mysqli = mysqli_connect(“hostname”, “username”, “password”, “database”);

2. To execute mysql query we use mysqli_query( ) function. The mysqli_query() function in PHP is used to send SQL query to MySQL

<?php

$mysqli = mysqli_connect(“localhost”, “shaik”, “Ayaan2013#”, “ShaikDB”);

if (mysqli_connect_errno())

{

printf(“Connect failed: %s\n”, mysqli_connect_error());

exit();

} else {

$sql = “CREATE TABLE testTable

(id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

testField VARCHAR(75))”;

$res = mysqli_query($mysqli, $sql);

if ($res === TRUE) {

echo “Table testTable successfully created.”;

} else {

printf(“Could not create table: %s\n”, mysqli_error($mysqli));

}

mysqli_close($mysqli);

}

Q) Explain Working with MySQL Data

The mysqli_query() function is used to execute the basic SQL queries such as INSERT, UPDATE, and DELETE.

1. Inserting Data with PHP: The easiest (and safest) method for inserting data is INSERT statement. The INSERT INTO statement is used to add new records to a MySQL table:

INSERT INTO table_name (column1, column2, column3,...) VALUES (value1, value2, value3,...)

Example: $sql = "INSERT INTO MyGuests (firstname, lastname, email) VALUES ('John', 'Doe',

'john@example.com')";

2. The DELETE statement is used to delete records from a table:

DELETE FROM table_name
WHERE some_column = some_value

Example: $sql = "DELETE FROM MyGuests WHERE id=3";

3. The UPDATE statement is used to update existing records in a table:

UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value

Example: $sql = "UPDATE MyGuests SET lastname='Doe' WHERE id=2";

4. The SELECT statement is used to select data from one or more tables:

SELECT column_name(s) FROM table_name

SELECT * FROM table_name

Example: $sql = "SELECT id, firstname, lastname FROM MyGuests";

Example:

<?php
$servername = "localhost";
$username = "shaik";
$password = "Ayaan2013#";
$dbname = "shaikDB";
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
$sql = "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('John', 'Doe', 'john@example.com')";
if ($conn->query($sql) === TRUE) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}
$conn->close();
?>

Q) Explain the creation of Online Address Book.

1. Planning and Creating the Database Tables

When you think of an address book, the obvious fields come to mind: name, address, telephone number, email address.

Let us consider an address book, with fields such as name, address, telephone number, email address.

Address Book Table and Field Names

Table Name Field Names

master_name id, date_added, date_modified, f_name, l_name

address id, master_id, date_added, date_modified, address, city,state, zipcode, type

telephone id, master_id, date_added, date_modified, tel_number,

type.

fax id, master_id, date_added, date_modified, fax_number,type.

email id, master_id, date_added, date_modified, email, type.

personal_notes id, master_id, date_added, date_modified, note.

The SQL statement for master_name is as follows:

CREATE TABLE master_name (

id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

date_added DATETIME, date_modified DATETIME,

f_name VARCHAR (75), l_name VARCHAR (75) );

The SQL statement for address is as follows:

CREATE TABLE address (

id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

master_id INT NOT NULL,

date_added DATETIME,

date_modified DATETIME,

address VARCHAR (255),

city VARCHAR (30),

state CHAR (2),

zipcode VARCHAR (10),

type ENUM (‘home’, ‘work’, ‘other’) );

The SQL statement for telephone is as follows:

CREATE TABLE telephone (

id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

master_id INT NOT NULL,

date_added DATETIME,

date_modified DATETIME,

tel_number VARCHAR (25),

type ENUM (‘home’, ‘work’, ‘other’) );

The SQL statement for fax is as follows:

CREATE TABLE fax (

id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

master_id INT NOT NULL,

date_added DATETIME,

date_modified DATETIME,

fax_number VARCHAR (25),

type ENUM (‘home’, ‘work’, ‘other’));

The SQL statement for email is as follows:

CREATE TABLE email (

id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

master_id INT NOT NULL,

date_added DATETIME,

date_modified DATETIME,

email VARCHAR (150),

type ENUM (‘home’, ‘work’, ‘other’));

The SQL statement for personal_notes is as follows:

CREATE TABLE personal_notes (

id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

master_id INT NOT NULL UNIQUE,

date_added DATETIME,

date_modified DATETIME,

note TEXT );

2. Creating an Include File for Common Functions.

It set up the database connection function. Save it as addressbook_include.php

addressbook_include.php

<?php

2: function doDB() {

3: global $mysqli;

5: //connect to server and select database; you may need it

6: $mysqli = mysqli_connect(“localhost”, “joeuser”,

7: “somepass”, “testDB”);

9: //if connection fails, stop script execution

10: if (mysqli_connect_errno()) {

11: printf(“Connect failed: %s\n”, mysqli_connect_error());

12: exit();

13: }

14: }

15: ?>

Lines 2–14 set up the database connection function, doDB. If the connection cannot

be made, the script exits when this function is called; otherwise, it makes the value

of $mysqli available to other parts of your script.

3. Creating Menu.

Create menu to perform several actions on Online address book , which is shown below.

mymenu.html

1: <!DOCTYPE html>

2: <html>

3: <head>

4: <title>My Address Book</title>

5: </head>

6: <body>

7: <h1>My Address Book</h1>

9: <p><strong>Management</strong></p>

10: <ul>

11: <li><a href=”addentry.php”>Add an Entry</a></li>

12: <li><a href=”delentry.php”>Delete an Entry</a></li>

13: </ul>

14:

15: <p><strong>Viewing</strong></p>

16: <ul>

17: <li><a href=”selentry.php”>Select a Record</a></li>

18: </ul>

19: </body>

20: </html>

4. Creating the Record-Addition Mechanism

addentry.php shows a basic record-addition script, which has two parts: what to do if the form should be displayed (lines 4–89) and what actions to take if the form is being submitted (lines 91–187). Lines 3–89 simply place the contents of the HTML form into a string called $display_block.

addentry.php:

1: <?php

2: include ‘addressbook_include.php;

4: if (!$_POST) {

5: //haven’t seen the form, so show it

6: $display_block = <<<END_OF_TEXT

7: <form method=”post” action=”$_SERVER[PHP_SELF]”>

8: <fieldset>

9: <legend>First/Last Names:</legend><br/>

10: <input type=”text” name=”f_name” size=”30”

11: maxlength=”75” required=”required” />

12: <input type=”text” name=”l_name” size=”30”

13: maxlength=”75” required=”required” />

14: </fieldset>

15:

16: <p><label for=”address”>Street Address:</label><br/>

17: <input type=”text” id=”address” name=”address”

18: size=”30” /></p>

19:

20: <fieldset>

21: <legend>City/State/Zip:</legend><br/>

22: <input type=”text” name=”city” size=”30” maxlength=”50” />

23: <input type=”text” name=”state” size=”5” maxlength=”2” />

24: <input type=”text” name=”zipcode” size=”10” maxlength=”10” />

25: </fieldset>

26:

27: <fieldset>

28: <legend>Address Type:</legend><br/>

29: <input type=”radio” id=”add_type_h” name=”add_type”

30: value=”home” checked />

31: <label for=”add_type_h”>home</label>

32: <input type=”radio” id=”add_type_w” name=”add_type”

33: value=”work” />

34: <label for=”add_type_w”>work</label>

35: <input type=”radio” id=”add_type_o” name=”add_type”

36: value=”other” />

37: <label for=”add_type_o”>other</label>

38: </fieldset>

<legend>Telephone Number:</legend><br/>

42: <input type=”text” name=”tel_number” size=”30” maxlength=”25” />

43: <input type=”radio” id=”tel_type_h” name=”tel_type”

44: value=”home” checked />

45: <label for=”tel_type_h”>home</label>

46: <input type=”radio” id=”tel_type_w” name=”tel_type”

47: value=”work” />

48: <label for=”tel_type_w”>work</label>

49: <input type=”radio” id=”tel_type_o” name=”tel_type”

50: value=”other” />

51: <label for=”tel_type_o”>other</label>

52: </fieldset>

53:

54: <fieldset>

55: <legend>Fax Number:</legend><br/>

56: <input type=”text” name=”fax_number” size=”30” maxlength=”25” />

57: <input type=”radio” id=”fax_type_h” name=”fax_type”

58: value=”home” checked />

59: <label for=”fax_type_h”>home</label>

60: <input type=”radio” id=”fax_type_w” name=”fax_type”

61: value=”work” />

62: <label for=”fax_type_w”>work</label>

63: <input type=”radio” id=”fax_type_o” name=”fax_type”

64: value=”other” />

65: <label for=”fax_type_o”>other</label>

66: </fieldset>

67:

68: <fieldset>

69: <legend>Email Address:</legend><br/>

70: <input type=”email” name=”email” size=”30” maxlength=”150” />

71: <input type=”radio” id=”email_type_h” name=”email_type”

72: value=”home” checked />

73: <label for=”email_type_h”>home</label>

74: <input type=”radio” id=”email_type_w” name=”email_type”

75: value=”work” />

76: <label for=”email_type_w”>work</label>

77: <input type=”radio” id=”email_type_o” name=”email_type”

78: value=”other” />

79: <label for=”email_type_o”>other</label>

80: </fieldset>

81:

82: <p><label for=”note”>Personal Note:</label><br/>

83: <textarea id=”note” name=”note” cols=”35”

84: rows=”3”></textarea></p>

85:

86: <button type=”submit” name=”submit”

87: value=”send”>Add Entry</button>

88: </form>

89: END_OF_TEXT;

Stop here for a minute and make sure

90: } else if ($_POST) {

91: //time to add to tables, so check for required fields

92: if (($_POST[‘f_name’] == “”) || ($_POST[‘l_name’] == “”)) {

93: header(“Location: addentry.php”);

94: exit;

95: }

96:

97: //connect to database

98: doDB();

99:

100: //create clean versions of input strings

101: $safe_f_name = mysqli_real_escape_string($mysqli,

102: $_POST[‘f_name’]);

103: $safe_l_name = mysqli_real_escape_string($mysqli,

104: $_POST[‘l_name’]);

105: $safe_address = mysqli_real_escape_string($mysqli,

106: $_POST[‘address’]);

107: $safe_city = mysqli_real_escape_string($mysqli,

108: $_POST[‘city’]);

109: $safe_state = mysqli_real_escape_string($mysqli,

110: $_POST[‘state’]);

111: $safe_zipcode = mysqli_real_escape_string($mysqli,

112: $_POST[‘zipcode’]);

113: $safe_tel_number = mysqli_real_escape_string($mysqli,

114: $_POST[‘tel_number’]);

115: $safe_fax_number = mysqli_real_escape_string($mysqli,

116: $_POST[‘fax_number’]);

117: $safe_email = mysqli_real_escape_string($mysqli,

118: $_POST[‘email’]);

119: $safe_note = mysqli_real_escape_string($mysqli,

120: $_POST[‘note’]);

121:

122: //add to master_name table

123: $add_master_sql = “INSERT INTO master_name (date_added,

124: date_modified, f_name, l_name) VALUES

125: (now(), now(), ‘“.$safe_f_name.”’, ‘“.$safe_l_name.”’)”;

126: $add_master_res = mysqli_query($mysqli, $add_master_sql)

127: or die(mysqli_error($mysqli));

128:

129: //get master_id for use with other tables

130: $master_id = mysqli_insert_id($mysqli);

131:

132: if (($_POST[‘address’]) || ($_POST[‘city’]) ||

133: ($_POST[‘state’]) || ($_POST[‘zipcode’])) {

134: //something relevant, so add to address table

135: $add_address_sql = “INSERT INTO address (master_id,

136: date_added, date_modified, address, city, state,

137: zipcode, type) VALUES

138: (‘“.$master_id.”’, now(), now(),

139: ‘“.$safe_address.”’, ‘“.$safe_city.”’,

140: ‘“.$safe_state.”’ , ‘“.$safe_zipcode.”’ ,

141: ‘“.$_POST[‘add_type’].”’)”;

142: $add_address_res = mysqli_query($mysqli, $add_address_sql)

143: or die(mysqli_error($mysqli));

144: }

145:

146: if ($_POST[‘tel_number’]) {

147: //something relevant, so add to telephone table

148: $add_tel_sql = “INSERT INTO telephone (master_id, date_added,

149: date_modified, tel_number, type) VALUES

150: (‘“.$master_id.”’, now(), now(),

151: ‘“.$safe_tel_number.”’, ‘“.$_POST[‘tel_type’].”’)”;

152: $add_tel_res = mysqli_query($mysqli, $add_tel_sql)

153: or die(mysqli_error($mysqli));

154: }

155:

156: if ($_POST[‘fax_number’]) {

157: //something relevant, so add to fax table

158: $add_fax_sql = “INSERT INTO fax (master_id, date_added,

159: date_modified, fax_number, type) VALUES

160: (‘“.$master_id.”’, now(), now(), ‘“.$safe_fax_number.”’,

161: ‘“.$_POST[‘fax_type’].”’)”;

162: $add_fax_res = mysqli_query($mysqli, $add_fax_sql)

163: or die(mysqli_error($mysqli));

164: }

165: if ($_POST[‘email’]) {

166: //something relevant, so add to email table

167: $add_email_sql = “INSERT INTO email (master_id, date_added,

168: date_modified, email, type) VALUES

169: (‘“.$master_id.”’, now(), now(), ‘“.$safe_email.”’,

170: ‘“.$_POST[‘email_type’].”’)”;

171: $add_email_res = mysqli_query($mysqli, $add_email_sql)

172: or die(mysqli_error($mysqli));

173: }

174:

175: if ($_POST[‘note’]) {

176: //something relevant, so add to notes table

177: $add_notes_sql = “INSERT INTO personal_notes (master_id,

178: date_added, date_modified, note) VALUES

179: (‘“.$master_id.”’, now(), now(),

180: ‘“.$safe_note.”’)”;

181: $add_notes_res = mysqli_query($mysqli, $add_notes_sql)

182: or die(mysqli_error($mysqli));

183: }

184: mysqli_close($mysqli);

185: $display_block = “<p>Your entry has been added. Would you

186: like to <a href=\”addentry.php\”>add another</a>?</p>”;

187: }

188: ?>

189: <!DOCTYPE html>

190: <head>

191: <title>Add an Entry</title>

192: </head>

193: <body>

194: <h1>Add an Entry</h1>

195: <?php echo $display_block; ?>

196: </body>

197: </html>

The output for add entry is as follows:

Q) Explain how to View Records of Online Address Book in MYSQL.

Here we create the two-part script that shows how to select and view records in your database.

selentry.php shows the select-and-view script, which has three parts:

1. the record-selection form (lines 5–42)

2. the code to display the record contents(lines 43–172), and

3. the HTML template that displays the dynamically generated strings (lines 176–184).

The Record-Selection Form

1: <?php

2: include ‘addressbook_include.php’;

3: doDB();

5: if (!$_POST) {

6: //haven’t seen the selection form, so show it

7: $display_block = “<h1>Select an Entry</h1>”;

9: //get parts of records

10: $get_list_sql = “SELECT id,

11: CONCAT_WS(‘, ‘, l_name, f_name) AS display_name

12: FROM master_name ORDER BY l_name, f_name”;

13: $get_list_res = mysqli_query($mysqli, $get_list_sql)

14: or die(mysqli_error($mysqli));

15:

16: if (mysqli_num_rows($get_list_res) < 1) {

17: //no records

18: $display_block .= “<p><em>Sorry, no records to select!</em></p>”;

19:

20: } else {

21: //has records, so get results and print in a form

22: $display_block .= “

23: <form method=\”post\” action=\””.$_SERVER[‘PHP_SELF’].”\”>

24: <p><label for=\”sel_id\”>Select a Record:</label><br/>

25: <select id=”sel_id\” name=\”sel_id\” required=\”required\”>

26: <option value=\”\”>-- Select One --</option>”;

27:

28: while ($recs = mysqli_fetch_array($get_list_res)) {

29: $id = $recs[‘id’];

30: $display_name = stripslashes($recs[‘display_name’]);

31: $display_block .=

32: “<option value=\””.$id.”\”>”.$display_name.”</option>”;

33: }

34:

35: $display_block .= “

36: </select>

37: <button type=\”submit\” name=\”submit\”

38: value=\”view\”>View Selected Entry\”></button>

39: </form>”;

40: }

41: //free result

42: mysqli_free_result($get_list_res);

The code to display the record contents

43: } else if ($_POST) {

44: //check for required fields

45: if ($_POST[‘sel_id’] == “”) {

46: header(“Location: selentry.php”);

47: exit;

48: }

49:

50: //create safe version of ID

51: $safe_id = mysqli_real_escape_string($mysqli, $_POST[‘sel_id’]);52:

52:

53: //get master_info

54: $get_master_sql = “SELECT concat_ws(‘ ‘,f_name,l_name) as display_name

55: FROM master_name WHERE id = ‘“.$safe_id.”’”;

56: $get_master_res = mysqli_query($mysqli, $get_master_sql)

57: or die(mysqli_error($mysqli));

58:

59: while ($name_info = mysqli_fetch_array($get_master_res)) {

60: $display_name = stripslashes($name_info[‘display_name’]);

61: }

62:

63: $display_block = “<h1>Showing Record for “.$display_name.”</h1>”;

64:

65: //free result

66: mysqli_free_result($get_master_res);

67:

68: //get all addresses

69: $get_addresses_sql = “SELECT address, city, state, zipcode, type FROM

70: address WHERE master_id = ‘“.$safe_id.”’”;

71: $get_addresses_res = mysqli_query($mysqli, $get_addresses_sql)

72: or die(mysqli_error($mysqli));

73:

74: if (mysqli_num_rows($get_addresses_res) > 0) {

75: $display_block .= “<p><strong>Addresses:</strong><br/>

76: <ul>”;

77:

78: while ($add_info = mysqli_fetch_array($get_addresses_res)) {

79: address = stripslashes($add_info[‘address’]);

80: $city = stripslashes($add_info[‘city’]);

81: $state = stripslashes($add_info[‘state’]);

82: $zipcode = stripslashes($add_info[‘zipcode’]);

83: $address_type = $add_info[‘type’];

84:

85: $display_block .= “<li>$address $city $state $zipcode

86: ($address_type)</li>”;

87: }

88: $display_block .= “</ul>”;

89: }

90: //free result

91: mysqli_free_result($get_addresses_res); 92: //get all tel

93: $get_tel_sql = “SELECT tel_number, type FROM telephone WHERE

94: master_id = ‘“.$safe_id.”’”;

95: $get_tel_res = mysqli_query($mysqli, $get_tel_sql)

96: or die(mysqli_error($mysqli));

97:

98: if (mysqli_num_rows($get_tel_res) > 0) {

99: $display_block .= “<p><strong>Telephone:</strong><br/>

100: <ul>”;

101:

102: while ($tel_info = mysqli_fetch_array($get_tel_res)) {

103: $tel_number = stripslashes($tel_info[‘tel_number’]);

104: $tel_type = $tel_info[‘type’];

105:

106: $display_block .= “<li>$tel_number ($tel_type)</li>”;

107: }

108: $display_block .= “</ul>”;

109: }

110: //free result

111: mysqli_free_result($get_tel_res);

112:

113: //get all fax

114: $get_fax_sql = “SELECT fax_number, type FROM fax WHERE

115: master_id = ‘“.$safe_id.”’”;

116: $get_fax_res = mysqli_query($mysqli, $get_fax_sql)

117: or die(mysqli_error($mysqli));

118:

119: if (mysqli_num_rows($get_fax_res) > 0) {

120: $display_block .= “<p><strong>Fax:</strong><br/>

121: <ul>”;

122:

123: while ($fax_info = mysqli_fetch_array($get_fax_res)) {

124: $fax_number = stripslashes($fax_info[‘fax_number’]);

125: $fax_type = $fax_info[‘type’];

126:

127: $display_block .= “<li>$fax_number ($fax_type)</li>”;

128: }

129: $display_block .= “</ul>”;

130: }

131: //free result

132: mysqli_free_result($get_fax_res);

133:

134: //get all email

135: $get_email_sql = “SELECT email, type FROM email WHERE

136: master_id = ‘“.$safe_id.”’”;

137: $get_email_res = mysqli_query($mysqli, $get_email_sql)

138: or die(mysqli_error($mysqli));

139: if (mysqli_num_rows($get_email_res) > 0) {

140: $display_block .= “<p><strong>Email:</strong><br/>

141: <ul>”;

142:

143: while ($email_info = mysqli_fetch_array($get_email_res)) {

144: $email = stripslashes($email_info[‘email’]);

145: $email_type = $email_info[‘type’];

146:

147: $display_block .= “<li>$email ($email_type)</li>”;

148: }

149: $display_block .= “</ul>”;

150: }

151: //free result

152: mysqli_free_result($get_email_res);

153:

154: //get personal note

155: $get_notes_sql = “SELECT note FROM personal_notes WHERE

156: master_id = ‘“.$safe_id.”’”;

157: $get_notes_res = mysqli_query($mysqli, $get_notes_sql)

158: or die(mysqli_error($mysqli));

159:

160: if (mysqli_num_rows($get_notes_res) == 1) {

161: while ($note_info = mysqli_fetch_array($get_notes_res)) {

162: $note = nl2br(stripslashes($note_info[‘note’]));

163: }

164: $display_block .= “<p><strong>Personal Notes:</strong><br/>

165: $note</p>”;

166: }

167: //free result

168: mysqli_free_result($get_notes_res);

169: $display_block .= “<br/>

170: <p style=\”text-align:center\”>

171: <a href=\””.$_SERVER[‘PHP_SELF’].”\”>select another</a></p>”;

172: }

173: //close connection to MySQL

174: mysqli_close($mysqli);

175: ?>

The HTML template that displays the dynamically generated strings

176: <!DOCTYPE html>

177: <html>

178: <head>

179: <title>My Records</title>

180: </head>

181: <body>

182: <?php echo $display_block; ?>

183: </body>

184: </html>

Q) Explain the mechanism of Record-Deletion.

The record-deletion mechanism is nearly identical to the script used to view a record.

(In fact, you can just take the first 42 lines, paste them into a new file called delentry.php, and change “View” to “Delete” in lines 24 and 38. Starting with a new line 43, the remainder of the code for delentry.php is shown in the following List)

43: } else if ($_POST) {

44: //check for required fields

45: if ($_POST[‘sel_id’] == “”) {

46: header(“Location: delentry.php”);

47: exit;

48: }

49:

50: //create safe version of ID

51: $safe_id = mysqli_real_escape_string($mysqli, $_POST[‘sel_id’]);

52:

53: //issue queries

54: $del_master_sql = “DELETE FROM master_name WHERE

55: id = ‘“.$safe_id.”’”;

56: $del_master_res = mysqli_query($mysqli, $del_master_sql)

57: or die(mysqli_error($mysqli));

58:

59: $del_address_sql = “DELETE FROM address WHERE

60: id = ‘“.$safe_id.”’”;

61: $del_address_res = mysqli_query($mysqli, $del_address_sql)

62: or die(mysqli_error($mysqli));

63:

64: $del_tel_sql = “DELETE FROM telephone WHERE id = ‘“.$safe_id.”’”;

65: $del_tel_res = mysqli_query($mysqli, $del_tel_sql)

66: or die(mysqli_error($mysqli));

67:

68: $del_fax_sql = “DELETE FROM fax WHERE id = ‘“.$safe_id.”’”;

69: $del_fax_res = mysqli_query($mysqli, $del_fax_sql)

70: or die(mysqli_error($mysqli));

71:

72: $del_email_sql = “DELETE FROM email WHERE id = ‘“.$safe_id.”’”;

73: $del_email_res = mysqli_query($mysqli, $del_email_sql)

74: or die(mysqli_error($mysqli));

75:

76: $del_note_sql = “DELETE FROM personal_notes WHERE

77: id = ‘“.$safe_id.”’”;

78: $del_note_res = mysqli_query($mysqli, $del_note_sql)

79: or die(mysqli_error($mysqli));

80:

81: mysqli_close($mysqli);

82:

83: $display_block = “<h1>Record(s) Deleted</h1>

84: <p>Would you like to

85: <a href=\””.$_SERVER[‘PHP_SELF’].”\”>delete another</a>?</p>”;

86: }

87: ?>

88: <!DOCTYPE html>

89: <html>

90: <head>

91: <title>My Records</title>

92: </head>

93: <body>

94: <?php echo $display_block; ?>

95: </body>

96: </html>

Search This Blog

computers

PHP unit5

Comments

Post a Comment

Popular posts from this blog

II B.COM CA- OFFICE AUTOMATION TOOLS - LAB PROGRAM 09

I YEAR - ICT - UNIT 5

C Lab Programs- I B.Com(CA)