Paging in Symfony

In this tutorial we will learn how to implement Paging in Symfony, the process is fairly simple and easy to implement, just a little know how to PHP, Symfony and Doctrine is required.
First the code we will write in Controller or Symfony module Action class.

public function executePaging(sfWebRequest $request){

        $results_per_page=10;
        $set_first_page=1;

        $this->pagination = new sfDoctrinePager('User', $results_per_page);
        $this->pagination->setQuery(Doctrine::getTable('User')->createQuery('u'));
        $this->pagination->setPage($request->getParameter('page', $set_first_page));
        $this->pagination->init();
        
    }

now the code we write in View or the ethodNameSuccess in our case PagingSuccess.php

<?php foreach ($pagination->getResults() as $users): ?>

<?php endforeach; ?> 
  
<?php echo $users->getId(); ?>
  
<?php echo $users->getFirstName(); ?>
  
<?php echo $users->getLastName(); ?>
  
<?php echo $users->getEmail(); ?>
 
<?php echo link_to('<<', 'myModule/Paging?page='.$pagination->getFirstPage()) ?>
<?php if ($pagination->haveToPaginate()): ?>
<?php $links = $pagination->getLinks(); foreach ($links as $page): ?>
<?php echo ($page == $pagination->getPage()) ? $page : link_to($page, 'myModule/Paging?page='.$page) ?>
<?php endforeach ?>
<?php endif ?>
<?php echo link_to('>>', 'myModule/Paging?page='.$pagination->getLastPage()) ?>

try to implement it and you will see its very easy :)

Continue Reading...

Symfony Doctrine Native SQL

in my earlier post, Symfony Doctrine Execute Raw SQL i execute Raw or Native SQL quries, however method describe previously can only perform SELECT operations, in this post we will execute all type of SQL quires i.e. SELECT, INSERT, UPDATE, DELETE

public function myDBWrapper($query,$type=0){
      $connection=Doctrine_Manager::getInstance()
                  ->getCurrentConnection()->getDbh();
      // Get Connection of Database

      $statement=$connection->prepare($query);
      // Make Statement

      $statement->execute();
      // Execute Query

      if($type==0){
          // Check if it is SELECT query
          
          $results = $statement->fetchAll();
          return $results;
      }

  }

Usage

public function executeMyDBWrapperTest(sfWebRequest $request){

       $insertQuery="INSERT INTO users (username,email)
                       VALUES ('Originative','originative@live.com')";
       $selectQuery="Select * from users";

       $this->myDBWrapper($insertQuery);
       // INSERT data in table
       
       $this->results=$this->myDBWrapper($selectQuery, 1);
       // Fetch data from table save output 
       // which will be displayed in
       // MyDBWrapperTestSuccess.php

   }

Continue Reading...

Symfony Doctrine Delete Row

In this tutorial we will delete a Row from database using Doctrine, lets do it with a simple example

public function executeDelete(sfWebRequest $request){

        Doctrine::getTable('User')
        ->findBy('id', $request->getParameter('id'))
        ->delete();

        $this->redirect('SOME/PAGE');
        $this->setTemplate(false);

    }

Doctrine simplify our CRUD operations, here we just give Id of the record to be deleted and use delete() method or Doctrine to remove that record.

Continue Reading...

Symfony Doctrine Execute Raw SQL

Converting to object oriented can be sometimes difficult as people are not used to it... same is the case with ORM, when you use it there may be some problems for the new comers.

Doctrine an ORM tool, can execute Raw SQL queries beside fully object and partial queries, executing raw sql queries is very simple following PHP code demonstrate it, here we use Doctrine with symfony.

public function executeShowUserInfo(sfWebRequest $request){

     $query = Doctrine_Query::create()
      ->query("select * from user");

     $result = $query->toArray();

     $count = count($result);

     for($i=0;$i<$count;$i++){

         echo $result [$i]["first_name"]
         ." => "
         .$result [$i]["email"]
         .",";

     }
}

here $query->toArray() convert returned response in to array.
Note : it is not recommended to echo from a controller, i do just for demonstration purpose.

Continue Reading...

Symfony Doctrine Row Count

Doctrine is a very powerful ORM for PHP application, this tutorial demonstrate how you can get the number of rows returned by Doctrine.

Suppose we want to check if a user is valid or not, for that we need user name and password, here we are using symfony.

public function executeValidateUser(sfWebRequest $request){

$username = $request -> getParameter("username");
$password = $request -> getParameter("password");

$query = Doctrine_Query::create()
->select("id")
->from("user")
->where("username=$username")
->andWhere("password=$password");

$rowcount= $query -> count();

if( $rowcount == 1 ){

//do something useful, User Authenticated

}
else{

// do something useful here too even thoug User is Not Authenticated :)

}
} 

here we first get the $username and $passowrd from the form submitted using symfony $request->getParamenter() method then we make a Doctrine query and then we check how many results are returned by the Doctrine Query using Doctrine count() method and lastly we check if it is exactly 1 which mean user name and password is correct and user is a valid user if not then the else part work.

Continue Reading...

Symfony Doctrine Print Raw SQL

When you are debugging your code, it is very helpful to know what queries are executing at the database end, it helps a lot to simplify debugging.

Talking about Symfony with Doctrine as ORM, its very simple to know what query Doctrine is passing to the Database server, here is a small example that demonstrate it.

Suppose we want to get the First and Last name of our user and table is user, first we make a query.

$query=Doctrine_Query::create()
   ->select("first_name, last_name")
   ->from("user");

now we get this query using the Doctrine method getSQLQuery()

echo $query->getSQLQuery();
exit();

on the browser we get something like this

SELECT u.id AS u__id, u.first_name AS u__first_name, u.last_name AS u__last_name FROM user u

Continue Reading...