Zendframework 3 – Working with Database

We will see how to conntect to database and run select,insert,update,delete (CRUD)coomand in Zendframework 3.

First you have to install the zend_db component to work with databse. You can run the following composer command in the terminal

composer require zendframework/zend-db

Next you can create an adapter for the databse by passing required config paraments

	$config = array(
	  		'driver'   => 'Pdo_Mysql',
	  		'dsn'      => 'mysql:dbname=restapi;host=127.0.0.1',
	  		'username' => 'root',
	  		'password' => '',
		);
        
        $adapter = new \Zend\Db\Adapter\Adapter($config);

Once you get the adapter, you can run followng code

    // select statement
        $statement=$adapter->query("select * from products");
        $rows = $statement -> execute();
        foreach ($rows as $row) {
        	//print_r($row);
        }

        // insert statement

        $result = $adapter->createStatement("insert into products (name,price) VALUES ('iPhone x',889)")->execute();
       
        // To get the Identity value of the lasted inserted row
        echo $adapter->getDriver()->getLastGeneratedValue();

        // update statement
        $adapter->createStatement("update  products set price=789 where id=9")->execute();


        // delete statement
        $adapter->createStatement("delete from products where id=9")->execute();

Possible Errors

Connect Error: SQLSTATE[HY000] [2002] No such file or directory
To solve above errots I changed local host to 127.0.0.1 in following code

$config = array(
	  		'driver'   => 'Pdo_Mysql',
	  		'dsn'      => 'mysql:dbname=restapi;host=localhost',
	  		'username' => 'root',
	  		'password' => '',
		);