PHP and Mysql, playing with the database - DaoOne package blog

PHP is the most popular programming language for web design. It is open source, powerful and popular. MySQL is a Relational Database Management System (RDBMS) that uses Structured Query Language (SQL). It is also free and open source. The combination of PHP and MySQL gives unmet options to create just about any kind of website - from small contact form to large corporate portal.

working with mysql and php is fun

For the next exercise, We will use the next library. EFTEC/DaoOne Database Access Object wrapper for PHP and MySqli in a single class -

https://github.com/EFTEC/DaoOne

The library is mainly built on top to generate a SQL instead of wrapping an object, so it must be as fast as a native query (i.e. it's only joining some string and arrays). It's also dependency-free (one class, no dependence, runs everywhere, MIT license). You also require PHP and Mysql up and running. For example, let's say that I have a table called Product and I want to list the information First, let's connect to the library and the database(the database is local and is called sakila, the user is root and the password is abc.123. You can change the include and add the library manually, or via composer. In this example, it uses composer (vendor/autoload.php) It also uses a library called mapache-commons. It's only used to display the table, so it is optional.

<?php

use eftec\DaoOne;
use mapache_commons\Collection;

include "../vendor/autoload.php";
// connecting to database sakila at 127.0.0.1 with user root and password abc.123
$dao=new DaoOne("127.0.0.1","root","abc.123","sakila","logdaoone.txt");
try {
    $dao->connect();
} catch (Exception $e) {
    echo "<h2>connection error:</h2>";
    echo $dao->lastError()."-".$e->getMessage()."<br>";
    die(1);
}

Then, let's create the database (DML)`

$sqlT1="CREATE TABLE `myproducts` (
    `idproduct` INT NOT NULL,
    `name` VARCHAR(45) NULL,
    `type` VARCHAR(45) NULL,
    `id_category` INT NOT NULL,
    PRIMARY KEY (`idproduct`));";

try {
    $dao->runRawQuery($sqlT1);
} catch (Exception $e) {
    echo $e->getMessage()."<br>";
}

$sqlT2="CREATE TABLE `product_category` (
    `id_category` INT NOT NULL,
    `catname` VARCHAR(45) NULL,
    PRIMARY KEY (`id_category`));";

try {
    $dao->runRawQuery($sqlT2);
} catch (Exception $e) {
    echo $e->getMessage()."<br>";
}

If you see the query, then it's just native and classic SQL, there is nothing special about it. And let's add some information to the table.

// adding some data
try {
    $dao->set(['id_category' => 1, 'catname' => 'cheap'])
        ->from('product_category')->insert();
    $dao->set(['id_category'=>2,'catname'=>'normal'])
        ->from('product_category')->insert();
    $dao->set(['id_category'=>3,'catname'=>'expensive'])
        ->from('product_category')->insert();
} catch (Exception $e) {
}
// adding categories
try {
    $dao->set(['idproduct'=>1,'name'=>'cocacola'
        ,'type'=>'drink','id_category'=>1])
        ->from("myproducts")->insert();
    $dao->set(['idproduct'=>2,'name'=>'fanta'
        ,'type'=>'drink','id_category'=>1])
        ->from("myproducts")->insert();
    $dao->set(['idproduct'=>3,'name'=>'sprite'
        ,'type'=>'drink','id_category'=>1])
        ->from("myproducts")->insert();
    $dao->set(['idproduct'=>4,'name'=>'iphone'
        ,'type'=>'phone','id_category'=>2])
        ->from("myproducts")->insert();
    $dao->set(['idproduct'=>5,'name'=>'galaxy note'
        ,'type'=>'phone','id_category'=>2])
        ->from("myproducts")->insert();
    $dao->set(['idproduct'=>6,'name'=>'xiami'
        ,'type'=>'phone','id_category'=>2])
        ->from("myproducts")->insert();
    $dao->set(['idproduct'=>7,'name'=>'volvo',
        'type'=>'car','id_category'=>3])
        ->from("myproducts")->insert();
    $dao->set(['idproduct'=>8,'name'=>'bmw'
        ,'type'=>'car','id_category'=>3])
        ->from("myproducts")->insert();
} catch (Exception $e) {
}

Now, the system is doing some magic. You could insert a value via a rawQuery or you could use a procedural chain. In the case of insert, you need set()->from()->insert(). There are so many ways to do the same with this library but I am explaining the easy one. Now, let's play Listing the products

// list products (null indicates it doesn't use parameters and true means it returns data
$products=$dao->runRawQuery("select * from myproducts",null,true);

So, it is another raw query, it is only SQL, nothing more and works flawlessly. 2. Listing the products, by using procedure calls (it's the same information than the previous exercise but it doesn't use native query) // Listing using procedure call $products=$dao->select("*")->from("myproducts")->toList();

Now, it's procedural. You could even add where(), order() and so on. The last command is important. You could 1. List() returns an arrays of array 2. first() or last() returns a single array. 3. firstScalar() it returns the first cell of the first value. It is useful for return count. 3. Joining the table of products and categories.

$products=$dao->select("*")->from("myproducts my")
    ->join("product_category  p on my.id_category=p.id_category")->toList();

4. It's good but we should columns that don't want to show. So, we need to clean it a bit.

$products=$dao->select("name,type,catname")->from("myproducts my")
    ->join("product_category  p on my.id_category=p.id_category")->toList();

5. And we could sort by name

$products=$dao->select("name,type,catname")->from("myproducts my")
    ->join("product_category  p on my.id_category=p.id_category")
    ->order("name")->toList();

6. We could limit the results, for example, returning the first 3 elements

$products=$dao->select("*")->from("myproducts my")
    ->join("product_category  p on my.id_category=p.id_category")
    ->order("name")->limit("1,3")->toList();

7. And finally, we could group by category

$products=$dao->select("catname,count(*) count")
    ->from("myproducts my")
    ->join("product_category  p on my.id_category=p.id_category")
    ->group("catname")
    ->toList();