Yii allows you to enter raw SQL in various ways. But more importantly Yii also allows you to write Yii PHP code in ways that resemble SQL without having to write any real SQL. First let’s cover how to execute SQL statements in Yii:
db->createCommand("UPDATE article SET promoted=’yes’ WHERE id=7")->execute();
So here we’re creating an SQL command and executing it. The command specifically is setting an article to promoted where the article id # is 7.
We’re not going to go too deep into the theory and syntax used here because our goal is simply to point out how Yii automates/simplifies/abstracts what rudimentarily could take many lines of code as you saw in the previous tutorial. The basic idea is that Yii has global tools you can use all starting with “Yii::app()”. So in this case, we’re accessing Yii’s database tool with the keyword “db”. Then we use it’s createCommand() method to declare some SQL to use and then the execute() method to execute it.
Now let’s see how to select data from the database:
$rows = Yii::app()->db->createCommand("SELECT * FROM article”)->query();
Here we simply extracted all the rows from the article table, assigned them to the $rows variable (which is now an array containing those rows), and executed the statement in this case with a query() method instead of an execute() method.
So that’s how you can execute raw SQL statements in Yii. It’s worth pointing out what’s called “fluid syntax” which is being used here. Notice how the query() method is attached to the createCommand() method with “->” in between, and even notice how “->” is used 2 times before to connect back to the “db” tool and all the way back to “Yii::app()”. What’s happening here is very cool, and one of my favorite things I first got the hang of. Basically each method is a method of an object returned from the previous method. So the createCommand() method simply returns a “command” object, and it has a query() method. You can make these chains on and on if it makes sense.