CodeIgniter Forums
Database trouble - Error Number: 1096 - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Database trouble - Error Number: 1096 (/showthread.php?tid=6377)

Pages: 1 2

Database trouble - Error Number: 1096 - El Forum - 02-24-2008

[eluser]coldKingdom[/eluser]
I get this message

Quote:An Error Was Encountered

Error Number: 1096

No tables used

SELECT *

when i use

Quote://Get data from the database
$this->db->select("*");
$this->db->from('tbl_gallery');
$this->db->join("tbl_users", "gallery_user = usr_id");
$this->db->join("tbl_gallery_albums", "gallery_album = album_id");
$this->db->where('gallery_user', $userid);
$this->db->where('gallery_album', $gallery);
$this->db->order_by('gallery_album');
$this->db->order_by('gallery_uploaded', 'DESC');
$this->db->limit($limit, $offset);

$this->db->count_all_results(); //This one troubles me

When i delete the line $this->db->count_all_results(); everything works perfectly.

I run CI 1.6.1 and MySQL

Thanks!

Database trouble - Error Number: 1096 - El Forum - 02-24-2008

[eluser]Armchair Samurai[/eluser]
I don't think this is a bug - AFAIK count_all_results() does not use select(), which is probably causing the error. Using count_all_results() creates

Code:
SELECT COUNT(*) AS numrows

meaning you can also get rid of the order_by() and limit() without affecting the outcome, so you'd end up with:

Code:
$this->db->from(’tbl_gallery’);
$this->db->join("tbl_users", “gallery_user = usr_id");
$this->db->join("tbl_gallery_albums", “gallery_album = album_id");
$this->db->where(’gallery_user’, $userid);
$this->db->where(’gallery_album’, $gallery);
$total = $this->db->count_all_results();


Database trouble - Error Number: 1096 - El Forum - 02-25-2008

[eluser]coldKingdom[/eluser]
Hi, and thanks!

But i still get the same error message, any other ideas?

It doesn't matter where i put $this->db->count_all_results(); on the site, it still breaks the sql-statement by saying "no tables used".

It works if i put it after $query = $this->db->get(); but then it came up with 1 when did a echo on it, when i echo the $this->db->count_all_results(); when on it's right place the right number comes up, so it's just the error message in the way Smile

Database trouble - Error Number: 1096 - El Forum - 02-25-2008

[eluser]Derek Allard[/eluser]
Which version of CI? I just can't recreate, and although that error message sounds vaguely familiar, I can't even find it in CodeIgniter. Can you provide any further information?

Database trouble - Error Number: 1096 - El Forum - 02-25-2008

[eluser]coldKingdom[/eluser]
Right now i'm running the SVN (1.6.2), i tested 1.6.1 and the same thing happened.

Everything works just fine except for this thing, and i need it for the pagination..
What more do you wanna know?

Database trouble - Error Number: 1096 - El Forum - 02-25-2008

[eluser]Derek Allard[/eluser]
OK, tell you what. Is that error coming from CodeIgniter? If you can tell me where its coming from, I can help you fix it. If you run a search for that error text, can you find it?

Database trouble - Error Number: 1096 - El Forum - 02-25-2008

[eluser]coldKingdom[/eluser]
I can find the Error Number: string in the DB_driver.php on row 308 if that's any help Smile

But the error string No tables used seems to be gone, weird..

The thing is that it works if i do an echo and it prints the right value, but it gives me an error even though it works

Database trouble - Error Number: 1096 - El Forum - 02-26-2008

[eluser]coldKingdom[/eluser]
Now i have done a complete fresh installation of 1.6.1 with no addons or anything activated besides the database library and on a different server.

welcome.php - Controller

Code:
<?php
class Welcome extends Controller {

    function Welcome()
    {
        parent::Controller();    
    }
    
    function index()
    {
        $this->load->model('Gallery');
        $data['query'] = $this->Gallery->get_last_ten_entries();

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

Gallery.php - Model

Code:
<?php
class Gallery extends Model {

    function Gallery()
    {
        // Call the Model constructor
        parent::Model();
    }
    
    function get_last_ten_entries()
    {
        $this->db->select("gallery_picture");
        $this->db->from('tbl_gallery');
        
        $total = $this->db->count_all_results(); //Without this it works perfectly
        
        $query = $this->db->get();
        
        return $query->result();
    }
}
?>

welcome.php - View

Code:
<html>
<head>
<title>Welcome to CodeIgniter</title>
</head>
<body>

<?php
    foreach($query as $item):
        print $item->gallery_picture.'<br /><br />';
    endforeach;
?&gt;

&lt;/body&gt;
&lt;/html&gt;

Error message from CodeIgniter 1.6.1

Quote:An Error Was Encountered
Error Number: 1096

No tables used

SELECT *

I don't know really what more i can do to make it easier. I can test 1.6.0 if you wan't me to.

Edit: Just tested 1.6.0 with the same error

Database trouble - Error Number: 1096 - El Forum - 02-26-2008

[eluser]xwero[/eluser]
[quote author="coldKingdom" date="1204059792"]
Gallery.php - Model

Code:
&lt;?php
class Gallery extends Model {

    function Gallery()
    {
        // Call the Model constructor
        parent::Model();
    }
    
    function get_last_ten_entries()
    {
        $this->db->select("gallery_picture");
        $this->db->from('tbl_gallery');
        
        $total = $this->db->count_all_results(); //Without this it works perfectly
        
        $query = $this->db->get();
        
        return $query->result();
    }
}
?&gt;


Quote:An Error Was Encountered
Error Number: 1096

No tables used

SELECT *

I don't know really what more i can do to make it easier. I can test 1.5.4 and 1.6.0 if you wan't me to.[/quote]

Try this
Code:
function get_last_ten_entries()
    {
        $this->db->start_cache(); //added
        $this->db->select("gallery_picture");
        $this->db->from('tbl_gallery');
        $this->db->stop_cache(); // added
        $total = $this->db->count_all_results(); //Without this it works perfectly
        
        $query = $this->db->get();
        $this->db->flush_cache(); // added
        return $query->result();
    }
Those functions are added in 1.6.1 because the count_all_results method resets all the above sql statement building methods.

Database trouble - Error Number: 1096 - El Forum - 02-26-2008

[eluser]coldKingdom[/eluser]
[quote author="xwero" date="1204060381"]

Try this
Code:
function get_last_ten_entries()
    {
        $this->db->start_cache(); //added
        $this->db->select("gallery_picture");
        $this->db->from('tbl_gallery');
        $this->db->stop_cache(); // added
        $total = $this->db->count_all_results(); //Without this it works perfectly
        
        $query = $this->db->get();
        $this->db->flush_cache(); // added
        return $query->result();
    }
Those functions are added in 1.6.1 because the count_all_results method resets all the above sql statement building methods.[/quote]

Thank you so much, you really saved my day!
It runs perfectly!!

Thanks again!