MENU


Procedural Style

Note that the equivalence comparison operator in SQL is = and not
connection.sql:
<!DOCTYPE html><html><head>
<style type="text/css">
   table, td {border: 1px solid;}
</style></head><body>
<?php

// ****** 1. Database Connection
$link=mysqli_connect("localhost","root","password","testDB");
if (mysqli_connect_errno()) {
   echo "Connection failed: ".mysqli_connect_error();
   exit();
}


// ****** 2. Table Definition
mysqli_query($link, "DROP TABLE IF EXISTS tbl1, tbl2");

mysqli_query($link, "
CREATE TABLE IF NOT EXISTS tbl1 (
   a TINYINT (5) ZEROFILL,
   b INT UNSIGNED AUTO_INCREMENT UNIQUE,
   c DECIMAL(5,2) PRIMARY KEY,
   d DATETIME DEFAULT CURRENT_TIMESTAMP 
                      ON UPDATE CURRENT_TIMESTAMP,
   e BINARY(3) NOT NULL,
   z TEXT)
   AUTO_INCREMENT 5,
   CHARACTER SET 'latin1'
");
echo mysqli_error($link); // reports any error

mysqli_query($link,"
ALTER TABLE tbl1 
   ADD f VARCHAR(5) AFTER e,
   CHANGE z g TEXT
");

mysqli_query($link, "
CREATE TABLE IF NOT EXISTS tbl2 (
   a TINYINT (5) ZEROFILL,
   x BLOB)
");
echo mysqli_error($link); // reports any error


// ****** 3. Data Management
mysqli_query($link, "
INSERT INTO tbl1 VALUES 
(1,10,1.23,'2015-06-25 19:30:00','abc','aaaaa','Hello World'),
(2,99,1.1,'2016-04-23 13:30:00','def','bbb','Testing 123')
");

mysqli_query($link, "
REPLACE tbl1 SET
a=3,c=9.9,e='mno',g='Good day'
");

mysqli_query($link, "
UPDATE tbl1 SET
c=8.8 WHERE a=3
");

mysqli_query($link, "
INSERT INTO tbl2 VALUES 
(4,'PHP'),
(5,'SQL')
");


// ****** 4. Data Retrieval
$result = mysqli_query($link, "
SELECT * FROM tbl1 ORDER BY a ASC,b DESC");
tabulate($result);

$result = mysqli_query($link, "
SELECT * FROM tbl2");
tabulate($result);

$result = mysqli_query($link, "
(SELECT a AS c1,g c2 FROM tbl1) UNION 
(SELECT * FROM tbl2) ORDER BY c1 DESC");
tabulate($result);

$result = mysqli_query($link, "
SELECT * FROM tbl1 JOIN tbl2 ON tbl1.a <= 3");
tabulate($result);

$result = mysqli_query($link, "
SELECT * FROM tbl1 JOIN tbl2 GROUP BY tbl1.a, tbl1.b HAVING SUM(tbl1.a)>3");
tabulate($result);

$result = mysqli_query($link, "
SELECT * FROM tbl1 T1 RIGHT JOIN tbl2 T2 ON T2.a <= 4");
tabulate($result);

mysqli_query($link, "UPDATE tbl2 SET a=3 WHERE a=4");
$result = mysqli_query($link, "
SELECT * FROM tbl1 JOIN tbl2 USING (a)");
tabulate($result);


// ****** A useful, general-purpose result printer
function tabulate($result){
   $fInfo = mysqli_fetch_fields($result);
   echo "<table><tr>";
   foreach($fInfo as $col){
      echo "<td>".$col->name."</td>";
   }
   while ($row = mysqli_fetch_row($result)){
      echo "</tr><tr>";
      foreach ($row as $val){
         echo "<td>".$val."</td>";
      }
   }
   echo "</tr></table><br/>";
}

?>
</body>
</html>


  

    a
    b
    c
    d
    e
    f
    g
  

  

    00001
    10
    1.23
    2015-06-25 19:30:00
    abc
    aaaa
    Hello World
  

  

    00002
    99
    1.10
    2016-04-23 13:30:00
    adf
    bhh
    Testing 123
  

  

    00003
    100
    8.80
    2014-04-27 09-29-05
    mno
     
    Good day
  





  

    a
    x
  

  

    0004
    PHP
  

  

    0005
    SQL
  





  

    c1
    c2
  

  

    5
    SQL
  

  

    4
    PHP
  

  

    3
    Good Day
  

  

    2
    Testing123
  

  

    1
    Hello World
  





  

    a
    b
    c
    d
    e
    f
    g
    a
    x
  

  

    00002
    99
    1.10
    2016-04-23 13:30:00
    def
    bbb
    Testing 123
    00004
    PHP
  

  

    00002
    99
    1.10
    2016-03-23 15:52:45
    def
    bbb
    Testing 123
    00005
    SQL
  

  

    00001
    10
    1.23
    15-06-25 19:26:23
    abc
    aaa
    Hello World
    00004
    php
  

  

    00001
    10
    1.23
    15-06-25 19:26:23
    abc
    aaa
    Hello World
    00005
    SQL
  

  

    00003
    100
    8.80
    2014-04-15
    09:26:05 
    mno
     
    Good day
    00004
    PHP
  

  

    00003
    100
    8.8
    2014-04-15
    09:26:05 
    mno
     
    Good day
    00005
    SQL
  





  

    a
    b
    c
    d
  

  

    00002
    99
    1.10
    2016
  

  

    00003
    100
    8.80
    2014