• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
How can I use transactions with these queries?

#1
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();
 } 
 } 
 } 





 }

Heart
Reply

#2
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 )
Reply

#3
(04-16-2018, 05:18 AM)InsiteFX Wrote: 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();
}
 } 





 }

Reply

#4
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 )
Reply

#5
(04-16-2018, 10:53 AM)InsiteFX Wrote: Test it with good and bad data to see what happens.

Is the approach correct though?

And what's bad data?
Reply

#6
Bad data is the data that will make transation fail
Reply

#7
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 )
Reply

#8
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();        

        }

Reply


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


Users browsing this thread:
1 Guest(s)


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2018 MyBB Group.