afbt_insertKeywords($params); break; case 'edit': $afbt_cls->afbt_updateKeywords($params); break; case 'delete': $afbt_cls->afbt_deleteKeywords($params); break; case 'edit_all': $afbt_cls->afbt_editKeywords($params); break; case 'get_posts': $afbt_cls->afbt_get_post_pages($params); break; case 'get_match': $afbt_cls->afbt_get_match($params); break; default: $afbt_cls->afbt_getKeywords($params); return; } class AFBT_Sql { protected $data = array(); public function afbt_getKeywords($params) { $this->data = $this->getRecords($params); echo json_encode($this->data); } public function __construct() { global $wpdb; $this->wpdb = $wpdb; $this->table_name = $afbt_table_name = $this->wpdb->prefix . 'affiliate_boost'; $this->table_name2 = $afbt_table_name2 = $this->wpdb->prefix . 'affiliate_boost_website'; $this->posts = $afbt_table_name3 = $this->wpdb->prefix . 'posts'; } function afbt_check_weblink($params,$type){ $params = sanitize_text_field($params); $check_web = $this->wpdb->get_row("SELECT * from ".$this->table_name2." WHERE website='" .$params. "'"); $check_webs = json_decode( json_encode($check_web), true); if (isset($check_webs)) { $lastid = $check_webs['w_id']; }else{ $insert_web = $this->wpdb->query( $this->wpdb->prepare("INSERT INTO ".$this->table_name2." (w_id, post_type,website) VALUES(null, '".sanitize_text_field($type)."', '".$params."')")); $lastid = $this->wpdb->insert_id; } return $lastid; } // Insert the new record function afbt_insertKeywords($params) { $lastid = $this->afbt_check_weblink($params["add_website"],$params["type_page"]); if(!empty($lastid)){ foreach($params["keywords"] as $key =>$key_link){ $affl_link = preg_replace('/ /', '', $params["affiliate_link"][$key]); $sql = $this->wpdb->query( $this->wpdb->prepare("INSERT INTO ".$this->table_name." (parent_id, keywords, affiliate_link) VALUES('" . sanitize_text_field($lastid) . "', '" . sanitize_text_field($key_link) . "','" . sanitize_text_field($affl_link) . "')")); echo $sql; } } } // Get the all records from the db function getRecords($params) { $rp = isset($params['rowCount']) ? $params['rowCount'] : 10; if (isset($params['current'])) { $page = $params['current']; } else { $page=1; }; $start_from = ($page-1) * $rp; $insert_sql = $sqlRec = $sqlTotal = $where = ''; if( !empty($params['searchPhrase']) ) { $where .=" WHERE "; $where .=" ( website LIKE '".$params['searchPhrase']."%' )"; } if( !empty($params['sort']) ) { $where .=" ORDER By ".key($params['sort']) .' '.current($params['sort'])." "; } // getting total number records without any search $insert_sql = "SELECT * FROM ".$this->table_name2.""; $sqlTotal .= $insert_sql; $sqlRec .= $insert_sql; //concatenate search sql if value exist if(isset($where) && $where != '') { $sqlTotal .= $where; $sqlRec .= $where; }else{ $sqlRec .= ' ORDER By w_id DESC'; } if ($rp!=-1) $sqlRec .= " LIMIT ". $start_from .",".$rp; $qtot = $this->wpdb->get_results($sqlTotal); $queryRecords = $this->wpdb->get_results($sqlRec); $count=1; if(!empty($queryRecords)){ foreach($queryRecords as $row){ $array_count =''; $array_count= array("w_id" => $count ); $rows = json_decode( json_encode($row), true); $title = "(".home_url(sanitize_title( $rows['website'] )).")"; $new_rowarray= array('website'=>$rows['website']." ".$title,'web_id'=>$rows['w_id']); $array_merge = array_merge($array_count, $new_rowarray); $data[] = $array_merge; $count++; } $json_data = array( "current" => intval($params['current']), "rowCount" => 10, "total" => intval(count($qtot)), "rows" => $data // total data array ); return $json_data; } } // Update the record into db function afbt_updateKeywords($params) { $lastid = $this->afbt_check_weblink($params["edit_website"],$params["type_page_edit"]); // check the website url if(!empty($lastid)){ foreach($params["keys_id"] as $key_id=>$ids){ $affl_link = preg_replace('/ /', '', $params["affiliate_link_edit"][$key_id]); $update_sql = $this->wpdb->query( $this->wpdb->prepare("Update ".$this->table_name." set parent_id = '" . $lastid . "', keywords='" .sanitize_text_field($params["keywords_edit"][$key_id])."', affiliate_link='" . sanitize_text_field($affl_link) . "' WHERE id='".$ids."'")); } if(!empty($params["keywords_edit_new"])){ foreach($params["keywords_edit_new"] as $key =>$key_val){ $affl_linkn = preg_replace('/ /', '', $params["affiliate_link_edit_new"][$key]); $update_sql = $this->wpdb->query( $this->wpdb->prepare("INSERT INTO ".$this->table_name." (parent_id, keywords, affiliate_link) VALUES('" .$lastid . "', '" . sanitize_text_field($key_val) . "','" . sanitize_text_field($affl_linkn) . "')")); } } echo $update_sql; } } // Delete record from tables function afbt_deleteKeywords($params) { if(!empty($params["key_id"])){ $delete_sql = $this->wpdb->query( $this->wpdb->prepare("delete from ".$this->table_name." WHERE id='".$params["key_id"]."'")); }else{ $delete_sql = $this->wpdb->query( $this->wpdb->prepare("delete from ".$this->table_name." WHERE parent_id='".$params["id"]."'")); $del_web_link= $this->wpdb->query( $this->wpdb->prepare("delete from ".$this->table_name2." WHERE w_id='".$params["id"]."'")); } echo $delete_sql; } // Edit records function afbt_editKeywords($params) { $query_weblink= $this->wpdb->get_results("SELECT * from ".$this->table_name2." WHERE w_id='" .$params['id'] . "'"); $get_weblink = json_decode( json_encode($query_weblink), true); $queryRecords = $this->wpdb->get_results("SELECT * from ".$this->table_name." WHERE parent_id='" .$params['id'] . "'"); $check_webs = json_decode( json_encode($queryRecords), true); $count=1; $queryRecords_type = $this->wpdb->get_results("SELECT * from ".$this->posts." WHERE post_status='publish' AND post_type='" . $get_weblink[0]['post_type'] . "'"); $check_records= json_decode( json_encode($queryRecords_type), true); if(!empty($check_records)){ $html_option='Select Option'; foreach($check_records as $row){ if($row['post_title'] == $get_weblink[0]['website']){ $html_option.=''.$row['post_title'].''; }else{ $html_option.=''.$row['post_title'].''; } } } $html=''; if(!empty($check_webs)){ $html = ''; foreach($check_webs as $row){ $html.='

x

'; $count++; } echo $html."++".$html_option."++".$get_weblink[0]['post_type']; }else{ echo $html."++".$html_option."++".$get_weblink[0]['post_type']; } } /* * get the post type */ function afbt_get_post_pages($params){ $queryRecords_type = $this->wpdb->get_results("SELECT * from ".$this->posts." WHERE post_status='publish' AND post_type='" .$params['page_type'] . "'"); $get_post_page= json_decode( json_encode($queryRecords_type), true); if(!empty($get_post_page)){ $html='Select Option'; foreach($get_post_page as $row){ $html.=''.$row['post_title'].''; } echo $html; } } function afbt_get_match($params){ $query_weblink= $this->wpdb->get_results("SELECT * from ".$this->table_name2." WHERE website='" .$params['page_val'] . "'"); $get_weblink = json_decode( json_encode($query_weblink), true); if($get_weblink){ $query_weblink_check= $this->wpdb->get_results("SELECT keywords from ".$this->table_name." WHERE parent_id='" .$get_weblink[0]['w_id'] . "' AND keywords='".$params['value']."'"); $get_weblink_check = json_decode( json_encode($query_weblink_check), true); if($get_weblink_check){ echo "Exists"; }else{ echo "not exists"; } }else{ echo "not exists"; } } } ?>