0

I added two lines (below) to the MySQL query that broke the code. I can't figure out what needs to be changed. I've checked all relevant files. The error I get is: "No data available in table" as a response in a DataTable. Without those two lines, it works perfectly.

Line A

lp.image AS Picture,

Line B

JOIN LetterIT lp ON lp.letterid = lst.letterid

Below you can see the Model, View, and Controller with the added lines.

Model (Relevant Snippet)

public function get_letter_feed($user_id) { 
$sql = " 
    SELECT 
        l.uuid AS Uuid, 
        ls.`statcode` AS StatusCode, 
        ls.`statname` AS StatusDescription, 
        lst.shortnotes AS Notes, 
        lp.image AS Picture,
        CONCAT(LEFT(l.messages, 65), '') AS MessageBody, 
        l.rcpnt AS Recipient, 
        lst.createdtime AS Created
    FROM Letters l
    JOIN LetterAT lst ON lst.letterid = l.id AND lst.deleted IS NULL
    JOIN LetterST ls ON ls.id=lst.statusId
    JOIN LetterIT lp ON lp.letterid = lst.letterid
    WHERE 
        l.ownerId = {$this->db->escape($user_id)}
    ORDER BY lst.created DESC
    LIMIT 10;";
return $this->db->query($sql)->result();  
}

View (Relevant Snippet)

<table id="tableDataset">
 <thead>
  <tr>
     <th>Recipient</th>
     <th>Message Body</th>
     <th>Date</th>
     <th>Status</th>
  </tr>
</thead>
<tbody>
  <?foreach($letter_feed as $feed_item): ?>
     <tr>
        <td><?=$feed_item->Recipient?> </td> 
        <td><?=$feed_item->MessageBody?> </td>
        <td><?=$feed_item->Created?>  </td>
        <td><?=$feed_item->StatusDescription?> <a href="<?=$feed_item->Picture?>" data-lightbox="photo"><b>(PHOTO)</b></a> </td>
     </tr>
  <?endforeach?>
</tbody>

Controller (Relevant Snippet) 

public function get_letter_status_feed_html() {
$letter_feed = $this->Letter_model->get_letter_feed($this->user->userId);
$this->load->view('dashboard/snippet/letter_status_feed', array('letter_feed'=>$letter_feed));
}

If I am missing anything, please let me know :)

anonameuser22
  • 108
  • 12
  • Have you run the query on your DB directly to eliminate problems with the query? Have you run var_dump($sql); to see your vars are being injected correctly? That eliminates two potential problem causers. – Dan Belden Sep 27 '15 at 07:59
  • I have run the query in my DB directly to eliminate problems with the query. It works perfectly there. I have not run var_dump($sql); to see if any vars are being injected incorrectly. I'll look into that now. Thanks! – anonameuser22 Sep 27 '15 at 08:01
  • Hey @BrianCurliss, I'd also recommend splitting your data retrieval like this: $query = $this->db->query($sql); $result = $query->result(); That way you can var_dump those two properties separately also, you can then get better visibility on your query execution and result access. Hope it helps sir! – Dan Belden Sep 27 '15 at 08:07
  • Still not working. Not sure why those two lines mess it up :/ – anonameuser22 Sep 29 '15 at 20:25
  • 1
    Is it definitely the model code that breaks it, or the additions to the view? If you remove the join and do `'foobar' as Picture,` does it still error? What about if you remove the picture part of the view? – Dezza Sep 29 '15 at 20:33
  • If I remove the join and do foobar as picture, the entire page errors. I've tried removing the picture part of the view. I believe you are correct that the model code breaks it somehow. – anonameuser22 Sep 29 '15 at 20:39
  • If 'lp.image' is a URL, do I need to encoding the string somehow? Is there a chance that this is what's breaking the model? – anonameuser22 Sep 29 '15 at 20:55
  • Is Picture a reserved word? Have you tried aliasing lp.image as something else? and then passing that new property through? – Dan Belden Sep 30 '15 at 05:08
  • Clever angle. I just tried with no luck. Since 'image.lp' is a URL and thinking that the URL might be a problem after seeing [this](http://stackoverflow.com/questions/22943382/parentheses-passed-in-url-break-codigniter-query) and [this](http://stackoverflow.com/questions/311801/url-encoded-forward-slashes-breaking-my-codeigniter-app?rq=1), I tired pulling other data. Not much changed until trying 'lp.created AS Picture' which got a 500 error. 'lp.created' also uses special characters and looks like "2011-07-02 15:51:39". Not sure if this helps, but though the added context might. – anonameuser22 Sep 30 '15 at 05:53
  • I've also tried changing the permitted chars in config.php with no luck. – anonameuser22 Sep 30 '15 at 06:10

0 Answers0