Working with Databases
and MySQL
Introduction to Databases
A database is an ordered collection of
information from which a computer program
can quickly access information
Each row in a database table is called a record
A record in a database is a single complete
set of related information
Each column in a database table is called a
field
Fields are the individual categories of
information stored in a record
Name of Book
Introduction to Databases
(continued)
Figure 8-1 Employee directory database
Introduction to Databases
(continued)
A flat-file database stores
information in a single table
A relational database stores
information across multiple related
tables
Understanding Relational
Databases
Relational databases consist of one or more
related tables
A primary table is the main table in a
relationship that is referenced by another
table
A related table (or child table)
references a primary table in a relational
database
A primary key is a field that contains a
unique identifier for each record in a
primary table
5
Understanding Relational Databases
(continued)
A primary key is a type of index,
which identifies records in a database
to make retrievals and sorting faster
A foreign key is a field in a related
table that refers to the primary key in a
primary table
Primary and foreign keys link records
across multiple tables in a relational
database
6
One-to-One Relationships
A one-to-one relationship exists between
two tables when a related table contains
exactly one record for each record in the
primary table
Create one-to-one relationships to break
information into multiple, logical sets
Information in the tables in a one-to-one
relationship can be placed within a single
table
Make the information in one of the tables
confidential and accessible only by certain
individuals
7
One-to-One Relationships
(continued)
Figure 8-2 One-to-one relationship
One-to-One Relationships
(continued)
Figure 8-2 One-to-one relationship (continued)
One-to-Many Relationship
A one-to-many relationship exists in a
relational database when one record in a
primary table has many related records in
a related table
Breaking tables into multiple related tables
to reduce redundant and duplicate
information is called normalization
Provides a more efficient and less
redundant method of storing this
information in a database
10
One-to-Many Relationship
(continued)
Figure 8-3 Table with redundant information
11
One-to-Many Relationship
(continued)
Figure 8-4 One-to-many relationship
12
One-to-Many Relationship
(continued)
Figure 8-4 One-to-many relationship (continued)
13
Many-to-Many Relationship
A many-to-many relationship exists
in a relational database when many
records in one table are related to
many records in another table
A junction table creates a one-tomany relationship for each of the two
tables in a
many-to-many relationship
A junction table contains foreign keys
from the two tables
14
Working with Database Management
Systems
A database management system (or DBMS)
is an application or collection of applications
used to access and manage a database
A schema is the structure of a database
including its tables, fields, and relationships
A flat-file database management system
is a system that stores data in a flat-file format
A relational database management
system (or RDBMS) is a system that stores
data in a relational format
15
Working with Database Management
Systems (continued)
Figure 8-5 Many-to-many relationship
16
Working with Database Management
Systems (continued)
Figure 8-5 Many-to-many relationship (continued)
17
Working with Database Management
Systems (continued)
Important aspects of database
management systems:
The structuring and preservation of the
database file
Ensuring that data is stored correctly in
a databases tables, regardless of the
database format
Querying capability
18
Working with Database Management
Systems (continued)
A query is a structured set of instructions
and criteria for retrieving, adding, modifying,
and deleting database information
Structured query language (or SQL) is a
standard data manipulation language used
among many database management
systems
Open database connectivity (or ODBC)
allows ODBC-compliant applications to
access any data source for which there is an
ODBC driver
19
Querying Databases with Structured
Query Language
Table 8-1 Common SQL keywords
20
MYSQL
Name of Book
21
What is MySQL?
MySQL is a database engine that uses
the standard SQL interface. MySQL is
very popular as a back end for web
applications.
The MySQL engine can be accessed
from most major
programming/scripting languages
such as perl, php, and ruby making it
easy to develop applications.
Name of Book
22
What is MySQL good for?
Web Applications: Because MySQL can
be easily integrated into scripting
languages such as perl and php it is very
popular for web applications.
GUI Applications: The same scripting
language integration that makes MySQL
work well with web applications also
makes it work well with local GUI and even
command line applications or anything
else that stores structured data.
Name of Book
23
MySQL Commands
To list / view / see all the database just
type:
show databases;
To use the database just type:
use <database_name>;
Ex:
use `sample1`;
24
MySQL Commands
To list / view / see all the tables in the
database just type:
show tables; (show the tables inside
the
current database)
or
show tables from <database_name>; (to
Ex:
see tables from different database(s));
show tables from `sample1`;
25
MySQL Commands
Create a database:
Syntax:
CREATE DATABASE <database_name>;
Ex:
CREATE DATABASE `sample1` ;
26
MySQL Commands
Edit / Rename a database:
MySql remove the rename feature so you
need to drop and create a new database
instead of just renaming it...
27
MySQL Commands
Delete a database:
Syntax:
DROP DATABASE <database_name>;
Ex:
DROP DATABASE `sample1` ;
28
MySQL Commands
Create a table:
Note: when creating a table, first you
need to be sure that you are using the
correct database in which you want to
create your new table and also create at
least 1 field for the table otherwise, an
error will occur...
29
MySQL Commands
Create a table:
Syntax:
CREATE TABLE <table_name> (<field_name>
<type>(size_of_the_field) NULL / NOT NULL)
-> if you want the field to accept Null
values or not
Ex:
CREATE TABLE `sample1` (`student_name`
VARCHAR( 100 ) NULL);
30
MySQL Commands
Edit / Rename a table:
Syntax:
ALTER TABLE <old_table_name>
RENAME <new_table_name>;
Ex:
ALTER TABLE `sample1` RENAME
`sample2` ;
31
MySQL Commands
Delete a table:
Syntax:
DROP TABLE <table_name>;
Ex:
DROP TABLE `sample2`;
32
MySQL Commands
To show all columns / fields from a
table just type:
Syntax:
Show columns from <table_name>;
Ex:
Show columns from `sample1`;
33
MySQL Commands
Insert a field / column to a table:
Syntax:
ALTER TABLE <table_name> ADD
<field_name> <type>( <size> ) NULL /
NULL ;
NOT
Ex:
ALTER TABLE `sample1` ADD `address`
VARCHAR( 100 ) NULL ;
34
MySQL Commands
Edit / Rename a field / column of a
table;
Syntax:
ALTER TABLE <table_name> CHANGE
<old_field_name> <new_field_name>
<type>(<size>) NULL / NOT NULL;
Ex:
ALTER TABLE `sample1` CHANGE `name1`
`name` varchar(100) NULL;
35
MySQL Commands
Delete a field / column of a table;
Syntax:
ALTER TABLE <table_name> DROP
<field_name>;
Ex:
ALTER TABLE `sample1` DROP
`address`;
36
MySQL Commands
Inserting new record:
Syntax:
INSERT INTO <table_name>
(<field1>,<field2>...) VALUES
(<value1>,<value2>...);
Ex:
INSERT INTO `sample1` ( ` name` ,
`address` ) VALUES ('marco elias palic
valencia', 'porac, pampanga');
37
MySQL Commands
Edit a record:
A: Without a condition:
Syntax:
UPDATE <table_name> set <field_name1>
= <new_value1>, <field_name2>
=<new_value2>... ;
Ex:
UPDATE `sample1` SET ` name` = 'marco
elias palic valencia1',`address` =
'porac, pampanga1';
38
MySQL Commands
Edit a record:
B: With a condition:
Syntax:
UPDATE <table_name> set <field_name1> =
<new_value1>, <field_name2> = <new_value2>...
Where <field_name1> = <condition_value1> and
<field_name2> = <condition_value2>...;
Ex:
UPDATE `sample1` SET ` name` = 'marco elias
palic valencia1',`address` =
'porac,
pampanga1'
where name` = 'marco elias palic valencia' and
`address` =
'porac, pampanga';
39
MySQL Commands
Delete a record:
A: Without a condition:
Syntax:
DELETE from <table_name>;
Ex:
DELETE from `sample1`;
40
MySQL Commands
Delete a record:
B: With a condition:
Syntax:
DELETE from <table_name> where
<field_name1> = <condition_value1> and
<field_name2> = <condition_value2>...;
Ex:
DELETE FROM `sample1` WHERE `name` =
elias palic valencia' AND
pampanga';
'marco
`address` = 'porac,
41
MySQL Commands
Viewing / Searching for record(s)
A: Viewing all records:
Syntax:
SELECT <field1>, <field2> ... from <table_name>;
or if you want to select all fields just type *
(asterisk)
SELECT * from <table_name>;
Ex:
SELECT `name`,`address` from `sample1`;
or
SELECT * from `sample1`;
42
MySQL Commands
Viewing / Searching for record(s)
B: Searching for a particular record:
Syntax:
SELECT <field1>, <field2> ... from <table_name>
where <field1>=<condition_value1> and
<field2>=<condition_value2>...;
or if you want to select all fields just type * (asterisk)
SELECT * from <table_name> where
<field1>=<condition_value1> and
<field2>=<condition_value2>...;
43
MySQL Commands
Viewing / Searching for record(s)
B: Searching for a particular record:
Ex:
SELECT `name`,`address` FROM `sample1`
WHERE `name` = 'marco elias palic
valencia'
AND `address` = 'porac,
pampanga';
or
SELECT * FROM `sample1` WHERE `name` =
'marco elias palic valencia' AND `address` =
'porac, pampanga';
44
Name of Book
45
Securing the Initial MySQL
Accounts
Deleting the Anonymous User Account
mysql> DELETE FROM mysql.user WHERE User = '';
mysql> FLUSH PRIVILEGES;
Assigning a Password to the Root Account
mysql> UPDATE mysql.user SET Password = PASSWORD('newpwd')
-> WHERE User = 'root';
mysql> FLUSH PRIVILEGES;
The password assigned to the root
account and other user accounts is case
sensitive
46
Creating Users
A proxy is someone or something that acts
or performs a request for another person
Create a separate account for each Web
application that needs to access a
database
Use a GRANT statement to create user
accounts and assign privileges
Privileges are the operations that a user
can perform with a database
47
Creating Users (continued)
Table 8-2 Common MySQL database privileges
48
GRANT Statement
The syntax for the GRANT statement is:
GRANT privilege [(column)] [, privilege [(columns)]] ...
ON {table | * | *.* | database.*}
TO user [IDENTIFIED BY 'password'];
The GRANT statement creates the user
account
if it does not exist and assigns the specified
privileges
If the user account already exists, the
GRANT statement just updates the privileges
49
Revoking Privileges
The syntax for the REVOKE statement is:
REVOKE privilege [(column)] [, privilege [(columns)]] ...
ON {table | * | *.* | database.*}
FROM user;
The REVOKE ALL PRIVILEGES statement
removes all privileges from a user
account for a specified table or database
You must be logged in with the root
account or have sufficient privileges to
revoke privileges
from another user account
50
Deleting Users
To delete a user:
Revoke all privileges assigned to the user
account
for all databases
Use the REVOKE ALL PRIVILEGES statement
View the privileges assigned to a user account
with the SHOW GRANTS FOR user statement
To delete an existing user, use the DROP
USER statement
Use the DROP USER user statement to
delete the account from the user table in
the mysql database
51
Specifying Field Data Types
Table 8-3 Common MySQL data types
52
Specifying Field Data Types
(continued)
Table 8-3 Common MySQL data types (continued)
53
Sorting Query Results
Use the ORDER BY keyword with the
SELECT statement to perform an
alphanumeric sort of the results
returned from a query
mysql> SELECT make, model FROM inventory ORDER BY make, model;
To perform a reverse sort, add the DESC
keyword after the name of the field by
which
you want to perform the sort
mysql> SELECT make, model FROM inventory ORDER BY make DESC,
model;
54
Filtering Query Results
The criteria portion of the SELECT
statement determines which fields to
retrieve from a table
You can also specify which records to return
by using the WHERE keyword
mysql> SELECT * FROM inventory WHERE make='Martin';
Use the keywords AND and OR to specify
more detailed conditions about the records
you want to return
mysql> SELECT * FROM inventory WHERE make='Washburn'
-> AND price<400;
55
Chapter 9
Manipulating MySQL
Databases with PHP
PHP Overview
PHP has the ability to access and
manipulate any database that is ODBC
compliant
PHP includes functionality that allows
you to work directly with different types
of databases, without going through
ODBC
PHP supports SQLite, database
abstraction layer functions, and PEAR DB
57
Enabling MySQL Support in PHP
On UNIX/Linux systems:
Configure PHP to use the mysqli extension
by specifying the --with-mysqli parameter
when you run the configure command
during installation
On Windows:
Copy the files libmysql.dll and
php_mysqli.dll to the installation directory
Edit the php.ini configuration file and
enable the extension=php_mysqli.dll
directive
58
Opening and Closing a MySQL
Connection
Open a connection to a MySQL database
server with the mysqli_connect() function
The mysqli_connect() function returns a
positive integer if it connects to the
database successfully or false if it does not
Assign the return value from the
mysqli_connect() function to a variable
that you can use to access the database in
your script
59
Opening and Closing a MySQL
Connection (continued)
The syntax for the mysqli_connect()
function is:
$connection = mysqli_connect("host"[, "user ", "password", "database"])
-use isam.sis.pitt.edu for host and your acct username/psword
The host argument specifies the host name
where your MySQL database server is
installed
The user and password arguments specify a
MySQL account name and password
The database argument selects a database
with which to work
60
Opening and Closing a MySQL
Connection (continued)
Table 9-1 MySQL server information functions
61
Opening and Closing a MySQL
Connection (continued)
Figure 9-1 MySQLInfo.php in a Web
browser
62
Selecting a Database
Select a database with the use
database statement when you log on
to the MySQL Monitor
The syntax for the
mysqli_select_db() function is:
mysqli_select_db(connection, database)
The function returns a value of true if
it successfully selects a database or
false if it
does not
63
Handling MySQL Errors
Reasons for not connecting to a
database server include:
The database server is not running
Insufficient privileges to access the data
source
Invalid username and/or password
64
Handling MySQL Errors
(continued)
Make sure you are using a valid
username and password
Figure 9-2 Database connection error message
65
Suppressing Errors with the Error
Control Operator
Writing code that anticipates and
handles potential problems is often
called bulletproofing
Bulletproofing techniques include:
Validating submitted form data
Using the error control operator (@)
to suppress error messages
66
Terminating Script Execution
The die() and exit() functions
terminate script execution
The die() version is usually used when
attempting to access a data source
Both functions accept a single string
argument
Call the die() and exit() functions as
separate statements or by appending
either function to an expression with the
Or operator
67
Terminating Script Execution
(continued)
$DBConnect = @mysqli_connect("localhost", "root", "paris");
if (!$DBConnect)
die("<p>The database server is not available.</p>");
echo "<p>Successfully connected to the database server.</p>";
$DBSelect = @mysqli_select_db($DBConnect, "flightlog");
if (!$DBSelect)
die("<p>The database is not available.</p>");
echo "<p>Successfully opened the database.</p>";
// additional statements that access the database
mysqli_close($DBConnect);
68
Terminating Script Execution
(continued)
$DBConnect = @mysqli_connect("localhost", "dongosselin",
"rosebud")
Or die("<p>The database server is not available.</p>");
echo "<p>Successfully connected to the database server.</p>";
@mysqli_select_db($DBConnect, "flightlog")
Or die("<p>The database is not available.</p>");
echo "<p>Successfully opened the database.</p>";
// additional statements that access the database server
mysqli_close($DBConnect);
69
Reporting MySQL Errors
Table 9-2 MySQL error reporting functions
70
Reporting MySQL Errors
(continued)
$User = $_GET['username'];
$Password = $_GET['password'];
$DBConnect = @mysqli_connect("localhost", $User, $Password)
Or die("<p>Unable to connect to the database server.</p>"
. "<p>Error code " . mysqli_connect_errno()
. ": " . mysqli_connect_error()) . "</p>";
echo "<p>Successfully connected to the database server.</p>";
@mysqli_select_db($DBConnect, "flightlog")
Or die("<p>The database is not available.</p>");
echo "<p>Successfully opened the database.</p>";
// additional statements that access the database
mysqli_close($DBConnect);
71
Reporting MySQL Errors
(continued)
Figure 9-4 Error number and message generated by
an invalid username and password
72
Reporting MySQL Errors
(continued)
$User = $_GET['username'];
$Password = $_GET['password'];
$DBConnect = @mysqli_connect("localhost", $User, $Password)
Or die("<p>Unable to connect to the database server.</p>"
. "<p>Error code " . mysqli_connect_errno()
. ": " . mysqli_connect_error()) . "</p>";
echo "<p>Successfully connected to the database server.</p>";
@mysqli_select_db($DBConnect, "flightplan")
Or die("<p>Unable to select the database.</p>"
. "<p>Error code " . mysqli_errno($DBConnect)
. ": " . mysqli_error($DBConnect)) . "</p>";
echo "<p>Successfully opened the database.</p>";
// additional statements that access the database
mysqli_close($DBConnect);
73
Reporting MySQL Errors
(continued)
Figure 9-5 Error code and message generated when
attempting to select a database that does not exist
74
Executing SQL Statements
Use the mysqli_query() function to send
SQL statements to MySQL
The syntax for the mysqli_query()
function is:
mysqli_query(connection, query)
The mysqli_query() function returns
one of three values:
For SQL statements that do not return results
(CREATE DATABASE and CREATE TABLE
statements) it returns a value of true if the
statement executes successfully
75
Executing SQL Statements
(continued)
For SQL statements that return results
(SELECT and SHOW statements) the
mysqli_query() function returns a result
pointer that represents the query results
A result pointer is a special type of variable
that refers to the currently selected row in a
resultset
The mysqli_query() function returns a
value of false for any SQL statements that
fail, regardless of whether they return
results
76
Working with Query Results
Table 9-3 Common PHP functions for accessing
database results
77
Retrieving Records into an
Indexed Array
The mysqli_fetch_row() function
returns the fields in the current row of a
resultset into an indexed array and
moves the result pointer to the next row
echo "<table width='100% border='1'>";
echo "<tr><th>Make</th><th>Model</th>
<th>Price</th><th>Quantity</th></tr>";
$Row = mysqli_fetch_row($QueryResult);
do {
echo "<tr><td>{$Row[0]}</td>";
echo "<td>{$Row[1]}</td>";
echo "<td align='right'>{$Row[2]}</td>";
echo "<td align='right'>{$Row[3]}</td></tr>";
$Row = mysqli_fetch_row($QueryResult);
} while ($Row);
78
Retrieving Records into an
Indexed Array (continued)
Figure 9-6 Output of the inventory table in a Web browser
79
Retrieving Records into an
Associative Array
The mysqli_fetch_assoc() function
returns the fields in the current row of a
resultset into an associative array and
moves the result pointer to the next row
The difference between
mysqli_fetch_assoc() and
mysqli_fetch_row() is that instead of
returning the fields into an indexed array,
the mysqli_fetch_assoc() function
returns the fields into an associate array
and uses each field name as the array key
80
Accessing Query Result
Information
The mysqli_num_rows() function
returns the number of rows in a
query result
The mysqli_num_fields() function
returns the number of fields in a
query result
Both functions accept a database
connection variable as an argument
81
Accessing Query Result Information
(continued)
$SQLstring = "SELECT * FROM inventory";
$QueryResult = @mysqli_query($DBConnect, $SQLstring)
Or die("<p>Unable to execute the query.</p>"
. "<p>Error code . mysqli_errno($DBConnect)
. ": " . mysqli_error($DBConnect)) . "</p>";
echo "<p>Successfully executed the query.</p>";
$NumRows = mysqli_num_rows($QueryResult);
$NumFields = mysqli_num_fields($QueryResult);
if ($NumRows != 0 && $NumFields != 0)
echo "<p>Your query returned .
mysqli_num_rows($QueryResult) . rows and "
. mysqli_num_fields($QueryResult) . fields.</p>";
else
echo "<p>Your query returned no results.</p>";
mysqli_close($DBConnect);
82
Accessing Query Result Information
(continued)
Figure 9-8 Output of the number of rows and fields
returned from a query
83
Closing Query Results
When you are finished working with
query results retrieved with the
mysqli_query() function, use the
mysqli_free_result() function to
close the resultset
To close the resultset, pass to the
mysqli_free_result() function the
variable containing the result pointer
from the mysqli_query() function
84
Creating and Deleting Databases
Use the CREATE DATABASE statement
with the mysqli_query() function to
create a new database
$SQLstring = "CREATE DATABASE real_estate";
$QueryResult = @mysqli_query($DBConnect, $SQLstring)
Or die("<p>Unable to execute the query.</p>"
. "<p>Error code " . mysqli_errno($DBConnect)
. ": " . mysqli_error($DBConnect)) . "</p>";
echo "<p>Successfully executed the query.</p>";
mysqli_close($DBConnect);
85
Creating and Deleting Databases
(continued)
Figure 9-9 Error code and message that prints when you attempt
to create a database that already exists
86
Creating and Deleting Databases
(continued)
Use the mysqli_db_select() function to
check whether a database exists before you
create or delete it
To use a new database, you must select it by
executing the mysqli_select_db() function
Deleting a database is almost identical to
creating one, except use the DROP DATABASE
statement instead of the CREATE DATABASE
statement with the mysqli_query() function
87
Creating and Deleting Databases
(continued)
$DBName = "real_estate";
...
if (@!mysqli_select_db($DBConnect, $DBName))
echo "<p>The $DBName database does not exist!</p>";
else {
$SQLstring = "DROP DATABASE $DBName";
$QueryResult = @mysqli_query($DBConnect, $SQLstring)
Or die("<p>Unable to execute the query.</p>"
. "<p>Error code . mysqli_errno($DBConnect)
. ": . mysqli_error($DBConnect)) . "</p>";
echo "<p>Successfully deleted the database.</p>";
}
mysqli_close($DBConnect);
88
Creating and Deleting Tables
To create a table, use the CREATE TABLE
statement with the mysqli_query() function
Execute the mysqli_select_db() function
before executing the CREATE TABLE
statement or the new table might be created
in the wrong database
To prevent code from attempting to create a
table that already exists, use a
mysqli_query() function that attempts to
select records from the table
89
Creating and Deleting Tables
(continued)
$DBName = "real_estate";
...
$SQLstring = "CREATE TABLE commercial (city VARCHAR(25), state
VARCHAR(25), sale_or_lease VARCHAR(25), type_of_use VARCHAR(40),
Price INT, size INT)";
$QueryResult = @mysqli_query($DBConnect, $SQLstring)
Or die("<p>Unable to execute the query.</p>"
. "<p>Error code " . mysqli_errno($DBConnect)
. ": " . mysqli_error($DBConnect)) . "</p>";
echo "<p>Successfully created the table.</p>";
mysqli_close($DBConnect);
90
Creating and Deleting Tables
(continued)
Figure 9-11 Error code and message that prints when you
attempt to create a table that already exists
91
Adding, Deleting, and Updating
Records
To add records to a table, use the
INSERT and VALUES keywords with
the mysqli_query() function
The values entered in the VALUES list
must be in the same order in which
you defined the table fields
You must specify NULL in any fields
for which you do not have a value
92
Adding, Deleting, and Updating
Records (continued)
To add multiple records to a database,
use the LOAD DATA statement and the
mysqli_query() function with a local
text file containing the records you
want to add
To update records in a table, use the
UPDATE, SET, and WHERE keywords
with the mysqli_query() function
93
Adding, Deleting, and Updating
Records (continued)
The UPDATE keyword specifies the name of
the table to update
The SET keyword specifies the value to
assign to the fields in the records that
match the condition in the WHERE keyword
To delete records in a table, use the
DELETE and WHERE keywords with the
mysqli_query() function
The WHERE keyword determines which
records to delete in the table
94
Using the
mysqli_affected_rows() Function
With queries that return results (SELECT
queries), use the mysqli_num_rows()
function to find the number of records
returned from the query
With queries that modify tables but do
not return results (INSERT, UPDATE, and
DELETE queries), use the
mysqli_affected_rows() function to
determine the number of affected rows
95
Using the mysqli_affected_rows()
Function (continued)
$SQLstring = "UPDATE inventory SET price=368.20
WHERE make='Fender' AND model='DG7'";
$QueryResult = @mysqli_query($DBConnect, $SQLstring)
Or die("<p>Unable to execute the query.</p>"
. "<p>Error code " . mysqli_errno($DBConnect)
. ": " . mysqli_error($DBConnect)) . "</p>";
echo "<p>Successfully updated "
. mysqli_affected_rows($DBConnect) . " record(s).</p>";
96
Using the mysqli_affected_rows()
Function (continued)
Figure 9-16 Output of mysqli_affected_rows() function
for an UPDATE query
97
Using the mysqli_info()
Function
For queries that add or update records, or
alter
a tables structure, use the mysqli_info()
function to return information about the query
The mysqli_info() function returns the
number of operations for various types of
actions, depending on the type of query
The mysqli_info() function returns
information about the last query that was
executed on the database connection
98
Using the mysqli_info() Function
(continued)
The mysqli_info() function returns
information about queries that match
one of the following formats:
INSERT INTO...SELECT...
INSERT INTO...VALUES (...),(...),(...)
LOAD DATA INFILE ...
ALTER TABLE ...
UPDATE
For any queries that do not match one
of these formats, the mysqli_info()
function returns an empty string
99
Using the mysqli_info() Function
(continued)
$SQLstring = "INSERT INTO inventory
VALUES('Ovation', '1777 LX Legend', 1049.00, 2),
('Ovation', '1861 Standard Balladeer', 699.00, 1),
('Ovation', 'Tangent Series T357', 569.00, 3)";
$QueryResult = @mysqli_query($DBConnect, $SQLstring)
Or die("<p>Unable to execute the query.</p>"
. "<p>Error code . mysqli_errno($DBConnect)
. ": " . mysqli_error($DBConnect)) . "</p>";
echo "<p>Successfully added the records.</p>";
echo "<p>" . mysqli_info($DBConnect) . "</p>";
100
Using the mysqli_info() Function
(continued)
Figure 9-17 Output of mysqli_info() function for an
INSERT query that adds multiple records
101
Using the mysqli_info() Function
(continued)
The mysqli_info() function also
returns information for LOAD DATA
queries
$SQLstring = "LOAD DATA LOCAL INFILE 'c:/temp/inventory.txt'
INTO TABLE inventory;";
$QueryResult = @mysqli_query($DBConnect, $SQLstring)
Or die("<p>Unable to execute the query.</p>"
. "<p>Error code . mysqli_errno($DBConnect)
. ": " . mysqli_error($DBConnect)) . "</p>";
echo "<p>Successfully added the records.</p>";
echo "<p>" . mysqli_info($DBConnect) . "</p>";
102
Using the mysqli_info() Function
(continued)
Figure 9-18 Output of mysqli_info() function for a
LOAD DATA query
103
Name of Book
104
<?php
$connection1 =
mysql_connect("localhost","root","");
mysql_select_db("mydb",$connection1);
?>
<html>
<head>
</head>
<body>
<center>
Name of Book
105
