MySQL Vs. Command Chain Raw requests

Question

In the past, I was involved in creating a large number of websites using my own cms / framework, and I developed a simple way to execute requests. I recently started playing with other frameworks such as a code igniter. They offer original queries such as ...

$this->db->query("SELECT * FROM news WHERE newsId=1;"); 

But they also offer a MySQL command chain using PHP methods.

 $this->db->select("*")->from("news")->where("newsId=?"); 

The question arises: what is the main difference and advantages of each option.

I know that the latter options prevent the implementation of MySQL, but to be honest, you can do the same with $this->db->escape() .

So, in the end, from what I see, the last option is only for you to use more letters on the keyboard, this, in your opinion, will slow you down.

Answer 1

The most recent php infrastructure developers use the AR (active record) / DAO (database access object) pattern. Because it is really faster than a raw request. Currently, AR technology is originally built from PDO (php data object).

why is an active recording really unstable?

its true record of requests is the best habit for the developer. But some problem complicates the work

1 .. When we write an insert and update a large query, it is sometimes difficult to match each row value .., but AR makes it easier. you just add the array first and then execute it easily.
2. It does not matter which database you are using.
3. Sometimes it is really difficult to read or write a request if it has many conditions. But in AR you can cascade many objects for 1 request.
4. AR will save your time for repeating approval

Answer 2

I think the implementation of activerecord in codeigniter is suitable for small and simple queries. When you need complex queries with lots of connections, it’s easier to write the query itself.

I don’t think that an extra layer of abstraction will ever give you better performance if you have a certain skill in SQL.

Answer 3

I cannot speak for CodeIgniter (what I saw, it seems to be quite frankly), but there are several reasons why such systems can be used:

• as part of an abstraction layer that supports different DBMS images, where, for example, ->offset(10)->limit(10) automatically generates the correct OFFSET , LIMIT and similar sentences for MySQL vs PostgreSQL, etc.
• as part of the ORM system, where the query result is automatically mapped to model objects of the corresponding class based on the requested tables and columns.
• abstract from the exact names of tables and columns for backward compatibility or installation requirements (for example, the table “news” can actually be called “app1_news” in a particular installation to avoid a collision with another application)
• for processing parameterized queries, as in your example; although they are largely unrelated to this kind of abstraction, they provide more than just slip away because the DBMS (MySQL or something else is used) knows which parts of the query are fixed and which are variables that can be useful for performance, as well as for security