Weird Database Behavior

[eluser]SitesByJoe[/eluser]
Everyone,

Thanks for taking a moment to read this: I'm scratching my head in disbelief atm.

Here's the deal: I have a site that's nearing completion. I go to upgrade my CI installation from 1.6.1 to 1.7.0 (1st stupid mistake since I was almost done)

None of my models work!

I rollback - still my models don't work!

I managed to isolate my newly acquired troubles to one particular piece of code:

This works: $this->db->query('SELECT * FROM songs');

This causes a 1051 error: $this->db->select('songs.*');


Here's the controller method I'm using to test:

Code:

function index()
{
      $query = $this->db->query('SELECT * FROM songs');
      print_r($query);
                
      $this->db->select('songs.*');
      $query2 = $this->db->get();
      print_r();
}

Here's the result in the browser:

CI_DB_mysql_result Object ( [conn_id] => Resource id #48 [result_id] => Resource id #68 [result_array] => Array ( ) [result_object] => Array ( ) [current_row] => 0 [num_rows] => 13 [row_data] => )

An Error Was Encountered

Error Number: 1051

Unknown table 'songs'

SELECT songs.*

What is going on?!?!? I tried reloading my CI files several times!

[eluser]TheFuzzy0ne[/eluser]
You can completely omit the select statement (as you are getting all of the columns), and provide the table name as the first parameter of the get() function call.

Code:

return $this->db->get('songs');

Hope this helps.

[eluser]SitesByJoe[/eluser]
That's true. However, in this case the line in question is part of a larger query that includes joins etc.

That said, my model class throws a 'table not found' error if my query includes anything that goes "table_name.*". It just doesn't make any sense.

[eluser]TheFuzzy0ne[/eluser]
I've noticed that it only seems to do that when no table name has been passed to $this->db->get(). As long as get has a table name passed to it, all should work as expected.

[eluser]SitesByJoe[/eluser]
Here's the whole function from the model. It worked before and structures like this are common in my coding:

Code:

function get_homepage_songs()
{
    $this->db->select('songs.*');
    return $this->db->get();
    $this->db->select('albums.artwork');
    $this->db->select('artists.name');
    $this->db->select('artists.slug');
    $this->db->from('songs');
    $this->db->join('albums', 'albums.id = songs.album_id', 'left');
    $this->db->join('artists', 'artists.id = songs.artist_id', 'left');
    $this->db->where('songs.feature', 1);
    $this->db->orderby('songs.sort_order', 'asc');
    $this->db->limit(10);
    return $this->db->get();
}

It worked up until I tried to upgrade to 1.7.0 and even after I rolled back its not working still. And the whole cannot find the table when its clearly right there is even odder.

Thanks again for looking at this!

[eluser]xzela[/eluser]
why are there two 'return' statements?

Also just a shot in the dark, see if you can print out the SQL query. It might not be appending the 'comma' after the '*'.

[eluser]TheFuzzy0ne[/eluser]
I'm not familiar with older versions of CodeIgniter, as I'm fairly new to it myself, but I can see your code is returning on the second line into the function, and no table has been selected. Remove that line, and it should work.

[eluser]SitesByJoe[/eluser]
Sorry, that first return was only in there for testing.

Here's the real function:

Code:

function get_homepage_songs()
{
    $this->db->select('songs.*');
    $this->db->select('albums.artwork');
    $this->db->select('artists.name');
    $this->db->select('artists.slug');
    $this->db->from('songs');
    $this->db->join('albums', 'albums.id = songs.album_id', 'left');
    $this->db->join('artists', 'artists.id = songs.artist_id', 'left');
    $this->db->where('songs.feature', 1);
    $this->db->orderby('songs.sort_order', 'asc');
    $this->db->limit(10);
    return $this->db->get();
}

It worked up until I tried to upgrade to 1.7.0 and even after I rolled back its not working still. And the whole cannot find the table when its clearly right there is even odder.

Thanks again for looking at this!

[eluser]xzela[/eluser]
Just another shot in the dark: Have you checked your config.php file to make sure all of the database connection information is still there.

Also, are any of the queries from CI working? Or is this just the only one that's causing these issues?

[eluser]TheFuzzy0ne[/eluser]
If the database connection data wasn't there, he wouldn't get as far as he has, he'd just get a database error page stating that there's a problem.

Please could I see the top line of your model, and the constructor?

One more thing to try, is to ensure that your cache (or in Internet Explorer's case; Temporary Internet Files), is clear.