Transcript 5. MySQL intro - University of Huddersfield

Website Development
More on Databases
What you will achieve today!
•Connecting to mySql
•Creating tables in mySql
•Saving data on a server using mySql
•Getting data from the server using
mySql
Reminder of the general process
: Customer
browser
web server
scripting
language
database
request service
access page
interpret
set data
get data
get data
return html
present html
today’s
emphasis
Essential PHP functions
http://www.php.net/manual/en/ref.mysql.php
mysql_connect ([string hostname, string username [, string password]]])
Returns a positive MySQL link identifier on success, or an error message on failure.
mysql_connect() establishes a connection to a MySQL server.
mysql_query (string query [, int link_identifier])
mysql_query() sends a query to the currently active database on the server that's
associated with the specified link identifier. If link_identifier isn't specified, the last
opened link is assumed.
mysql_query() returns TRUE (non-zero) or FALSE to indicate whether or not the query
succeeded. A return value of TRUE means that the query was legal and could be
executed by the server. It does not indicate anything about the number of rows
affected or returned. It is perfectly possible for a query to succeed but affect no rows
or return no rows.
MyDBFunctions.php
array mysql_fetch_row (int result)
Returns: An array that corresponds to the fetched row, or false if there are no more
rows.
mysql_fetch_row() fetches one row of data from the result associated with the
specified result identifier. The row is returned as an array. Each result column is
stored in an array offset, starting at offset 0.
Subsequent call to mysql_fetch_row() would return the next row in the result set, or
false if there are no more rows.
mysql_num_rows (int result)
mysql_num_rows() returns the number of rows in a result set. This
command is only valid for SELECT statements.
mysql_num_fields (int result)
mysql_num_fields() returns the number of fields in a result set.
Now some SQL
CREATE TABLE <tablename> (
col_name col_type,
col_name col_type,
...
)
http://www.mysql.com/doc/C/R/CREATE_TABLE.html
CREATE TABLE DATEPERSON (
NAME TEXT,
AGE INT,
HOBBIES TEXT,
EMAIL TEXT,
LIKES TEXT,
HATES TEXT,
POSTCODE TEXT,
LOWERAGE INT,
UPPERAGE INT
)
test
DROP TABLE <tablename>
DROP TABLE DATEPERSON
http://www.mysql.com/doc/D/R/DROP_TABLE.html
test
INSERT INTO <tablename> SET
Col_name = value,
Col_name = value,
...
http://www.mysql.com/doc/I/N/INSERT.html
INSERT INTO DATEPERSON SET
NAME = “ROMEO",
AGE = 14,
HOBBIES = "maudling",
EMAIL = "[email protected]",
LIKES = "Mercutio",
HATES = "Tibbalt",
POSTCODE = "VE21 2BB",
LOWERAGE = 14,
UPPERAGE = 16
test
SELECT <column names>
FROM <tablename>
WHERE <col_name comparison value>,
<col_name comparison value>,
...
http://www.mysql.com/doc/S/E/SELECT.html
SELECT * FROM DATEPERSON
test
What else?
•
•
•
•
Forms for input – JavaScript validation?
Forms for editing existing data
Better error trapping
An attractive interface – style sheets etc.
So what have we got now?
Full database access
Internet presentation
In principle, most business applications can be served in
this way