View providing no results, but table has recent entries

[eluser]Bob Puzld[/eluser]
Hi, I am having problems with my site. The site is a bottle/can collection site, where people can request to have bottles and cans picked up and then they can request payout of their account. Well, the page where you can view all requested payouts is not showing any results even though the table says differently. This has been a problem since the server was upgraded with PHP Version 5.2.6 (previously 4.32) and MySQL Version 5.0.67 (previously 3.23). Any suggestions would be great?? Here is some info to start with...

VIEW PAGE (I added var_dump to check it)

Code:

<h1>Payments Requested (&lt;?=$payouts->num_rows?&gt;)</h1>

&lt;?
    echo "<pre>";
    var_dump ($payouts->result_array);
    echo "</pre>";
?&gt;

&lt;? if($payouts->num_rows != 0): ?&gt;
  &lt;?=form_open('users/payment_requests_process')?&gt;
  <table id="report" cellspacing="0">
  <tr><th>Account Number</th><th>Name</th><th>Address</th><th>Balance</th><th>Amount Requested</th></tr>
  &lt;? $i = 1;
     foreach($payouts->result as $row): ?&gt;
    <tr &lt;?= alternator('',' class="altrow"') ?&gt;>
      <td>&lt;?=$row->id?&gt;&lt;input type="hidden" name="payout&lt;?=$i?&gt;" value="&lt;?=$row-&gt;payout_id?&gt;" /></td>
      <td>&lt;?=$row->fname.' '.$row->lname?&gt;</td>
      <td>&lt;?= $row->address.'<br />'.$row->city.', '.$row->state.' '.$row->zip?&gt;</td>
      <td>&lt;?=$row->balance?&gt;</td>
      <td>&lt;?=$row->amount?&gt;</td>
    </tr>
  &lt;? ++$i;
     endforeach; ?&gt;
  </table>
  &lt;input type="hidden" name="num_payouts" value="&lt;?= $payouts-&gt;num_rows ?&gt;" />
  <p>&lt;input type="submit" value="Pay Requests" /&gt;&lt;/p>
  &lt;?=form_close()?&gt;
&lt;? else: ?&gt;
  No pending payouts.
&lt;? endif; ?&gt;

MODEL PAGE

Code:

function GetPaymentRequests() {
    $this->db->select('users.*, payouts.amount, payouts.id as payout_id');
    $this->db->from('payouts');
    $this->db->join('users','payouts.user_id = users.id','INNER');
    $this->db->where('date_paid','0');
    $this->db->where('organization_id',0);
    $this->db->orderby('date_requested ASC');
    $query = $this->db->get();
    return $query->result_array();
  }

CONTROLLER PAGE

Code:

function payment_requests() {
    $data2 = array('payouts'=>$this->mdlUser->GetPaymentRequests());

    $data = array('page_title'=>'Activate New Users',
                  'left_col'=>$this->load->view('nav',0,true),
                  'right_col'=>$this->load->view('users/payment_requests',$data2,true));

    $this->load->view('index', $data);
  }

  function payment_requests_process() {
    for($i=1;$i<=$_POST['num_payouts'];++$i) {
      $this->db->where('id',$_POST['payout'.$i]);
      $this->db->update('payouts',array('date_paid'=>date('Y-m-d')));
    }

I don't think I missed anything. By the way, I am a newbie CodeIgniter and did not create this site. Any help would be appreciated. Thanks in advance.

-Bob

[eluser]wabu[/eluser]
Hello, if it were me I'd want to determine that the query CI is generating actually returns results. You could trace the query in your MySQL log file and then run it in your preferred client.

You could also try commenting out the where functions (clauses) in your model and see if that returns records. For example, 'date_paid' needs to be '0,' so perhaps there's some sort of default/data typing issue. Maybe something changed in the MySQL upgrade.

This all presumes there's no other error, which based on your description there wasn't.

[eluser]garymardell[/eluser]

Code:

foreach($payouts->result as $row): ?>

should be $payouts->result() as a function i believe to make $row an object.

[eluser]Bob Puzld[/eluser]
Thanks for the idea. I tried adding the () after result, but still get "No pending payouts".

[eluser]Bob Puzld[/eluser]
Grad, thanks for replying. How do I get to my "MySQL log file". I am logged into PhpMyAdmin. Can I find that in here?

Also, I tried commenting out the where clauses. Nothing changed though.

[eluser]wabu[/eluser]
You can find MySQL log docs here:

http://dev.mysql.com/doc/refman/5.0/en/server-logs.html

I'm not sure if phpMyAdmin helps with this or not--maybe somebody else here knows.

But I don't know if that route's going to lead to a quick solution. Before digging into that stuff, how about trying to temporarily comment out the two '$this->db->where' lines in your model? The idea is to eliminate all filter criteria from your query and hopefully return a bunch of records (in the absence of some other, hidden error we're not seeing).

[eluser]Bob Puzld[/eluser]
I did comment out the where clauses, but nothing changed...

[eluser]wabu[/eluser]
Oh, your edit about removing the where functions must have crossed with my last post . . .

From your first post it sounds like other queries are working, so how about temporarily eliminating the join? I know you mentioned the table definitely has data, so barring a connection error which doesn't seem to be the case it must be the query itself?

Can you run the join query successfully in phpMyAdmin (assuming it has that capability)?

Another suggestion is to keep simplifying the model code until you start seeing results.

[eluser]InsiteFX[/eluser]
Also I would re-read the database user guide your orderby clause should be

Note: order_by() was formerly known as orderby(), which has been deprecated.

$this->db->order_by("name", "asc");

Also num_rows should be num_rows() when checking in an if statement.

Enjoy
InsiteFX

[eluser]Bob Puzld[/eluser]
[quote author="wabu" date="1254531957"]Oh, your edit about removing the where functions must have crossed with my last post . . .

From your first post it sounds like other queries are working, so how about temporarily eliminating the join? I know you mentioned the table definitely has data, so barring a connection error which doesn't seem to be the case it must be the query itself?

Can you run the join query successfully in phpMyAdmin (assuming it has that capability)?

Another suggestion is to keep simplifying the model code until you start seeing results.[/quote]

-------------

Sorry it took so long to get back to you. Yes, other queries are working fine. I have eliminated the join and same results. Not sure how to run join in phpMyAdmin. I have also simplified the code as much as possible, but still same results.