Insert help needed!

[eluser]debow[/eluser]
Hello all, I'm really new to CI and I'm trying to take my old insert statement which was working in reg php and convert it over to CI format and not having much luck. Below is the existing sql.

I have this from my old project. It works. I have the first part of inserting a new athlete working. I'm just having issue with the 2nd sql statement trying to insert info from 2 tables into one.

Code:

$today = date('Y');
    $sql = "INSERT INTO athlete SET firstname='$firstname', lastname='$lastname', gender='$gender'";
    $result = mysql_query($sql);
    
    $last_id = mysql_insert_id();
            
    $sql = "INSERT INTO event_results (athlete_id,gender,event,date) SELECT $last_id,athlete.gender,events.event,$today
    FROM athlete,events
    WHERE athlete.id = $last_id";
    
    $result = mysql_query($sql);
    
        if ($result)
        {    
            return true;
        } else
        {
            return false;
        }
   }

This is the controller section of CI code I have that is working.

Code:

function add()
    {        
        // Validate form
        $this->form_validation->set_rules('firstname', 'firstname', 'trim|required');
        $this->form_validation->set_rules('lastname', 'lastname', 'trim|required');
        $this->form_validation->set_rules('gender', 'type', 'trim|required');
        
        if($this->form_validation->run())
        {
            // Validation passes
            $athId = $this->athlete_model->AddAthlete($_POST);
            
            if($athId)
            {
                $this->session->set_flashdata('flashConfirm', 'The athlete has been successfully added.');
                redirect('athletes');
            }
            else
            {
                $this->session->set_flashdata('flashError', 'A database error has occured, please contact your administrator.');
                redirect('athletes');
            }
        }
            $data['main_content'] = 'athletes/athletes_add_form';
            $this->load->view('includes/template', $data);
        
    }

This is the model section of CI code.

Code:

function AddAthlete($options = array())
    {
        // required values
        if(!$this->_required(
            array('firstname', 'lastname', 'gender'),
            $options)
        ) return false;
        
        $options = $this->_default(array('athStatus' => 'active'), $options); //declares all athletes active by default
        
        $this->db->insert('athlete', $options);
        
        return $this->db->insert_id(); //This will return the id the athlete was given
        
    }

At this point I'm having issues trying to get the second half of the existing code working in CI. I need help converting this to CI code.

Code:

$today = date('Y');
$last_id = mysql_insert_id();
            
    $sql = "INSERT INTO event_results (athlete_id,gender,event,date) SELECT $last_id,athlete.gender,events.event,$today
    FROM athlete,events
    WHERE athlete.id = $last_id";
    
    $result = mysql_query($sql);
    
        if ($result)
        {    
            return true;
        } else
        {
            return false;
        }

Thanks for any help!

[eluser]debow[/eluser]
So I've got a little further but not much.

This is what I have so far.

Code:

function AddAthlete($options = array())
    {
        // required values
        if(!$this->_required(
            array('firstname', 'lastname', 'gender'),
            $options)
        ) return false;
        
        $options = $this->_default(array('athStatus' => 'active'), $options); //declares all users active by default
        
        //$this->db->insert('athlete', $options);
        
        //return $this->db->insert_id(); //This will return the id the user was given
        
        if(!$this->db->insert('athlete', $options))
        {
            return FALSE;
        
        }
        echo $this->db->insert_id();  //this shows me the correct athID
        $this->db->affected_rows();
        $last_id = $this->db->insert_id();
        
        
        //The problem is here but not sure why.
        $query = $this->db->query('INSERT INTO eresults (athlete_id,gender,event) SELECT $last_id,athlete.gender,events.event
        FROM athlete,events
        WHERE athlete.athId = $last_id');
        
        
    }

The athlete is added. I can echo the athID by insert_id() and they match. But when assigning $last_id = $this->db->insert_id(); I get the below error when running it.

Code:

A Database Error Occurred

Error Number: 1054

Unknown column '$last_id' in 'field list'

INSERT INTO eresults (athlete_id,gender,event) SELECT $last_id,athlete.gender,events.event FROM athlete,events WHERE athlete.athId = $last_id

Again this works in my normal php project but not sure why it doesn't in CI.

[eluser]CroNiX[/eluser]
I'm betting you didn't use variable names within single quotes of you previous SQL statement.

You are basically saying:

$orig = 'hello';
echo '$orig'; (echos "$orig", not "hello" because single quotes are literal)
echo "$orig"; (echos "hello")

There seems to be more here that is a problem, but that will get you going. Just change your single quotes to double quotes at the beginning and end of your SQL statement.

[eluser]debow[/eluser]
Thanks for the help. That was my issue. I just changed the below...

Code:

$query = $this->db->query('INSERT INTO eresults (athlete_id,gender,event) SELECT $last_id,athlete.gender,events.event
        FROM athlete,events
        WHERE athlete.athId = $last_id');

TO...

Code:

$query = $this->db->query("INSERT INTO eresults (athlete_id,gender,event) SELECT $last_id,athlete.gender,events.event
        FROM athlete,events
        WHERE athlete.athId = $last_id");

Didn't even think to try that. Thanks again.