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']);

• jmack: This is great, so your saying that I can create multiple amounts of new History() objects and when I am done $em->flush and all these objects will be inserted as multiple rows in the DB? Regards
• gvf: Exactly that :-)
• jmack: For anyone else researching this for more information look up SYMFONY BATCH INSERT. gvf thank you very much for your time.