Yii DB built in functions – Reference

Quick reference – Yii inbuilt functions – DB related
—————————————————-

###### Active Record functions: ############
Different ways to query
// 1st way
$criteria=new CDbCriteria;
$criteria->select=’title’; // only select the ‘title’ column
$criteria->condition=’postID=:postID’;
$criteria->params=array(‘:postID’=>10);
$post=Post::model()->find($criteria);

// 2nd way
$post=Post::model()->find(array(
‘select’=>’title’,
‘condition’=>’postID=:postID’,
‘params’=>array(‘:postID’=>10),
));

//Other ways
// Find All
// find all rows satisfying the specified condition
$posts=Post::model()->findAll($condition,$params);
// find all rows with the specified primary keys
$posts=Post::model()->findAllByPk($postIDs,$condition,$params);
// find all rows with the specified attribute values
$posts=Post::model()->findAllByAttributes($attributes,$condition,$params);
// find all rows using the specified SQL statement
$posts=Post::model()->findAllBySql($sql,$params);

User::model()->findAll(‘first_name=? AND last_name=?’, array(‘Paul’, ‘Smith’));
User::model()->findAllByAttributes(array(‘first_name’=>’Paul’, ‘last_name’=>’Smith’));

// find one
$post=Post::model()->find($condition,$params);
$post=Post::model()->find(‘postID=:postID’, array(‘:postID’=>10));

// find by primary key
$post=Post::model()->findByPk($postID,$condition,$params);

// find by attributes
$post=Post::model()->findByAttributes($attributes,$condition,$params);
$post=Post::model()->findByAttributes();

// find by specified SQL statement
$post=Post::model()->findBySql($sql,$params);

// count
$n=Post::model()->count($condition,$params);

// CREATE : creating record
$post=new Post;
$post->title=’sample post’;
$post->content=’content for the sample post’;
$post->create_time=time(); // $post->create_time=new CDbExpression(‘NOW()’);
$post->save();

// UPDATE: Updating Record
$post=Post::model()->findByPk(10);
$post->title=’new post title’;
$post->save(); // save the change to database

// DELETE: Delete record
$post=Post::model()->findByPk(10); // assuming there is a post whose ID is 10
$post->delete();

// Scopes
how to make use of scopes
In your model make a entry as below
// scope to get the 5 records of status=1 and recently updated
public function scopes()
{
return array(
‘published’=>array(
‘condition’=>’status=1’,
),
‘recently’=>array(
‘order’=>’create_time DESC’,
‘limit’=>5,
),
);
}

And then use the below statement to query.
$posts=Post::model()->published()->recently()->findAll();

// write in model class to to some ops before saving
public function beforeSave() {
if ($this->isNewRecord)
$this->created = new CDbExpression(‘NOW()’);
else
$this->modified = new CDbExpression(‘NOW()’);
// anything else.. like, date modification, time modification, etc..

return parent::beforeSave();
}

######## Query builder functions: ############
// NEXT : sql command writing
$command = Yii::app()->db->createCommand(‘SELECT * FROM tbl_user’);

// fetch row
$user = Yii::app()->db->createCommand()
->select(‘id, username, profile’) // to fetch all use ->select(‘*’)
->from(‘tbl_user’) // for multiple table use as ->from(array(‘tbl_user’, ‘tbl_profile’))
->where(‘id=:id’, array(‘:id’=>$id))
->queryRow();
OR
$row = Yii::app()->db->createCommand(array(
‘select’ => array(‘id’, ‘username’),
‘from’ => ‘tbl_user’,
‘where’ => ‘id=:id’,
‘params’ => array(‘:id’=>1),
))->queryRow();

// Fetch row using join
$user = Yii::app()->db->createCommand()
->select(‘id, username, profile’)
->from(‘tbl_user u’)
->join(‘tbl_profile p’, ‘u.id=p.user_id’) // join, leftJoin, rightJoin, crossJoin, naturalJoin
->where(‘id=:id’, array(‘:id’=>$id))
->queryRow();

// fetch all
$user = Yii::app()->db->createCommand()
->select(‘id, username, profile’)
->from(‘tbl_user’)
->where(‘id=:id’, array(‘:id’=>$id))
->limit(10, 20) // limit 10 and offset 20
->queryAll();

$users = Yii::app()->db->createCommand()
->select(‘*’)
->from(‘tbl_user’)
->queryAll();

// display SQL statement
$sql = Yii::app()->db->createCommand()
->select(‘*’)
->from(‘tbl_user’)
->text;

// INSERT
// build and execute the following SQL:
// INSERT INTO `tbl_user` (`name`, `email`) VALUES (:name, :email)
$command->insert(‘tbl_user’, array(
‘name’=>’Tester’,
’email’=>’tester@example.com’,
));

// UPDATE `tbl_user` SET `name`=:name WHERE id=:id
$command->update(‘tbl_user’, array(
‘name’=>’Tester’,
), ‘id=:id’, array(‘:id’=>1));

// DELETE FROM `tbl_user` WHERE id=:id
$command->delete(‘tbl_user’, ‘id=:id’, array(‘:id’=>1));

// NEXT
// create a new entry with time modification example
$post=new Post;
$post->title = “Some title”;
$post->desciption = “Some description”;
$post->create_time=new CDbExpression(‘NOW()’);
// $post->create_time=’NOW()’; will not work because
// ‘NOW()’ will be treated as a string
$post->save();

Tips to make your website run faster

http://developer.yahoo.com/performance/rules.html

  • Minimize HTTP Requests
  • Images : CSS Sprites are the preferred method for reducing the number of image requests. Combine your background images into a single image and use the CSS background-image and background-position properties to display the desired image segment.
  • Use a Content Delivery Network
  • Add an Expires or a Cache-Control Header
  • Gzip Components
  • Put Stylesheets at the Top
  • Put Scripts at the Bottom : The problem caused by scripts is that they block parallel downloads.
  • Make JavaScript and CSS External: Using external files in the real world generally produces faster pages because the JavaScript and CSS files are cached by the browser.
  • Split Components Across Domains : The HTTP/1.1 specification suggests that browsers download no more than two components in parallel per hostname. If you serve your images from multiple hostnames, you can get more than two downloads to occur in parallel.
  • Reduce DNS Lookups : DNS lookups take time. Reducing the number of unique hostnames has the potential to reduce the amount of parallel downloading that takes place in the page. Avoiding DNS lookups cuts response times, but reducing parallel downloads may increase response times.
  • Minify JavaScript and CSS
  • Remove Duplicate Scripts : Due to large number of developers and size of project its possible a script etc may get loaded multiple times.
  • Don’t Scale Images in HTML
  • Avoid Empty Image src
  • Use Profiling tools to identify which parts of your code are consuming more time and more memory
  • Fetch Data in Parallel : (multi curl for PHP, node.js, etc)
  • Use EXPLAIN statement to analyze your SQL queries. Based on that add proper indexes
  • Prepared statements in SQL need to be parsed just once
  • Use Opcode Cache which will save the server from parsing your php again
  • Use Memcache DB for storing frequent calls to the database
  • Use MySQL Master Slave for faster read access
  • See if schema needs to be split vertically or horizontally for faster access

Salted Password Hashing – Doing it Right

http://crackstation.net/hashing-security.htm

Salting inherently makes your user’s passwords better automatically by prepending or appending a random string. So lookup tables become useless.
But if the password hash and salt table is compromised then salt will not help (as in LinkedIn case). LinkedIn should have been using bcrypt, which is an adaptive hash that would have slowed the brute force time down to the order of 10s of hashes per second.

Cookie vs Sessions

Both cookies and sessions are available to you as a PHP developer, and both accomplish much the same task of storing data across pages on your site. However, there are differences between the two that will make each favourable in their own circumstance.

Cookies can be set to a long lifespan, which means that data stored in a cookie can be stored for months if not years. Cookies, having their data stored on the client, work smoothly when you have a cluster of web servers, whereas sessions are stored on the server, meaning in one of your web servers handles the first request, the other web servers in your cluster will not have the stored information.

Sessions are stored on the server, which means clients do not have access to the information you store about them – this is particularly important if you store shopping baskets or other information you do not want you visitors to be able to edit by hand by hacking their cookies. Session data, being stored on your server, does not need to be transmitted with each page; clients just need to send an ID and the data is loaded from the local file. Finally, sessions can be any size you want because they are held on your server, whereas many web browsers have a limit on how big cookies can be to stop rogue web sites chewing up gigabytes of data with meaningless cookie information.

So, as you can see, each have their own advantages, but at the end of the day it usually comes down one choice: do you want your data to work when you visitor comes back the next day? If so, then your only choice is cookies – if you have any particularly sensitive information, your best bet is to store it in a database, then use the cookie to store an ID number to reference the data. If you do not need semi-permanent data, then sessions are generally preferred, as they are a little easier to use, do not require their data to be sent in entirety with each page, and are also cleaned up as soon as your visitor closes their web browser.

http://www.tuxradar.com/practicalphp/10/1/0

MyISAM vs InnoDB

MyISAM was the default storage engine chosen by MySQL database, when creating a new table. But since 5.5 version, InnoDB is the default storage engine.

The major differences between these two storage engines are :

  • InnoDB supports transactions which is not supported by tables which use MyISAM storage engine. InnoDB table supports foreign keys, commit, rollback, roll-and forward operations
  • InnoDB has row-level locking, relational integrity i.e. supports foreign keys, which is not possible in MyISAM.
  • InnoDB ‘s performance for high volume data cannot be beaten by any other storage engines available.
  • The size of MyISAM table can be up to 256TB, which is huge. InnoDB tables can be upto 64 TB
  • In addition, MyISAM tables can be compressed into read-only tables to save space.

Tables created in MyISAM are known to have higher speed compared to tables in InnoDB. But since InnoDB supports volume, transactions, integrity it’s always a better option which you are dealing with a larger database. It is worth mentioning that a single database can have tables of different storage engines.

MyISAM is good for read-heavy applications, but it doesn’t scale very well when there are a lot of writes. Even if you are updating one field of one row, the whole table gets locked, and no other process can even read from it until that query is finished. MyISAM is very fast at calculating SELECT COUNT(*) types of queries.

InnoDB tends to be a more complicated storage engine and can be slower than MyISAM for most small applications. But it supports row-based locking, which scales better. It also supports some more advanced features such as transactions.

Advantages and Disadvantages of AJAX

Advantages

  • Your page will be more pleasant to use, when you can update parts of it without a refresh, which causes the browser to flicker and the statusbar to run.
  • Because you only load the data you need to update the page, instead of refreshing the entire page, you save bandwidth.

Disadvantages

  • Because the updates are done by JavaScript on the client, the state will not register in the browsers history, making it impossible to use the Back and Forward buttons to navigate between various states of the page.
  • For the same reason, a specific state can’t be bookmarked by the user.
  • Data loaded through AJAX won’t be indexed by any of the major search engines.
  • People using browsers without JavaScript support, or with JavaScript disabled, will not be able to use the functionality that you provide through AJAX.

Rules of XHTML

  • Open with proper Doctype and namespace
  • Declare content type using META content element
  • Write all elements and attribute names in lowercase
  • Quote all attribute values
  • Assign values to all attributes
  • Close all tags
  • Close “emtpy” tags with space and slash
  • Do not put double dashes inside a comment
  • Ensure that less than and ampersand are < and &

SOAP vs REST

http://spf13.com/post/soap-vs-rest

SOAP:
– Transport Independant (REST requires HTTP. SOAP can work on HTTP, SMTP, etc)
– Built in Error Handling
– Only XML
– Provides good ACID Transactions.
– Exposes pieces of application logic , Exposes operations
– Enterprise level security since it supports WS-Security
– Suitable for banking applications

REST
– Easier Learning Curve
– Efficient and Fast : Smaller message format
– Can use XML, JSON, etc
– REST gives access to named resources