Using Nice_db with PHP and MySQL or MariaDB

Setting Up Database System and PHP

You will need to install

mysql or mariadb, your choice
install PHP with PDO
run the following install script

Setting up OUR database tables

The following SQL commands will create the database, a table, and grant permissions to our user. This assumes the database is running on the local host (localhost), which is network address 127.0.0.1.

I found that sometimes certain OS’s assume IPv6, so 'localhost' defaults to IPv6 localhost, but the database may only be listenning on IPv4, so we will hardcode the IPv4 address into the later PHP config file.

create database future;

use future

create table users
( name varchar(128),
  office varchar( 128),
  country varchar (128),
  primary key( name ));

insert into users ( name, office, country )
  VALUES ('Joe', 'Somewhere', 'Canada');

grant all on future.* to sample@localhost IDENTIFIED BY 'drop2345';

Create config.php

This file will contain the userid, password and path to the database. 'name' here is the name of the database.

<?php

$db_name = 'future';
$db_host = '127.0.0.1';
$db_user = 'sample';
$db_pass = 'drop2345';
$db_port = 3306;

Sample PHP program

The main functions we will use are: $db→sqlarray() which takes an SQL statement and a list of paremeters. $db→sqlfirst() which gets the first results into $db→Query1, and the pair: $db→eof() which returns True on end of list, and $db→sqlnext() which fills $db→Query1 with the next results if there are any and sets the internal EOF flag if non exist.

A second funciton of note is $db→getone( $sql, $variablename, [ $parameters…] ) which does all the above and returns the values from the first found result.

We’ll use that to check for the existance of 'Erick', and if not found, we’ll add the record.

Note	$db is a global variable, if you use it in a subroutine you must specify it as global on the start of the subroutine.

Note	always used parameterized variables as shown, never use '$user' directly in your SQL statements or you will be vulnerable to SQL injection attacks.

<?php
include "config.php";
include 'nice_db.php';

$name = 'Erick';
$office = "Here";
$country = 'Canada';

// get a list of all matching users
$db->sqlarray("SELECT * FROM future.users", [] );

$db->sqlarray("SELECT * FROM future.users WHERE name = ? AND office like ? ",
  [ $name, $office  ]);
$db->sqlfirst();
$count = 0;
while (! $db->eof() ) {
   print_r( $db->Query1 );
   ++$count;
   $db->sqlnext();
}
print "There were $count results\n";

 // or to get the first result only, there is a shortcut
if ( ! $db->getone("SELECT name FROM users WHERE name = ? AND office = ?",'name',
 [ $name, $office ] )) {

 // not found, so add some values to users list

  $db->sqlarray("INSERT INTO users (name, office, country) VALUES ( ?, ?, ? ) ",
    [ $name, $office, $country ]);
}


?>

Importing Data from EWB

How do you transfer data from EWB apps to PHP?

In your EWB app, create a TServerRequest: Req;

  req.method := rmPost;
  // the next function requires my Nice toolkit
  req.Content := GetJSONOf( dataset );
  req.url := 'path to php';
  req.execute;

<?php

// get the contents submitted by client in POST

$passed = file_get_contents('php://input');
// for test purposes, we show what would be passed if nothing were submitted
// don't do this in real code
if ( $passed == NULL ) $passed=
'{
    "rows": [
        {
            "name": "Erick Engelke",
            "office" : "Here"
        },
        {
            "name": "Rosie",
            "office" : "There"
        }
    ]
}';

// convert data to array structure
$data = json_decode( $passed, true );

$rows = $data['rows'];

foreach ( $rows as $cur ) {
   $name = $cur['name'];
   $office = $cur['office'];

   print " $name @ $office\n";
}

?>

How to Transfer Data from PHP to EWB

<?php

include( 'nice_db.php' );

// we'll create a list of rows
$rows = array();

// first row
$row = array();
$row['name'] = "Erick";
$row['job'] = 'Developer';

// add it to the list
$rows[] = $row;

// second row
$row = array();
$row['name'] = "Rosie";
$row['job'] = 'Manager';

// add it to the list
$rows[] = $row;


// create data transfer unit
$data = array();
$data['rows'] = $rows;

$data = json_encode( $data );
print $data;
?>

Enjoy