dongtun2459 2014-02-18 11:56
浏览 35
已采纳

如何优化SQL查询并向现有数组添加更多数组元素?

I've an array named $insert_users as follows:

Array
(
    [0] => 9def02e6337b888d6dbe5617a172c18d
    [1] => a6d22e4cc3f65778a60b359842bcec82
    [2] => e17151c98358b60910d4d9fd6ae73ac4
    [3] => 472d3d7eca74cb3518bcba787aee8540
)

Now I have to fire an SQL query for each of these ids from the above array. The code I've written for this is as follows:

foreach($insert_users as $key => $value ) {


$sql  = " SELECT DISTINCT ud.user_mobile_number, u.user_first_name, u.user_last_name FROM OCN.users_groups_subscribe ugs JOIN OCN.users_details ud ON ud.user_id=ugs.subscribe_user_id JOIN OCN.users u ON u.user_id=ugs.subscribe_user_id WHERE ugs.subscribe_user_id ='".$value."'";

            $this->mDb->Query( $sql);
            $students_data = $this->mDb->FetchArray();                          
            }

It returns me following array for single id element from the $insert_users array:

Array
(
    [0] => Array
        (
            [user_mobile_number] => 9929933345
            [user_first_name] => Ashish
            [user_last_name] => Patil
        )

)

But I'm not getting the array in desired format. I want the array $students_data in following format and for all the values from the array $insert_users:

Array(  
     [0]=>Array
     (
        [user_full_name] => Ashish Patil //This key should be newly generated & should contain values of keys user_first_name & user_last_name concatenated to each other
        [user_mobile_number] => 9929933345
     )
)

Also, can anyone tell me is there any way to fire the SQL query just once and get the desired array which will contain details for all the ids from array $insert_users?

  • 写回答

2条回答 默认 最新

  • dongya4089 2014-02-18 12:07
    关注

    Rather than doing

    foreach($insert_users as $key => $value ) {
    
    
        $sql  = " SELECT DISTINCT ud.user_mobile_number, u.user_first_name, u.user_last_name FROM OCN.users_groups_subscribe ugs JOIN OCN.users_details ud ON ud.user_id=ugs.subscribe_user_id JOIN OCN.users u ON u.user_id=ugs.subscribe_user_id WHERE ugs.subscribe_user_id ='".$value."'";
    
        $this->mDb->Query( $sql);
        $students_data = $this->mDb->FetchArray();                          
    }
    

    Do

    $value = "'" . implode("','", $insert_users) . "'";
    $sql  = " SELECT DISTINCT ud.user_mobile_number, CONCAT(u.user_first_name,' ',u.user_last_name) AS user_full_name FROM OCN.users_groups_subscribe ugs JOIN OCN.users_details ud ON ud.user_id=ugs.subscribe_user_id JOIN OCN.users u ON u.user_id=ugs.subscribe_user_id WHERE ugs.subscribe_user_id IN(" .$value . ")";
    
    $this->mDb->Query( $sql);
    $students_data = $this->mDb->FetchArray();
    

    Now you should get all the result you want in one query

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?

悬赏问题

  • ¥20 firefly-rk3399上启动卡住了
  • ¥15 如何删除这个虚拟音频
  • ¥50 hyper默认的default switch
  • ¥15 网站打不开,提示502 Bad Gateway
  • ¥20 基于MATLAB的绝热压缩空气储能系统代码咨询
  • ¥15 R语言建立随机森林模型出现的问题
  • ¥15 中级微观经济学,生产可能性边界问题
  • ¥15 TCP传输时不同网卡传输用时差异过大
  • ¥15 请各位看看我写的属于什么算法,或者有更正确的写法?
  • ¥15 html5 qrcode 扫描器