Inserting and updating data for a website monitoring app

Question

I have created an app in which URLs stored in a database are pinged via curl to check their availability and the database is updated with the results.

Two tables are updated with each fresh ping of a website (Site -- the status recieved 200, 404 etc) and (History - time of each ping, time taken, result etc) From my understanding because I am using Doctrine I have to create an object each time and update the database one object at a time. Is there another way around this? This solution means for each site I have I must create two queries for each and this could quickly get out of hand.

About the Database

Site and History have a many to one relationship. One Site can have many history entries but each history only has one site relating to it.

Is there a more efficient way of doing this?

Default Controller.php

<?php

namespace AppBundle\Controller;

use AppBundle\Controller\Base\Controller;
use Sensio\Bundle\FrameworkExtraBundle\Configuration\Route;

use AppBundle\Entity\Site;
 use AppBundle\Entity\History;
use AppBundle\Entity\Status;

class DefaultController extends Controller
{

/**
 * @Route("/scan", name="homepage")
 */
public function indexAction()
{
    $allSites = $this->getSites(); // Returns sites repo
    $allStatuses = $this->getStatuses(); // returns all possible statusus 404, 200 etc

    $results = $this->getScanManager()->scanBatch($allSites); // scans all the sites and returns results array


    foreach ($results as $result) {

        $history = new History();
        $history->setSiteId($this->findSiteById($result['id'], $allSites)); // RETURNS THE CORRESPONDING SITE OBJECT - SINCE IT IS AN ID
        $history->setTime(date('h:i:s d-m-Y'));
        $history->setStatusId($this->findStatusById($result['status'], $allStatuses));//RETURNS THE CORRESPONDING STATUS OBJECT - SINCE IT IS AN ID
        $history->setResponseTime($result['speed_download']);

        $em = $this->getDoctrine()->getManager();
        $em->persist($history);
        $em->flush();


        $site = $this->findSiteById($result['id'], $allSites);
        $site->setSiteStatus($this->findStatusById($result['status'], $allStatuses));
        $site->setSitePingTime($result['speed_download']);
        $site->setSiteLastUpdate(date('h:i:s d-m-Y'));

        $em = $this->getDoctrine()->getManager();
        $em->persist($site);
        $em->flush();

    }


    return $this->render('default/index.html.twig');
}

}

Answer 1

You can optimize it so that it's just 2 DB queries:

    $allSites = $this->get('SiteRepository')->CustomQuery(); // Returns Site array indexed by id

    $allStatuses = $this->get('StatusRepository')->CustomQuery(); // Returns Status array indexed by id

    $results = $this->getScanManager()->scanBatch($allSites); // scans all the sites and returns results array

    $em = $this->getDoctrine()->getManager();

    foreach ($results as $result) {

        $history = new History();
        $history->setSite($allSites[$result['id']]);
        $history->setTime(date('h:i:s d-m-Y'));
        $history->setStatus($allStatuses[$result['status']]);
        $history->setResponseTime($result['speed_download']);

        $em->persist($history);

        $site = $this->findSiteById($result['id'], $allSites);
        $site->setSiteStatus($allStatuses[$result['status']]);
        $site->setSitePingTime($result['speed_download']);
        $site->setSiteLastUpdate(date('h:i:s d-m-Y'));
    }

    $em->flush();

    return $this->render('default/index.html.twig');

Of course this should be adjusted to your app, if you allways ping all the sites, then it's ok, but i you just ping a few, then it might be inefficient, but at least you get the idea.

BTW, if your setter methods return $this, you could write it in a prettier way:

$history->setSite($allSites[$result['id']])
   ->setTime(date('h:i:s d-m-Y'))
   ->setStatus($allStatuses[$result['status']])
   ->setResponseTime($result['speed_download']);