minte9
LearnRemember   PHP





Install

Doctrine is the best set of PHP libraries to work with databases. 
 
composer create-project symfony/skeleton doctrine
cd doctrine/

# Install Doctrine support via the orm Symfony pack.

composer require symfony/orm-pack
composer require --dev symfony/maker-bundle

symfony server:start
http://localhost:8000   # Welcome to Symfony

Database 

 
# .env
DATABASE_URL="mysql://admin:password@127.0.0.1:3306/db_name?serverVersion=8.0.27"

php bin/console doctrine:database:create
    # Created database db_name for connection named default

php bin/console make:entity
    # created: src/Entity/Product.php
    # created: src/Repository/ProductRepository.php

php bin/console make:migration
    # Review the new migration "migrations/Version20220213125613.php"

php bin/console doctrine:migrations:migrate
    # Migrating up to DoctrineMigrations\Version20220213125613
    # Table product created

php bin/console make:entity
    # add descrition property to product entity

php bin/console make:migration
php bin/console doctrine:migrations:migrate

Controller

You can create a new Product object and save it in database. 
 
/**
 * ProductController
 * 
 * php bin/console make:controller ProductController
 * src/Controller/ProductController.php
 * 
 * Save products in DB.
 * 
 * Whether you're creating or updating objects ...
 * Doctrine is smart enough to know if it should INSERT or UPDATE
 * 
 * http://localhost:8000/product        # New product saved, id: 1
 * http://localhost:8000/product        # New product saved, id: 2
 * http://localhost:8000/fetch_one      # Product id 2, name: Desk
 * http://localhost:8000/fetch_sql      # Last from list (id > 3): 7
 */
namespace App\Controller;

use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use Symfony\Component\HttpFoundation\Response;
use Symfony\Component\Routing\Annotation\Route;

use App\Entity\Product;
use Doctrine\Persistence\ManagerRegistry;

class ProductController extends AbstractController
{
    #[Route('/product', name: 'create_product')]
    public function index(ManagerRegistry $doctrine): Response
    {
        $entityManager = $doctrine->getManager();

        $product = new Product();
        $product->setName('Desk');
        $product->setPrice(500);
        $product->setDescription('Ergonomic');

        $entityManager->persist($product); // no queries yet
        $entityManager->flush(); // queries executed

        return new Response('New product saved, id: ' . $product->getId());
    }

    #[Route('/fetch_one')]
    public function fetch_one(ManagerRegistry $doctrine): Response
    {
        $repository = $doctrine->getRepository(Product::class);
        $one = $repository->findOneBy(['id' => 2]);

        return new Response(
            'Product id 2, name: ' . $one->getName());
    }

    #[Route('/fetch_sql')]
    public function fetch_sql(ManagerRegistry $doctrine): Response
    {
        $repository = $doctrine->getRepository(Product::class);
        $data = $repository->findAllGreaterThanId(3);
        $last = $data[0];

        return new Response(
            'Last from list (id > 3): ' . $last['id']);
    }
}

Fetch

You can query directly with SQL if you need to. 
 
/**
 * ProductRepository
 * 
 * src/Repository/ProductRepository.php
 * 
 * You can think of a repository as a PHP class whose only job ...
 * is to help you fetch entities of a certain class.
 * 
 * Once you have a repository object, you have many helper methods:
 * find(), findOneBy(), findAll(), findBy()
 */

namespace App\Repository;

use App\Entity\Product;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\Persistence\ManagerRegistry;

class ProductRepository extends ServiceEntityRepository
{
    public function __construct(ManagerRegistry $registry)
    {
        parent::__construct($registry, Product::class);
    }

    public function findAllGreaterThanId(int $id): array
    {
        $conn = $this->getEntityManager()->getConnection();
        $sql = "
            SELECT * FROM product p 
            WHERE p.id > :id
            ORDER BY p.id DESC
        ";
        $stmt = $conn->prepare($sql);
        $result = $stmt->executeQuery(['id' => $id]);

        return $result->fetchAllAssociative();
    }
}




References: