Revised Update Grid

<?php session_start(); ?>
<?php
include('../login/includes/api.php');
?>
<?php
if(!is_logged_in()) {
    header('Location: ../login/login.php');
    exit;
}
?>
<?php
/**
 * PHP Grid Component
 *
 * @author Abu Ghufran <gridphp@gmail.com> - http://www.phpgrid.org
 * @version 1.5.2
 * @license: see license.txt included in package
 */

// include db config
include_once("../config.php");

// set up DB
mysql_connect(PHPGRID_DBHOST, PHPGRID_DBUSER, PHPGRID_DBPASS);
mysql_select_db(PHPGRID_DBNAME);

// include and create object
include(PHPGRID_LIBPATH."inc/jqgrid_dist.php");
$g = new jqgrid();

// set few params
$grid["caption"] = "GHL | Todays Leads";

// $g->select_command = "Select * From tbl_leads Inner Join dbo_advisers On tbl_leads.Adviser = dbo_advisers.ContactFullName where LeadDate >= Now() - Interval 180 Day";

$g->select_command = "Select
  c.client_id,
  c.Adviser,
  c.Client,
  c.DOB,
  c.Address1,
  c.Address2,
  c.Town,
  c.postcode,
  c.Phone,
  c.Email,
  c.Marketing,
  l.id,
  l.Status,
  l.StatusDate,
  l.CompDate,
  l.NPWDate,
  l.InvalidDate,
  l.Source,
  l.MortgageSize,
  l.MortgageType,
  l.PropertyValue,
  l.plan_type,
  l.provider,
  l.product,
  l.LeadDate,
  l.Cost,
  l.broker_fee
From
  tbl_lead l Inner Join
  tbl_clients c On c.client_id = l.client_id";

  // this db table will be used for add,edit,delete
$g->table = "tbl_lead";

// caption of column
$col = array();
$col["title"] = "client_id"; // caption of column
$col["name"] = "client_id"; // grid column name
$col["editable"] = false;
$col["hidden"] = true; // hide on grid
$col["width"] = "10";
$cols[] = $col;

$col = array();
$col["title"] = "id"; // caption of column
$col["name"] = "id"; // grid column name
$col["editable"] = false;
$col["hidden"] = true; // hide on grid
$col["width"] = "10";
$cols[] = $col;

$col = array();
$col["title"] = "Adviser"; // caption of column
$col["name"] = "Adviser"; // grid column name
$col["dbname"] = "dbo_advisers.ContactFullName"; // this is required as we need to search in name field, not id
$col["editable"] = true;
$col["edittype"] = "select"; // render as select # fetch data from database, with alias k for key, v for value
$str = $g->get_dropdown_values("select distinct ContactFullName as k, ContactFullName as v from dbo_advisers");
$col["editoptions"] = array("value"=>":;".$str);
$col["searchoptions"] = array("value" => ":;".$str);
$col["formatter"] = "select"; // display label, not value
$cols[] = $col;

$col = array();
$col["title"] = "Client"; // caption of column
$col["name"] = "Client"; // grid column name
$col["editable"] = true;
$col["link"] = 'javascript:window.open("http://localhost/form.php/?id={client_id}","newwind","height=600,width=300"); void(0);';
$cols[] = $col;

$col = array();
$col["title"] = "DOB"; // caption of column
$col["name"] = "DOB"; // grid column name, must be exactly same as returned column-name from sql (tablefield or field-alias)
$col["formatter"] = "date";
$col["formatoptions"] = array("srcformat"=>'Y-m-d',"newformat"=>'d/m/Y');
$col["hidden"] = true; // hide on grid
$col["editable"] = true;
$col["editrules"] = array("edithidden"=>true);  // show on add
$cols[] = $col;

$col = array();
$col["title"] = "Address1"; // caption of column
$col["name"] = "Address1"; // grid column name
$col["hidden"] = true; // hide on grid
$col["editable"] = true;
$col["editrules"] = array("edithidden"=>true); // show on add
$cols[] = $col;

$col = array();
$col["title"] = "Address2"; // caption of column
$col["name"] = "Address2"; // grid column name
$col["hidden"] = true; // hide on grid
$col["editable"] = true;
$col["editrules"] = array("edithidden"=>true);  // show on add
$cols[] = $col;

$col = array();
$col["title"] = "Town"; // caption of column
$col["name"] = "Town"; // grid column name
$col["hidden"] = true; // hide on grid
$col["editable"] = true;
$col["editrules"] = array("edithidden"=>true);  // show on add
$cols[] = $col;

$col = array();
$col["title"] = "PostCode"; // caption of column
$col["name"] = "postcode"; // grid column name
$col["hidden"] = false; // hide on grid
$col["editable"] = true;
$col["editrules"] = array("edithidden"=>true);  // show on add
$cols[] = $col;

$col = array();
$col["title"] = "Phone"; // caption of column
$col["name"] = "Phone"; // grid column name
$col["editable"] = true;
$cols[] = $col;

$col = array();
$col["title"] = "Email"; // caption of column
$col["name"] = "Email"; // grid column name
$col["editable"] = true;
$cols[] = $col;

$col = array();
$col["title"] = "Status"; // caption of column
$col["name"] = "Status"; // grid column name
$col["dbname"] = "tbl_status.Status"; // this is required as we need to search in name field, not id
$col["editable"] = true;
$col["edittype"] = "select"; // render as select
# fetch data from database, with alias k for key, v for value
$str = $g->get_dropdown_values("select distinct LeadStatus as k, LeadStatus as v from tbl_status");
$col["editoptions"] = array("value"=>":;".$str);
$col["searchoptions"] = array("value" => ":;".$str);
$col["formatter"] = "select"; // display label, not value
$cols[] = $col;

$col = array();
$col["title"] = "Date"; // caption of column
$col["name"] = "StatusDate"; // grid column name, must be exactly same as returned column-name from sql (tablefield or field-alias)
$col["formatter"] = "date";
$col["formatoptions"] = array("srcformat"=>'Y-m-d',"newformat"=>'d-m-Y');
$col["hidden"] = false; // hide on grid
$col["editable"] = true;
$col["editrules"] = array("edithidden"=>true);  // show on add
$cols[] = $col;

$col = array();
$col["title"] = "Source"; // caption of column
$col["name"] = "Source"; // grid column name
$col["dbname"] = "tbl_source.source"; // this is required as we need to search in name field, not id
$col["editable"] = true;
$col["edittype"] = "select"; // render as select
# fetch data from database, with alias k for key, v for value
$str = $g->get_dropdown_values("select distinct Source as k, Source as v from tbl_source");
$col["editoptions"] = array("value"=>":;".$str);
$col["formatter"] = "select"; // display label, not value
$cols[] = $col;

$col = array();
$col["title"] = "Loan"; // caption of column
$col["name"] = "MortgageSize"; // grid column name
$col["editable"] = true;
$col["hidden"] = true; // hide on grid
$cols[] = $col;

$col = array();
$col["title"] = "Type"; // caption of column
$col["name"] = "MortgageType"; // grid column name
$col["dbname"] = "tbl_mrtgtype.Status"; // this is required as we need to search in name field, not id
$col["editable"] = true;
$col["edittype"] = "select"; // render as select
# fetch data from database, with alias k for key, v for value
$str = $g->get_dropdown_values("select distinct MortgageType as k, MortgageType as v from tbl_mrtgtype");
$col["editoptions"] = array("value"=>":;".$str);
$col["formatter"] = "select"; // display label, not value
$cols[] = $col;

$col = array();
$col["title"] = "Value"; // caption of column
$col["name"] = "PropertyValue"; // grid column name
$col["editable"] = true;
$col["hidden"] = true; // hide on grid
$cols[] = $col;

$col = array();
$col["title"] = "Cost"; // caption of column
$col["name"] = "Cost"; // grid column name
$col["editable"] = true;
$cols[] = $col;

$col = array();
$col["title"] = "Send";
$col["name"] = "email_lead";
$col["width"] = "60";
$col["editable"] = true;
$col["editrules"] = array("edithidden"=>true);  // show on add
$col["edittype"] = "checkbox"; // render as checkbox
$col["editoptions"] = array("value"=>"Yes:No"); // with these values "checked_value:unchecked_value"
$col["editoptions"]["onclick"] = 'send_email(this)';
$col["formatter"] = "checkbox";
$col["formatoptions"]["disabled"] = true;
$cols[] = $col;


// show all other columns after single column format
//$g->set_columns($cols,true);

// pass the cooked columns to grid
$g->set_columns($cols);

// expand grid to screen width
$grid["autowidth"] = true;

//insert data into 2 tables

//$e["js_on_select_row"] = "grid_select";
$e["on_insert"] = array("add_client", null, false);
$e["on_update"] = array("update_client", null, false);
$g->set_events($e);

function add_client($data)
{
    // first insert in clients table
    mysql_query("insert into tbl_clients (Adviser,Client,DOB,Address1,Address2,Town,postcode,Phone,Email,Marketing)
              values ('{$data[params][Adviser]}',
                  '{$data[params][Client]}',
                  '{$data[params][DOB]}',
                              '{$data[params][Address1]}',
                              '{$data[params][Address2]}',
                              '{$data[params][Town]}',
                              '{$data[params][postcode]}',
                              '{$data[params][Phone]}',
                              '{$data[params][Email]}',
                          '{$data[params][Marketing]}')");

    // get insert id
    $client_id = mysql_insert_id();

    // insert in tbl_lead table
    mysql_query("insert into tbl_lead (client_id,Status,StatusDate,CompDate,NPWDate,InvalidDate,Source,MortgageSize,MortgageType,PropertyValue,plan_type,provider,product,Cost,broker_fee,comms)
         values ($client_id,
                '{$data[params][Status]}',
                '{$data[params][StatusDate]}',
                '{$data[params][CompDate]}',
                            '{$data[params][NPWDate]}',
                            '{$data[params][InvalidDate]}',
                            '{$data[params][Source]}',
                            '{$data[params][MortgageSize]}',
                            '{$data[params][MortgageType]}',
                            '{$data[params][PropertyValue]}',
                            '{$data[params][plan_type]}',
                            '{$data[params][provider]}',
                            '{$data[params][product]}',
                            '{$data[params][Cost]}',
                            '{$data[params][broker_fee]}',
                '{$data[params][comms]}')");

    // all done
            die;
}

function update_client($data)
{
        // first insert in tbl_clients table

        mysql_query("update tbl_clients
            set `Adviser` = '{$data[params][Adviser]}',
                 Client = '{$data[params][Client]}',
                 DOB =  '{$data[params][DOB]}',
                             Address1 =  '{$data[params][Address1]}',
                             Address2 =  '{$data[params][Address2]}',
                             Town =  '{$data[params][Town]}',
                             postcode =  '{$data[params][postcode]}',
                             Phone =  '{$data[params][Phone]}',
                             Email = '{$data[params][Email]}',
                             Marketing = '{$data[params][Marketing]}' WHERE client_id = '{$data[params][client_id]}'");

        // insert in tbl_lead table
        mysql_query("update tbl_lead
        set client_id='{$data[params][client_id]}',
            Status = '{$data[params][Status]}',
            StatusDate = '{$data[params][StatusDate]}',
            CompDate = '{$data[params][CompDate]}',
                    NPWDate = '{$data[params][NPWDate]}',
                    InvalidDate = '{$data[params][InvalidDate]}',
                    Source = '{$data[params][Source]}',
                    MortgageSize = '{$data[params][MortgageSize]}',
                    MortgageType = '{$data[params][MortgageType]}',
                    PropertyValue = '{$data[params][PropertyValue]}',
                    plan_type = '{$data[params][plan_type]}',
                    provider = '{$data[params][provider]}',
                    product = '{$data[params][product]}',
                    Cost = '{$data[params][Cost]}',
                    broker_fee = '{$data[params][broker_fee]}',
            comms = '{$data[params][comms]}'WHERE id = '{$data[id]}'");

        // all done
        die;
}

// render grid
$out = $g->render("list1");
?>