更新或检索数据库中的行的最佳方法是什么？

Question

I refered to Wordpress' database to create config setting for my app in Laravel. I created a app_settings table with id, setting_name and setting_value as columns to store each config name and value in rows. It works fine but the problem occurs whenever I retrieve or update data.

$appsetting = new AppSetting();
return $appsetting->all();

When I query the database it returns me a json like:

[
{
    "id": 37,
    "setting_name": "site_name",
    "setting_value": "Title",
    "created_at": "2015-03-09 10:40:35",
    "updated_at": "2015-03-11 03:23:48"
},
{
    "id": 38,
    "setting_name": "site_url",
    "setting_value": "http://localhost:800",
    "created_at": "2015-03-09 10:40:35",
    "updated_at": "2015-03-11 03:23:48"
},
{
    "id": 39,
    "setting_name": "site_admin",
    "setting_value": "[e-mail]",
    "created_at": "2015-03-09 10:40:35",
    "updated_at": "2015-03-11 03:23:48"
}
]

Case 1:

Whenever I had to use a variable I had to remember the column index to get it's value. Eg. $settings[0]['setting_value'] to retrieve Titleand this make code pretty much static than using something like $settings['site_name'].

Case 2:

If I had to update multiple settings at once, I had to use multiple update commands with where clause.

$appsetting::where('setting_name', '=', 'site_name')->update(['setting_value' => $setting['title']]);

$appsetting::where('setting_name', '=', 'site_url')->update(['setting_value' => $setting['url']]);

$appsetting::where('setting_name', '=', 'site_admin')->update(['setting_value' => $setting['email']]);

What are the best way to update or retrieve rows in database in this case?

I want output like this while using attributes like site_title in rows

[{
    "site_title"    : "Title",
    "site_url"      : "http://localhost",
    "site_admin"    : "local@host"
}]

Answer 1

$output = array();
foreach ($settings as $setting) {
 $output[] = array($setting['setting_name'] => $setting['setting_value']);
}
return json_encode($output);