Symfony model layer tips and tricks – A PHP Framework – Part 3

I had a tough time searching for examples over the net for all kind of symfony queries. Though symfony is well documented here, I didn’t find enough examples there.

If you have landed up on this post straight and are new to symfony, you may want to first read previous posts:
Getting started with Symfony – A PHP Framework – Part 1
How to build a login-registration system using Symfony – A PHP Framework – Part 2

If you are well equipped with the basics, read on

Select Query:
Lets start with the few basic example queries.

  1. Extract list of all users from the users table

    $c = new Criteria();
    $this->resultSet = UsersPeer::doSelect($c);

    Now lets see how can we use this $resultset within the model layer and in the template files.
    To use the resultset within model layer, we can do something like this:

    foreach($this->resultset as $rs) {
        $uid = $ps->getId();
        $fname = $ps->getFirstName();
        $lname = $ps->getLastName();
    }

    To use the resultset in template files (viewer layer), we can do something like this:

    foreach($resultset as $rs) {
        $uid = $ps->getId();
        $fname = $ps->getFirstName();
        $lname = $ps->getLastName();
    }

  2. Extract list of first 10 users only

    $c = new Criteria();
    $c->setLimit(10);
    $this->resultSet = UsersPeer::doSelect($c);

  3. Extract list of 10 users starting from 100th user

    $c = new Criteria();
    $c->setOffset(100);
    $c->setLimit(10);
    $this->resultSet = UsersPeer::doSelect($c);

  4. Extract user data where username = ‘imoracle’

    $c = new Criteria();
    $c->add(UsersPeer::USERNAME,”imoracle”);           // Note USERNAME is all in caps, even if your column name is in small case
    $this->resultSet = UsersPeer::doSelect($c);

  5. Another approach to do a select query

    $c = new Criteria();
    $c->add(UsersPeer::USERNAME,”imoracle”);
    $resultSet = UsersPeer::doSelectRS($c);

    To access this resultset in the model layer itself, we can do something like this:

    while($resultSet->next()) {
        $uid = $resultSet->get(1);
        $fname = $resultSet->get(2);
        $lname = $resultSet->get(3);
    }

    Where get(1) fetches the 1st column for you of the resultset. Hence $resultSet->get(n) will fetch you the nth column of the resultSet.

Insert Query:
Lets see a few quick examples for inserting a new row in a table.

  1. Insert a user entry in user table

    $new_user = new Users();
    $new_user->setUserName($uname);
    $new_user->setFirstName($fname);
    $new_user->setLastName($lname);
    $new_user->setPassword(md5($pass));

    $new_user->save();
    $new_user_id = $new_user->getId();

    Finally we get the last inserted user row id.

Update Query:
Symfony provides nothing special for the update queries. The same above format works for updating a row as well. Symfony is smart enough to analyze whether its a select or update query.

  1. First Method for Update Query (Recommended)

    $c = new Criteria();
    $c->add(UsersPeer::USERNAME,”imoracle”);
    $c->add(UsersPeer::PASSWORD,$newpass);
    $res = UsersPeer::doUpdate($c);

    In the above example we chose to update the password for user with username “imoracle”.

  2. Another Method for Update Query

    $user = new User();
    $user->setNew(false);
    $user->setId($uid);
    $user->setPassword($newpass);
    $user->save();

    Important code in above 5 lines is the 2nd line. Without this line it will add a new row and not update. Also another drawback which I found in this approach is that in third line you need to give the PK (primary key) of the table. Like our 1st method you cannot give username in the third line.

    Finally I haven’t tried and tested this method enough. So I will not recommend this.

Delete Query:
Its basically the same above methods, with change of final call.

  1. Delete a user’s account where username = ‘imoracle’

    $c = new Criteria();
    $c->add(UsersPeer::USERNAME,’imoracle’);
    $res = UsersPeer::doDelete($c);

How to alter a table in symfony?

  1. Method 1 (Non-standard)
    A drawback which I have seen in symfony till now is that, when you try to alter a table by adding new columns in your schema.yml, It flushes the data from the table and gives you an altered table with no data. This is really bad, if you don’t know this behavior and move ahead altering the table. Though Symfony provider propel-dump-data to handle alter tables, but it is still quite buggy and slow for big databases. The clean and smooth way to achieve this is:

    1. Take a mysql dump with following options:
      mysqldump -h hostname -u username -p password -c -t dbname > dbname.sql
      Do not forget to use option -c and -t, without which it can land you in a problem
    2. Update your schema.yml file, with new columns which you want to add and run
      sudo symfony propel-build-all
    3. Restore the database content using following command:
      mysql -h hostname -u username -p password dbname < dbname.sql

    And you have your new altered table without any loss of data.

  2. Method 2 (Standard)
    If you are afraid of your data being flushed by the stupid symfony, then you may choose not to touch your database using propel at all. Here is what you can do in such a case.

    1. Update your schema.yml file and run
      sudo symfony propel-build-model
    2. Then manually go and alter the table in the database.

    Here propel-build-model will only rebuild the ORM layer to incorporate your schema.yml changes. However propel-build-all not only builds the ORM layer, but also actually creates the table in the database (flushing the data)

In future I shall keep adding more SQL queries on this post, as and when I write them using symfony. So keep glued to this one 😉

  • Sowmya

    Interesting ! Looking forward to more posts from you 🙂

  • manish

    Hey,

    Which do think is better php framework. Cake PHP or Symphony. Can you give your views.

  • I haven’t used either for a production huge system. Had to work on symfony back in office, so I know that it can scale well. Some of the big properties like Yahoo Bookmarks etc use symfony.

    Cake PHP is good as far as I have heard, but i guess symfony have the required strength and architecture to scale.

    It’s fair if you can take views from someone who has used both extensively.

  • SEO ZONE is a search engine optimization(seo) firm, provides seo, seo article, seo tools,seo news and seo related informations,helping companies leverage the internet to increase revenues and profits.

  • Nayan

    You saved my lots of search work…
    Thanks very much yarrrrrr

  • hi, im just started learning SF and your article helps a lot to me..
    i have a question, if we are going to change the schema.yml, then we regenerate again using command “doctrine:build-all” then what happen to the custom model? will it be removed also? thanx..

  • this is my code of custom paging.
    i wanna use of offest to set the pointer at time of my clicking on first/next/prev/last but it shows me just blank screen rather than records starts from offset to limit.. solve my problem ….

    THIS IS MY CODE : OF ACTION PAGE

    total_page=floor($totalRows/$limit); //No of pages
    $start_page=1; //Set Start Page
    $this->fst=$start_page; //Set First Page
    $this->lst=$this->total_page; //Set Last Page
    $count=1;

    if(!isset($this->cur)){ //Set Current Page
    $this->cur=1;
    }

    $this->nxt=$this->getRequestParameter(‘nxt’); //Next Page
    if(!isset($this->nxt)){
    $this->nxt=$this->cur;
    }else{
    if($this->nxttotal_page){
    $this->nxt++;
    }
    $this->cur=$this->nxt;
    $this->prev=$this->nxt;
    $this->fst=”;
    }

    $this->prev=$this->getRequestParameter(‘prev’); //Previous Page
    if(!isset($this->prev)){
    $this->prev=$this->cur;
    //$this->cur=$this->prev;
    }else{
    if($this->prev>$start_page){
    $this->prev–;
    }
    $this->cur=$this->prev;
    $this->nxt=$this->prev;
    $this->fst=”;
    }
    $offset=$this->cur*$limit;

    $c = new Criteria();
    $c->addAscendingOrderByColumn(CsvPeer::ID);
    $c->setLimit($limit);
    $c->setOffset($offset);
    $this->countRows=CsvPeer::doCount($c);
    if($this->countRows>1){
    $fetchRows=CsvPeer::doSelect($c);
    $this->getRows=$fetchRows;
    }
    }
    }
    ?>