Beep Beep - 7 months ago 20
PHP Question

MVC query join command denied

I should be simple enough, I have my query i return it as a variable I then set that variable as a array, the pass it into the view. how ever I get this error..

Error Number: 1142

SELECT command denied to user '******** ip.secureserver.net' for table 'Comments'

SELECT * FROM `Report_Comments`.`Comments`, `Report_Comments`.`Comment_Date`, `Login`.`Username` WHERE `ReportID` = '53'

Filename: models/report/Report_model.php

Line Number: 92


anyone see where i have gone wrong ?

Model

function get_comment()
{
$query = $this->db->get('Report_Comments.Comments, Report_Comments.Comment_Date, Login.Username')
->from('Report_Comments')
->join('Login', 'Report_Comments.UserID = Login.LoginID');
return $query->result();
}


View

<?php if (isset($reports)) :
foreach ($reports as $row) : ?>
<tr>
<td><?php echo $row->Comments; ?></td>
</tr>
<tr>
<td><?php echo $row->Comment_Date; ?></td>
</tr>
<tr>
<td><?php echo $row->Username; ?></td>
</tr>
</table>
<hr>
<?php endforeach; ?>

<?php else : ?>
<p>No Comments</p>
<?php endif; ?>


Controller

function comments()
{
$data = array();
$this->db->where('ReportID', $this->uri->segment(3));

if ($query = $this->report_model->get_comment()) {
$data['reports'] = $query;
}
$this->template['middle'] = $this->load->view($this->middle = 'comments/comment_view', $data, true);
}

Answer

New answer:

Sql is saying you the specified used does not have permission to run a select command on table Comments. You'll want to check that your user has the appropriate permissions for the db and/or table to resolve that mysql issue. Your PHP shouldn't have anything to do with that.

Original answer to original question/issue:

(The edit is throwing me off.)

In order to get a result object that includes rows you need to invoke the function that returns that object.

So in your controller

$data['reports'] = $result->result();

Variable assignment usage

Also in your model, it is useless to set $result = $this->db->get(); The return is going to just pass back $this->db->get(); - remove $result =.

And in your controller, you are testing the value of $this->report_model->get_comment() in if($result = $this->report_model->get_comment()) so, if the value is a get object then how php interprets that as true or false is somewhat loose ended - it'll return its "truthiness" which is not always straight forward. Alternatively, you could do something definite like:

$query = $this->report_model->get_comment();
if ($query->num_rows() > 0) {
    $data['reports'] = $result->result();
}

You could also just pass that $query right to the view and do that test in place of your if (isset($reports)) => if ($query->num_rows() > 0): foreach ($query->result() as $row):. That would reduce one if check.

Database query builder across functions

So, you'll end up with more bugs or weird situations when you build a query through various levels of functions. Also, it's harder to maintain your codebase as it grows since you use your model function in unpredictable ways. Instead of setting the where just before calling the model function, pass the id as a parameter with a predefined value if you want it to be optional:

function get_comment($report_id = null)
{
     if (isset($report_id)) {
         $this->db->where('ReportID', $report_id);
     }
     $this->db->select('Report_Comments.Comments, Report_Comments.Comment_Date, Login.Username')
        ->from('Report_Comments')
        ->join('Login', 'Report_Comments.UserID = Login.LoginID');
    return  $result = $this->db->get();
}

And your controller:

function comments()
{
    $data = array();

    $query = $this->report_model->get_comment($this->uri->segment(3));
    if($query->num_rows() > 0)
    {
        $data['reports'] = $query->result();
    }

    $this->template['middle'] = $this->load->view ($this->middle = 'comments/comment_view',$data, true);
    $this->layout();
}