Connecting to MySQL from PHP.doc - bhabuu's home page

mexicanmorningData Management

Dec 16, 2012 (4 years and 6 months ago)

280 views

Introduction to PHP

Server
-
Script Lifecycle



Recall that server
-
side scripting languages are invoked in much the same way as CGI
programs, with a couple of key distinctions.

1.

The browser sends an HTTP GET or POST command to the webserver, specifying
the pat
h to the requested document (which in this case is a server
-
side script,
although the browser doesn't know or care about this distinction).

2.

The webserver notes that the requested document is a server
-
side script (usually
by examining the file extension: .
php, .asp, .jsp, etc.) rather than a static HTML
document.

3.

The webserver loads the requested script file into the server's memory and passes
it to the server
-
side script language interpreter (usually running as a module
within the webserver's process spac
e).

4.

The interpreter scans through the script file, looking for special "escape" tags (e.g.
"<?" in PHP). As soon as it encounters such a tag, it begins interpreting the code
that follows until it reaches the end of the "escaped" section. Any output
produc
ed by the statements in that code section are inserted into the HTML
document in place of the escaped section. The interpreter doesn't change any of
the HTML content outside of the escape tags.

5.

The interpreter returns the generated HTML document to the we
bserver.

6.

The webserver returns the generated HTML document to the browser, passing
along the HTTP header information as well.

PHP Development Process



Developing web applications that are deployed to a remote server requires that you
perform a little 3
-
st
ep dance whenever you want to view the results of your changes to a
PHP file:

o

Save the file to a local drive.

o

Copy the file from your local drive to the webserver using a tool like iXplorer
(SFTP).

o

Refresh your browser to view the changes.



If you're pr
oficient with one of the Linux text editors (vi, emacs, joe), you can skip the
second step as your changes would be made directly to the file on the webserver.

The PHP Language



Rather than rehash all of the existing PHP documentation, I'll refer you to a
few PHP
resources, and just point out the tricky bits. For a fuller description of the language, check
out:

o

The PHP Manual

<
www.ph
p.net/manual/en/print
>

o

A Simple PHP Tutorial

<
www.php.net/manual/en/print/tutorial.php
>

o

PHP From the Ground Up

<
hotwired.lycos.com/webmonkey/01/48/index2a.html?tw
=progra
mming
>

o

PHP/MySQL Tutorial

<
hotwired.ly
cos.com/webmonkey/programming/php/tutorials/tut
orial4.html
>

o

Another PHP Tutorial

<
www
.gimpster.com/wiki/PhpTutorial/PrinterFriendly
>

o

PHP 101

<
www.devshed.com/Server_Side/PHP/PHP101
>

o

Yet Another PHP Tutorial

<
www.freewebmasterhelp.com/tutorials/php
>

o

PHPBuilder

<
www.phpbuilder.com
>



Most of PHP's syntax is borrowed from C. Don't forget those semicolons!



Variable and function names (identifiers) are case
-
sensitive in PHP. (As are filenames f
or
Apache.)



Variables always start with $. To print a literal $ in a double
-
quoted string, use
\
$.



PHP line comments start with //, multi
-
line comments begin with /* and end with */.



PHP is essentially typeless, although variables may store integer, flo
ating
-
point, string,
array, or even object values. If necessary, you can even typecast variables like in C.



Like SQL, variables may store the value NULL (itself a PHP keyword).



There are two ways to define strings: with single quotes or double quotes. Si
ngle
-
quoted
strings will not evaluate any embedded variable names and will simply print them out
literally. Double
-
quoted strings will evaluated embedded variables, as well as C
-
style
escape characters (e.g.
\
n,
\
r,
\
t). To include literal single quote in
a single
-
quoted string
(an apostrophe, for example), escape the quote:
\
'.



Arrays in PHP are very flexible, and are properly thought of as "associative arrays": each
element consists of a key and a value. Associative arrays are indexed by key (meaning
$so
mearray['keyname'] will produce the corresponding value). Associative arrays can
mimic C
-
style arrays by simply using a 0
-
based integer sequence for keys.



PHP arrays may store any of PHP's "types" as values, and a single array may contain
values of many d
ifferent types. Array keys must be either integers or strings.



PHP may store array elements in any order, so you may want to use one of the many
sorting functions to arrange the elements in a specific order.



The foreach statement is the easiest way to it
erate through an array. It has two different
syntactical forms: one that produces just values, and one that produces both keys and
values.



Multi
-
dimensional arrays in PHP are actually just one
-
dimensional arrays with element
values that are themselves arr
ays. Therefore, multi
-
dimensional PHP arrays can be
"jagged" (i.e. not all elements are of the same dimension).



User
-
defined functions are introduced with the function keyword. There is no return type.



Global variables are not visible within functions un
less the global statement is used to
indicate which global variables are visible within the function body. This is very different
behaviour from most programming languages!



Functions use the return statement to pass back values, just as in C.



PHP files m
ay be "included" into other PHP files using one of the include
-
style
statements: include, include_once, require, require_once.



Unlike some scripting languages, the file to be included may be determined at run
-
time.



Included files are evaluated at the sco
pe
-
level of the include
-
style statement.




Introduction to MySQL

Converting DDL Scripts from Oracle to MySQL



Remove any
ALTER TABLE DROP CONSTRAINT

statements for FOREIGN KEY
constraints. MySQL doesn't understand them, and is quite happy dropping tables
even if
referential integrity will be violated.



Change the
DROP TABLE

statements to add the
IF EXISTS

keywords immediately
before the name of the table. Doing so will prevent MySQL from complaining that the
table doesn't exist.



Add the line
TYPE = INNODB

immediately following the closing parenthesis of the
CREATE TABLE

statement and before the semi
-
colon. Doing so will enable both
referential integrity and transaction support for queries involving that table.



There are no sequences in MySQL, so delete an
y
CREATE

or
DROP

statements to do with
sequences. Once that's done, add the
AUTO_INCREMENT

keyword to the end of any
column definition that previously took its values from a sequence. There can be only one
AUTO_INCREMENT

column per table and it should be t
he primary key. See below for how
to populate these surrogate key columns.



Convert to MySQL's datatypes where necessary:
NUMBER(
x
)

should be replaced with a
suitably
-
sized
INT
-
style datatype;
NUMBER(
x
,
y
)

should be replaced with
FLOAT

or
DOUBLE
;
LONG

shou
ld be replaced with a suitably
-
size
TEXT
-
style datatype.
VARCHAR()
,
CHAR()
, and
DATE

datatypes can be left as before, although MySQL also has
TIME

and
DATETIME

datatypes to store just times, or combinations of dates and times together,
respectively.



FOREI
GN KEY columns must be manually indexed. To do this, just add the line(s)
INDEX
(
fk_column
)

to the table definition once for every foreign key column.



To populate tables that previously used sequences, just search for and replace every
instance of
sequenc
e_name
.NEXTVAL

with
NULL
. This will instruct MySQL to simply
use the next value of the
AUTO_INCREMENT

column when populating the table.



Converting dates can be a bit tricky as you have to get from Oracle's dd
-
mmm
-
yy format
to MySQL's yyyy
-
mm
-
dd format. A
bit of manual drudgery and the use of a good regular
expression tool can make this much easier. For large population scripts, you may want to
consider a short program to perform the conversion.

MySQL Metadata Commands

Unlike Oracle, and other RDBMSes for
that matter, MySQL uses the
show

command to display
metadata. Refer to section 4.5.6 of the MySQL Reference Manual for a complete description of all
the the
show

possibilities, but the ones listed in the table below are the most common.

Command

Descriptio
n

show

databases;

Lists all of the databases to which you have some sort of
access (i.e. the
USAGE

permission).

show

tables;

Lists all of the tables in the current database to which you
have some sort of access.

show

columns

from

table
;

Lists all of the

columns in the specified
table
, their type,
nullability, and whether they are part of a constraint or
index. The
Key

column indicates index partitipation:
PRI

=
PRIMARY KEY constraint,
UNI

= UNIQUE constraint, and
MUL

= non
-
unique index.

show

index

from

table
;

Lists all of the indexes (including those automatically
generated by PRIMARY KEY and UNIQUE constraints)
and their properties for the specified
table
.

show

table

status;

Lists all of the tables in the current database along with a
whole pile of t
echnical info about the tables: type, size, row
count, next value of any auto_increment fields, FOREIGN
KEY constraints, etc.

show

grants

for

account
-
name
@localhost;

Displays the SQL statements previously used to grant
permissions to the
account
-
name

use
r.

show

create

table

table

Displays the corresponding
CREATE TABLE

statement for
the specified
table
.

phpMyAdmin

The phpMyAdmin utility is terribly convenient but will not be covered in the course. It can be
found at:
https://199.247.245.45/mysqladmin
. You must accept the SSL certificate to begin using
phpMyAdmin. Log in using your project server user account name and your MySQL password.
You may also want to visit the
official phpMyAdmin site

<
www.phpmyadmin.net
>.


Connecting to MySQL from PHP

Executing Database Queries in Four Easy Steps



Every procedural language needs a software "brid
ge" to be able to execute database
queries.



There are a number of such bridges, each optimized for particular language and database
combinations: ODBC, JDBC, ADO, OLEDB, VBSQL, and any number of native libraries
written in, and for, C.



Regardless of the
make or model of such a bridge (often called drivers or database
connectivity layers), the pattern of executing an SQL query is virtually identical:

1.

Establish a connection to the database, supplying (optionally) the host address,
database name, user accou
nt name, and password.

2.

Through this connection, execute a SQL DML statement (DDL is also possible,
but is the exception for most applications).

3.

Fetch the results of the query, depending upon the type of DML statement:

a.

For a
SELECT

statement, iterate thr
ough each row of the returned result
set.

b.

For an
INSERT
,
UPDATE
, or
DELETE

statement, check the number of
rows affected.

4.

Close the database connection.



Depending on the type of bridge and language involved, the syntax could vary widely, but
in every cas
e a SQL statement is passed to the database as a text string.

Executing MySQL Database Queries in Four Easy
--
but
Different
--
Steps



The process is nearly identical when accessing a MySQL database from PHP (the "bridge"
in this case is built in to PHP) but t
here isn't a one
-
to
-
one correspondance between steps
and function calls:

1.

Establish a connection to the database by calling the following two functions in
order:

a.

mysql_connect(
host
-
name
,
user
-
account
-
name
,
password
);


b.

mysql_select_db(
database
-
name
);


2.

Thro
ugh this connection (PHP keeps a handle to the connection internally),
execute a SQL DML statement using
mysql_query('
SQL
-
statement
');

(For
SELECT

statements, the return value of
mysql_query()

should be assigned to a
variable, often called
$result
).

3.

Fetch

the results of the query, depending upon the type of DML statement:

a.

For a
SELECT

statement, iterate through each row of the returned result
set (
$result
) by calling the
mysql_fetch_query($result)

function and assigning the return value (an associative ar
ray of column
values in that row) to a variable (often called
$row
). The values in
$row

may then be referenced by the column name of the originating database
table (or column alias).

b.

For an
INSERT
,
UPDATE
, or
DELETE

statement, the
mysql_affected_rows()

fu
nction returns the number of rows
affected.

4.

Close the database connection by calling
mysql_close()
. This final step is
actually optional in PHP, as it will automagically close any open connections once
the page has been completely interpreted.



For exampl
e, the following PHP code snippet will establish a connection to a database
called "testdb" on the local machine using a user account name of "testuser" with a
password of "testpassword", and then retrieve all of the rows in the table "testtable" using
a
S
ELECT

statement, printing out the value of the "testcolumn" column on separate lines:



mysql_connect('localhost', 'testuser', 'testpassword');






mysql_select_db('testdb');






$result = mysql_query('SELECT * FROM testtable');






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




echo $row['testcolumn'] . '<br>';



}



For another example, the following snippet establishes the same connection but this time
updates all rows in the "testtable" table for which the value of the "testcolumn" column is
equal to "testvalue". Th
e number of rows affected is then displayed:



mysql_connect('localhost', 'testuser', 'testpassword');






mysql_select_db('testdb');






$result = mysql_query('DELETE FROM testtable WHERE testcolumn =
\
'testvalue
\
'');






echo mysql_affected_rows() . '<br>';



Many o
f PHP's
mysql_

functions either return a structured data type (e.g. resource,
array) or a Boolean FALSE value if an error has occurred. Therefore, you'll often see PHP
code including an
or die()

expression for each statement so that processing is aborted
i
f any of the database functions fail to complete. The earlier
SELECT

statement example
would then be written something like the following:



mysql_connect('localhost', 'testuser', 'testpassword')




or die('Invalid user name or password');






mysql_select_db
('testdb')




or die('Database does not exist');






$result = mysql_query('SELECT * FROM testtable')




or die('Syntax error in SQL query');






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




echo $row['testcolumn'] . '<br>';



}

Because, like C, PHP processes

Boolean expressions using "short
-
circuit evaluation", the
die()

statements will not execute unless the
mysql_

function returns FALSE.



After inserting a row into a table with an
AUTO_INCREMENT

column, the newly generated
surrogate key value can be retriev
ed using
mysql_insert_id()

function.



The number of rows returned in the result set of a
SELECT

statement can be retrieved
using the
mysql_num_rows()

function.



A simple tutorial

Table of Contents

What do I need?


Your first PHP
-
enabled page


Something Useful


Dealing with Forms


Using old code with new versions of PHP


What's next?

Here we would l
ike to show the very basics of PHP in a short, simple tutorial. This
text only deals with dynamic web page creation with PHP, though PHP is not only
capable of creating web pages. See the section titled
What can PHP do

for more
information.

PHP
-
enabled web pages are treated just like regular HTML pages and you can create
and edit them the same way you normally create regular HTML pages.

What do I need?

In this tutorial
we assume that your server has activated support for PHP and that all
files ending in
.php

are handled by PHP. On most servers, this is the default
extension for PHP files, but ask your server administrator to be sure. If your server
supports PHP, then you

do not need to do anything. Just create your
.php

files, put
them in your web directory and the server will automatically parse them for you.
There is no need to compile anything nor do you need to install any extra tools.
Think of these PHP
-
enabled files

as simple HTML files with a whole new family of
magical tags that let you do all sorts of things. Most web hosts offer PHP support,
but if your host does not, consider reading the
» PHP Links

section
for resources on
finding PHP enabled web hosts.

Let us say you want to save precious bandwidth and develop locally. In this case,
you will want to install a web server, such as
» Apache
, and of course
» PHP
. You will
most likely want to install a database as well, such as
» MySQL
.

You can either install these individually or choose a simpler way. O
ur manual has
installation instructions for PHP

(assuming you already have some web server set
up). In case you have problems with installing PHP yourself
, we would suggest you
ask your questions on our
» installation mailing list
. If you choose to go on the
simpler route, then
» locate a pre
-
configured package

for your operating system,
which automatically installs all of these with just a few mouse clicks. It is easy to
setup a web server with PHP support on any operating system, including MacOSX,
Li
nux and Windows. On Linux, you may find
» rpmfind

and
» PBone

helpful for
locating RPMs. You may also want to visit
» apt
-
get

to find packages for Debian.




Your first PHP
-
enabled page

What can PHP do?



Last updated: Sun, 23 Sep 2007





add a note

User Contributed Notes

A simple tutorial


Rob (Freefallfire at gmail dot com)

07
-
Apr
-
2007 05:54


On the note of fast and easy developer setup's, Xa
mpp is of particular
note. If you're interested, just Google 'Xampp' ... It is a product
from the Apache Friends group and includes the Apache web server, PHP
and Perl along with some other interesting modules, Filezilla ftp
server and MySQL. All within on
e installer, making it a very usefull
and broad developers or learners platform.


Michael

01
-
Mar
-
2007 01:54


Agreed. Some of the pre
-
configured packs and associated tutorials
really

can simplify the installation and get you up and running in
minutes but you MUST make sure that as an absolute minimum you add a
secure password
-

ideally a mix of letters, numbers, not a real word
etc. Otherwise, when your site/system is live, it will be

potentially
open to abuse.


cvncpu

25
-
Oct
-
2006 11:53


just keep in mind that these installation packages are for development
use, and are not built for a production enviroment, the

preformance and
security of these tools is not setup for use in a public website.


add a note



Your first P
HP
-
enabled page

What can PHP do?




What can PHP do?

Anything. PHP is mainly focused on server
-
side scripting, so you can do anything any
other CGI program can do, such as collect form data, generate dynamic page
content, or send and receive cookies. But PHP

can do much more.

There are three main areas where PHP scripts are used.



Server
-
side scripting. This is the most traditional and main target field for
PHP. You need three things to make this work. The PHP parser (CGI or server
module), a web server and
a web browser. You need to run the web server,
with a connected PHP installation. You can access the PHP program output
with a web browser, viewing the PHP page through the server. All these can
run on your home machine if you are just experimenting with P
HP
programming. See the
installation instructions

section for more information.



Command line scripting. You can make a PHP script to run it without any
s
erver or browser. You only need the PHP parser to use it this way. This type
of usage is ideal for scripts regularly executed using cron (on *nix or Linux) or
Task Scheduler (on Windows). These scripts can also be used for simple text
processing tasks. See

the section about
Command line usage of PHP

for more
information.



Writing desktop applications. PHP is probably not the very best langu
age to
create a desktop application with a graphical user interface, but if you know
PHP very well, and would like to use some advanced PHP features in your
client
-
side applications you can also use PHP
-
GTK to write such programs.
You also have the ability

to write cross
-
platform applications this way. PHP
-
GTK is an extension to PHP, not available in the main distribution. If you are
interested in PHP
-
GTK, visit
» its own website
.

PHP can be used on all major o
perating systems, including Linux, many Unix variants
(including HP
-
UX, Solaris and OpenBSD), Microsoft Windows, Mac OS X, RISC OS,
and probably others. PHP has also support for most of the web servers today. This
includes Apache, Microsoft Internet Inform
ation Server, Personal Web Server,
Netscape and iPlanet servers, Oreilly Website Pro server, Caudium, Xitami,
OmniHTTPd, and many others. For the majority of the servers PHP has a module, for
the others supporting the CGI standard, PHP can work as a CGI pr
ocessor.

So with PHP, you have the freedom of choosing an operating system and a web
server. Furthermore, you also have the choice of using procedural programming or
object oriented programming, or a mixture of them. Although not every standard
OOP featur
e is implemented in PHP 4, many code libraries and large applications
(including the PEAR library) are written only using OOP code. PHP 5 fixes the OOP
related weaknesses of PHP 4, and introduces a complete object model.

With PHP you are not limited to ou
tput HTML. PHP's abilities includes outputting
images, PDF files and even Flash movies (using libswf and Ming) generated on the
fly. You can also output easily any text, such as XHTML and any other XML file. PHP
can autogenerate these files, and save them
in the file system, instead of printing it
out, forming a server
-
side cache for your dynamic content.

One of the strongest and most significant features in PHP is its support for a wide
range of databases. Writing a database
-
enabled web page is incredibly

simple. The
following databases are currently supported:

Adabas D

InterBase

PostgreSQL

dBase

FrontBase

SQLite

Empress

mSQL

Solid

FilePro (read
-
only)

Direct MS
-
SQL

Sybase

Hyperwave

MySQL

Velocis

IBM DB2

ODBC

Unix dbm

Informix

Oracle (OCI7 and OCI8)



Ingres

Ovrimos



We also have a database abstraction extension (named PDO) allowing you to
transparently use any database supported by that extension. Additionally PHP
supports ODBC, the Open Database Connection standard, so you can connect to any
othe
r database supporting this world standard.

PHP also has support for talking to other services using protocols such as LDAP,
IMAP, SNMP, NNTP, POP3, HTTP, COM (on Windows) and countless others. You can
also open raw network sockets and interact using any o
ther protocol. PHP has
support for the WDDX complex data exchange between virtually all Web
programming languages. Talking about interconnection, PHP has support for
instantiation of Java objects and using them transparently as PHP objects. You can
also us
e our CORBA extension to access remote objects.

PHP has extremely useful text processing features, from the POSIX Extended or Perl
regular expressions to parsing XML documents. For parsing and accessing XML
documents, PHP 4 supports the SAX and DOM standa
rds, and you can also use the
XSLT extension to transform XML documents. PHP 5 standardizes all the XML
extensions on the solid base of libxml2 and extends the feature set adding
SimpleXML and XMLReader support.

At last but not least, we have many other i
nteresting extensions, the mnoGoSearch
search engine functions, the IRC Gateway functions, many compression utilities
(gzip, bz2, zip), calendar conversion, translation...

As you can see this page is not enough to list all the features and benefits PHP ca
n
offer. Read on in the sections about
installing PHP
, and see the
function reference

part for explanation of the extensions mentioned here.


add a note

User Contributed Notes

What can PHP do?


geeky dot guy at
gmail dot com

21
-
Aug
-
2007 08:30


In short,what PHP can do is convert a static website that has content
that has to be changed by hand into a dynamic one that can display
cont
ent based on any criteria you can think of.