Calculates the total sum of prices for each record based on specific conditions
Calculate the sum for each record is in associative array form after json decode
converting json format to associative record for single field
Add the sum to the response data or existing $data
Calculate the sum for each social site across all records is in associative array form after json decode
How to remove nonnumeric char to perform calculation
Calculates the total sum of prices for each record based on specific conditions
public function allinfluencerdata(Request $request)
{
log::info("data allinfluencerdata");
$currentURL = url()->current();
$login_email = Auth::user()->email;
$profiles = Addprofile::all();
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', '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();
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);
return response()->json($data);
}
Explanation
Get Current URL and User Email:
$currentURL = url()->current();
$login_email = Auth::user()->email;
Database Query
$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();
this is a complex database query using Laravel's query builder. It fetches data from multiple tables (social_url, countries, states, addcarts, cities) with various joins, conditions, and ordering.
i got data in associative array form output
[
{"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"}
]
Calculate Sums and Log Information:
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");
}
Add the sum to the response data or existing $data
$row->total_price = $sum;
// Add the total sum to the response data
log::info("Record ID: {$row->id}, Total Sum of Prices: $sum");
see differnce check before or after applying query log::info($data);
How to remove 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);
}
}
Calculate the sum for each record is in associative array form after json decode
public function allinfluencerdata(Request $request)
{
log::info("data allinfluencerdata");
$currentURL = url()->current();
$login_email = Auth::user()->email;
$profiles = Addprofile::all();
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', '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',
'countries.country_name',
'states.state_name',
'cities.city_name'
)
->orderBy('id', 'desc')
->get();
foreach ($data as $row) {
$cartSocials = json_decode($row->cart_socials, true);
$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");
}
return response()->json($data);
}
Explanation
Get Current URL and User Email:
$currentURL = url()->current();
$login_email = Auth::user()->email;
Database Query
$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',
'countries.country_name',
'states.state_name',
'cities.city_name'
)
->orderBy('id', 'desc')
->get();
this is a complex database query using Laravel's query builder. It fetches data from multiple tables (social_url, countries, states, addcarts, cities) with various joins, conditions, and ordering.
i got data in associative array form output
[
{"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"}
]
Calculate Sums and Log Information:
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");
}
Record ID: 157, Total Sum: 323
converting json format to associative record for single field
$cartSocials = json_decode($row->cart_socials, true);
before json_decode
{"facebook":"24","youtube":"78","wordpress":"88","instagram":"78","quora":"55"}
after json_decode
array (
'facebook' => '24',
'youtube' => '78',
'wordpress' => '88',
'instagram' => '78',
'quora' => '55',
)
Add the sum to the response data or existing $data
$row->record_sum = $recordSum;
see differnce check before or after applying query log::info($data);
calculate the sum of all social site prices across all records in your dataset
$totalSumAllRecords = 0;
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");
// Accumulate the total sum across all records
$totalSumAllRecords += $recordSum;
}
log::info("Total Sum Across All Records: $totalSumAllRecords");
Calculate the sum for each social site across all records is in associative array form after json decode
public function allinfluencerdata(Request $request)
{
log::info("data allinfluencerdata");
$currentURL = url()->current();
$login_email = Auth::user()->email;
$profiles = Addprofile::all();
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', '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',
'countries.country_name',
'states.state_name',
'cities.city_name'
)
->orderBy('id', 'desc')
->get();
foreach ($data as $row) {
$cartSocials = json_decode($row->cart_socials, true);
foreach ($cartSocials as $platform => $price) {
if (!isset($socialSums[$platform])) {
$socialSums[$platform] = 0;
}
$socialSums[$platform] += $price;
}
$row->socialSums = $socialSums;
log::info("Record ID: {$row->id}, Total Sum: " . json_encode($socialSums));
}
// Add the sum to the response data
log::info($data);
return response()->json($data);
}
Explanation
Get Current URL and User Email:
$currentURL = url()->current();
$login_email = Auth::user()->email;
Calculate Sums and Log Information:
foreach ($data as $row) {
$cartSocials = json_decode($row->cart_socials, true);
foreach ($cartSocials as $platform => $price) {
if (!isset($socialSums[$platform])) {
$socialSums[$platform] = 0;
}
$socialSums[$platform] += $price;
}
$row->socialSums = $socialSums;
log::info("Record ID: {$row->id}, Total Sum: " . json_encode($socialSums));
}
How to remove nonnumeric char to perform calculation
How to remove nonnumeric char to perform calculation like $70
Calculate the sum for each social site across all records is in associative array form after json decode
{"facebook":"$24","youtube":"78","wordpress":"$88","instagram":"78","quora":"55"}
apply it
$numericPrice = floatval(preg_replace('/[^0-9.]/', '', $price));
foreach ($data as $row) {
$cartSocials = json_decode($row->cart_socials, true);
$socialSums = []; // Initialize $socialSums for each row
foreach ($cartSocials as $platform => $price) {
// Remove non-numeric characters and convert to float for summation
$numericPrice = floatval(preg_replace('/[^0-9.]/', '', $price));
if (!isset($socialSums[$platform])) {
$socialSums[$platform] = 0;
}
$socialSums[$platform] += $numericPrice;
}
$row->socialSums = $socialSums;
log::info("Record ID: {$row->id}, Total Sum: " . json_encode($socialSums));
}
Calculate the sum for each record is in associative array form after json decode
foreach ($data as $row) {
$cartSocials = json_decode($row->cart_socials, true);
log::info($cartSocials);
$recordSum = 0;
foreach ($cartSocials as $platform => $price) {
// Remove non-numeric characters and convert to float for summation
$numericPrice = floatval(preg_replace('/[^0-9.]/', '', $price));
$recordSum += $numericPrice;
}
// Add the sum to the response data
$row->record_sum = $recordSum;
log::info("Record ID: {$row->id}, Total Sum: $recordSum");
}
Top comments (0)