VYSHNAVI DEGREE cOLLEGE,

U.KUMARASWAMY MCA.
For example, if you want to find records in the grocery_inventory table where the (irst ina

of the item starts with the letter A, you would use the following:
name LIKE
item
WHERE
SELECT * FROM grocery inventory
ICurr_qty |
| id | item name item desc | item _price
+
0.25 | 1000
| 1 | Apples | Beautiful, ripe apples.
+

1 row in set (0.00 sec)

tables: fruitfrom
Suppose that you have twoSelecting
Multiple Tables
and color. You can select all rows from each of the

two tables
When youbywant
using two separate
to select SELECT
rom both tables statements.
at once, the syntax of the SELECT statement differs
somewhat. First, you must ensure that all the tables you're usng n your query appear in the
LECT statement. Using the fruit and color example. if you simply
FROM
want to clause of the
select all SELI and rows from both tables, you might think you
columns would use the
following SELECT statement: fruit, color;
SELECT * FROM
id | colorname |
| id | fruitname
+.

1 | apple 1 | red |
1 | red |
orange
1 ced
| 3 | grape
4 banana 1|red
1 apple |2 orange
orange 2 | orange
3 grape 2 | orange
4 | banana 2 | orange
1 | apple purple
2 | orange 3 | purple
3 grape purplo
4 | banana 3 purple
1 | apple 4 yellow
| orange 4 | yellow
| 3 grape 4 yellow
4 | banana | 4 | yellow

16 roWs in set (0.00 sec)

Sixteen rows of repeated information are probably not what you were looking for. What this
query did is literally join a row in the color table to each row in the fruit table. Because there
are 4 recordsin the fruit table and 4 entries in the color table, thae's 16 records returned to
you.
When you select from multiple tables, you must build proper WHERE clauses to ensure that
youreally get what you want. In the case of the fruit and color tables, what you really want
is to see the fruitname and colorname records from these two tables where the IDs of each
match up.
Simply, you append the table name to the field name, like this:
tablename.fieldname
So, the query for selecting fruitname and colorname from both tables where the IDs match
wOuld be as follows:
SELECT fruitname, colorname FROM fruit, color WHERE fruit.id =
This query produces a better result for you: color.id;

Programming with PHP and MySQL For BSC VÊh Sem Page| 76
 VYSHNAVI DEGREE COLLEGE, YMG.
U . K U M A R AS W A M YMCA.

fruitname colorname

| apple |red
orange | orange
| grape I purple
banana | yelloN
4

4 ros in set (0.00 sec)

U s i n gJ O I N

several types of
JOINs in MySQL, all of which refer to the order in which the
You
can
use
together
and the results are displayed. The type of JOIN used with the fruit
are put
tables

tables is an INNER JOIN, although it wasn't written explicitly as such. To rewrite

using the proper INNER JOIN syntax, you use the following:
color
and statement

the SQL CEILECT fruitname, colorname EROM fuit

TYNZR JOIN color ON Eruit.id = color.id:
thic:
resultset
looks like
Your
| fruitname| colorname

| apple | red
| orange | orange
grape | purple
| banana | yellow
4 rows in set (0.00 sec)

When joining two tables with LEFT JOIN,

Another common type of JOIN 0s the LEFT JOIN. whether there are matches in the second
matter
all rows from the first table are returned, no
table.
SELECT £irstname, lastname, emai1
FROM master name
name.name id = email.name id;
LEFT JOIN email ON master

with the table order reversed. In other words,

A RIGHT JOIN works like LEFT JOIN but
second table are refurned, no matter whether
when using a RIGHT JOIN, all rows from the
matches exist in the first table. master name
SELECT firstname lastname, email EROM
name.name id = email.name id;
RIGHT JOIN email ON master

Using Subqueries
SQL statement.
statement that appears within another
Simply stated, a subquery is a SELECT for bulky
prove extremely useful because they often eliminate the need
Such queries can elíminate the need
queries, and in the case of application programming, subqueries can
JOIN
for multiple queries within loops.
syntax is shown here:
An example of the basic subquerycolumns FROM table name WHERE somecolumn
=
expressions and
SELECT
(SUBQUERY) ;
as shown here:
UPDATE and DELETE statements, (SUBOUERY) ;
Youcan also use subqueries with =
DELETE FROM table
name WHERE somecolumn
WHERE somecolumn =
= 'something'
somecolumn
UPDATE table name
SET
(SUBQUERY) ;
in parentheses-no exceptions!
The subquery must always appear
Page | 77
PHP and MySQL For BSC
VÊh Sem
Programming with
 U.KUMARA SWAMY MCA. VYSHNAVI DEGREE COLLEGE,

The following example uses a subauery to obtain records from users 1n te adster
table who have an email address in the cmail table:
name
name
SELECT £irstname, lastname FROM master

email) ;
FROM
WHERE name id IN (SELECT name id

Using the UPDATE Command to Modify Records columns

UPDATE is the SQL cOmmand used to modify the contents of one or more
this:
jn
syntax looks like
UPDATE
an existing record or set of records. The most basic
UPDATE table name
SET columnl=' new value'
column2= new value2
LWESRE Some condition is trueJ
status |
|id fruit name +
+- -

1 | apple
| ripe
rotten
2 | orange
| ripe
3 | grape
| rotten
4 | banana

(0.00 sec)
4 rows in set
To update the status of the fruit to ripe, useSET status
'ripe';
UPDATE f r u i t 'grape' i
fruit name
UPDATE fruit SET names will be
set to 'grape'.You
'ripe'and all fruit
But abOve commands all fruits status set. as table, unless you reallyintend to
when updating8 a
must be careful and use a condition
records to the same value.
change all the columns for all
fruit name | status
| id |
|ripe
1 grape
| ripe
2 grape
3 | grape | ripe
4 | grape | ripe

(0.00 sec)
4 rows in set

WHERE clauses to match specific

Conditional UPDATES
means that you are using
Making a conditional UPDATE UPDATE statement is just like using a WHERE clause
records. Using a WHERE clause
in an
statement. contains
in a SELECT
table has not been completely filled with grapes but instead
Assume that your fruit
with a spelling mistake (grappe instead of grape).
four records, one mistake is as follows:
spelling
to fix the
The UPDATE statement f r u i t name = grape HERE £ruit name 'grappe';
£ruit SET this result
UPDATE
one row was changed, as shown with
one row was matched and
In this case, only
U.KUMARA SWAMY MCA.
VYSHNAVI DEGREE COLLEGE, YMG

id fruit name status

+

| 1 apple ripe
2 pear ripe
3 | banana | ripe
4 | grape | ripe
4 rows in set (0.00 sec)

Using the REPLACE Command

for modifying records is to use the REPLACE
command, which is
method

Another
similar to the
INSERT command.
remarkably
INTO table name (column list) VALUES (colunn values) ;
works like this: If the record you are inserting into the table
R E P L A C E

statement
the record in the
that matches a record alreadyin the table,
REPLACE

The key value

c o n t
a primary the new recordIinserted in its place.
a i n s

deleted and replaces the entry for Apples:

t a b l e is inventory table, the following command
grocery_ 'Granny Smi th
inventory VALUES (1,
Using the REPLACE INTO grocery
'Sweet!", '0.50', 1000) ;
Apples',
following result:
the
Vou should see
2rows affected (0.00 sec)result states 2 rows affected. In this case, because
id is a
OK,
Query
result, notice that the grocery_inventory table, the
original row was
query the
In the matching value in
key that hada rows affected.
was inserted: 2 does
key in the new record
primary
the new row primary
deleted and and the value of the inserted, and
use a REPLACE statement, table, the record is simply
Tf vou already in the
match avalue for a primary key
not affected.
only one row is
DELETE Command
Using the
follows:
DELETE syntax is as
The basic DELETE FROM table
name
condition is true]
[WHERE some you use
Command-when
[LIMIT rows] DELETE
specification is used in the
column
Notice that no removed.
entire record is
DELETE, the
using DELETE.
careful when records in the
table:
You must be statement removes all
DELETE FROM f r u i t ;

the following UPDATE

statement,
SELECTor
conditional
just like a
Conditional DELETE:
statement, records.
DELETE specific
match
conditional
clauses to
A WHERE

means you
are using

Page | 79
 U.KUMARA SWAMY MCA. VYSHNAVI DEGREE.COLLEGE,

fruit name status |

| 1d
- + -
|ripe
1 | apple
| rotten
2 | pear
|ripe
3 | banana rotten
4 | grape
sec)
4 rows in set (0.00
fruit from the fruit table:
A prime example is to remove all rocords for rottenstatus = r o t t e n ' ;
DELEE FROM fruit WHERE
Two records were deleted:
Query OK, 2rows affected (0.00 sec)
+
status
| id | fruit name
+-.

| 1 |apple ripe
| 3 | banana ripe
+-
2 rows in set (0.00 sec)
To remove the oldest record, first use ORDER BY to sort the results appropriately, and then
use LIMIT to remove just one record:
DETEE FROM access log ORDER BY date accessed DESC LIMIT 1;

Frequently Used String Functions in MySQL

MySQL's built-in string-related functions can be used several ways. You can use functions in
SELECT statements without specifying a table to retrieve a result of the function.
Or you can use functions to enhance your SELECT results by concatenating two fields to
form a new string.
Length and Concatenation Functions
The group of length and concatenation functions focuses on the length of strings and
concatenating strings together. Length-related functions include LENGTH( ),
OCTET_LENGTH( ), CHAR_LENGTH(), and CHARACTER_LENGTH(), which do almost
the same thing: count characters in a string.
SELECT LENGTH (This is cool! ');
The result is this:
+ -+

| LENGTH(This is cool|') |
13 |
- -+
1 row in set (0.00 sec)
The fun begins with the CONCAT) function, which concatenates two or more strings:
SELECT CONCAT ('My', 'S', QL');
This query results in the following:

Programming with PHP and MySQL For BSc VIth Sem

Page |80
UKUMARASWAMYMCA.
VYSHNAVI DEGREE COLLEGE, YMG.
+

| CONCAT('My', 'S, 'QL') |

| MysQL
1 rOW in set (0.00 sec)
SELECT
CONCAT (£irstname, lastname ) EROM master name;

also
use concat () function on any table.
We
can

WS) stands for concatenate with separator. The separator can be anything you
uses whitespace:
CONTACT_W

butt the following example, firstname, lastname) FROM master name;

choose,

SELECT
CONCAT WS (
and | Padding Functions:
Trimming

provides several
functions for adding and removing extra characters (including
MySQL
from strings. The RTRIM) and
LTRIMO functions remove whitespace from
whitespace)
left, side of astring:
either theright or SELECT RTRIM('stringstring '):
results inthe following, although it is difficult to se the change:
This query +
| RTRIM('stringstring ")|

| stringstring
+

1 row in set (0.00 sec)

easier to see:
The LTRIM)function results are stringstring');
SELECT LTRIM(
following, with the whitespace clearly stripped:
This query results in the +

| LTRIM(' stringstring') +|
| stringstring
+
sec)
1 row in set (0.00
leading Xcharacters from the
For example, to remove the
string XXXneedleXXX, use FROM
'XXXneedleXXX') ;
SELECT TRIM (LEADING 'X

Here is the result of this query:

+
FROM 'XXXneedleXXX' ) |
| TRIM(LEADING 'X' +

needleXXX
+
sec)
1 row in set (0.00 from the end of the
string:
remove the characters
to
You can use TRAILING TRIM(TRAILING
'XXXneedleXXX');
X FROM
SELECT
are as follows:
The results of this query

Page |81
MySQL For BSC V[h Sem
Programming with PHP and
U.KUMARA SWAMY MCA. VYSHNAVI DEGREE

+
COLLEGE, VNMG.
I IRIM(TRAILING'X FROM 'XXXneedleXXX') |
+

| XXXneedle

1 row in set (0.00 sec)

If neither LEADING nor TRAI ING isindicated, both are assumea:

XXXneedleXXX') ;
SELECT TRIM( g FROM
This query results in this:
+

'XXXneedleXXX') |
T TRIM('X FROM - -+
|needle

1 rOw in set (0.00 sec)

Just as RTRIM) and LTRIMOremove nadding characters, RPAD0 and LPAD) add
characters to a string. For example, you might want to add specific identification characters

to a string that is part of an order number, in adatabase used for sales.

When you use the padding functions, the required elenments are the string, the target length,
and the padding character. For example. pad the string needle with the Xcharacter until the
string is 10 characters long uing this query:
SEIECT RPAD ('needle', 10, 'x') ;
You will see this result:

| RPAD('needle', 10, 'X') |

| needleXXXX
1 row in set (0.00 sec)
Location and Position Functions:
The group of location and position functions is useful for finding parts of strings within
other strings. The LOCATE)function returns the position of the first occurrence of a given
substring within the target string. For example,'haystackneedlehaystack');
SELECT LOCATE('needle'
you can look for anedle in ahaystack:
You should see this result:
+

| LOCATE('needle', 'haystackneedlehaystack') |
+

9|

1 row in set (0.00 sec)

The substring needle begins at position 9in the target string. If the substring cannot be found
in the target string, MySQL returns 0 as a result.
An extension of the LOCATE) function is to use a third argument for starting position.

Programming with PHP and MySQL For BSc V[h Sem Page | 82
 U.KUMARASWAMYMCA. VYSHNAVI DEGREE COLLEGE, YMG.

looking
for needle in haystack before position 9, you'll receive a result.
needle. starts at position 9, youll receive a0result if you
O h e r w i s e , because,
you
slart

specify a greater
Il
slarting
position.
SubstringFunctions:
Ilyourgoal is to extract a substring from a target string, several functions fit the bill. Given a

starting
position,
and length, you can use the SUBSTRING( ) function. This example
string,
characters from the string MySQL, starting at position 2:
gets
t h r e e

SELECT SUBSTRING ("MySQL", 2, 3);

follows:

resultis
as
The
| SUBSTRING("MySQL", 2, 3) |

|y$Q
1 row in set (0.00 sec)
want a few characters from the left or right ends of a string, use the
If youjust functions:
LEFT) and RIGHT()
SELECT LEFT ("MySQL", 2) ;
The result of this query is as follows:

| LEFT("My SQL", 2) |
| My
+

1 row in set (0.00 sec)

Similarly, using RIGHT)
SELECT RIGHT ("MySOL", 3) ;
produces the following results:

|RIGHT("MySQL", 3) |
SQL

1 row in set (0.00 sec)

String Modification Functions:
PHP has numerous functions to modify the appearance of strings, but if you can perform the
task as part of the SQL statement, all the better -let the database system do as much work as
possible.
The MySQL LCASE) and UCASE)functions transform a string into lowercase or uppercase:
SELECT LCASE('MYSQL');
This query produces the following results:

Programming with PHP and MySQL For BSc VIh Sem Page | 83
 U.KUMARA SWAMY MCA.
VYSHNAVI DEGREE
COLLEGE,,YMG.
| LCASE(MYSQL') |
-+

| mysql
+ -+

1 row in set (0.00 sec)

For uppercasing, use the following

This query produces SELECT UCASE('mysql')i
the following
+. results:
| UCASE ('mysql') I
| MYSQL

1 row in set (0.00 sec)

Another fun
uke-repeats astring-manipulation function is REPEATO, which does just what it
string for a given number of imes:
sounds
You should see this SELECT REPEAT ("bowWOw" t 4) i
result:
| REPEAT("bowwow", 4)

| bowwowbowwowbowwowbowwOw |
+ +

1 row in set (0.00 sec)

1ne KEPLACE) function replaces all oceurrences of a given string with
another Stin8
SEECT REPLACE ('bowwowbowwowbowwowbowwow', 'wow'WON )
This query
produces the following:
+

| REPLACE(' bowwowbowwowbowwowbowow 'wow', "WOw') |

| bowwOWbowwOWbowwoWbowwOW

1 row in set (0.00 sec)

Using Date and Time functions in MySQL

We can use MySQL's built-in date-related functions in SELECT
statements,
without specifying a table, to retrieve a result of the function. Or we can use the with or
with any type of date field: date, datetime, timestamp, and year. functions
Working with Days :
The DAYOFWEEK) and WEEKDAY) functions do similar
results.
things with slightly different
DAYOFWEEK) returns the week day number (1 for Sunday,2 for Monday .....7 for
Saturday ) for a date specified as argument.

Programming with PHP and MySQL For BSc V.h Sem

Page |84
UKUMARA SWAMYMCA. VYSHNAVI DEGREE COLLEGE, YMG.

DAYOFWEEK (date)
S y n t a x :

Example-
SELECT DAYOEWEEK ('2012-01-09'):
T h i sq u e r y
produces the following result:
DAYOFWEEK(2012-01-09') |

|2|

sec)
in set (0.00
1row
Ther e s u l ts h o w s
sthat January 9, 2012, was weekday index 2, or
The WEEKDAY) function returns the weekday index for adateMonday.
value (a number from 0to).
Note: 0= Monday, 1= Tuesday, 2=Wednesday,3= Thursday, 4= Friday, 5 =
Saturday, 6= Sunday.
Syntax - WEEKDAY(date)
SELECT WEEKDAY(2012-01-09");
| WEEKDAY(2012-01-09") |

|0|
+

1row in set (0.00 sec)

The result shows that January 9, 2012, was weekday index 0. Because WEEKDAY) uses
Monday as the first day of the week at position 0and Sunday as the last day at position 6, 0
is accurate: Monday.
The DAYOFMONTHÌ and DAYOFYEAR) functions are more straightforward, with oniy
one result and a range that starts at 1 and ends at 31 for DAYOFMONTH) and 366 for
DAYOFYEAR). Some examples follow:
SELECT DAYOFMONTH(2012-01-09);
This query produces the following results:
+

| DAYOFMONTH(2012-01-09) |
|9|

1 row in set (0.00 sec)

SELECT DAYOFYEAR(2012-01-09);
This query produces the following results:

DAYOFYEAR(2012-01-09") |
|09

1row in set (0.00 sec)

It might seem odd to have a function that returns the day of the month on a particular date
because the day is right there in the string. But think about using these types of functions in
WHERE clauses to perform comparisons on records. If we have a table that holds online

Programming with PHP and MySQL For BSc V[h Sem Page | 85
U.KUMARA SWAMY MCA. VYSHNAVI DEGREE
COLLEGE
orders with a field the order was placed, we can quickly get a
E,YMG.
containing
the orders placed on any the date
orders were counl of
the first half of the month given day of the week, or see how many
versus the
placed during,
The following two queries show second hal. orders were placed during the first three
the week (throughout all months) how many the remaining days of the
week: days of
and then <4;
SELECT COUNT(id) FROM orders WHERE
DAYOFWEEK(date_ordered)

>3;
SELECT COUNT(id) FROM orders WHERE
DAYOFWEEK(date_ordered)

the number of orders

Using DAYOFMONTHÌ, the following examples show
second half:
placed during the first half iof any month versus the <16;
SELECT COUNT(id) FROM orders
D A Y O F M O N T H ( d a t e _ o r d e r e d )

WHERE ) >15;
SELECT
DAYOFMONTH(date_ordered)

COUNT(id)
we can use FROM orders
the DAYNAMEO s WHERE
function to add nore life to your results because it returns the

name of the weekday for any given date:

SELECT DAYNAME(date ordered) FROM orders;
This query produces results such as these:
+

| DAYNAME(date_ordered) |
Thursday |
Monday |
Thursday
Thursday |
Wednesday |
Thursday |
Sunday |
Sunday |
8 rows in set (0.00 sec)
Functions aren't limited to WHERE clauses- we can use them in OkDEK BI
clauses as well, such as the following:
orders ORDER BY
SELECT DAYNAME(date ordered) FROM
DAYOFWEEK(date_ordered):
parts of the calendar, and
Working with Months and Years Davs of the week are not the only
well. Just like the DAYOFWEEK)
MysQL has functions specifically for months and years as
MONTHNAME) return the number of the
and DAYNAME) functions, MONTH) and
example:
month in ayear and the name of the month for a given date. For
SELECT MONTH(2012-01-09), MONTHNAME(2012-01-09");
This query produces the following:

|MONTH(2012-01-09') | MONTHNAME(2012-01-09) |
+

|1|January |
+
1row in set (0.00 sec)
a lot of
Using MONTHNAME) on the orders table shows the proper results but can show
repeated data:
+.

|MONTHNAME(date_ordered) |

Programming with PHP and MySQL For BSc VÊh Sem Page |86
U K U M A R AS W A M YMCA. VYSHNAVI DEGREE COLLEGE, YMG.

/November/ YEARO function returns the year ofa

the
with )
years,

SELECT DISTINCT YEAR(date_

given date:
work ordered) FROM orders;
TFor
h i sq u e r yp r o d u c e s results like the following
YEAR(date_ordered)

/ 2011

/ 2012

sec)
set(0.00
1rowin
Working with Weeks:
DAYNAME)RReturns the name of the week day of adate specified in the argument.
Syntax - DAYNAME(date1)

GELECT DAYNAME(2001-12-30');
Here's proof:
+

|DAYNAME('2001-12-30) |
| Sunday |
1row in set (0.00 sec)
WEEK) returns the week number for agiven date.
Syntax :- WEEK(date[,mode]):
Working with Hours, Minutes, and Seconds:
HOUR) -Returns the HOUR of atime. The return value is within the range of 0to 23
for time-of-day values. The range of time values may be larger than 23.
Syntax - HOUR(ime)
MINUTE) - returns a MINUTE from a time or datetime value.
Syntax: MINUTE(imel);
SECOND)- Returns the second for atime. The return value is in the range of 0to 59.
Syntax: SECOND(tm)
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;
This query produces the following:
t
| hour | minute | second |

|7|27 | 49 |
t-+ -+

1row in set (0.00 sec)

Thae's a lot of queries to get at one time from a datetime field-we can put the

Programming with PHP and MySQL For BSc VÊth Sem Page |87
 U.KUMARA SWAMY MCA. VYSHNAVI DEGREE
COLLEGE, YMG.
hour and minute together and even use CONCAT_WS)to put the: between the
results and get a representation of the time:
SELECT CcONCAT_WwS(:",HOUR(2012-01-09 07:27:49"),
MINUTE(2012-01-09 07:27:49))AS sample_time;
This query produces the following:

|sample_time |
+

| 7:27 |
+

1row in set (0.00 sec)

In the next section, we learn how to use the DATE FORMATO function to
properly format dates and times.

Formatting Dates and Times with MySQL:

The DATE _FORMAT) function formats a date, datetime, or timestamp field
into astring by using options that tell it exactly how to display the results. The syntax
of
DATE FORMATO is as follows:
DATE_FORMAT(date,format)

TABLE 16.2 DATE_FORMAT () Format String Options

Option Result
Month name (January through December)
sb Abbreviated month name (Jan through Dec)
Month, padded digits (01 through 12)
Month (1 through 12)
Weekday name (Sunday through Saturday)
%a Abbreviated weekday name (Sun through Sat)
Day of the month using the English suffx, such as first, second,
third, and so on
Day of the month, padded digits (00 through 31)
Day of the month (0 through 31)
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 sv
Four-digit year for the week where Monday is the first day; used
with %v
Day of the week (0-Sunday..6=Saturday)
Week (O through 53) where Sunday is the first day of the week
Su Week (O through 53) where Monday is the first day of the week
Week (1 through 53) where Sunday is the first day of the week:
used with %X
Week (1 through 53) where Monday is the first day of the week:
used with %x
&H Hour, padded digits (00 through 23)

Programming with PHP and MySQL For BSC V[h Sem Page | 88
U.KUMARASWAMYMCA. VYSHNAVI DEGREE COLLEGE, YMG.
T A B L E1 6 . 2
Continued
Hour (0 through 23)
k Hour, padded digits (01
through 12)
Hour (1 through 12)
Minutes, padded d1gits (00
through 59)
Seconds, padded digits (00
through
Seconds, padded digits (00 through 59)
59)
Time, 12-hour clock
(hh:mm:ss [APIM)
Time, 24-hour clock (hh:mm:ss)
AM or PM

Todisplay the 02:02 result that we rigged in the previous section, you use the %h
and %i options to return the hour and minute from the date with a: between the

two
options. For example:
SELECT DATE FORMAT(2012-01-09 02:02:00', "%h:%i) AS sample_time;
This query produces the following:
| sample_time |
+.

|02:02 |

1row in set (0.00 sec)

The following are just afew more examples of the LDATE_FORMAT) function in
use, but this function is best understood by practicing it yourselE.
SELECT DATE_FORMAT(2012-01-09, "%N, %M %D, %Y) AS sample_time;
This query produces the following output:
|sample_time |
|Monday, January 9th, 2012 |
1row in set (0.00 sec)
Using Transactions
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.
Basic Syntax Used in Transactions You need to understand the following key terms when
thinking about using transactions with MySQL:
.COMMIT-This command occurs at the end of the series of queries in your 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 your
transaction fails and resets the affected tables to their pretransaction state.
a sequence of events in the MySQL command-line interface might look something like this:

Programming with PHP and MySQL For BSc VIà Sem Page 89
 U.KUMARA SWAMY MCA. VYSHNAVI DEGREE
1. Issue the BEGIN Command to begin a new transaction. COLLEGE, VMG.
2. Select avalue from tablel to insert into table2.
3. If a value cannot be selected from tablel, issue a ROLLBACK command to
transaction ends and that the tables return to their previous state. ensure ththal the
4. If a value can be selected from tablel, insert a value into table2.
5. If the insertion of a record into table2 fails, issue a ROLLBACK command to ensure that
the transaction ends and that the tables return to their previous state.
6. Ifa value can be inserted into tablel, insert a value into table2.
7. If the insertion of a record into table3 fails, issue a ROLLBACK command
the transaction ends and that the tables return to their to ensure that
previous state.
8. However, if the insertion of arecord into table3 is successful,
issue a COMMIT
toensure that the transaction ends and that the tables update
appropriately. Command
Stored procedure
a stored procedure is a procedure in SQL that is stored in the database server rather
web server. than the
. 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.
The syntax for creating a basic stored procedure is as follows:
CREATE PROCEDURE procedure_name () query /7
CREATE PROCEDURE spl 0 SELEcT * FROM emp where deptno=10;
To call the stored procedure, use the CALL command:
CALL sp1 () ;
Above CALL query displays all the details of employees working in department number 10.
Connecting to MySQL with PHP
To successfully use the PHP functions to talk to MySQL, you must have MySQL running at a
location to which your web server can connect (not necessarily the same machne as yor
web server). You also must havecreated a user (witha password), and you must know the
name of the database to which you want to connect.
Making a Connection:
The basic syntax for a connection to MySQL 0s as follows:
Fmysqli = nysqli connect ("hostname", "username", "password",
"database") i
The value of $mnysgli is the result of the function and is used in later furnctions for
communicating with MySQL.
With sanmple values inserted, the connection code looks like this:
Smysqli = mysqli_connect ("localhost", "joeuser", "somepas s",
"testDB") ;
is a working example of a connection script. It creates a new connection in line 2 and then
tests to see whether an error occurred. If an error occurred, line 5 prints an error mnessage and
uses the mysqli connect_error(0 function to print the message. If no error occurs, line 8prints

Programming with PHP and MySQL For BSc VIjh Sem Page | 90
 VYSHNAVIDEGREE COLLEGE, YMG.
UKUMARASWAMYMCA.
includes
hostinformation resulting from calling the mysqli get host_info)
that

message

a
function. mysqli( ("localhost", "joeuser", "somepass , "testDB*);
< p h p

= new
Smysqli
1:

2: (mysqli
Connect errno(0)
3:
printf( "Connect failed: %s\n", mysqli connect error0);
if
4:
exit();

5:
6 ) else {
printf("Host information: %s\n". mysqli get host info(Smy sqli) );
7
8.
9: }
10: ?>
this script as mysqlconnect.php and place it in the document area of your web server.
t h es scriptt with your web browser and you willsee something like the following, if the
Save
Access
connection was successful.

Host information: localhost via TCP/IP

connection fails, an error message is printed. Line 5 generates an error via the
If the
mysqli_connect error) function. An example is shown here:
Connect failed: Access denied for user "joeuser'@'localhost' (using password:
YES)
However, if the connection is successful,line 8 prints the outputtof
mysqli get host info(), such as examples above.
Although the connection closes when the script finishes its execution, it is a good practice to
close the connection explicitly.
Executing Queries:
Half the battle in executing MySQL queries using PHP is knowing how to write the
SQL
query)
and you've already learned the basics of this in previous chapters. The mysqlimake
function in PHP is used to send your SQL query to MySQL. In your script, first the
connection and then execute a query. The script in Listing 18.3 creates a simple table called
testTable.
LISTING 18.3 A Script to Create a Table
1: <?php
2: $mysqli = mysqli connect ("l0calhost", "joeuser", "somepass", "testDB);
3
4: if (mysqli_connect_errno()) {
5: printf("Connect failed: %s\n", mysqli connect error());
6: exit ();
7: }else {
8 $sql = "CREATE TABLE testTable
9: (id INT NOT NULL PRIMARY KEY AUTO INCREMENT,
10: testField VARCHAR (75) )";
11: $res - mysqli_query (Smysqli, $sql);
12:
13: if ($res === TRUE) {
14: echo "Table testTable successfully created.;
15: } else {
16: printf("Could not create table: %s\n', mysqli_error(Smysqli));
17:
18:
19: my sqli_close (Smysqli);
20
21: ?>

Programming with PHP and MySQL For BSc V[h Sem Page 91
 U.KUMARA SWAMY MCA.
VYSHNAVI DEGREE E
Retrieving Error Messages: COLLEGE, YMG.
Take some time to familiarize yourself with the mysqli_error() function; it
friend. When used in conjunction with the PHP die() function, will become
which
at the point at which it appears, the mysqli error)funcion returns a simply
helpful
exits the your
when youmake a mistake. error m script
messag
Working with MySQL Data
Inserting, updating, deleting, and retrieving data all revolve around the use
mysqli query) function to execute the basic SQL queries you learned about
"Learning Basic SQL Commands." For INSERT, UPDATE, and DELETEin Chapter 16,
of the

additional scripting is required after the query has been executed because queries, no
displaying any results (unless you want to). When using SELECT queries, you're no
you have
a few
options for displaying the data retrieved by your query.
Inserting Data with PHP:
The easiest (and safest) method for inserting data at this stage in the game is to
simply hard-code the INSERT statement, as shown in
1: <7php
2: Surysqli = ysqli_ connect(" localhost', 'joeuser", "somepass, 'testD8'):
4:
if (ysqli_connect errno (0) {
printf("Connect failed: As\n', mysqli_ connect_error());
6: exit ();
7: } else {
8: $sql = INSERT INTO testTable (testField) VALUES ('some value'):
9: ^res = mysqli_query (Smy sqli, $sql);
10:
11: if (Sres == TRUE) {
12: echo "A record has been inserted.':
13: }else {
14: print ("Could not insert record: %s\n, mysqli error ($mysqli));
15:
16:
17: aysqli close (S=ysqli) ;
18:
19: >

Retrieving Data with PHP:

Because you have a few rows in your testTable table,you can write a PHP script to retrieve
that data. Starting with the basics, we write a script that issues a SELECT query but doesn't
overwhelm you with result data. Let's just get the number of rows. To do this, use the
mysqi
1:
num rows)function; see line 12 of Listing bellow
<?php
2:
3:
^mysqli = sysqli_connect ("localhost, 'joeuser, "somepass", testDB'):
4: if (mysqli connect errno() ) {
5:
printf ("Connect failed: $sin", mysql1_connect error(0);
6: exit () ;
7: } else {
8: $sql = "SELECT * FROM testTable;
9:
10:
$res = mysqli_query(Smy sqli, Ssql);
11: if ($res) {
12: Snumber of_roWS = y
13: printfCResult set q num
e rows ($res);
n. Snumber of_rows) i
14: } else {
15:
16:
printf("Could not retrieve records: %s\n", mysqli_error ($mysqli));
17:
18: mysqli_free _result (Sres);
19: mysqli_ close(Smysqli) ;
20:
21:

Programming with PHP and MySQL For BSc VIth Sem Page | 92
U.KUMARASWAMY MCA.
VYSHNAVI DEGREE COLLEGE, YMG,

Creating an Online Address Book

Creatingthe
Database Tables
Planning
and
think
of an address book, theobvious fields come to mind: name, address,
you
number,
address. In an online address book, a set of related tables helps
email

redundancy and repetition of information and allows you to display all

When
telephone
the
alleviale

in a
unified view.
inlormaltion
table and field names to use
for your online address book.
shows sample
Table
20.1.

you use 15QL

actual statements to create the tables, but first you should
and try to see the relationships appear. Ask yourself which
m i n u t e ,

In a information
atthis
look
fields: should be primary or unique keys
ofthe TABLE 20.1 Address Book Table and Field Names
Table Name Field Names
master name id, date added, date modified, f name, 1_name
address id, master_id, date added, date modified, address. city.
state, zipcode, type
telephone id, master_id, date added, datemodified, 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 following SQL statement creates the master name table:
CREATE TABLE master name (
id INT NOT NULL PRIMARY KEY AUTO INCREMENT,
date added DATETIME,
date modified DATETIME,
f name VARCHAR (75),
1 name VARCHAR (75)

CREATE TABLE address (

id INT NOT NULL PRIMARY KEY AUTO INCREMENT,
master id INT NOT NULL,
date added DATETIME,
date odified DATETIME,
address VARCHAR (255),
city VARCHAR (30),
state CHAR (2),
zipcode VARCHAR (10),
type ENUM ('home', 'work', 'other')

The telephone, fax, and email tables are all variations on the same
theme :
CREATE TABLE telephone (
id INT NOT NULL PRIMARY KEY AUTO INCREMENT,
master id INT NOT NULL,
date added DATETIME,
date modified DATETIME,

Programming with PHP and MySQL For BSc VIh Sem Page 93
 VYSHNAVI DEGREE COLLEGE,NMG,
U.KUMARA SWAMY MCA.

tel number VARCHAR (25),

type ENUM ('h ome', 'Work', 'other')
);
CREATE TABLE fax (
id INT NOT NULL PRIMARY KEY AUTO INCREMENT,
master id INT NOT NULL,
date added DATETIME,
date modi fied DATETIME,
fax number VARCHAR (25),
type ENUM ( 'home, 'work! 'other)
);
CREATE TABLE email (
id INT NOT NULL PRIMARY KEY AUTO INCREMENT
master id INT NOT NULL,
date added DATETIME,
date modi fied DATETIME,
email VARCHAR (150),
type ENUM (home', 'work!, 'other! )

CREATE TABLE personal notes (

1d int NOT NULL PRIMARY KEY AUTO INCREMENT;
master id INT NOT NULL UNIQUE,
date added DATETIME,
date modified DATETIME,
note TEXT

Creating a Menu: makes sense to create

a
Your online address book contains severallactions, so it
simple menu for all the scripts you Create
menu for your links. Listing 20.2 creates a
in this chapter, called mymenu.html
LISTING 20.2 Address Book Menu
1: <JD0CTYPE htal>
2: <html>
3: <head>
4: <title>My Address Book</title>
5: </head>
6: <body>
7: <h1>My Address Book</h1>
8:
9: <p<strong>Management</strong></p>
10: <ul>
11: <lib<a href=addentry. php">Add an Entry</a></li>
12: <li><a href='delentry. php">De lete an Entry<fa></li>
13: </ul>
14:
15: <p><strong> Viewing</ strong></p>
16: <ul>
17: <lis <a href='selentry. php">Select a Record</a></li>
18: </ul>
19: </body>
20: </html>

Programming with PHP and MySQL For BSC VIh Sem Page 94
 VYSHNAVI DEGREE COLLEGE, YMG.
U.KUMARASWAMY MCA.

C Ohep/locahort

My Address Book
sMmpoeat

" Dekte sn Enty

Verig

the Record-Addition Mechanism:

Creating

because you'll poterntially l be adding information to six different tables doesn't mean
Just will be monstrous.
your
form or script
OAdd an intry
c ohrp/ocahos/ 23j 03entry pro K

Add an Entry

S t A1dh;

-AMro

-Teplcne Nuatet

-FSb

Viewing Records
If you verified your work in the preceding section by issuing queries through the
MySQL monitor or other interface, you probably became tired of typing SELECT *
FROM...for every table.

Select an Entry
Seedt a Recat
Doa. lana
-Saled Ong
Atla. Anne

Smth Jctn

Programming with PHP and MySQL For BSc VIh Sem Page | 95
 U.KUMARA SWAMY MCA. VYSHNAVI DEGREE COLLEGE,.VMG.

Creating the Record-Deletion Mechanism

1ne reCord-deletion mechanism is nearly identical to the script used to view a record. Inr.
you can just takechange "View" to "Delete"
MyRucrd

Record(s) Deleted
odd1oo eto dekte ancthr7

Adding Subentries to a Record:

At this point in the chapter, you've learned how to add, remove, and view records.
What's missing is adding additional erntries to the related tables after you've already entered
a master record- entriesfor home versus work telephone number, for example.