PHP-MySQL tutorial -basics: Difference between revisions
mNo edit summary |
mNo edit summary |
||
Line 2: | Line 2: | ||
== Introduction == | == Introduction == | ||
This is a beginners tutorial for [[PHP]] and [[ | This is a beginners tutorial for [[PHP]] and [[MySQL]]. Is is made from (older) slides and needs some '''extra work''' ... - [[User:Daniel K. Schneider|Daniel K. Schneider]] | ||
;Objectives | ;Objectives | ||
Line 22: | Line 22: | ||
=== PHP and MYSQL === | === PHP and MYSQL === | ||
PHP can interact with most [[RDBMS]]. | PHP can interact with most [[database|RDBMS]]. | ||
To interact with MySQL a PHP 5.2+ developer has three options. Let's shortly have a look them. | To interact with MySQL a PHP 5.2+ developer has three options. Let's shortly have a look them. | ||
Line 43: | Line 43: | ||
== The MySQL extension == | == The MySQL extension == | ||
'''A complete documented example:''' | '''A complete documented example:''' | ||
* [http://tecfa.unige.ch/guides/php/examples/mysql-demo/main.html application] | |||
(Sorry, the application itself is currently locked since we can't cope with spamming ...) | |||
'''To add records we need | '''To show all records:''' [http://tecfa.unige.ch/guides/php/examples/mysql-demo-source/dump_results.phps dump_results.phps] | ||
'''To add records we need two scripts:''' | |||
: <tt>new-entry.php</tt> produces the HTML form | : <tt>new-entry.php</tt> produces the HTML form | ||
: <tt>insert-entry.php</tt> inserts the values from the forms an gives some feedback. | : <tt>insert-entry.php</tt> inserts the values from the forms an gives some feedback. | ||
: Add a record ([http://tecfa.unige.ch/guides/php/examples/mysql-demo-source/new-entry.phps new-entry.phps] and [http://tecfa.unige.ch/guides/php/examples/mysql-demo-source/insert-entry.phps insert-entry.phps]) | |||
'''Same principle for editing:''' | '''Same principle for editing:''' | ||
Line 61: | Line 60: | ||
: <tt>edit-entry.php</tt> produces the form | : <tt>edit-entry.php</tt> produces the form | ||
: <tt>replace-entry.php</tt> replaces old values by new ones. | : <tt>replace-entry.php</tt> replaces old values by new ones. | ||
: Edit a record ([http://tecfa.unige.ch/guides/php/examples/mysql-demo-source/edit-entry.phps edit-entry.phps] and [http://tecfa.unige.ch/guides/php/examples/mysql-demo-source/replace-entry.phps replace-entry.phps]) | |||
: Kill a record ([http://tecfa.unige.ch/guides/php/examples/mysql-demo-source/delete-entry.phps delete-entry.phps]) | |||
=== An overview of PHP - MySQL functions === | === An overview of PHP - MySQL functions === | ||
Line 147: | Line 147: | ||
'''Generation of an HTML table with the results''': | '''Generation of an HTML table with the results''': | ||
: [http://tecfa.unige.ch/guides/php/examples/mysql-demo/dump_results_demo.php http://tecfa.unige.ch/guides/php/examples/mysql-demo/dump_results_demo.php] | : [http://tecfa.unige.ch/guides/php/examples/mysql-demo-source/dump_results_demo.php http://tecfa.unige.ch/guides/php/examples/mysql-demo-source/dump_results_demo.php] | ||
: [/guides/php/examples/mysql-demo/dump_results_demo.phps /guides/php/examples/mysql-demo/dump_results_demo.phps ] | : [/guides/php/examples/mysql-demo-source/dump_results_demo.phps /guides/php/examples/mysql-demo-source/dump_results_demo.phps ] | ||
: [http://tecfa.unige.ch/guides/php/examples/mysql-demo/dump_results_demo.source /guides/php/examples/mysql-demo/dump_results_demo.source ] | : [http://tecfa.unige.ch/guides/php/examples/mysql-demo-source/dump_results_demo.source /guides/php/examples/mysql-demo-source/dump_results_demo.source ] | ||
<source lang="php"> | <source lang="php"> | ||
Line 190: | Line 190: | ||
: se next slide ... | : se next slide ... | ||
: '''HTML generation using field names''' | : '''HTML generation using field names''' | ||
: [http://tecfa.unige.ch/guides/php/examples/mysql-demo/dump_results_demo2.php http://tecfa.unige.ch/guides/php/examples/mysql-demo/dump_results_demo2.php] ''' | : [http://tecfa.unige.ch/guides/php/examples/mysql-demo-source/dump_results_demo2.php http://tecfa.unige.ch/guides/php/examples/mysql-demo-source/dump_results_demo2.php] ''' | ||
: [http://tecfa.unige.ch/guides/php/examples/mysql-demo/dump_results_demo2.phps /guides/php/examples/mysql-demo/dump_results_demo2.phps ] | : [http://tecfa.unige.ch/guides/php/examples/mysql-demo-source/dump_results_demo2.phps /guides/php/examples/mysql-demo-source/dump_results_demo2.phps ] | ||
: [http://tecfa.unige.ch/guides/php/examples/mysql-demo/dump_results_demo2.source /guides/php/examples/mysql-demo/dump_results_demo2.source ] | : [http://tecfa.unige.ch/guides/php/examples/mysql-demo-source/dump_results_demo2.source /guides/php/examples/mysql-demo-source/dump_results_demo2.source ] | ||
<source lang="php"> | <source lang="php"> |
Revision as of 21:09, 6 February 2010
Introduction
This is a beginners tutorial for PHP and MySQL. Is is made from (older) slides and needs some extra work ... - Daniel K. Schneider
- Objectives
- Understand the "traditional" low-level PHP-MySQL interface
- Learn how to connect to a MySQL database and send SQL commands
- Understand how to extract information from the results
- Prerequisites
- Some PHP
- Some SQL
- Some HTML
- Some XML
- Next steps
- XQuery tutorial - basics (if you have interest in XML databases)
PHP and MYSQL
PHP can interact with most RDBMS.
To interact with MySQL a PHP 5.2+ developer has three options. Let's shortly have a look them.
(1) PHP's MySQL Extension is the "traditional API" and works with all MySQL version in use (as of Jan 2010). “This is the original extension designed to allow you to develop PHP applications that interact with a MySQL database. The mysql extension provides a procedural interface and is intended for use only with MySQL versions older than 4.1.3. This extension can be used with versions of MySQL 4.1.3 or newer, but not all of the latest MySQL server features will be available.”(Overview, PHP Manual, retrieved 18:58, 6 February 2010 (UTC)) However, as of early 2010, it seems to us, that most CMS still use this older interface, probably in order to support older PHP/MySQL installations...
(2) PHP's mysqli Extension adds extra functionalities. “The mysqli extension, or as it is sometimes known, the MySQL improved extension, was developed to take advantage of new features found in MySQL systems versions 4.1.3 and newer. The mysqli extension is included with PHP versions 5 and later. The mysqli extension has a number of benefits, the key enhancements over the mysql extension being: Object-oriented interface [(but also a procedural one)], Support for Prepared Statements, Support for Multiple Statements, Support for Transactions, Enhanced debugging capabilities, Embedded server support.”(Overview, PHP Manual, retrieved 18:58, 6 February 2010 (UTC))
(3) The PHP Data Objects (PDO) is an abstraction layer. “PHP Data Objects, or PDO, is a database abstraction layer specifically for PHP applications. PDO provides a consistent API for your PHP application regardless of the type of database server your application will connect to. In theory, if you are using the PDO API, you could switch the database server you used, from say Firebird to MySQL, and only need to make minor changes to your PHP code. [...] While PDO has its advantages, such as a clean, simple, portable API, its main disadvantage is that it doesn't allow you to use all of the advanced features that are available in the latest versions of MySQL server. For example, PDO does not allow you to use MySQL's support for Multiple Statements.” (Overview, PHP Manual, retrieved 18:58, 6 February 2010 (UTC)) Alternatively, many 2rd party abstraction layers exist.
The principle shortly :
- In PHP, SQL queries are built (ususally as a result of user request)
- These queries are sent by PHP to a MySQL server
- MySQL returns a result datastructure
- Thes results from MySQL are then arranged an inserted into an HTML page.
The MySQL extension
A complete documented example:
(Sorry, the application itself is currently locked since we can't cope with spamming ...)
To show all records: dump_results.phps
To add records we need two scripts:
- new-entry.php produces the HTML form
- insert-entry.php inserts the values from the forms an gives some feedback.
- Add a record (new-entry.phps and insert-entry.phps)
Same principle for editing:
- edit-entry.php produces the form
- replace-entry.php replaces old values by new ones.
- Edit a record (edit-entry.phps and replace-entry.phps)
- Kill a record (delete-entry.phps)
An overview of PHP - MySQL functions
Connect to a database
First, you have to connect to a server
You need:
- the host name of the database (usually "localhost")
- the database user name and password
Syntax for a persistent connect:
mysql_pconnect(host, username, password);
Example:
$link = mysql_pconnect("localhost","clavel","secret");
$link is a so-called link identifier and will contain the link to the open connection (or "FALSE" if something went wrong).
Then you need some code to select a database. A MySQL server hosts several databases. You need to tell which one you will use.
Syntax:
mysql_select_db(dbname, [linkID]);
Example:
mysql_select_db("demo");
Then you can send SQL statement using the mysq_query function. This call will return an identifier for the result.
Example:
$result = mysql_query("SELECT * FROM demo1");
$result is a so-called resource, i.e. a special variable, holding a reference to an external resource. It can be used like a "boolean" to test if a result was found.
- TRUE if ok
- 0 (FALSE) if there was a problem.
Processing of results
There exist several ways of dealing with the result. Here we show two
Deal with the result row by row:
mysql_fetch_row
Example:
mysql_fetch_row(resource)
Complete SQL/result example:
$result = mysql_query("SELECT * FROM demo1")
$row = mysql_fetch_row ($result);
Mysql_fetch_row returns a line of the result as an array (a list of values). If you call it again, it will return the next line
Number of fields returned:
mysql_num_fields
Example:
mysql_num_fields(resource) $nb_champs = mysql_num_fields($result);
Error handling
For most mysql operations, there can be errors and PHP does remember these. Use the PHP functions:
mysql_errno(resource)
- returns a number code for the last mySQL operation or zero (0) if not error was found.
mysql_error(resource)
- returns an string with some information.
Simple error handling example
<?php
mysql_pconnect("nohost", "basuser", "wrongpass");
echo mysql_errno().": ".mysql_error()."<BR>";
mysql_select_db("nonexistentdb");
echo mysql_errno().": ".mysql_error()."<BR>";
$conn = mysql_query("SELECT * FROM nonexistenttable");
echo mysql_errno().": ".mysql_error()."<BR>";
?>
Generation of an HTML table with the results:
- http://tecfa.unige.ch/guides/php/examples/mysql-demo-source/dump_results_demo.php
- [/guides/php/examples/mysql-demo-source/dump_results_demo.phps /guides/php/examples/mysql-demo-source/dump_results_demo.phps ]
- /guides/php/examples/mysql-demo-source/dump_results_demo.source
<?
'''mysql_pconnect'''("localhost","nobody","");
'''mysql_select_db'''("demo");
$result = '''mysql_query'''("SELECT * FROM demo1");
?>
<table border="1"> <tr>
<?
while ($row = '''mysql_fetch_row'''($result)) {
echo "<tr>";
for ($i=0; $i<'''mysql_num_fields'''($result); $i++) {
echo "<td>";
// test if this is the URL
if ($i == 4) { echo "<a href='$row[$i]'>$row[$i]</a>"; }
else { echo "$row[$i]"; }
echo "</td>";
} }
?>
</table>
Processing a result using field names with mysql_num_rows ():
mysql_num_rows(resource);
Example:
$nb_enregistrements = mysql_num_rows($result);
- Returns number of returned records in $result
The mysql_result() function allows to retrieve cells:
mysql_result(resource, index, champ);
Example:
$nom = mysql_result($result,0,’fullname’);
- index holds the record number. Indexation starts at 0 ! (zero).
- champ is the name of the field.
- se next slide ...
- HTML generation using field names
- http://tecfa.unige.ch/guides/php/examples/mysql-demo-source/dump_results_demo2.php
- /guides/php/examples/mysql-demo-source/dump_results_demo2.phps
- /guides/php/examples/mysql-demo-source/dump_results_demo2.source
<?php
mysql_pconnect( "localhost", "nobody", "") or die( "Unable to connect to SQL server");
mysql_select_db("demo") or die ( "Unable to select database");
$result = mysql_query( "select * from demo1");
?>
<table border="1">
<?php
$i = 0;
while ($i < '''mysql_num_rows'''($result)) {
echo "<tr>";
echo "<td>";
echo '''mysql_result'''($result,$i,’id’);
echo "</td>";
echo "<td>";
echo '''mysql_result'''($result,$i,’fullname’);
echo "</td>";
echo "<td>";
echo '''mysql_result'''($result,$i,’love’);
echo "</td>";
echo "<td>";
echo '''mysql_result'''($result,$i,’sports’);
echo "</td>";
echo "</tr>";
$i++;
}
echo "</table>";
?>
Workthough example: Guestbook.
Example files:
- Create a comments directory in your web server.
- Copy files: comments-list.source, comments-insert.source, comments.html
- http://tecfa.unige.ch/guides/tie/code/act-php-mysql-FC/
Rename .source to .php !!!
Structure of the comments table:
create table comments (
id int(10) default ’0’ not null auto_increment,
nom char(20) default ’’ not null,
prenom char(20) default ’’,
email char(50) default ’’ ,
computer char(10),
browser char(10),
version char(10),
comments char(200),
primary key (id),
key nom (nom)
);
Task
- comments.html displays the form and sends values to comments-insert.php.
- comments-insert.php writes data to mySQLServer : localhost database name : .....table : comments database user name: ....database user password : ....
- comments-list.php will display all comments in a table.
- the HTML is funtional, but you will have to fix the 2 php files (comments are in french sorry)
Start with comments-insert.php then file comments-list.php
Solution: