Strange Database problem! PHP MySQL Codeigniter

Question

I am experiencing a strange problem with a simple query that returned the number of rows in a table.

This was always working fine and correct.

However! Yesterday I added a new function to my website that updates a column in existing rows in my table. This function called add_file()

Now my website is giving a wrong value of what it should be:

Currently there are 76 rows in my table called "procedure" viewed from phpMyadmin and SQLyog.

However in my website it is saying there are 70.

Machines DO NOT LIE, so this is most likely my doing

I have a hunch that my function called add_file() is to blame.

What this function does is update the "edocument" column in my procedure table if a user uploads a file corresponding to that record. So that the system knows what that file is called and can construct a url for it.

        public function add_filename($file)
        {

            //This is the extension of the file retrieved from an array
            $extension = $file['upload_data']['file_ext'];


            //variable for updating row which is constructed from Username+Filename+Extension
            $filename = array
            (
                'edocument' => ($this->session->userdata('name').$this->input->post('record_id')).$extension
            );

            //find row that matches the row just submitted by user
            $this->db->where('procedure_id',$this->input->post('record_id'));
            //update that row with the filename of the document uploaded
            $this->db->update('procedure', $filename); 

        }

If you look at this screenshot you will see my "edocument" column from 72-76 has values.

https://i.stack.imgur.com/nxDnE.jpg

So is the update function breaking my database?

Thanks

+1 for great comments in code. That's what comments should be like! — Pindatjuh, Jul 31 '11 at 17:16
Thankyou! :) commenting is important to me otherwise I forget what my code does later — sqlmole, Jul 31 '11 at 17:19
"in my website it is saying there are 70" - What is the SELECT query used that gives you 70 results? The UPDATE itself should not have anything to do with it. That *is* what your question is, correct? You're asking why it only displays 70 records? — Wesley Murch, Jul 31 '11 at 17:59
It is saying there are 70 rows in my table but there is 76, why?!! is Codeigniter broken? — sqlmole, Jul 31 '11 at 18:25
Try printing `$this->db->last_query();` after your call to the model (when updating and retrieving) to see if any weird SQL calls are being made. — Sukumar, Jul 31 '11 at 18:28
Okay I have found something strange with doing what you requested: Here is the query `UPDATE `procedure` SET `edocument` = 'Bob73.jpg' WHERE `procedure_id` = '73'` However checking my database through phpmyadmin it is actually row 77 which has that column value of "bob73.jpg" what could this be? — sqlmole, Jul 31 '11 at 18:35
Im gonna delete and remake my database from backup see if that fixes it. — sqlmole, Jul 31 '11 at 18:47
Your function looks fine to me. As shaggy pointed out, your auto_increment column might not exactly correspond with the COUNT values CodeIgniter is running. If you delete rows in your table, the `patient_id` value will still increment based on the last inserted ID. — hohner, Jul 31 '11 at 22:50

score 1 · Accepted Answer · answered Jul 31 '11 at 22:37

1

Are you sure that there are 76 rows?
I see in your picture column patient_id, I guess it is auto increment (or at least it is linked with patients table that have auto increment primary key).
But if you had run some DELETE queries you see only 70 rows, but the auto increment id will start from the last used number (from 76 in your case).

Have you tried running simple COUNT query (without using codeigniter active record)? If yes, what was the result?

answered Jul 31 '11 at 22:37

shaggy

1,708
2
15
17

70. I deleted my database and remade it from backup - it is now fixed but I will give you the points as I think your answer may indicate what went wrong with my database, the auto increment. – sqlmole Aug 01 '11 at 12:15

Strange Database problem! PHP MySQL Codeigniter

1 Answers1