I've seen other similar questions but none are really helpful to what I want to do.

I have three tables; sites, user_sites and site_domains. I want to query a site and the list of users and domains from the user_sites and site_domains tables. Here is an example of tables:

db.sites

+------+-------------+---------------------+--------------+
| id   | site_name   | site_options        | created_on   |
+------+-------------+---------------------+--------------+
| 1    | name1       | {serialized data}   | 10/10/12     |
+------+-------------+---------------------+--------------+
| 2    | name2       | {serialized data}   | 24/10/12     |
+------+-------------+---------------------+--------------+
| 3    | name3       | {serialized data}   | 08/09/12     |
+------+-------------+---------------------+--------------+
| 4    | name4       | {serialized data}   | 03/01/12     |
+------+-------------+---------------------+--------------+
| 5    | name5       | {serialized data}   | 10/12/11     |
+------+-------------+---------------------+--------------+

db.user_sites

+------+-----------+-----------+-----------+
| id   | user_id   | site_id   | default   |
+------+-----------+-----------+-----------+
| 1    | 1         | 1         | 0         |
+------+-----------+-----------+-----------+
| 2    | 3         | 2         | 0         |
+------+-----------+-----------+-----------+
| 3    | 1         | 3         | 0         |
+------+-----------+-----------+-----------+
| 4    | 10        | 1         | 0         |
+------+-----------+-----------+-----------+
| 5    | 5         | 1         | 1         |
+------+-----------+-----------+-----------+

db.site_domains

+------+-----------+-------------------+
| id   | site_id   | site_domain       |
+------+-----------+-------------------+
| 1    | 1         | www.domain1.com   |
+------+-----------+-------------------+
| 2    | 1         | sub.domain2.com   |
+------+-----------+-------------------+
| 3    | 3         | www.domain3.com   |
+------+-----------+-------------------+
| 4    | 2         | www.domain4.com   |
+------+-----------+-------------------+
| 5    | 3         | www.domain5.com   |
+------+-----------+-------------------+

When it's done. I want to be able to populate an object like this:

array (
    [1] => array (
        [site_name]  => 'Name1',
        [created_on] => '10/10/12',
        [site_options]    => {serialized data},
        [domains]    => array (
                            [0] => 'www.domain1.com',
                            [1] => 'www.domain2.com'
                        ),
        [users]      => array (
                            [0] => 1,
                            [1] => 10,
                            [2] => 5
                        )
    ),
    [2] => array (
        [site_name] => 'Name2',
        [created_on] => '24/10/12',
        [site_options] => {serialized data},
        [domains]    => array (
                            [0] => 'www.domain4.com'
                        ),
        [users]      => array (
                            [0] => 3
                        )
    )
)

....and so on.

I'm using Codeigniter to create my application but I can translate it into using Codeigniter if I just knew the SQL for it. SQL isn't my biggest suit but any help would be much appreciated.

I think you will really struggle to get this into one query.

I've not tested this, but something like this may give you some mileage:

function get_site_data() {

    $sites = $this->db->get('sites')->result();

    foreach ($sites as &$row)
    {
       $row['domains'] = $this->get_domains_for_site($row->id);
       $row['users'] = $this->get_users_for_site($row->id);
    }

    return $sites;
}

function get_domains_for_site($site_id) {
    return $this->db->get_where("site_domains", array("site_id" => $site_id))->result();
}

function get_users_for_site($site_id) {
    return $this->db->get_where("user_sites", array("site_id" => $site_id))->result();
}