CodeIgniter Forums
Insert and Update database checking - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: Best Practices (https://forum.codeigniter.com/forumdisplay.php?fid=12)
+--- Thread: Insert and Update database checking (/showthread.php?tid=63949)



Insert and Update database checking - pb.sajjad - 12-26-2015

Hello...
I'm using CI3 and its query builders. I want know if I insert (or update) some data into tables, should I check insert (or update) result (to sure about operation)? In fact, is there some situation that MySQL does not insert or update? What is these reasons?
thanks...


RE: Insert and Update database checking - RobertSF - 12-26-2015

(12-26-2015, 12:14 PM)pb.sajjad Wrote: I'm using CI3 and its query builders. I want know if I insert (or update) some data into tables, should I check insert (or update) result (to sure about operation)? In fact, is there some situation that MySQL does not insert or update? What is these reasons?

This may not be the best practice, but in general, I do not check for SQL errors. If my field names are correct and I've already validated the data, the circumstances under which an SQL update could fail are very rare, I imagine (someone pulling the plug on the server?).

If you wish to check for errors, CodeIgniter has $this->db->error(), which is found here: https://www.codeigniter.com/user_guide/database/queries.html#handling-errors

But I think if the SQL engine encounters an error, it will bring the PHP script to a halt with an error message SQL generates. Actually handing errors in PHP involves try/catch. Here's a tutorial of unknown quality: http://www.dreamincode.net/forums/topic/185726-database-error-handling-in-php-5/ and here's the official PHP docs on it: http://php.net/manual/en/language.exceptions.php

Now, if you would like to retrieve the autoincrement field value immediately after you insert a row, you can use SQL's LAST_INSERT_ID() function. I implement it this way.
PHP Code:
public function add_user($data)
{
 
   $this->db->insert('users'$data);
 
   return $this->db->query('SELECT LAST_INSERT_ID() AS REC_ID;')->row()->REC_ID;


And I realize this is not part of your question, but personally, I don't use CodeIgniter's query builders. Why? Because you should learn SQL anyway, and also, some queries are too complex for the query builders anyway. It's very convenient to "black box" the database connection, but for all but the simplest of queries, I like to write my own queries out in SQL. You can execute any SQL statement, even if it returns no rows, via $this->db->query(). So, for example, in my model file, I would have methods like this.
PHP Code:
public function get_gifts_for($user_id)
{
 
   return $this->db->query(
 
       'SELECT
            gifts.id AS id,
            gifts.name AS gift,
            gifts.price AS price,
            gifts.description AS description,
            gifts.url AS url,
            glists.name AS glist,
            glists.max_gifts AS glist_max_gifts,
            glists.max_price AS glist_max_price,
            u2.name AS owner
        FROM gifts
        INNER JOIN users ON gifts.for_user_id = users.id
        INNER JOIN glists ON glists.id = gifts.glist_id
        INNER JOIN users u2 ON glists.owner = u2.id
        WHERE users.id = ' 
$user_id ';')->result_array();


Alright, good luck!  Smile


RE: Insert and Update database checking - skunkbad - 12-26-2015

CodeIgniter makes it easy to use DB transactions to make sure your inserts and updates all went well.

http://www.codeigniter.com/user_guide/database/transactions.html

(12-26-2015, 04:07 PM)RobertSF Wrote:
PHP Code:
public function get_gifts_for($user_id)
{
 
   return $this->db->query(
 
       'SELECT
            gifts.id AS id,
            gifts.name AS gift,
            gifts.price AS price,
            gifts.description AS description,
            gifts.url AS url,
            glists.name AS glist,
            glists.max_gifts AS glist_max_gifts,
            glists.max_price AS glist_max_price,
            u2.name AS owner
        FROM gifts
        INNER JOIN users ON gifts.for_user_id = users.id
        INNER JOIN glists ON glists.id = gifts.glist_id
        INNER JOIN users u2 ON glists.owner = u2.id
        WHERE users.id = ' 
$user_id ';')->result_array();


If you're going to use queries like this, you need to understand that you will no longer benefit from the escaping (sanitizing) that CodeIgniter provides, in this case for the variable $user_id. I also think it's important to know how to use plain MySQL, but CodeIgniter does a lot for you, and you can get into trouble if you don't know what you're doing with plain MySQL. In the case of this example query, I would have type casted $user_id as an integer.


RE: Insert and Update database checking - pmbaldha - 12-27-2015

(12-26-2015, 12:14 PM)pb.sajjad Wrote: Hello...
I'm using CI3 and its query builders. I want know if I insert (or update) some data into tables, should I check insert (or update) result (to sure about operation)? In fact, is there some situation that MySQL does not insert or update? What is these reasons?
thanks...

You can know inserted or updated performed of not by
$this->db->affected_rows();
if affected rows are greater than zero, insert or update operation is affected.

-----------------------------------------------------------------------------------------------------
 is there some situation that MySQL does not insert or update? What is these reasons?
In development environment error reporting is on, so if any error occurred in insert or update query, webpage will throw error. in dev environment, error reporting is on.
But in production environment, if any error occured in sql query then you will not be notified and there is possibility that you insert (or update) will not affected at db level and still you are not aware of it.

Generally, We are developing code in dev environment and make fully testing and then we are moving code to production environment, so logically this situation is not possible but technically this situation is possible if you are directly coding in production environment.

To understand codeigniter environment, please visit https://www.codeigniter.com/user_guide/general/environments.html


RE: Insert and Update database checking - pb.sajjad - 12-27-2015

(12-27-2015, 01:27 AM)pmbaldha Wrote:
(12-26-2015, 12:14 PM)pb.sajjad Wrote: Hello...
I'm using CI3 and its query builders. I want know if I insert (or update) some data into tables, should I check insert (or update) result (to sure about operation)? In fact, is there some situation that MySQL does not insert or update? What is these reasons?
thanks...

You can know inserted or updated performed of not by
$this->db->affected_rows();
if affected rows are greater than zero, insert or update operation is affected.

-----------------------------------------------------------------------------------------------------
 is there some situation that MySQL does not insert or update? What is these reasons?
In development environment error reporting is on, so if any error occurred in insert or update query, webpage will throw error. in dev environment, error reporting is on.
But in production environment, if any error occured in sql query then you will not be notified and there is possibility that you insert (or update) will not affected at db level and still you are not aware of it.

Generally, We are developing code in dev environment and make fully testing and then we are moving code to production environment, so logically this situation is not possible but technically this situation is possible if you are directly coding in production environment.

To understand codeigniter environment, please visit https://www.codeigniter.com/user_guide/general/environments.html

Thanks for replying...
Your're right, and I know about $this->db->affected_rows(); . I've already read codeigniter different environment.
So, as your explanation, if I test everything in development environment and everything will be correct, there is no need to check insert/update result. Is this true?
Is it possible that everything is correct in development environment, but in live server and in production environment, an error will be occured (MySQL do not operate correctly) ? For example, after user registering, user info does not insert, or after editing info, update row encounter with errors (assume that I validate all info and...). Or about transactions or...


RE: Insert and Update database checking - pmbaldha - 12-27-2015

(12-27-2015, 04:37 AM)pb.sajjad Wrote:
(12-27-2015, 01:27 AM)pmbaldha Wrote:
(12-26-2015, 12:14 PM)pb.sajjad Wrote: Hello...
I'm using CI3 and its query builders. I want know if I insert (or update) some data into tables, should I check insert (or update) result (to sure about operation)? In fact, is there some situation that MySQL does not insert or update? What is these reasons?
thanks...

You can know inserted or updated performed of not by
$this->db->affected_rows();
if affected rows are greater than zero, insert or update operation is affected.

-----------------------------------------------------------------------------------------------------
 is there some situation that MySQL does not insert or update? What is these reasons?
In development environment error reporting is on, so if any error occurred in insert or update query, webpage will throw error. in dev environment, error reporting is on.
But in production environment, if any error occured in sql query then you will not be notified and there is possibility that you insert (or update) will not affected at db level and still you are not aware of it.

Generally, We are developing code in dev environment and make fully testing and then we are moving code to production environment, so logically this situation is not possible but technically this situation is possible if you are directly coding in production environment.

To understand codeigniter environment, please visit https://www.codeigniter.com/user_guide/general/environments.html

Thanks for replying...
Your're right, and I know about $this->db->affected_rows(); . I've already read codeigniter different environment.
So, as your explanation, if I test everything in development environment and everything will be correct, there is no need to check insert/update result. Is this true?
Is it possible that everything is correct in development environment, but in live server and in production environment, an error will be occured (MySQL do not operate correctly) ? For example, after user registering, user info does not insert, or after editing info, update row encounter with errors (assume that I validate all info and...). Or about transactions or...
So, as your explanation, if I test everything in development environment and everything will be correct, there is no need to check insert/update result. Is this true?
Yes, right. 
Is it possible that everything is correct in development environment, but in live server and in production environment, an error will be occured (MySQL do not operate correctly) ? For example, after user registering, user info does not insert, or after editing info, update row encounter with errors (assume that I validate all info and...). Or about 
There is not error possibility minor error if we dump same database in live and same storage engine of database for live database. Moreover make sure that mysql version of development and production environment should be same to avoid performance related issue.
There is possible error in database connection due to wrong credential. I don't seem that there might be any transaction related error on live if everything is perfect in development environment.


RE: Insert and Update database checking - pb.sajjad - 12-27-2015

(12-27-2015, 05:20 AM)pmbaldha Wrote:
(12-27-2015, 04:37 AM)pb.sajjad Wrote:
(12-27-2015, 01:27 AM)pmbaldha Wrote:
(12-26-2015, 12:14 PM)pb.sajjad Wrote: Hello...
I'm using CI3 and its query builders. I want know if I insert (or update) some data into tables, should I check insert (or update) result (to sure about operation)? In fact, is there some situation that MySQL does not insert or update? What is these reasons?
thanks...

You can know inserted or updated performed of not by
$this->db->affected_rows();
if affected rows are greater than zero, insert or update operation is affected.

-----------------------------------------------------------------------------------------------------
 is there some situation that MySQL does not insert or update? What is these reasons?
In development environment error reporting is on, so if any error occurred in insert or update query, webpage will throw error. in dev environment, error reporting is on.
But in production environment, if any error occured in sql query then you will not be notified and there is possibility that you insert (or update) will not affected at db level and still you are not aware of it.

Generally, We are developing code in dev environment and make fully testing and then we are moving code to production environment, so logically this situation is not possible but technically this situation is possible if you are directly coding in production environment.

To understand codeigniter environment, please visit https://www.codeigniter.com/user_guide/general/environments.html

Thanks for replying...
Your're right, and I know about $this->db->affected_rows(); . I've already read codeigniter different environment.
So, as your explanation, if I test everything in development environment and everything will be correct, there is no need to check insert/update result. Is this true?
Is it possible that everything is correct in development environment, but in live server and in production environment, an error will be occured (MySQL do not operate correctly) ? For example, after user registering, user info does not insert, or after editing info, update row encounter with errors (assume that I validate all info and...). Or about transactions or...
So, as your explanation, if I test everything in development environment and everything will be correct, there is no need to check insert/update result. Is this true?
Yes, right. 
Is it possible that everything is correct in development environment, but in live server and in production environment, an error will be occured (MySQL do not operate correctly) ? For example, after user registering, user info does not insert, or after editing info, update row encounter with errors (assume that I validate all info and...). Or about 
There is not error possibility minor error if we dump same database in live and same storage engine of database for live database. Moreover make sure that mysql version of development and production environment should be same to avoid performance related issue.
There is possible error in database connection due to wrong credential. I don't seem that there might be any transaction related error on live if everything is perfect in development environment.

If you found useful, please rate my answer or me.

Thanks a lot. Your explanation is good.
I think ever everything is good and do all validation, may be some error from server in order to insert or update database table.


RE: Insert and Update database checking - pmbaldha - 12-27-2015

(12-27-2015, 10:09 AM)pb.sajjad Wrote:
(12-27-2015, 05:20 AM)pmbaldha Wrote:
(12-27-2015, 04:37 AM)pb.sajjad Wrote:
(12-27-2015, 01:27 AM)pmbaldha Wrote:
(12-26-2015, 12:14 PM)pb.sajjad Wrote: Hello...
I'm using CI3 and its query builders. I want know if I insert (or update) some data into tables, should I check insert (or update) result (to sure about operation)? In fact, is there some situation that MySQL does not insert or update? What is these reasons?
thanks...

You can know inserted or updated performed of not by
$this->db->affected_rows();
if affected rows are greater than zero, insert or update operation is affected.

-----------------------------------------------------------------------------------------------------
 is there some situation that MySQL does not insert or update? What is these reasons?
In development environment error reporting is on, so if any error occurred in insert or update query, webpage will throw error. in dev environment, error reporting is on.
But in production environment, if any error occured in sql query then you will not be notified and there is possibility that you insert (or update) will not affected at db level and still you are not aware of it.

Generally, We are developing code in dev environment and make fully testing and then we are moving code to production environment, so logically this situation is not possible but technically this situation is possible if you are directly coding in production environment.

To understand codeigniter environment, please visit https://www.codeigniter.com/user_guide/general/environments.html

Thanks for replying...
Your're right, and I know about $this->db->affected_rows(); . I've already read codeigniter different environment.
So, as your explanation, if I test everything in development environment and everything will be correct, there is no need to check insert/update result. Is this true?
Is it possible that everything is correct in development environment, but in live server and in production environment, an error will be occured (MySQL do not operate correctly) ? For example, after user registering, user info does not insert, or after editing info, update row encounter with errors (assume that I validate all info and...). Or about transactions or...
So, as your explanation, if I test everything in development environment and everything will be correct, there is no need to check insert/update result. Is this true?
Yes, right. 
Is it possible that everything is correct in development environment, but in live server and in production environment, an error will be occured (MySQL do not operate correctly) ? For example, after user registering, user info does not insert, or after editing info, update row encounter with errors (assume that I validate all info and...). Or about 
There is not error possibility minor error if we dump same database in live and same storage engine of database for live database. Moreover make sure that mysql version of development and production environment should be same to avoid performance related issue.
There is possible error in database connection due to wrong credential. I don't seem that there might be any transaction related error on live if everything is perfect in development environment.

If you found useful, please rate my answer or me.

Thanks a lot. Your explanation is good.
I think ever everything is good and do all validation, may be some error from server in order to insert or update database table.

As per me, ever everything is good and do all validation there is 0.01% chance  of  error from server in order to insert or update database table.


RE: Insert and Update database checking - Diederik - 12-28-2015

I think it's just bad practise to just display a message like "page was saved" when some posted data is validated. You did not verify a single row was affected.
I can think of numerous situation were this can go wrong. You could have some mistake in your database scheme for example where you forgot to make an id field (or url slug etc) a unique field and you end up having 2 different pages with the same id/slug after a manual import of a .sql file from a live environment or something.
Or the database could be in read only mode for some server related issue, or your database user does not have write access to a particular table etc.

I have setup a routine to check for these kind of unexpected results when the effected rows does not match the expected value, or when an insert on a auto increment table does not return a new id etc. I generate an error message and write the event to a log.

To omit some checks out of laziness can it ever be best practise.

That being said, the chances of something like this is very limited but still i don't feel comfortable omitting these checks to save a few hours of dev time. Whenever you need to debug a situation you should be happy you have spend those few hours because they will pay off!


RE: Insert and Update database checking - pb.sajjad - 12-28-2015

(12-27-2015, 08:58 PM)pmbaldha Wrote:
(12-27-2015, 10:09 AM)pb.sajjad Wrote:
(12-27-2015, 05:20 AM)pmbaldha Wrote:
(12-27-2015, 04:37 AM)pb.sajjad Wrote:
(12-27-2015, 01:27 AM)pmbaldha Wrote: You can know inserted or updated performed of not by
$this->db->affected_rows();
if affected rows are greater than zero, insert or update operation is affected.

-----------------------------------------------------------------------------------------------------
 is there some situation that MySQL does not insert or update? What is these reasons?
In development environment error reporting is on, so if any error occurred in insert or update query, webpage will throw error. in dev environment, error reporting is on.
But in production environment, if any error occured in sql query then you will not be notified and there is possibility that you insert (or update) will not affected at db level and still you are not aware of it.

Generally, We are developing code in dev environment and make fully testing and then we are moving code to production environment, so logically this situation is not possible but technically this situation is possible if you are directly coding in production environment.

To understand codeigniter environment, please visit https://www.codeigniter.com/user_guide/general/environments.html

Thanks for replying...
Your're right, and I know about $this->db->affected_rows(); . I've already read codeigniter different environment.
So, as your explanation, if I test everything in development environment and everything will be correct, there is no need to check insert/update result. Is this true?
Is it possible that everything is correct in development environment, but in live server and in production environment, an error will be occured (MySQL do not operate correctly) ? For example, after user registering, user info does not insert, or after editing info, update row encounter with errors (assume that I validate all info and...). Or about transactions or...
So, as your explanation, if I test everything in development environment and everything will be correct, there is no need to check insert/update result. Is this true?
Yes, right. 
Is it possible that everything is correct in development environment, but in live server and in production environment, an error will be occured (MySQL do not operate correctly) ? For example, after user registering, user info does not insert, or after editing info, update row encounter with errors (assume that I validate all info and...). Or about 
There is not error possibility minor error if we dump same database in live and same storage engine of database for live database. Moreover make sure that mysql version of development and production environment should be same to avoid performance related issue.
There is possible error in database connection due to wrong credential. I don't seem that there might be any transaction related error on live if everything is perfect in development environment.

If you found useful, please rate my answer or me.

Thanks a lot. Your explanation is good.
I think ever everything is good and do all validation, may be some error from server in order to insert or update database table.

As per me, ever everything is good and do all validation there is 0.01% chance  of  error from server in order to insert or update database table.

I agree with you, and just for this 0.01% chance, I've checked database insert/update result. I just want to sure Smile
Thanks.