instead of write a query for mysql and call the db->query on it, we should use db methods like get where etc. to make it

[eluser]searain[/eluser]
Even I know a project is for sure on mysql and no needs to change to other database in the future.

I will still use the CI built in db methods to write the sql codes portable to other database, instead of write a mysql sql statement and db->query on it.

I don't see too many extra works here in CI. Is it a good practice, avoiding query for certain database only such as mysql, even I am sure that mysql is the only database for this project?

Here is a example.

Random select 5 products from 5000+ products.

1) to make its database independent, I will use db->select, get all products, and shuffle the results array.

but if for mysql only

2) I can use order by rand() limit 0, 5.

It seems the second approach would perform faster.

So what should I do? if the performance issue is not so crucial but still...

[eluser]Thorpe Obazee[/eluser]
I don't know about the random function in other dbms, but you could always get 5 results and use order_by with the parameter 'random' in the AR.

[eluser]Michael Wales[/eluser]
Random selects are supported as bargain mentioned, but the Active Record library, on MySQL. Unfortunately, I believe MySQL is the only database that supports this.

If you know you aren't going to switch database engines ever, it's still a good idea to use Active Record for two reasons:
1) It will keep you thinking in PHP, no need to bounce back and forth between PHP and SQL.
2) It will protect your application from those nasty SQL injection exploits.

[eluser]searain[/eluser]
Thanks! then it is solved. using Active Record always!

[eluser]tonanbarbarian[/eluser]
firstly i always use AR if i can, but it is not possible in every situation

having said that with the exception of a couple of things like rand, it is easily possible to always write SQL that is usable in every database. DEspite working almost exclusively in MySQL for the last 10 years I still right standard SQL statements that I know will work in all databases.

What a lot of people dont remember is that if you want your database to be able to be installed on any database it is not just the SQL statements during the app that must be standard, it is the table structures themselves.

For this reason I always do the following
1. I never use views or stored procedures. While most databases support these the syntax and or implementation of them can be so varied it is not worth attempting to make them cross platform

2. Max length for varchar fields is 254. Since that is the lowest max length I have found in my research I always use that rather than 255 or higher

3. I never use non standard field types such as enum

4. and of course i always look at how much data actually needs to be stored in a field and set the parameters accordingly. Why waste space if you dont need to.

The one thing I do do is use autoincrement, despite not all database engines supporting this. But frankly if there is a database that cannot do autoincrement or implement a simple equivalent then it is not worth using, no matter how popular it may be

[eluser]Jondolar[/eluser]
Using AR is like using PHP instead of C or Assembly. It is easier to use but may not be able to accomplish everything or may slow down certain critical areas of your code. I recommend start with everything using AR and then drop down to writing full queries when needed (including using the great function rand()) or when you need to optimize for speed (doubtful as AR is very efficient I have heard).