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 ?
Thank you in advance.
Hi jemz,
Reusable code and avoiding duplication are definitely good things to aim for. Have you thought about using an ORM library or something like that?
I am not familiar with this
I think the easiest way for you (though it could get messy if you start adding a lot of them) is to simply have the function accept arguments. eg.
function my_query($table_name) {
.....
my_query("employee");
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.
Here’s some quick examples (using Idorm):
<?php
// Retrieving data
$person = ORM::for_table('person')->find_one(5);
echo $person->name;
// Saving data
$person = ORM::for_table('person')->create();
$person->name = 'Joe Bloggs';
$person->age = 40;
$person->save();
Thank you for this, are you also using this in your projects ?
So explain to me how
$person = ORM::for_table('person')->create();
$person->name = 'Joe Bloggs';
$person->age = 40;
$person->save();
Saves code,overhead, or repeated queries [How does the ORM know what ‘person’ looks like?] over
mysqli_query("INSERT INTO person('name','age') VALUES('Joe Bloggs',40)");
?
If you’re not familiar with ORMs and ActiveRecords here are the major php ones to check out.
My preference for ActiveRecord is Eloquent and Mapper Doctrine.
The wonderful thing about learning an ORM and/or ActiveRecord is all the terminology transitions nicely across languages.
Examples:
Sequalize – Node JS based ORM
Rails ActiveRecord RoR ActiveRecord
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.
Looking at the small picture, you are right. But, you have to look at the bigger picture and what problems ORMs are solving overall.
Read this article by Martin Fowler.
Then have a look at the repository pattern and then look in depth at one of the ORMs oddz mentioned and how they use entities or the repository pattern, then you can probably answer your own question.
But, if you still aren’t sure, start a new thread. It would be an interesting discussion for sure.
Scott
Yeah, i still dont see how an ORM helps a PHP script, something that fires once, creates objects, and then cleans them all up in garbage less than a millisecond later.
If its “just” a PHP script, you are right. An ORM is overkill. But if it is a large application, an ORM helps considerably.
Scott
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.
Scott
Hey Scott,
What exactly do you mean here by ‘normalize’?
This.
Scott
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…
Yes, that’s what I understand normalization to mean too when talking about DBs… in which case I’m confused as to why you say that using an ORM removes the need for normalization?
Yeah, ok. I started my adventure with Doctrine and the ODM. I see I am wrong about not needing to normalize with the ORM.
Scott