How escape function with single and double quotation in?

Question

i am trying to use doctrine date format function, in orded to compare date in m-d with today m-d don't know how to handle escape in my addWhere, here is a snippet:

->andWhere('date("m-d", strtotime('r.dateOfDeath')) = :now')
->setParameter('now',\date("m-d", time()))

Did you try either escaping them via \ (backslash) or just changing to one type of quotation? — kero, Apr 17 '15 at 14:35
yes i did, i think the problem start from date function i have tried to escape it using (\) and it didn't resolved the problem. — Amr, Apr 17 '15 at 14:38

score 0 · Answer 1 · edited May 23 '17 at 12:25

0

If your goal is to search by dateOfDeath with a specific month/day combination, you will probably find Doctrine lacking.

In MySQL you would generally do WHERE MONTH(r.dateOfDeath) = MONTH(NOW()) AND DAY(r.dateOfDeath) = DAY(NOW()) To make it easy for yourself you could just use a Native MySQL query, see ex. Execute raw SQL using Doctrine 2

In Doctrine the DAY, NOW and MONTH functions are missing. The now part you can solve with a parameter. The DAY AND MONTH functions you'd need to implement through a bundle like: https://github.com/beberlei/DoctrineExtensions

Another possible solution might be to simply make dateOfDeath a string and do a ->andWhere("r.dateOfDeath LIKE '%:md')->setParameter('md', date('m-d'))

edited May 23 '17 at 12:25

Community

1
1

answered Apr 17 '15 at 14:42

Rein Baarsma

1,466
13
22

my problem is about comparing record date in format of `m-d` with now date in format of `m-d`, the use of DateTime will use time. – Amr Apr 17 '15 at 14:52
I updated the answer to better reflect your question. – Rein Baarsma Apr 17 '15 at 15:06
Many thanks @Rein i need to vote up for your answer but i need 15 reputation score :) – Amr Apr 18 '15 at 08:35

score 0 · Answer 2 · answered Apr 18 '15 at 08:34

I have resolve the problem using doctrine extension [see]: https://github.com/uvd/Doctrine/tree/master/UVd/DoctrineFunction "UVD Doctrine"

config.yml

 orm:
    default_entity_manager: default
    entity_managers:
        default:
            connection: default
            dql:
                datetime_functions:
                    DATE_FORMAT: AppBundle\Extension\Doctrine\DateFormat

Usage

register function

 protected function registerDateFunctions() 
 {
    $emConfig = $this->getEntityManager()->getConfiguration();
    $emConfig->addCustomStringFunction('DATE_FORMAT','AppBundle\Extensions\Doctrine\DateFormat');
  }

create Query Builder

 public function getAllRecords()
{
    $this->registerDateFunctions();
    $qb = $this->createQueryBuilder('r')
        ->select('r')
        ->addSelect('d')
        ->addSelect('b.country')
        ->leftJoin('r.religion', 're')
        ->leftJoin('r.country_of_birth', 'b')
        ->leftJoin('r.country_of_death', 'd')
        ->where('r.status = :status')
        ->setParameter(':status', 1)
        ->andWhere("DATE_FORMAT(r.dateOfDeath,'%m-%d') = :now")
        ->setParameter(':now',\date("m-d", time()))
        ->getQuery();
    return $qb->getArrayResult();

}

credit: "http://www.uvd.co.uk/blog/using-mysqls-date_format-in-doctrine-2-0/"

How escape function with single and double quotation in?

2 Answers2