If user ID exists in database, UPDATE. Otherwise, INSERT (HELP)

[eluser]Barwick[/eluser]
So as the title explains, I'm creating a subscription function that's connected to a "subscriptions" database. On the form, a logged in user is required to select a subscription. 3 different options. In the form, I have two hidden fields, one for user ID and the other for email address. Both pulled from the session data.

In my controller and model below, I want to (1) check to make sure the user hasn't already set-up a subscription (verified by user ID). If they haven't, it's added on form submit. If the query comes back true that a subscription was created previously using the user ID, then I'd like to update the table row.

But all my code wants to do is INSERT even if there's already a record in the database. What am I doing wrong? Duplication error will occur...

Controller (part of it):

Code:

public function update()
    {
        $userid = $this->session->userdata('user_id');  
        
        $this -> db -> where('id');
        $query = $this -> db -> get('subscriptions');
        
        if($query -> num_rows() == $userid) // Check to see if user id exists
        {
            // if subscription record found, update:
            $this -> subscribe_model -> update_subscription();
            redirect('dashboard');            
        }
        else
        {
            // if no records found, add:
            $this -> subscribe_model -> add_subscription();
            redirect('dashboard');  
        }
    }

Model:

Code:

class Subscribe_model extends CI_Model {
    
    public function __construct() {
        parent::__construct();
    }
    
    // Add Subscription to database
    public function add_subscription()
    {
        $data = array(
            'id' => $this -> input -> post('userID'),
            'email' => $this -> input -> post('userEmail'),
            'subscription' => $this -> input -> post('package')
        );
        $this -> db -> insert('subscriptions', $data);
    }
    
    // Update Subscription to database
    public function update_subscription()
    {
        $data = array(
            'id' => $this -> input -> post('userID'),
            'email' => $this -> input -> post('userEmail'),
            'subscription' => $this -> input -> post('package')
        );

        $this->db->where('id', $id);
        $this->db->update('subscriptions', $data);
    }
    

}

[eluser]CroNiX[/eluser]
MySQL: INSERT ... ON DUPLICATE KEY UPDATE

[eluser]Barwick[/eluser]
Hmm, is there not Active Record way of doing this?

[eluser]CroNiX[/eluser]
Nope, active record only does basic things that are common to all databases (its an abstraction layer so the same AR will work on mysql as mssql as oracle as...). It probably only uses 5% of the functions available in mysql.

If you limit yourself to only what AR will do, you will only be able to do basic things. Use it where appropriate and just use $this->db->query($query) on more complex things. Just be sure to escape anything a user can alter going into the database and it will be ok.

Like you will have to use multiple queries to do this instead of efficiently using mysql to do it in one. That's inefficient and I wouldn't use AR to dumb down my app with that query.

[eluser]Barwick[/eluser]
Valid points. No easy way about it...I respect your knowledge and help! Good for newbie's like myself trying to master this sh*t.

If I remove any primary or unique keys, I can update/insert fine in the database. Not even sure in this instance I need a primary (or would that be breaking a cardinal rule?).

However, I'm running an IF statement in my controller (pasted above & below which doesn't work) that should check the database to see if the userId exits, if it does, run the UPDATE function from the model. If it's doesn't, run the INSERT. But I can't seem to get this beast to work...or should I really just be doing the ON DUPLICATE KEY UPDATE function? not sure where to start with that though.

Anything stand out? Feel free to comment n the code for easier explanation...

Code:

public function update()
    {
        $userid = $this->session->userdata('user_id');  
        
        $this -> db -> where('id');
        $query = $this -> db -> get('subscriptions');
        
        if($query -> num_rows() == $userid) // Check to see if user id exists
        {
            // if subscription record found, update:
            $this -> subscribe_model -> update_subscription();
            redirect('dashboard');            
        }
        else
        {
            // if no records found, add:
            $this -> subscribe_model -> update_subscription();
            redirect('dashboard');  
        }
    }

[eluser]CroNiX[/eluser]
Look at the logic in this line:

Code:

if($query -> num_rows() == $userid)

So if the user ID is 234234 you will need to have that many rows returned in your query for it to trigger your code.
probably should be

Code:

if($query -> num_rows())

which means if you get ANY results, the subscription already exists.

[eluser]PhilTem[/eluser]
Or MySQL REPLACE INTO

(basically the same as @CroNiX' "Insert on Duplicate Key Update"

[eluser]CroNiX[/eluser]
replace always does a delete and then an insert, so it basically always acts as an insert since it deletes the existing row if it exists. Still a little more overhead (2 operations) than ON DUPLICATE KEY UPDATE.

[eluser]Barwick[/eluser]
Hmm, running into issues with that as well. You know what, I'm going to ditch this work around and go back to the ON DUPLICATE KEY stuff. I'm sure you'll vouch that this is the proper method anyways.

How would you write the model? I wouldn't need both the add_subscription() and update_subscription() methods I guess now, huh?

Any help would be GREATLY appreciated! I know a <i>tiny</i> bit of MySQL, but not sure how I'd lay it out in a model in CI.

[eluser]Barwick[/eluser]
[quote author="CroNiX" date="1352170165"]replace always does a delete and then an insert, so it basically always acts as an insert since it deletes the existing row if it exists. Still a little more overhead (2 operations) than ON DUPLICATE KEY UPDATE.[/quote]

Haha, yeah I don't like the sounds of delete THEN ask questions later. I've read some horror stories pertaining to this.