Sending sum of field to another table
sending single value of record of one table to another table
sending value of multiple record of one table to another table as key value pair
Sending sum of field to another table
step 1: retrive data from table
$data = DB::connection('payments')->table("social_url")
->leftJoin('countries', 'social_url.country_id', '=', 'countries.country_id')
->leftJoin('states', 'social_url.state_id', '=', 'states.state_id')
->leftJoin('addcarts', 'social_url.user_id', '=', 'addcarts.influencer_admin_id')
->leftJoin('cities', 'social_url.city_id', '=', 'cities.city_id')
->where('social_url.user_email', '<>', $login_email)
->select(
'social_url.*',
'addcarts.influencer_admin_id',
'addcarts.cart_socials',
'addcarts.admin_id',
'addcarts.twitter_price',
'addcarts.youtube_price',
'addcarts.face_price',
'addcarts.wordpress_price',
'addcarts.tumblr_price',
'addcarts.instagram_price',
'addcarts.quora_price',
'addcarts.reddit_price',
'addcarts.koo_price',
'addcarts.scoopit_price',
'addcarts.slashdot_price',
'addcarts.fb_grp_price',
'addcarts.linkedin_grp_price',
'addcarts.telegram_price',
'addcarts.linkedin_price',
'addcarts.roposo_price',
'addcarts.chingari_price',
'addcarts.mitron_price',
'addcarts.pinterest_price',
'countries.country_name',
'states.state_name',
'cities.city_name'
)
->orderBy('id', 'desc')
->get();
step2: sending data to another table
$row->total_price = $sum;
foreach ($data as $row) {
$sum = 0;
foreach ($row as $field => $value) {
if (strpos($field, '_price') !== false && is_numeric($value)) {
// Check if the field name ends with '_price' and the value is numeric
$sum += floatval($value);
}
}
$row->total_price = $sum;
// Add the total sum to the response data
log::info("Record ID: {$row->id}, Total Sum of Prices: $sum");
}
log::info($data);
[
{"id":163,"user_id":"32","user_name":"rakeshkumar","user_email":"vibiv32093@bitofee.com","file_pic":"","slug_id":null,"slug":"rakeshkumar","slugname":null,"country_id":"101","state_id":"4025","city_id":"58078","mobile":"7488127637","digital_marketer":null,"bio":"influencer","social_site":"{\"facebook\":\"https:\\/\\/www.facebook.com\\\/ravi.cotocus\",\"twitter\":\"https:\\\/\\\/www.twitter.com\\\/rajesh.cotocus\",\"youtube\":null,\"wordpress\":null,\"tumblr\":null,\"instagram\":null,\"quora\":null,\"pinterest\":null,\"reddit\":null,\"koo\":null,\"scoopit\":null,\"slashdot\":null,\"telegram\":null,\"fb_grp\":null,\"linkedin_grp\":null,\"linkedin\":null,\"roposo\":null,\"chingari\":null,\"mitron\":null}","social_price":"{\"facebook\":\"24\",\"twitter\":\"67\",\"youtube\":null,\"wordpress\":null,\"tumblr\":null,\"instagram\":null,\"quora\":null,\"pinterest\":null,\"reddit\":null,\"koo\":null,\"scoopit\":null,\"slashdot\":null,\"telegram\":null,\"fb_grp\":null,\"linkedin_grp\":null,\"linkedin\":null,\"roposo\":null,\"chingari\":null,\"mitron\":null}","influencer_admin_id":32,"cart_socials":"{\"facebook\":\"24\"}","admin_id":"26","country_name":"India","state_name":"Jharkhand","city_name":"Bok\u0101ro"},
{"id":157,"user_id":"28","user_name":"admins","user_email":"admins@gmail.com","file_pic":"","slug_id":null,"slug":"admins","slugname":null,"country_id":"101","state_id":"4025","city_id":"58078","mobile":"7488127637","digital_marketer":"faceinfluencer","bio":"dxfbfvb","social_site":"{\"facebook\":\"http:\\\/\\\/www.facebook.com\\\/ravimy.cotocus\",\"twitter\":null,\"youtube\":\"https:\\\/\\\/www.youtube.com\\\/watch?v=4Tpm7D4-6DU&list=RDGMEMPipJmhsMq3GHGrfqf4WIqA&index=27\",\"wordpress\":\"https:\\\/\\\/wordpress.com\\\/\",\"tumblr\":null,\"instagram\":\"https:\\\/\\\/www.instagram.com\\\/\",\"quora\":\"https:\\\/\\\/www.quora.com\\\/\",\"pinterest\":null,\"reddit\":null,\"koo\":null,\"scoopit\":null,\"slashdot\":null,\"telegram\":null,\"fb_grp\":null,\"linkedin_grp\":null,\"linkedin\":null,\"roposo\":null,\"chingari\":null,\"mitron\":null}","social_price":"{\"facebook\":\"24\",\"twitter\":null,\"youtube\":\"78\",\"wordpress\":\"88\",\"tumblr\":null,\"instagram\":\"78\",\"quora\":\"55\",\"pinterest\":null,\"reddit\":null,\"koo\":null,\"scoopit\":null,\"slashdot\":null,\"telegram\":null,\"fb_grp\":null,\"linkedin_grp\":null,\"linkedin\":null,\"roposo\":null,\"chingari\":null,\"mitron\":null}","influencer_admin_id":28,"cart_socials":"{\"facebook\":\"24\",\"youtube\":\"78\",\"wordpress\":\"88\",\"instagram\":\"78\",\"quora\":\"55\"}","admin_id":"26","country_name":"India","state_name":"Jharkhand","city_name":"Bok\u0101ro"}
]
optimizing-sum-calculations-in-laravel-queries-for-influencers
Some more Examples
foreach ($data as $row) {
$sum = 0;
foreach ($row as $field => $value) {
if (strpos($field, '_price') !== false && is_numeric($value)) {
// Check if the field name ends with '_price' and the value is numeric
$sum += floatval($value);
}
}
$row->total_price = $sum;
// Add the total sum to the response data
log::info("Record ID: {$row->id}, Total Sum of Prices: $sum");
}
after removing nonnumeric char to perform calculation like $70
foreach ($data as $row) {
$sum = 0;
foreach ($row as $field => $value) {
if (strpos($field, '_price') !== false && is_numeric($value)) {
// Extract numeric part using regular expression
preg_match_all('/[0-9,.]+/', $value, $matches);
$numericValue = implode('', $matches[0]);
$sum += floatval($numericValue);
}
}
Sending another table after calculating sum from key value pair
foreach ($data as $row) {
$cartSocials = json_decode($row->cart_socials, true);
log::info($cartSocials);
$recordSum = 0;
foreach ($cartSocials as $platform => $price) {
$recordSum += $price;
}
// Add the sum to the response data
$row->record_sum = $recordSum;
log::info("Record ID: {$row->id}, Total Sum: $recordSum");
}
sending single value of record of one table to another table
$taskdata=sharedata::where('admin_id', $id)->get();
foreach ($users as $user) {
// Find the corresponding sharedata based on orders_id and order_id
$sharedata = $taskdata
->where('orders_id', $user->order_id)
->first();
// Check if sharedata is found and set values accordingly
if ($sharedata) {
$user->order_product_id = $sharedata->order_product_id;
$user->tasklockstatus = $sharedata->tasklockstatus;
$user->status = $sharedata->status;
} else {
// If there is no matching sharedata, set default values or leave them as null
$user->order_product_id = null;
$user->tasklockstatus = null;
}
}
output
[{"id":5,"payment_id":"PAYID-ORDS68155146","influencer_admin_id":"[28]","payer_id":null,"payer_email":null,"amount":1.1,"currency":null,"payment_status":"approved","admin_id":"26","user_name":"rakeshdev","Pay_date":"2024-02-06","cart_id":"CART-p3Kr-26","product_id":"[325]","order_id":"ORD-1707200651-6939","slug":null,"org_slug":null,"admin_email":"rakeshdev.cotocus@gmail.com","influencer_email":"[\"admins@gmail.com\"]","influencer_name":"[\"admins\"]","created_at":"2024-02-06T07:23:13.000000Z","updated_at":"2024-02-06T06:24:11.000000Z","order_product_id":"325","tasklockstatus":"locked","status":"Approve"},{"id":6,"payment_id":"PAYID-ORDS98181036","influencer_admin_id":"[28,28,32]","payer_id":null,"payer_email":null,"amount":1.1,"currency":null,"payment_status":"approved","admin_id":"26","user_name":"rakeshdev","Pay_date":"2024-02-09","cart_id":"CART-4rgo-26","product_id":"[325,330,331]","order_id":"ORD-1707446725-1697","slug":null,"org_slug":null,"admin_email":"rakeshdev.cotocus@gmail.com","influencer_email":"[\"admins@gmail.com\",\"admins@gmail.com\",\"vibiv32093@bitofee.com\"]","influencer_name":"[\"admins\",\"admins\",\"rakeshkumar\"]","created_at":"2024-02-09T03:44:30.000000Z","updated_at":"2024-02-09T02:45:25.000000Z","order_product_id":"325","tasklockstatus":"place","status":"not approved"}]
sending value of multiple record of one table to another table as key value pair
$users = Paytm::whereIn('order_id', $shareresult)->get();
log::info("myshares");
log::info($users);
foreach ($users as $user) {
// Find the corresponding sharedata based on orders_id and order_id
$product = $user->product_id;
log::info("product id");
log::info($product);
log::info($user->order_id);
$myid= $user->order_id;
$sharedata=sharedata::where('admin_id', $id)->where('orders_id', $myid)->get();
log::info("sharedata");
$user->tasklockstatus = [];
$user->status = [];
$taskLockStatusArray = [];
$statusArray = [];
if ($sharedata->isNotEmpty()) {
foreach ($sharedata as $data) {
// Access properties of each $data item and store in separate arrays
$taskLockStatusArray[$data->order_product_id] = $data->tasklockstatus;
$statusArray[$data->order_product_id] = $data->status;
// Perform actions with retrieved data
}
} else {
log::info("rfgv id");
}
// Assign the arrays to the user object outside the loop
$user->tasklockstatus = $taskLockStatusArray;
$user->status = $statusArray;
}
log::info("mydgvdfshares");
log::info($users);
"tasklockstatus":{"325":"place","330":"place","331":"place"},"status":{"325":"not approved","330":"not approved","331":"not approved"}
[{"id":5,"payment_id":"PAYID-ORDS68155146","influencer_admin_id":"[28]","payer_id":null,"payer_email":null,"amount":1.1,"currency":null,"payment_status":"approved","admin_id":"26","user_name":"rakeshdev","Pay_date":"2024-02-06","cart_id":"CART-p3Kr-26","product_id":"[325]","order_id":"ORD-1707200651-6939","slug":null,"org_slug":null,"admin_email":"rakeshdev.cotocus@gmail.com","influencer_email":"[\"admins@gmail.com\"]","influencer_name":"[\"admins\"]","created_at":"2024-02-06T07:23:13.000000Z","updated_at":"2024-02-06T06:24:11.000000Z","tasklockstatus":{"325":"locked"},"status":{"325":"Approve"}},{"id":6,"payment_id":"PAYID-ORDS98181036","influencer_admin_id":"[28,28,32]","payer_id":null,"payer_email":null,"amount":1.1,"currency":null,"payment_status":"approved","admin_id":"26","user_name":"rakeshdev","Pay_date":"2024-02-09","cart_id":"CART-4rgo-26","product_id":"[325,330,331]","order_id":"ORD-1707446725-1697","slug":null,"org_slug":null,"admin_email":"rakeshdev.cotocus@gmail.com","influencer_email":"[\"admins@gmail.com\",\"admins@gmail.com\",\"vibiv32093@bitofee.com\"]","influencer_name":"[\"admins\",\"admins\",\"rakeshkumar\"]","created_at":"2024-02-09T03:44:30.000000Z","updated_at":"2024-02-09T02:45:25.000000Z","tasklockstatus":{"325":"place","330":"place","331":"place"},"status":{"325":"not approved","330":"not approved","331":"not approved"}}]
Sending single field of another table
public function allinfluencerdata(Request $request) {
log::info("data allinfluencerdata");
$currentURL = url()->current();
$login_email = Auth::user()->email;
// Fetch data from Addprofile table
$addProfilesData = Addprofile::all(['user_id', 'file_pic']);
$profiles = $addProfilesData->pluck('file_pic', 'user_id');
$data = DB::connection('payments')->table("social_url")
->leftJoin('countries', 'social_url.country_id', '=', 'countries.country_id')
->leftJoin('states', 'social_url.state_id', '=', 'states.state_id')
->leftJoin('addcarts', function($join) use ($login_email) {
$join->on('social_url.user_id', '=', 'addcarts.influencer_admin_id')
->where('addcarts.admin_email', '=', $login_email);
})
->leftJoin('cities', 'social_url.city_id', '=', 'cities.city_id')
->where('social_url.user_email', '<>', $login_email)
->whereNotNull('social_url.social_price')
->select(
'social_url.user_id',
DB::raw('MAX(social_url.id) as max_id'),
'social_url.*',
'addcarts.influencer_admin_id',
'addcarts.cart_socials',
'addcarts.admin_id',
'countries.country_name',
'states.state_name',
'cities.city_name'
)
->groupBy('social_url.user_id')
->orderBy('max_id', 'desc')
->get();
// Merge data from Addprofile table based on user_id
foreach ($data as $key => $value) {
$user_id = $value->user_id;
$data[$key]->file_pic = $profiles[$user_id] ?? null;
}
log::info($data);
return response()->json($data);
}
==============================================================
Practical Code
public function allinfluencerdata(Request $request)
{
log::info("data allinfluencerdata");
$currentURL = url()->current();
$login_email = Auth::user()->email;
$profile = Addprofile::all();
$addProfilesData = Addprofile::all(['user_id', 'file_pic']);
$profiles = $addProfilesData->pluck('file_pic', 'user_id');
log::info("data if ke andar hai");
$data = DB::connection('payments')->table("social_url")
->leftJoin('countries', 'social_url.country_id', '=', 'countries.country_id')
->leftJoin('states', 'social_url.state_id', '=', 'states.state_id')
->leftJoin('addcarts', function($join) use ($login_email) {
$join->on('social_url.user_id', '=', 'addcarts.influencer_admin_id')
->where('addcarts.admin_email', '=', $login_email);
})
->leftJoin('cities', 'social_url.city_id', '=', 'cities.city_id')
->where('social_url.user_email', '<>', $login_email)
->whereNotNull('social_url.social_price')
->select(
'social_url.user_id',
DB::raw('MAX(social_url.id) as max_id'),
'social_url.id',
'social_url.user_name',
'social_url.user_email',
'social_url.slug_id',
'social_url.slug',
'social_url.country_id',
'social_url.state_id',
'social_url.city_id',
'social_url.mobile',
'social_url.digital_marketer',
'social_url.bio',
'social_url.social_site',
'social_url.social_price',
'social_url.social_currency',
'addcarts.influencer_admin_id',
'addcarts.cart_socials',
'addcarts.admin_id',
'countries.country_name',
'states.state_name',
'cities.city_name'
)
->groupBy('social_url.user_id')
->orderBy('max_id', 'desc')
->get();
foreach ($data as $key => $value) {
$user_id = $value->user_id;
$data[$key]->file_pic = $profiles[$user_id] ?? null;
}
log::info($data);
return response()->json($data);
}
Sending single field to another table single row
$addcart = addcart::all(['cart_id', 'cart_socials']);
$addcartdata = $addcart->pluck('cart_socials', 'cart_id');
===============================
$users= DB::connection('payments')
->table("sharedatas")->where('id', $id)->where('orders_id', $orders_id)
->where('order_product_id', $order_product_id)->first();
========================================
if ($users) {
$order_cart_id = $users->order_cart_id;
$users->cart_socials = $addcartdata[$order_cart_id] ?? null;
}
$users_order = Paytm::find($id);
Log::info('order this function accounts_admin_edit '.$users_order);
$mys_order = Paytm::where('id',$id)->value('influencer_admin_id');
$dataArray = json_decode($mys_order);
$userNames = [];
foreach ($dataArray as $value) {
$userName = Addprofile::where('user_id', $value)->value('user_name');
$userNames[] = $userName;
}
if ($users_order) {
$users_order->influencer_name = $userNames;
}
explain
$mys_order = Paytm::where('id', $id)->value('influencer_admin_id');
Log::info("mutryuu");
Log::info($mys_order);
[2024-03-22 10:12:29] local.INFO: [84,22]
Log::info($userNames);
[2024-03-22 10:12:29] local.INFO: array (
0 => 'Ashwani',
1 => 'sourav',
)
public function influencerview(Request $request)
{
Log::info('influencerview me aata hain na');
Log::info($request);
$id=$request->id;
$orders_id=$request->orders_id;
$order_product_id=$request->order_product_id;
$input = $request->all();
$addcart = addcart::all(['cart_id', 'cart_socials']);
$addcartdata = $addcart->pluck('cart_socials', 'cart_id');
Log::info('influencerview me getClientOriginalName',$request->all());
$users= DB::connection('payments')
->table("sharedatas")->where('id', $id)->where('orders_id', $orders_id)
->where('order_product_id', $order_product_id)->first();
if ($users) {
$order_cart_id = $users->order_cart_id;
$users->cart_socials = $addcartdata[$order_cart_id] ?? null;
}
return response()->json($users);
}
Top comments (0)