在laravel 5.2中从数据库中获取数据

Question

I am trying to fetch data from database. For that in controller I have created variables for table_name and select_fields as follows -

$tbl_name="user_master";
$select="'user_master.*', 'country.country_name','city.city_name','login_master.email_id','login_master.password'";
$users=$obj->getdata($tbl_name,$select);  

In model I assigned these variables to query as follows -

Model -

public function getdata($tbl_name,$select)
{
    $users = DB::table($tbl_name)
                ->join('country', 'user_master.country_id', '=', 'country.country_id')
                ->join('city', 'user_master.city_id', '=', 'city.city_id')
                ->join('login_master', 'user_master.user_id', '=', 'login_master.user_id')
                ->select($select)
                ->paginate(5);

    return $users;
}

But It is not working. It is showing error- Syntax error or access violation: 1064 You have an error in your SQL syntax;
If I try as -

$users = DB::table('user_master')
                ->join('country', 'user_master.country_id', '=', 'country.country_id')
                ->join('city', 'user_master.city_id', '=', 'city.city_id')
                ->join('login_master', 'user_master.user_id', '=', 'login_master.user_id')
                ->select('user_master.*', 'country.country_name','city.city_name','login_master.email_id','login_master.password')
                ->paginate(5);

return $users;

This above query working fine but when I assign select fields to variable it is not working.
Please help me.
Thanks.

Answer 1

Your problem is the fact that the coma (,) separating your selection of different table in the select() function is being read as a string, hence it breaks the SQL query. Your select is meant to be like this: select('table1.*', 'table2.someColumn'); Notice the coma is not inside the single quote ''

So try this:

    $select= ['user_master.*', 'country.country_name', 'city.city_name', 'login_master.email_id', 'login_master.password'];