Web Programming 5_ PHP and MySQL PHP and MySQL PHP and the Data

Document Sample
Web Programming 5_ PHP and MySQL PHP and MySQL PHP and the Data Powered By Docstoc
					                                                                                PHP and MySQL
        Berner Fachhochschule-Technik und Informatik
                                                                   Introduction
                                                                   Basics of MySQL
                                                                     Create a Table
                Web Programming                                      See the content of a DB
               5) PHP and MySQL                                      Tables: Change rows and Insert data
                                                                     Select Information

                                                                   PhpMyAdmin
                              Dr. E. Benoist
                                                                   PHP and MySQL together
                      Fall Semester 2011/2012                        PDO
                                                                     mysql

                                                                   Conclusion

      Web Programming 5) PHP and MySQL                                 Web Programming 5) PHP and MySQL
                                                            1                                                                     2




     PHP and the Data Bases                                        MySQL a Data Base for the
MySQL syntax
   Create a new Data Base
                                                                            Web
   Set the rights for a DB                                      Open-Source DB
   Create tables
                                                                    Free
   Fill information into tables
   Select information (can sometime be very tricky)                 Present in any Linux distribution
   Update information                                               Available for fast any OS (Windows, Free-BSD, Mac-OS X,...)
PHP MyAdmin                                                     The perfect solution for web
   A PHP program for managing MySQL BD’s                            LAMP architecture (Linux, Apache, MySQL, PHP) is one of
   Graphical and practical                                          the web standards
   Do not require to log on the machine (only web access)           An application (phpMyAdmin) for managing the DB without
PHP Library for MySQL                                               shell access
   The old function-oriented library                                Perfect integration within PHP.
   The new object-oriented library
      Web Programming 5) PHP and MySQL                                 Web Programming 5) PHP and MySQL
      Introduction                                          3          Introduction                                               4
   Basics of MySQL commands                                                               Creation of a table
                                                                          Syntax
Creation functions (often done within PHP-MyAdmin)                            CREATE TABLE table name (definition of the fields)
    Create a new table                                                    Create a small table
    Set the properties of fields (auto-increment, default value,...)
                                                                          CREATE TABLE ‘category‘ (
Routine functions (will be used in your programs)                          ‘name‘ VARCHAR( 100 ) NOT NULL ,
    Insert an element in a table                                           ‘categoryID‘ TINYINT NOT NULL AUTO_INCREMENT ,
                                                                           PRIMARY KEY ( ‘categoryID‘ )
    Select elements out of a table
                                                                          );
    Select elements out of many tables
    Change the content of a record                                            Create a table with two fields
    Delete some records                                                       a string which length can not exceed 100
                                                                              A primary key that is a counter

          Web Programming 5) PHP and MySQL                                         Web Programming 5) PHP and MySQL
          Basics of MySQL                                             5            Basics of MySQL: Create a Table               6




                Create a new table                                            Create a new table (Cont.)
The table can have fields of the following types:
    TINYINT SMALLINT MEDIUMINT INT BIGINT that are integers
    (more or less long)
    VARCHAR for short strings (smaller than 256 chars)                    Other attributes or features
    TEXT for texts with a fixed length (max 64 kB)                             NULL or NOT NULL
    DATE date in format YYYY-MM-DD                                            AUTO INCREMENT for counters
    TIMESTAMP contains a unix timestamp                                   The table has also properties
    TIME format hh:mm:ss                                                      PRIMARY KEY
    DECIMAL number with a point.                                              COMMENT description of the table
    FLOAT
    DOUBLE real numbers
    BLOB Any Binary data (image, sound, long text, . . . )
    ...
          Web Programming 5) PHP and MySQL                                         Web Programming 5) PHP and MySQL
          Basics of MySQL: Create a Table                             7            Basics of MySQL: Create a Table               8
              Create other tables                                    See the content of a data base
The article and vat tables
CREATE TABLE ‘article‘ (                                            See all tables
 ‘articleID‘ INT NOT NULL AUTO_INCREMENT ,
 ‘name‘ VARCHAR( 100 ) NOT NULL ,                                   mysql> show tables;
 ‘vatID‘ TINYINT NOT NULL ,                                         +-------------------+
 ‘categoryID‘ INT NOT NULL ,                                        | Tables_in_example |
 ‘Price‘ DECIMAL NOT NULL ,                                         +-------------------+
 PRIMARY KEY ( ‘articleID‘ )                                        | article           |
);                                                                  | category          |
                                                                    | vat               |
CREATE TABLE ‘vat‘ (                                                +-------------------+
 ‘vatID‘ TINYINT NOT NULL AUTO_INCREMENT ,                          3 rows in set (0.00 sec)
 ‘rate‘ DECIMAL NOT NULL ,
 PRIMARY KEY ( ‘vatID‘ )
) COMMENT = ’The table containing VAT rates’;
       Web Programming 5) PHP and MySQL                                     Web Programming 5) PHP and MySQL
       Basics of MySQL: Create a Table                        9             Basics of MySQL: See the content of a DB               10




 See the content of a data base                                             Change a Table - ALTER
            (Cont.)                                                 Remove columns

                                                                    ALTER TABLE t2 DROP COLUMN c, DROP COLUMN d;
See all columns of a table
                                                                    Add a new column
mysql> show columns from vat;
+-------+---------------+------+-----+---------+----------------+
| Field | Type          | Null | Key | Default | Extra          |   ALTER TABLE ‘article‘ ADD ‘description‘ BLOB
+-------+---------------+------+-----+---------+----------------+     NOT NULL ;
| vatID | tinyint(4)    |      | PRI | NULL    | auto_increment |
| rate | decimal(10,2) |       |     | 0.00    |                |   Change an existing column
+-------+---------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)                                            ALTER TABLE ‘article‘ CHANGE ‘Price‘ ‘price‘
                                                                        DECIMAL( 10, 2 ) DEFAULT ’0’ NOT NULL;



       Web Programming 5) PHP and MySQL                                     Web Programming 5) PHP and MySQL
       Basics of MySQL: See the content of a DB              11             Basics of MySQL: Tables: Change rows and Insert data   12
  Fill data into a table - INSERT                                      Change the content of one or
Syntax
    INSERT INTO tablename [(list of fields)] VALUES (list of
                                                                               many rows
    values);
    all not null fields must be set, other can be just two
    commas.
Insert a row in a table                                              UPDATE a table
INSERT INTO ‘article‘ ( ‘articleID‘ , ‘name‘ , ‘vatID‘ ,
                                                                     UPDATE ‘article‘ SET ‘description‘ =
   ‘categoryID‘ , ‘price‘ , ‘description‘ )
                                                                       ’A very nice black pencil with white stripes’
 VALUES (’’, ’Pencil’, ’0’, ’0’, ’1.50’, ’’);
                                                                       WHERE ‘articleID‘ = ’1’ LIMIT 1 ;
Other possibility
INSERT INTO article values
  (’’,’Mercedes Class E’,’0’,’0’,’100000’,
   ’The same Mercedes Lady Diana has used’
  );
        Web Programming 5) PHP and MySQL                                    Web Programming 5) PHP and MySQL
        Basics of MySQL: Tables: Change rows and Insert data    13          Basics of MySQL: Tables: Change rows and Insert data   14




                 Select information                                        Select information(Cont.)
Syntax
    SELECT Field list FROM list of tables [WHERE conditions]
    [LIMIT limits]                                                   Select only some columns
    Field list can also be a joker (*)
    Conditions can be combined with boolean connectors (AND,         mysql> select name, price from article;
    OR, NOT)                                                         +------------------+-----------+
    If we only want to see a part of a list, we can limit it.        | name             | price      |
Select all the rows and columns of a table                           +------------------+-----------+
                                                                     | Pencil           |       1.70 |
mysql> select * from vat;
                                                                     | Mercedes Class E | 100000.00 |
+-------+------+
                                                                     +------------------+-----------+
| vatID | rate |
                                                                     2 rows in set (0.00 sec)
+-------+------+
|     1 | 7.00 |
|     2 | 7.65 |
+-------+------+
        Web Programming 5) PHP and MySQL                                    Web Programming 5) PHP and MySQL
        Basics of MySQL: Select Information                     15          Basics of MySQL: Select Information                    16
                          Select data                             Merge data from different
                                                                           tables
Select only some rows                                       Merge two tables
                                                                 Fields must know from which table they come (the same field
mysql> select name, price from article                           can be in the two tables).
    -> where articleID=1;
                                                                 We can rename a requested field with the AS keyword.
+--------+-------+
| name   | price |                                          mysql> select article.name, vat.rate, article.price
+--------+-------+                                              -> from article, vat where article.vatID= vat.vatID;
| Pencil | 1.70 |                                           +------------------+------+-----------+
+--------+-------+                                          | name             | rate | price      |
1 row in set (0.01 sec)                                     +------------------+------+-----------+
                                                            | Pencil           | 7.00 |       1.70 |
                                                            | Mercedes Class E | 7.00 | 100000.00 |
                                                            +------------------+------+-----------+

       Web Programming 5) PHP and MySQL                              Web Programming 5) PHP and MySQL
       Basics of MySQL: Select Information             17            Basics of MySQL: Select Information                      18




                  Merge ...(Cont.)                                                                 Join
Merge and compute                                           INNER JOIN If there is no match, the row is not shown

mysql> select article.name, vat.rate, article.price,         select article.name, vat.rate, article.price
    -> article.price*(1+vat.rate/100) as priceWithVAT               from article inner join vat
    -> from article, vat where article.vatID= vat.vatID;            on article.vatID= vat.vatID;
+------------------+------+-----------+--------------+      LEFT JOIN If there is no match, the second table is replaced by
| name             | rate | price     | priceWithVAT |      an empty record.
+------------------+------+-----------+--------------+
                                                            select article.name from article left join vat
| Pencil           | 7.00 |      1.70 |       1.8190 |
                                                                    on article.vatID= vat.vatID
| Mercedes Class E | 7.00 | 100000.00 | 107000.0000 |
                                                                    where vat.rate is null;
+------------------+------+-----------+--------------+
2 rows in set (0.00 sec)                                    (gives the list of articles with undefined VAT)



       Web Programming 5) PHP and MySQL                              Web Programming 5) PHP and MySQL
       Basics of MySQL: Select Information             19            Basics of MySQL: Select Information                      20
                   More on SELECT                                           Select and more options
Result of a select can be put into a temporary table                Order result (DESC or ASC)

create temporary table valueVAT                                     select name, price from article order by price desc;
  (select vat.rate, article.name
   from vat,article                                                 Group rows
   where vat.vatID=article.vatID
                                                                    mysql> select vatID, count(vatID)
  )
                                                                         > from article GROUP BY vatID;
;
                                                                    +-------+--------------+
You can access to the content and then delete this table            | vatID | count(vatID) |
                                                                    +-------+--------------+
select * from valueVAT;                                             |     1 |            2 |
                                                                    +-------+--------------+
drop table IF EXISTS valueVAT;                                      1 row in set (0.00 sec)

                                                                    SELECT can have a lot of functions an combine all of them
       Web Programming 5) PHP and MySQL                                     Web Programming 5) PHP and MySQL
       Basics of MySQL: Select Information                     21           Basics of MySQL: Select Information                 22




                        Delete fields                                    Administrate MySQL with a
                                                                              Web interface
                                                                    phpMyAdmin
                                                                        A PHP program for managing MySQL Data Bases
Delete the content of a table respectively to a where clause            Free available at http://www.phpmyadmin.net
                                                                        Included in most of the Linux distrib
delete from article where articleID=3;
                                                                        Internationalization
                                                                    Management made easy
                                                                    Generate and displays the SQL query corresponding.
                                                                        Create a new Data Base
                                                                        Create a new Table
                                                                        Add or remove a column in a table

       Web Programming 5) PHP and MySQL                                     Web Programming 5) PHP and MySQL
       Basics of MySQL: Select Information                     23           PhpMyAdmin                                          24
                       phpMyAdmin                                                          Conclusion -
                                                                                        MySQL/phpMyAdmin
Management of data
    Select data made easy                                                   Not as much powerful as other DB’s
    Update using a visual interface (does not work for BLOBs)                   MySQL does not implement all of SQL
    A lot of selection boxes containing all the possible values                 It is enough to handle a small web site
Import / Export of data                                                         Very useful and easy to install, configure and manage

    Can create SQL Dump                                                     PHP supports all other DB’s
    Can export in a lot of formats: SQL, CSV, LaTeX, CSV for                    Oracle
    excel, XML                                                                  ODBC (MS-SQL server, Access)
    With a lot of properties (zipped, gzipped, with delete tables or            Postgess
    not, . . . )                                                                DBase
                                                                                ...

        Web Programming 5) PHP and MySQL                                              Web Programming 5) PHP and MySQL
        PhpMyAdmin                                                     25             PhpMyAdmin                                       26




                 PHP and MySQL                                                       PHP and MySQL (Cont.)
                                                                            mysql
                                                                                Used from the beginning of the language
Four Libraries                                                                  Compatible with a lot of existing code
    mysql old library                                                       mysqli
    mysqli new library
                                                                                New since php5
    pear MDB2 multi-database library and abstraction layer
                                                                                Contains objects and encapsulation
    (merge of PEAR DB and Metabase abstraction layer)
    PDO Object Oriented generic library                                     PDO
                                                                                Compatible with almost any Data Base
                                                                                Syntax is the same and is platform independent
                                                                                Not as optimized as the two dedicated routines


        Web Programming 5) PHP and MySQL                                              Web Programming 5) PHP and MySQL
        PHP and MySQL together                                         27             PHP and MySQL together                           28
      PHP Data Objects - PDO                                                                   Create a connection
                                                                                      The first step is to create a connection to the DB server
                                                                                         • Needs an URL for connecting:
                                                                                         • protocol:host=<hostname>;dbname=<dbname>
                                                                                         • Arguments are username and password
     PDO is a generic connector for any database                                      Close the connection by setting the handler to null
        • Code is (almost) platform independant
        • Support is provided for all major Data Bases servers (MySQL,            <?php
          Oracle, MS SQL Server, Sybase, Informiy, ODBC, PostgreSQL,              $hostname = ’localhost’;
                                                                                  $username = ’username’;
          SQLITE)
                                                                                  $password = ’password’;
     PDO is an oject oriented interface                                           try {
        • Creates a connection object                                                  $dbh = new PDO(”mysql:host=$hostname;dbname=example”,
        • Creates Statements and Results that are objects                                                           $username, $password);
                                                                                      echo ’Connected to database’;
                                                                                       /∗∗∗ close the database connection ∗∗∗/
                                                                                       $dbh = null;
                                                                                       }
                                                                                  catch(PDOException $e) { echo $e−>getMessage(); }
                                                                                  ?>
         Web Programming 5) PHP and MySQL                                                 Web Programming 5) PHP and MySQL
         PHP and MySQL together: PDO                                         29           PHP and MySQL together: PDO                            30




            Execute a SQL query                                                                       Update records
     Execute a SQL Query : $dbh->exec($sql);
     Value returned = number of affected rows
     Should be used if no result set is returned (INSERT / UPDATE)
                                                                                      PDO::exec() is used each time no selection is needed
try {
     $dbh = new PDO(”mysql:host=$hostname;dbname=animals”,
                                  $username, $password);                              /∗∗∗ INSERT data ∗∗∗/
    /∗∗∗ INSERT data ∗∗∗/                                                             $query=”UPDATE animals SET animal name=’bruce’”;
     $sql = ”INSERT INTO article (name, price) VALUES (’Journal’, ’1.9’)”;            $query .= ” WHERE animal name=’troy’”;
     $count = $dbh−>exec($sql);                                                       $count = $dbh−>exec($query);
     /∗∗∗ echo the number of affected rows ∗∗∗/
     echo $count;
                                                                                      /∗∗∗ echo the number of affected rows ∗∗∗/
     /∗∗∗ close the database connection ∗∗∗/                                          echo $count;
     $dbh = null;
}
catch(PDOException $e){
     echo $e−>getMessage();
}

         Web Programming 5) PHP and MySQL                                                 Web Programming 5) PHP and MySQL
         PHP and MySQL together: PDO                                         31           PHP and MySQL together: PDO                            32
                  Select queries                                     Fetch the result set
                                                           There are multiple ways to visit a result set
                                                              • The SPL traversible iterator (i.e. a foreach on the resultset
SELECT returns a result set                                     itself)
Method is PDO::query()                                        • Fetch the result in arrays or objects
                                                           Fetch :
Returned statement can be visited like an array               • $result = $stmt->fetch(PDO::FETCH ASSOC); : Creates
(implements the SPL traversible iterator)                       an associative array $result containing one record.
                                                              • If fetch is repeated, moves one record ahead
/∗∗∗ The SQL SELECT statement ∗∗∗/                            • Visit with a while (fetch returns false at the end of the
$sql = ”SELECT ∗ FROM animals”;                                 selection)
foreach ($dbh−>query($sql) as $row)
                                                       $sql = ”SELECT ∗ FROM article”;
    {
                                                       /∗∗∗ fetch into an PDOStatement object ∗∗∗/
    print $row[’animal type’] .’ − ’.
                                                       $stmt = $dbh−>query($sql);
             $row[’animal name’] . ’<br />’;
                                                       while ($result = $stmt−>fetch(PDO::FETCH ASSOC)){
    }
                                                         echo ’Name = ’.$result[’name’].’, price=’.$result[price].”<br />\n”;
                                                       }
    Web Programming 5) PHP and MySQL                           Web Programming 5) PHP and MySQL
    PHP and MySQL together: PDO                   33           PHP and MySQL together: PDO                                      34




    Different types of Fetch                                           Fetch into Objects
Into an associative array:
$result = $stmt−>fetch(PDO::FETCH ASSOC);
...                                                        Fetch can create a ad-hoc object, having the columns
echo $result[’name’];                                      names as properties
Into a numeric array
                                                       $obj = $stmt−>fetch(PDO::FETCH OBJ);
$result = $stmt−>fetch(PDO::FETCH NUM)
..                                                     /∗∗∗ Visit the object directly ∗∗∗/
echo $result[1]                                        echo $obj−>name.”<br />\n”;
Into both associative and numeric array                echo $obj−>price;

$result = $stmt−>fetch(PDO::FETCH BOTH)
..
echo $result[1].’, ’.$result[’name’];

    Web Programming 5) PHP and MySQL                           Web Programming 5) PHP and MySQL
    PHP and MySQL together: PDO                   35           PHP and MySQL together: PDO                                      36
                           Fetch Lazy                             Fetch a record into an object of
                                                                            a given class
     PDO::FETCH LAZY is odd as it combines PDO::FETCH BOTH
     and PDO::FETCH OBJ.
                                                                      PDO::FETCH CLASS instantiates a new instance of the
                                                                      specified class.
$obj = $stmt−>fetch(PDO::FETCH LAZY);
                                                                        • The field names are mapped to properties (variables) within
/∗∗∗ Visit the object directly ∗∗∗/                                       the class called.
                                                                        • This saves quite a bit of code and speed is enhanced as the
echo $obj−>name.”<br />\n”;
                                                                          mappings are dealt with internally.
echo $obj[1].”<br />\n”;
echo $obj[’price’];                                                   method fetchALL() creates an array containing all the
                                                                      records.




         Web Programming 5) PHP and MySQL                                 Web Programming 5) PHP and MySQL
         PHP and MySQL together: PDO                         37           PHP and MySQL together: PDO                                   38




Fetch a record into an object of                                     Fetching into an new Object
     a given class (Cont.)                                            We define the fetch mode of a statement
class article{
      public $articleID;                                          $sql = ”SELECT ∗ FROM article”;
      public $name;
      public $vatID;                                              /∗∗∗ fetch into an PDOStatement object ∗∗∗/
      public $categoryID;
                                                                  $stmt = $dbh−>query($sql);
      public $price;
      public $description;
      public function displayElementLine(){                       /∗∗∗ set the fetch mode with PDO::setFetchMode() ∗∗∗/
           echo $this−>name.”,”.$this−>price.”<br />\n”;          $stmt−>setFetchMode(PDO::FETCH INTO, new article);
      }
}
...                                                               /∗∗∗ loop over the PDOStatement directly ∗∗∗/
$stmt = $dbh−>query($sql);                                        foreach($stmt as $article){
$obj = $stmt−>fetchALL(PDO::FETCH CLASS, ’article’);                $article−>displayElementLine();
foreach($obj as $article){                                        }
    $article−>displayElementLine();
}
         Web Programming 5) PHP and MySQL                                 Web Programming 5) PHP and MySQL
         PHP and MySQL together: PDO                         39           PHP and MySQL together: PDO                                   40
                     Error Handling                                                  Error Handling (Cont.)
                                                                           try {
                                                                             $dbh = new PDO(”mysql:host=$hostname;dbname=animals”,
                                                                                                           $username, $password);
    Default: Errors are Dye statements                                       /∗∗∗ echo a message saying we have connected ∗∗∗/
       • Program is interrupted                                              echo ’Connected to database<br />’;
       • Error is displayed on Screen                                        /∗∗∗ set the error reporting attribute ∗∗∗/
    We should throw exceptions                                               $dbh−>setAttribute(PDO::ATTR ERRMODE,
       • Change the error mode such that it sends exceptions,                                       PDO::ERRMODE EXCEPTION);
       • Then catch all axceptions                                           ...
       • It prevents an attacker to access to internal information.
                                                                             $dbh = null;
                                                                           }
                                                                           catch(PDOException $e){
                                                                             echo $e−>getMessage();
                                                                           }

        Web Programming 5) PHP and MySQL                                           Web Programming 5) PHP and MySQL
        PHP and MySQL together: PDO                                   41           PHP and MySQL together: PDO                                    42




             Prepared Statement                                                Using a prepared statement
    A precompiled SQL statement                                            $article id = 6; $name = ’20 Minuten’;
      • Accepts 0 or more parameters                                       $sql = ”SELECT ∗ FROM article WHERE articleID=:article id OR name=:name”;
      • Usefull for using a query multiple times                           $stmt = $dbh−>prepare($sql);
                                                                           $stmt−>bindParam(’:article id’, $article id, PDO::PARAM INT);
      • Usefull for preventing SQL Injection
                                                                           $stmt−>bindParam(’:name’, $name, PDO::PARAM STR, 5);
                                                                           $stmt−>execute();
$sql = ”SELECT ∗ FROM article”.
                                                                           $result = $stmt−>fetchAll();
            ”WHERE articleID = :article id OR name = :name”;               foreach($result as $row){ echo $row[’name’].”<br />\n”; }
$stmt = $dbh−>prepare($sql);                                               $article id = 5;
$stmt−>bindParam(’:article id’, $article id, PDO::PARAM INT);              $name = ’24 Heures’;
$stmt−>bindParam(’:name’, $name, PDO::PARAM STR, 5);                       $stmt−>execute();
$stmt−>execute();                                                          $result = $stmt−>fetchAll();
$result = $stmt−>fetchAll();                                               foreach($result as $row){ echo $row[’name’].”<br />\n”; }
foreach($result as $row){                                                  $article id = 1;
  echo $row[’articleID’].’, ’;                                             $name = ’Nesquik’;
  echo $row[’name’].’, ’;                                                  $stmt−>execute();
  echo $row[’price’].”<br />\n”;                                           $result = $stmt−>fetchAll();
                                                                           foreach($result as $row){ echo $row[’name’].’, ’; }
}
        Web Programming 5) PHP and MySQL                                           Web Programming 5) PHP and MySQL
        PHP and MySQL together: PDO                                   43           PHP and MySQL together: PDO                                    44
                            Transaction                                                 Transaction (Example)
    Transactions are used to group requests that must                       try{
    remain together                                                         ...
                                                                                $dbh−>beginTransaction();
       • For efficiency reason (do not lock the file too many times)               $table = ”CREATE TABLE animals (
       • For consistency of database (Group some queries that should                animal id MEDIUMINT(8) NOT NULL AUTO INCREMENT PRIMARY KEY,
         remain together)                                                           animal type VARCHAR(25) NOT NULL,
    Examples                                                                        animal name VARCHAR(25) NOT NULL
                                                                                )”;
       •    Insertion of many records
                                                                                $dbh−>exec($table);
       •    Credit and Debit operations (Bookkeeping)
                                                                                $dbh−>exec(”INSERT INTO animals (animal type, animal name) VALUES (’emu’, ’bruce’)”)
       •    Delivering and stock management                                     ...
       •    ...                                                                 $dbh−>exec(”INSERT INTO animals (animal type, animal name) VALUES (’lizard’, ’bruce’)”
    Syntax                                                                      $dbh−>commit();
       • At the begining of the transaction                                     echo ’Data entered successfully<br />’;
                                                                            }
         $dbh->beginTransaction();
                                                                            catch(PDOException $e){
       • At the end of the transaction $dbh->commit();
                                                                                $dbh−>rollback();
       • In order to cancel a transaction (before commit)                       echo $sql . ’<br />’ . $e−>getMessage();
                                                                            }
           Web Programming 5) PHP and MySQL                                            Web Programming 5) PHP and MySQL
           PHP and MySQL together: PDO                                 45              PHP and MySQL together: PDO                                     46




       Get the index of the last                                               Use the Singleton pattern for
           inserted element                                                       the connection object
    When inserting an element, index may be
    autoinctemented                                                              Connection to the DB should be unique
       • Programmer needs a way to access the index                                • Connection requires a lot of time
       • Can update other tables                                                   • Number of connections to the DB may be limited
       • Can create cross reference with new records                             A design Pattern exists for creating only one instance of
                                                                                 a class
/∗∗∗ INSERT a new row ∗∗∗/                                                         • The ”singleton” design pattern
$sql = ”INSERT INTO article (name, price) VALUES (’Laptop’, 500)”;               Ideas:
$dbh−>exec($sql);                                                                  •    Make the constructor private
                                                                                   •    Make the clone private
/∗∗∗ display the id of the last INSERT ∗∗∗/                                        •    Create an instance as class parameter
echo ”The last inserted element has ID: ”;                                         •    Create a static method getInstance() that creates the
echo $dbh−>lastInsertId().”<br />\n”;                                                   instance if it did not exist or returns the existing one.


           Web Programming 5) PHP and MySQL                                            Web Programming 5) PHP and MySQL
           PHP and MySQL together: PDO                                 47              PHP and MySQL together: PDO                                     48
                       The Singleon                                                                 Usage
class db{                                                         try {
  private static $instance = NULL;                                     /∗∗∗ query the database ∗∗∗/
  private function construct() {
                                                                       $result = DB::getInstance()−>query(”SELECT ∗ FROM article”);
          /∗∗∗ maybe set the db name here later ∗∗∗/
  }
  public static function getInstance() {                              /∗∗∗ loop over the results ∗∗∗/
     if (!self::$instance){                                           foreach($result as $row)
         self::$instance = new PDO(                                   {
”mysql:host=localhost;dbname=example”, ’root’, ’’);;                      print $row[’name’] .’ − ’. $row[’price’] . ’<br />’;
         self::$instance−> setAttribute(PDO::ATTR ERRMODE,            }
                     PDO::ERRMODE EXCEPTION);                     }
     }
                                                                  catch(PDOException $e)
     return self::$instance;
  }                                                               {
  private function clone(){ }                                         echo $e−>getMessage();
} /∗∗∗ end of class ∗∗∗/                                          }

        Web Programming 5) PHP and MySQL                                   Web Programming 5) PHP and MySQL
        PHP and MySQL together: PDO                          49            PHP and MySQL together: PDO                            50




         mysql :(The old library)                                               Connection to a DB
                                                                  Establish the connection
<?php
$db = @mysql connect(’localhost’,’root’,’toto14’);                $db = @mysql connect(’localhost’,’root’,’toto14’);
if ($db)                                                          Handle
{
   echo ”Connection OK <br>\n”;                                       Returned by the connection
   echo ”Handle ID= $db\n”;                                           One can play with more than one connection (multi-server)
}                                                                     Is a number
else{
                                                                  Error handling
   echo ”Connection could not be established<br>”;
   echo mysql error();                                             echo mysql error();
}                                                                 Disconnect from the DB
mysql close();
?>                                                                mysql close();


        Web Programming 5) PHP and MySQL                                   Web Programming 5) PHP and MySQL
        PHP and MySQL together: mysql                        51            PHP and MySQL together: mysql                          52
                   Execute Queries                                         Fetch result of a select
execute a SQL query: mysql query(sql,conn)                      require once(”connDB.php”);
SQL queries are stored in an array and executed
                                                                $query = ”select ∗ from article”;
require(”connDB.php”);
                                                                $result = mysql query($query , $conn);
$querys= array();
                                                                if($result){
                                                                   $nbRows = mysql num rows($result);
$querys[]=”insert into article values(0,’Milk’,1,1,”.              echo ”$nbRows have been selected by query:<br>$query <br>\n”;
          ”’10.90’,’Super milk for the kids’);”;
$querys[]=”insert into article values(0,’Cafe’,1,”.               // arrayType can be : MYSQL ASSOC, MYSQL NUM, MYSQL BOTH
                                                                  while( $row = mysql fetch array($result, MYSQL ASSOC)){
          ”1,’10.90’,’Super cafe for the adults’);”;                echo implode(”,”,$row).”<br>\n”;
                                                                  }
foreach($querys as $query){                                     }
  mysql query($query , $conn);                                  else{
                                                                  echo mysql error();
}                                                               }
mysql close($conn);                                             mysql close($conn);
        Web Programming 5) PHP and MySQL                                  Web Programming 5) PHP and MySQL
        PHP and MySQL together: mysql                      53             PHP and MySQL together: mysql                            54




 Fetch result of a select (Cont.)                                     Fetch a row into an object

                                                                Creates an object whose member variables are fields
Types of arrays
    MYSQL ASSOC keys are field names                             if($result){
                                                                   $nbRows = mysql num rows($result);
    MYSQL NUM keys are numbers
                                                                   echo ”$nbRows have been selected by query:<br>$query <br>\n”;
    MYSQL BOTH keys are both numbers and field names                while( $obj = mysql fetch object($result)){
fetch a single row until no more is accessible                       echo ”{$obj−>name}, => CHF {$obj−>price} <br>\n”;
  while( $row = mysql fetch array($result, MYSQL ASSOC))           }
                                                                }




        Web Programming 5) PHP and MySQL                                  Web Programming 5) PHP and MySQL
        PHP and MySQL together: mysql                      55             PHP and MySQL together: mysql                            56
           Insert data into a table                                              Insert and Update
$query = ”insert into article values(0,’Milk UHT’, ”.
        ”1,1,’7.90’,’Super milk for kids’)”;
                                                                Insert
$result= mysql query($query , $conn);                               Create a new record in the table
if(!$result){                                                       ID automatically generated (with auto increment)
    echo mysql error;                                               We need to access this counter:
}
$newID = mysql insert id();                                         $newID = mysql insert id();

$query2 = ”update article set price=’5.5’”.
                                                                Update
          ” where articleID=$newID”;                                Useful to change the tables
$result2= mysql query($query2 , $conn);
                                                                    Programmer want to know if it worked
if(!$result2){                                                      Indication: the number of affected rows:
    die(”Could not execute $query2 ”.mysql error());
}                                                                   mysql affected rows($conn)
$affectedRows = mysql affected rows($conn);
echo ”number of affected rows:$affectedRows<br>\n”;
          Web Programming 5) PHP and MySQL                               Web Programming 5) PHP and MySQL
          PHP and MySQL together: mysql                    57            PHP and MySQL together: mysql                        58




Manipulate the Data Base itself                                                          Conclusion I
require once(”connDB.php”);
$res = mysql list dbs($conn);
for($i=0;$i < mysql num rows($res);$i++){
                                                                mysql Library
  $dataBase= mysql tablename($res,$i);
  $result=0;                                                        Uses no objects,
  $query = ”show tables from $dataBase”;
                                                                    Based and ”handles” that can be passed as parameters to
  $result = mysql query($query , $conn);
  if($result){                                                      functions
     $nbTables = mysql num rows($result);                       PDO
     echo ”<b>$dataBase</b>has $nbTables tables <br>\n”;
                                                                    Generic Access to any database
    while( $row = mysql fetch array($result,                        Most of the code is plattform independant
                                     MYSQL ASSOC)){
      echo implode(”,”,$row).”<br>\n”;                              Must sometime be adapted
    }
  }
}
mysql close($conn);

          Web Programming 5) PHP and MySQL                               Web Programming 5) PHP and MySQL
          PHP and MySQL together: mysql                    59            Conclusion                                           60
                      Conclusion II                                                      Resources
DB are the center of our work
    Do not require a programmer to write HTML
    they are used to access DB’s
    forms and db’s are the two pillars of web programming          Pear Documentation
    a lot of other finesses to be discovered                        http://pear.php.net
    SQL : a semester course of 2 hours a week
PHP supports all data bases
    A standard web architecture is LAMP: Linux Apache MySQL
    PHP



       Web Programming 5) PHP and MySQL                               Web Programming 5) PHP and MySQL
       Conclusion                                             61      Conclusion                         62

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:14
posted:10/3/2011
language:German
pages:16