Array to string conversion error

问题:

I have a query that's pulling a list of IDs. Those IDs are in an array and I need to search another table with those IDs. I tried using implode to make those IDs a string that I could use in a where clause but I keep getting this error.

My current code is:

 $query = $this->db->query('
        SELECT *
        FROM system_scoperights
        WHERE user = '. $this->session->userdata('username') .'
    ');

    foreach ($query->result() as $row) {
        $scope = $row->site;
        $data[] = $scope;
    } 
    $dataScope[] = $data;

    $idList = implode(',', $dataScope);   <---- Error Line

    $where = 'WHERE scope_scopes.sc_ID IN '. $idList .'';

I've tried different things I found on forums like:

 $idList = implode(',', array_values($dataScope));

and

 $idList = implode(',', join($dataScope));

but none of those work. (I've never even heard of the join function)

Thanks in advance for the help.

回答1:

$dataScope[] = $data;

but

$data[] = $scope;

therefore $dataScope has an array inside it's array. implode only work on one level, so that why you're getting this error.

You should note that this is actually possible in SQL:

 SELECT * FROM some_table WHERE id IN (SELECT site FROM another_table WHERE ... )

which would eliminate the entire need for this code.

That is:

$where = 'WHERE scope_scopes.sc_ID IN (SELECT site
                                       FROM system_scoperights
                                       WHERE user = '. $this->session->userdata('username') . ')';

回答2:

I partially agree with Jay's answer...just remove the line:

$dataScope[] = $data

and use the $data variable directly since it's already an array:

$idList = implode(',', $data);

However you also should use ( and ) in your where clause:

$where = 'WHERE scope_scopes.sc_ID IN (' . $idList . ')';

Using sub-queries in your where clauses, although they do have their place at times, can cost a lot of overhead, especially using 'SELECT *'. Never ask for more than you need from your db tables :)