Hi, how can I create dynamic SQL insert statement,because I have many functions to create if I manually insert it to table, for example if I want to insert to user table, I’m going to create function for this for insert statement of user, and if I insert to employee table I need to create another function to insert to employee table,but how can I create only one function that can be reusable ?
An ORM is basically a library that lets your store and retrieve data from your DB without having to write the SQL by hand (that’s an over-simplified explanation, google if you want to know more).
There are quite a few available for PHP, but they usually come in one of two flavors: Active Record or Data Mapper. Active Record (AR) is the most common type, and is probably easier to get started with - AR basically means that you work with an objects/arrays that represent individual tables in your DB.
All of them pretty much follow the same patterns. So if you learn the patterns it should be easy enough to understand in any context and every application needs some type of persistence layer.
Using plain jane SQL has its place but it is so 1999 with all the wonderful ORM and ActiveRecord options available. I suggest you try one or a few you just might get hooked at the level of ease to persistent *most data structures.
many also have community extensions to support things like hierarchical structures using adjacency list, closures, nested sets, etc.
It’s worth noting that using an ORM comes at a cost. For a start an ORM has been put in place to make life easier for the developers. One of the side effects of doing this is you tend to bunch functionality in order to cover a range of scenarios. Redudant calls to a database/joining willy-nilly and not using SQL to its full (complex indexes). Scalling a database is hard+expensive. It makes more sense to optimise your SQL, something you won’t be able to do with an ORM.
Sure, using an ORM is going to come with some overhead, but if you’re building a prototype or a small/medium sized app that doesn’t really need to scale up (probably the situation of most forum users here) then trading some efficiency for ease/speed of development is a reasonable compromise.
Depends on the ORM. Idorm expects your tables to follow certain conventions, such as the table’s primary key being called ‘id’, although it can be configured otherwise.
Your example isn’t really a like-for-like comparison, as Idorm is actually using prepared statements behind the scenes. Using an ORM saves you from having to write a bunch of queries for basic CRUD operations.
I have to disagree. An ORM does a lot of heavy lifting for 80% of the usual database work (not to mention taking away the huge effort for the developer of having to normalize data to fit it in the RDBMS). For the other 20%, there is nothing stopping a dev from optimizing the usage of the database based on queries she constructs herself, like through Doctrine’s DBAL query builder (Ok, we can argue about having to have DQL).
If you also notice that certain ORM created queries are causing a bottleneck of any kind, there is also absolutely nothing stopping the dev (or dba) from adding indexes to improve performance too. In other words, just because an ORM is in place, it doesn’t mean all activity to optimize performance is thrown out the window.
That said, there are also some best practices, which should be followed for an ORM to perform at its best too. Like Martin Fowler said, doing the split ORMs do isn’t easy.
Well i’ll give you the like-for-like, because a like-for-like prepared statement is going to be the same size. (prepare, bind, bind, execute) But… I still dont understand how an ORM knows that ‘person’ has a ‘name’, unless the ORM is making redundant DESCRIBE queries to the database. Idorm cant know that ‘person’ has a ‘name’ and ‘age’ unless something tells it that it does [either a redundant query, or the programmer taking time to define the object.]. Person could have ‘trible’ and ‘warkwark’ as fields. It can assume the existance of ‘id’, but thats about it; unless you’re saying that an ORM is dumbfiring structures at the database and hoping that it accepts them…