Blog. Yii. Advice on Yii. Сhapter 7.

Advice on Yii. Сhapter 7.

Basics of working with a database, we take Open Real Estate as an example.

1. findByPk.

   1. $id = 25;       
   2. $result = Apartment::model()->findByPk($id);    
   3. echo $result->title_en;    

   Result:

   Apartment on Novy Arbat St.

    

2. findAllByPk.

   1. $ids = array(25, 26, 27);       
   2. $result = Apartment::model()->findAllByPk($ids);    
   3.     
   4. foreach ($result as $item) {    
   5.     echo '<pre>';    
   6.     print_r($item->title_en);    
   7.     echo '</pre>';    
   8. }  

   Result:

   Apartment on Novy Arbat St.

   Apartment, Bolshaya Polyanka St., 28

   1-Bedroom Apartment near Prospekt Mira metro station

    

3. findByAttributes.

   1. $result = Apartment::model()->findByAttributes(array('title_en' => 'Apartment on Novy Arbat St.'));    
   2.                 
   3. echo $result->title_en;    
   4. echo '<br>'.$result->id;    

   Result::

   Apartment on Novy Arbat St.

   25

    

4. findAllByAttributes.

   1. $result = Apartment::model()->findAllByAttributes(array('title_en' => array('2-Bedroom Apartment within a minute walk from Paveletskaya metro station', 'Apartment, Bolshaya Polyanka St., 28')));    
   2.             
   3. foreach ($result as $item) {    
   4.     echo '<pre>';    
   5.     print_r($item->title_en);    
   6.     print_r('<br>'.$item->id);    
   7.     echo '</pre>';    
   8. }   

   Result:

   Apartment, Bolshaya Polyanka St., 28

   26

   2-Bedroom Apartment within a minute walk from Paveletskaya metro station

   29

   One more example:

   1. $result = Apartment::model()->findAllByAttributes(array('id' => array(19, 20), 'title_en' => array('Apartment on Novy Arbat St.', 'House on Trubetskaya St.')));    
   2.             
   3. foreach ($result as $item) {    
   4.     echo '<pre>';    
   5.     print_r($item->title_en);    
   6.     print_r('<br>'.$item->id);    
   7.     echo '</pre>';    
   8. }   

   As a result we will have the following::

   House on Trubetskaya St.

   19

    

5. find.

   1. $numMax = 19;       
   2. $result = Apartment::model()->find('id < :numMax', array(':numMax' => $numMax));    
   3.     
   4. echo $result->title_en;    
   5. echo '<br>'.$result->id;  

   Result:

   2-Bedroom Apartment, Aviamotornaya St.

   18

    

6. findAll.

   1. $numMax = 20;       
   2. $result = Apartment::model()->findAll('id < :numMax', array(':numMax' => $numMax));    
   3.         
   4. foreach ($result as $item) {    
   5.     echo '<pre>';    
   6.     print_r($item->title_en);    
   7.     print_r('<br>'.$item->id);    
   8.     echo '</pre>';    
   9. }   

   or:

   1. $numMax = 20;       
   2. $result = Apartment::model()->findAll("id < {$numMax}");    
   3.         
   4. foreach ($result as $item) {    
   5.     echo '<pre>';    
   6.     print_r($item->title_en);    
   7.     print_r('<br>'.$item->id);    
   8.     echo '</pre>';    
   9. }   

   Result:

   2-Bedroom Apartment, Aviamotornaya St.

   18

   House on Trubetskaya St.

   19

   The same way we can choose all elements that are sorted by the title:

   1. $result = Apartment::model()->findAll(array('order' => 'title_en ASC'));  

    

7. findBySql.

   1. $result = Apartment::model()->findBySql('SELECT id FROM {{apartment}} WHERE square = 60 AND title_en = "2-Bedroom Apartment, Aviamotornaya St."');        
   2. echo $result->id;     

   Result:

   18

    

8. findAllBySql.

   1. $result = Apartment::model()->findAllBySql('SELECT id, title_en FROM {{apartment}} WHERE square <= 50 AND square >= 40');    
   2.                 
   3. foreach ($result as $item) {    
   4.     echo '<pre>';    
   5.     print_r($item->title_en);    
   6.     print_r('<br>'.$item->id);    
   7.     echo '</pre>';    
   8. }   

   Result:

   Apartment, Pskovskaya St.

   21

   Apartment on Novy Arbat St.

   25

    

9. countBySql.

   1. $result = Apartment::model()->countBySql('SELECT COUNT(id) FROM {{apartment}} WHERE square <= 50 AND square >= 40');    
   2. echo $result;   

   Result:

   2

    

10. count.

    1. $result = Apartment::model()->count('id < 25');    
    2. echo $result;   

    or:

    1. $result = Apartment::model()->count('id < :num', array(':num' => 25));    
    2. echo $result;   

    Result:

    Apartment on Novy Arbat St.

     

11. exists.

    1. $result = Apartment::model()->exists('id < :num', array(':num' => 25));    
    2. echo var_dump($result);   

    Result:

    bool(true)

    1. $result = Apartment::model()->exists('id < :num', array(':num' => 1));    
    2. echo var_dump($result);    

    Result:

    bool(false)

     

12. UpdateByPK.

    1. $ids = array(18, 19, 20);       
    2. $result = Apartment::model()->updateByPk($ids, array('title_en' => 'New title'));  

    Result:

    It returns true or false.

    If it has been successfully executed, the value of a title_en field for listings with ID of 18, 19 and 20 in the table is displayed as a "New title".

     

13. updateAll.

    1. $result = Apartment::model()->updateAll(array('title_en' => 'New title'), 'title_en = :title', array(':title' => '2-Bedroom Apartment, Aviamotornaya St.'));   

    Result:

    It returns true or false.

    If it has been successfully executed, listings with a title_en field which have a value of '2-Bedroom Apartment, Aviamotornaya St.' will have a new value that is a "New title".

     

14. deleteByPk.

    1. $result = Apartment::model()->deleteByPk(18);   

    Result:

    It returns true or false.

    If it has been successfully executed, the listing with ID of 18 will be deleted.

     

15. deleteAll.

    1. $result = Apartment::model()->deleteAll('title_en = :title', array(':title' => 'House on Trubetskaya St.'));   

    Result:

    It returns true or false.

    If it has been successfully executed, the listing with a title_en field which has a value of 'House on Trubetskaya St.' will be deleted.

     

Discuss the article in the forum