Debug School

rakesh kumar
rakesh kumar

Posted on • Updated on

Migrating and Inserting Data in Laravel Tables

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();
Enter fullscreen mode Exit fullscreen mode

step2: sending data to another table

 $row->total_price = $sum;
Enter fullscreen mode Exit fullscreen mode
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);
Enter fullscreen mode Exit fullscreen mode

[
{"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"}
]  
Enter fullscreen mode Exit fullscreen mode

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");
            }
Enter fullscreen mode Exit fullscreen mode

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);
                    }
                }
Enter fullscreen mode Exit fullscreen mode

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");
}
Enter fullscreen mode Exit fullscreen mode

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;
                            }
                        }
Enter fullscreen mode Exit fullscreen mode

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"}]  
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode
"tasklockstatus":{"325":"place","330":"place","331":"place"},"status":{"325":"not approved","330":"not approved","331":"not approved"}
Enter fullscreen mode Exit fullscreen mode
 [{"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"}}] 
Enter fullscreen mode Exit fullscreen mode

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);
}
Enter fullscreen mode Exit fullscreen mode

==============================================================
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);
}
Enter fullscreen mode Exit fullscreen mode

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;
        }
Enter fullscreen mode Exit fullscreen mode
  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);           


    }
Enter fullscreen mode Exit fullscreen mode

Top comments (0)