How can I use transactions with these queries?

Hey guys;

This page could receive up to 50k hits at the same time.

How can I use transactions here?

Would it be right if I simply add $this->db->trans_start(); and $this->db->trans_end(); to the beginning and end of index method?

Thanks

PHP Code:

<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class Data_receiver extends CI_Controller {

 public function __construct()
 {
 parent::__construct();
 }

 public function index()
 {
 //Validate Account Owner
 $sql = "SELECT * FROM launch_owners WHERE id = ?";
 $query = $this->db->query($sql, array($_POST['user_id']));
 if($query->num_rows()!=1)
 {
 echo "Invalid Request";exit();
 }
 else
 {
 $results = $query->result_array()[0]; 
 //More Validate Account Owner
 if($results['status']!='active' || $results['secret_key']!=$_POST['secret_key'])
 {
 echo "Inactive Account Or Invalid Secret";exit();
 }
 else 
 {
 //Validate Launch
 $sql = "SELECT * FROM launch_launches WHERE id = ? AND launch_type = ?";
 $query = $this->db->query($sql, array($_POST['launch_id'], 'evergreen'));
 if($query->num_rows()==1)
 {
 //Does Prospect exist under this user?
 $sql = "SELECT * FROM launch_prospects WHERE email = ? AND owner_id = ?";
 $query = $this->db->query($sql, array($_POST['prospect_email'], $_POST['user_id']));
 if($query->num_rows()==1)
 {
 $prospect_id = $query->result_array()[0]['id'];
 }
 else 
 {
 $data = array(
        'email' => $_POST['prospect_email'],
        'owner_id' => $_POST['user_id'],
 );
 $this->db->insert('launch_prospects', $data);
 $prospect_id = $this->db->insert_id();
 }
 //Delete prospect from this launch if already exist in the seqeunce
 $this->db->delete('launch_launch_prospect', array('launch_id' => $_POST['launch_id'], 'prospect_id'=>$prospect_id)); 
 //Add Prospect To Launch
 $data = array(
        'launch_id' => $_POST['launch_id'],
        'prospect_id' => $prospect_id,
        'time_added' => time(),
        'date_added' => date('Y-m-d')
 );
 $this->db->insert('launch_launch_prospect', $data);

 //Insert the same into stats table
 $data = array(
        'launch_id' => $_POST['launch_id'],
        'prospect_id' => $prospect_id,
        'time_added' => time(),
        'date_added' => date('Y-m-d')
 );
 $this->db->insert('launch_launch_prospect_history', $data);
 echo "Added Successfully";
 }
 else 
 {
 echo "Invalid launch";exit();
 } 
 } 
 } 





 }
} 


See: The CodeIgniter User's Guide

Running Transactions Manually

What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )

See: The CodeIgniter User's Guide

Running Transactions Manually

How does this look?

PHP Code:

defined('BASEPATH') OR exit('No direct script access allowed');

class Data_receiver extends CI_Controller {

 public function __construct()
 {
 parent::__construct();
 }

 public function index()
 {
$this->db->trans_begin();
 //Validate Account Owner
 $sql = "SELECT * FROM launch_owners WHERE id = ?";
 $query = $this->db->query($sql, array($_POST['user_id']));
 if($query->num_rows()!=1)
 {
 echo "Invalid Request";exit();
 }
 else
 {
 $results = $query->result_array()[0]; 
 //More Validate Account Owner
 if($results['status']!='active' || $results['secret_key']!=$_POST['secret_key'])
 {
 echo "Inactive Account Or Invalid Secret";exit();
 }
 else 
 {
 //Validate Launch
 $sql = "SELECT * FROM launch_launches WHERE id = ? AND launch_type = ?";
 $query = $this->db->query($sql, array($_POST['launch_id'], 'evergreen'));
 if($query->num_rows()==1)
 {
 //Does Prospect exist under this user?
 $sql = "SELECT * FROM launch_prospects WHERE email = ? AND owner_id = ?";
 $query = $this->db->query($sql, array($_POST['prospect_email'], $_POST['user_id']));
 if($query->num_rows()==1)
 {
 $prospect_id = $query->result_array()[0]['id'];
 }
 else 
 {
 $data = array(
        'email' => $_POST['prospect_email'],
        'owner_id' => $_POST['user_id'],
 );
 $this->db->insert('launch_prospects', $data);
 $prospect_id = $this->db->insert_id();
 }
 //Delete prospect from this launch if already exist in the seqeunce
 $this->db->delete('launch_launch_prospect', array('launch_id' => $_POST['launch_id'], 'prospect_id'=>$prospect_id)); 
 //Add Prospect To Launch
 $data = array(
        'launch_id' => $_POST['launch_id'],
        'prospect_id' => $prospect_id,
        'time_added' => time(),
        'date_added' => date('Y-m-d')
 );
 $this->db->insert('launch_launch_prospect', $data);

 //Insert the same into stats table
 $data = array(
        'launch_id' => $_POST['launch_id'],
        'prospect_id' => $prospect_id,
        'time_added' => time(),
        'date_added' => date('Y-m-d')
 );
 $this->db->insert('launch_launch_prospect_history', $data);
 echo "Added Successfully";
 }
 else 
 {
 echo "Invalid launch";exit();
 } 
 } 

if ($this->db->trans_status() === FALSE)
{
        $this->db->trans_rollback();
}
else
{
        $this->db->trans_commit();
}
 } 





 }
} 


Test it with good and bad data to see what happens.

What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )

Test it with good and bad data to see what happens.

Is the approach correct though?

And what's bad data?

Bad data is the data that will make transation fail

If you set your id = 0 it should error out because you cannot have an id of 0 (Zero).

What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )

Ok this code seems to be working well.

But how can I check to see it is working ok on bulk requests?

Basically, how can I make 50k api calls?

PHP Code:

<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class Data_receiver extends CI_Controller {

    public function __construct()
        {
            parent::__construct();
        }

    public function index()
        {
            $this->output->enable_profiler(TRUE);
            $this->db->trans_start();
            //Validate Account Owner
            $sql = "SELECT * FROM launch_owners WHERE id = ?";
            $query = $this->db->query($sql, array($_POST['user_id']));
            if($query->num_rows()!=1)
                {
                    echo "Invalid Request";exit();
                }
            else
                {
                    $results = $query->result_array()[0];    
                    print_r($_POST);
                    print_r($results);
                    //More Validate Account Owner
                    if($results['status']!='active' || $results['secret_key']!=$_POST['secret_key'])
                        {
                            echo "Inactive Account Or Invalid Secret";exit();
                        }
                    else 
                        {
                            //Validate Launch
                            $sql = "SELECT * FROM launch_launches WHERE id = ? AND launch_type = ?";
                            $query = $this->db->query($sql, array($_POST['launch_id'], 'evergreen'));
                            if($query->num_rows()==1)
                                {
                                    //Does Prospect exist under this user?
                                    $sql = "SELECT * FROM launch_prospects WHERE email = ? AND owner_id = ?";
                                    $query = $this->db->query($sql, array($_POST['prospect_email'], $_POST['user_id']));
                                    if($query->num_rows()==1)
                                        {
                                            $prospect_id = $query->result_array()[0]['id'];
                                        }
                                    else 
                                        {
                                            $data = array(
                                                'email' => $_POST['prospect_email'],
                                                'owner_id' => $_POST['user_id'],
                                            );
                                            $this->db->insert('launch_prospects', $data);
                                            $prospect_id = $this->db->insert_id();
                                        }
                                    //Delete prospect from this launch if already exist in the seqeunce
                                    $this->db->delete('launch_launch_prospect', array('launch_id' => $_POST['launch_id'], 'prospect_id'=>$prospect_id)); 
                                    //Add Prospect To Launch
                                    $data = array(
                                                'launch_id' => $_POST['launch_id'],
                                                'prospect_id' => $prospect_id,
                                                'time_added' => time(),
                                                'date_added' => date('Y-m-d')
                                        );
                                    $this->db->insert('launch_launch_prospect', $data);

                                    //Insert the same into stats table
                                    $data = array(
                                                'launch_id' => $_POST['launch_id'],
                                                'prospect_id' => $prospect_id,
                                                'time_added' => time(),
                                                'date_added' => date('Y-m-d'),
                                                'source' => $_POST['source']
                                        );
                                    $this->db->insert('launch_launch_prospect_history', $data);
                                    echo "Added Successfully";
                                }
                            else 
                                {
                                    echo "Invalid launch";exit();
                                }    
                        }    
                }    



        $this->db->trans_complete();        

        }
}