Leave a different message when table in the database already exists

Question

I am trying to create a table in the database. If the table already exists, I want Ajax to leave a message to say that this table already exists.

This is what I have so far:

JS

<script>
    // Add table
    $(document).ready(function() {  
        $("#sender_table").find(".add-icon").click(function() {
            var tableName = $(this).attr("data-tablename");
            $.ajax({                  
              type: 'post', 
              url: "<?= base_url(); ?>table/moveTable/",
              data: "table_name=" + $(this).attr("data-tablename"), 
              success: function(r) {
                 $.notify(tableName + " was successfully added.", "success");
              }
            });
        });
    });
</script>

PHP

    public function moveTable() {
        $this->load->model('Connection_model'); 
        $sTablename = $this->input->post('table_name', true);       
        $db1 = $this->session->userdata('receiver_db');
        $db2 = $this->session->userdata('sender_db');

        // Clone table
        $this->Connection_model->get_custom_db('receiver')->query("CREATE TABLE $db1.$sTablename LIKE $db2.$sTablename");

        // Copy data
        $query = $this->Connection_model->get_custom_db('sender')->get($sTablename);
        foreach ($query->result() as $row) {
            $this->Connection_model->get_custom_db('receiver')->insert($sTablename, $row);
        }   
// Check if table exists
        if ($this->Connection_model->get_custom_db('receiver')->table_exists($sTablename)) {
            return true;
        }
        else {
            return false;
        }
    }

Currently the message is always "tablename was successfully added", even when the table already exists.

score 1 · Answer 1 · answered Apr 03 '15 at 22:47

1

Not sure what framework you are using but you have to send a response like this,

echo json_encode(array('success'=> true));

then in Javascript you can check like this

success: function(r) {
   if(r.success){
     $.notify(tableName + " was successfully added.", "success");
   }
   else{
     $.notify(tableName + " was not added.", "fail");       
   }
}

answered Apr 03 '15 at 22:47

Sohel

431
3
10

I am using Codeigniter 3 as framework. I have just tried your code but r.success returns undefined. There is no way I could check it. – Reza Saadati Apr 03 '15 at 23:50

score 0 · Answer 2 · edited May 23 '17 at 11:50

You need to validate your input.

Your function return value is true/false depending on whether the table exists. There are several cases where the return value could be 'true' even if there are problems processing the new table name. I'd recommend checking if the table exists at the beginning of your function, and if it does, return false or throw and exception before trying the create/clone operations.

If you're still having problems, consider adding debug break-points and tailing the apache / mysql logs for clues about what might be causing the problem.

Edit: also, for a more efficient way of cloning data between tables, you should consider using 'SELECT INTO' - see here.

Leave a different message when table in the database already exists

2 Answers2