Friday, November 27, 2015

Passing Array of Conditions To doctrine expr()->orx()

Problem
I needed to construct DQL with a QueryBuilder like this in one of my Symfony project.
[QUERY]... AND WHERE e.type = x OR e.type = Y OR e.type = N [...]
I have list of types in the array but was confused on how to pass the dynamic array to my query builder. List of types will be dynamic.
$qb->andWhere($qb->expr()->orx(CONDITIONS));

Solution:
After some research, I found out about $orX->addMultiple($conditions) method and it fixed my problem.
First I built array of condition using foreach loop and then passed that condition array inside addMultiple method and finally passed orx variable inside addWhere method.
You can build array of condition using foreach like below.
/* $conditions = array('e.type = x', 'e.type = Y', 'e.type = N'); */


foreach($types as $type)
{
   $conditions[] = $qb->expr()->eq('e.type', ':' . $type . '_value');
   $qb->setParameter($type . '_value', $type);
}

 $orX = $qb->expr()->orX();
 $orX->addMultiple($conditions);
 $qb->andWhere($orX);
In this way you can store multiple orx expressions and then add it to andWhere.

Ref: http://stackoverflow.com/a/32250191

Wednesday, November 18, 2015

Fetching Objects from the Database using Doctrine and Symfony

CRUD stands for Create, Read, Update and Delete, the basic operations performed on the database. In this article, I will show you how to perform C(R)UD with Doctrine.

In Doctrine and Symfony, there are multiple ways of accomplishing the same task. This gives you flexibility to pick the best approach suitable for each situation. Therefore we shall explore these different methods for each method of the fetching and object(i.e Read in CRUD).

The act of fetching records/object from the database is very easier using doctrine. Most of these are performed on Doctrine_Table objects, which is obtained by $this->getDoctrine()->getRepository() and passing the model class name to it. When you query for a particular type of object, you always use what's known as its repository.
$repository = $this->getDoctrine()
    ->getRepository('AppBundle:Product');
The AppBundle:Product string is a shortcut you can use anywhere in Doctrine instead of the full class name of the entity (i.e. AppBundle\Entity\Product). As long as your entity lives under the Entity namespace of your bundle, this will work.

Once you have your repository, you have access to all sorts of helpful methods:
find()
The find() method fetches a record by the Primary Key value.
// query by the primary key (usually "id")
$product = $repository->find($id);

findOneBy*()
This is a magic method. You can simply append the column name, and it will search the table by that column.
// dynamic method names to find based on a column value
$product = $repository->findOneByName('foo');
You can also take advantage of the useful findOneBy method to easily fetch objects based on multiple conditions:
// query for one product matching by name and price
$product = $repository->findOneBy(
    array('name' => 'foo', 'price' => 19.99)
);
It fetches only one record.

findBy*()
Another magic method, which works similarly, but fetches multiple rows. It returns a Doctrine_Collection object.
// find a group of products based on an arbitrary column value
$products = $repository->findByPrice(19.99);
You can also take advantage of the useful findBy method to easily fetch objects based on multiple conditions:
// query for all products matching the name, ordered by price
$products = $repository->findBy(
    array('name' => 'foo'),
    array('price' => 'ASC')
);

DQL
It stands for Doctrine Query Language. It is actually a major feature. Most advanced queries are performed using DQL. You can use Doctrine's native SQL-like language called DQL to make a query for this:
$em = $this->getDoctrine()->getManager();
$query = $em->createQuery(
    'SELECT p
    FROM AppBundle:Product p
    WHERE p.price > :price
    ORDER BY p.price ASC'
)->setParameter('price', '19.99');

$products = $query->getResult();
// to get just one result:
// $product = $query->setMaxResults(1)->getOneOrNullResult();
If you're comfortable with SQL, then DQL should feel very natural. The biggest difference is that you need to think in terms of "objects" instead of rows in a database. For this reason, you select from the AppBundle:Product object (an optional shortcut for AppBundle\Entity\Product) and then alias it as p.
The getResult() method returns an array of results. To get only one result, you can use getOneOrNullResult():
$product = $query->setMaxResults(1)->getOneOrNullResult();

Doctrine's Query Builder
Instead of writing a DQL string, you can use a helpful object called the QueryBuilder to build that string for you. This is useful when the actual query depends on dynamic conditions, as your code soon becomes hard to read with DQL as you start to concatenate strings:
$repository = $this->getDoctrine()
    ->getRepository('AppBundle:Product');

// createQueryBuilder automatically selects FROM AppBundle:Product
// and aliases it to "p"
$query = $repository->createQueryBuilder('p')
    ->where('p.price > :price')
    ->setParameter('price', '19.99')
    ->orderBy('p.price', 'ASC')
    ->getQuery();

$products = $query->getResult();
// to get just one result:
// $product = $query->setMaxResults(1)->getOneOrNullResult();
The QueryBuilder object contains every method necessary to build your query. By calling the getQuery() method, the query builder returns a normal Query object, which can be used to get the result of the query.
Source: http://symfony.com/doc/current/book/doctrine.html#querying-for-objects

Friday, November 13, 2015

How To TRUNCATE TABLE With Symfony And Doctrine?

There are two possibilities for bulk deletes with Doctrine. You can either issue a single DQL DELETE query or you can iterate over results removing them one at a time.

Example of single DQL Delete query:
 $em = $this->getDoctrine()->getManager();
 $em->createQuery('DELETE FROM AppBundle\Entity\Customer')->execute();

It is very simple to truncate or empty the table from mysql using Symfony and Doctrine.

Note: If you have relations you should be careful to handle the linked entities.
Ref: http://doctrine-orm.readthedocs.org/en/latest/reference/batch-processing.html#dql-delete

Wednesday, November 4, 2015

How To Upload Image To Remote Server With cURL And PHP?

If you want to upload images to an external server which is uploaded to your site by a client, you are at the right tutorial.

cURL can do just about anything that a normal web browser can do including send a file via a post request.

CurlFile should be used to upload files with CURLOPT_POSTFIELDS. The curl API will be modified to look for objects of type CurlFile. The file given to CurlFile will not be opened/read until curl_setopt() call.

Alternative 1: Send a file with php/cURL via POST using absolute file uploaded path
$file_name_with_full_path = realpath('./upload/example.png');
 
$curl_handle = curl_init("https://example.com");
 
curl_setopt($curl_handle, CURLOPT_POST, 1);
$args['file'] = new CurlFile($file_name_with_full_path, 'image/png');
curl_setopt($curl_handle, CURLOPT_POSTFIELDS, $args); 
curl_setopt($curl_handle, CURLOPT_RETURNTRANSFER, true);

 
//execute the API Call
$returned_data = curl_exec($curl_handle);
curl_close ($curl_handle);

echo $returned_data;


Alternative 2: Send a file with php/cURL via POST using form file field: Without CurlFile file uploading function
//index.php
<?php
if(isset($_POST['check_submit']))
{ 
    $tmpfile = $_FILES['assets']['tmp_name'];
    $filename = basename($_FILES['assets']['name']); 
    $filetype = $_FILES['assets']['type']; 
    
/* 
As the @ format is now deprecated. Use curl_file_create function instead as below
       $POST_DATA = array(
   'file' => '@'.$tmpfile.';filename='.$filename
  );
*/

    $POST_DATA = array(
   'file' => curl_file_create($tmpfile, $filetype, $filename)
  );

    // Connecting to external api via cURL
    $curl_handle = curl_init("https://example.com");  
    curl_setopt($curl_handle, CURLOPT_POST, 1);
    curl_setopt($curl_handle, CURLOPT_POSTFIELDS, $POST_DATA); 
    curl_setopt($curl_handle, CURLOPT_RETURNTRANSFER, true);
   
    //execute the API Call
    $returned_data = curl_exec($curl_handle);
    curl_close ($curl_handle);
 
    echo $returned_data;
}
?>


<form method="POST" action="" enctype="multipart/form-data">

<p>Select a file to upload : </p>

<input type="file" name="assets">

<input type="submit" name="check_submit"/>

</form>

This API is both inconvenient and insecure, it is impossible to send data starting with '@' to the POST, and any user data that is being re-sent via cURL need to be sanitized so that the data value does not start with @. In general, in-bound signalling usually vulnerable to all sorts of injections and better not done in this way.

Instead of using the above method, the following (Alternative 3) should be used to upload files with CURLOPT_POSTFIELDS:

Alternative 3: Send a file with php/cURL via POST using form file field: Recommended
Screenshot:
//index.php
<?php
if(isset($_POST['check_submit']))
{ 
    $tmpfile = $_FILES['assets']['tmp_name'];
    $filename = basename($_FILES['assets']['name']); 
    $filetype = $_FILES['assets']['type']; 

    // Connecting to external api via cURL
    $curl_handle = curl_init("https://example.com");  
    curl_setopt($curl_handle, CURLOPT_POST, 1);
    $args['file'] = new CurlFile($tmpfile, $filetype, $filename);
    curl_setopt($curl_handle, CURLOPT_POSTFIELDS, $args); 
    curl_setopt($curl_handle, CURLOPT_RETURNTRANSFER, true);
   
    //execute the API Call
    $returned_data = curl_exec($curl_handle);
    curl_close ($curl_handle);
 
    echo $returned_data;
}
?>

<form method="POST" action="" enctype="multipart/form-data">

<p>Select a file to upload : </p>

<input type="file" name="assets">

<input type="submit" name="check_submit"/>

</form>
The important function to handle file post via curl is CurlFile function. The syntax of CurlFile function is shown below:
/**
* @param string $name File name
* @param string $mimetype Mime type, optional
* @param string $postfilename Post filename, defaults to actual filename
*/
$args['file'] = new CurlFile($name, $mimetype = '', $postfilename = '');

Ref: https://wiki.php.net/rfc/curl-file-upload
http://php.net/manual/en/class.curlfile.php
http://stackoverflow.com/a/29122849