Queries Question

[eluser]RS71[/eluser]
I'm currently making a Profile edit form/page which has 4 sections in it:

Basic Info
(has 1 row in a table. since you can only have 1 name, etc)

Educational History
(has n rows in a table. you can have multiple rows with info on each item)

Professional History
(has n rows in a table. you can have multiple rows with info on each item)

Languages
(has n rows in a table. you can have multiple rows with info on each item)

What I am currently doing to fetch these items to pre-populate the form is:

Code:

function get_profile($user_ID)
    {
        $profile= array();
    
        //Fetch BASIC INFO
        $this->db->from('profile_basic_info')->where('user_ID', $user_ID);
        $basic_info = $this->db->get();
        if ($basic_info->num_rows() > 0) {
            $profile['basic_info'] = $basic_info->row();
        }
        
        //Fetch EDU HISTORY
        $this->db->from('profile_edu_history')->where('user_ID', $user_ID);
        $edu_history = $this->db->get();
        if ($edu_history->num_rows() > 0) {
            $profile['edu_history'] = $edu_history->result();
        }
        
        //Fetch PROF HISTORY
        $this->db->from('profile_prof_history')->where('user_ID', $user_ID);
        $prof_history = $this->db->get();
        if ($prof_history->num_rows() > 0) {
            $profile['prof_history'] = $prof_history->result();
        }
        
        //Fetch LANGS
        $this->db->from('profile_lang')->where('user_ID', $user_ID);
        $langs = $this->db->get();
        if ($langs->num_rows() > 0) {
            $profile['langs'] = $langs->result();
        }
        
        return $profile;
    }

So currently I'm doing 4 queries to get four result sets from four tables.

My question is...

Am I doing this right?

Thanks in advance.

[eluser]RS71[/eluser]
Oh forgot to ask this:

I'm only 3/5 of the way into the completing the form code and its currently consuming:

MEMORY USAGE
2,266,544 bytes

I have no idea if this is too much or too little. Is it affected by the queries?

[eluser]ericsodt[/eluser]
[quote author="RS71" date="1233015553"]Oh forgot to ask this:

I'm only 3/5 of the way into the completing the form code and its currently consuming:

MEMORY USAGE
2,266,544 bytes

I have no idea if this is too much or too little. Is it affected by the queries?[/quote]

You can do those queries with join statements, however you're going to have to parse the data to populate your user object, which would make little sense.

I don't know if you have worked with an ORM tool before or not, but I work with Hibernate and Hibernate does exactly what you're doing. One way to speed it up might be to add some index's on the fields that you're querying the most.

Goodluck

[eluser]RS71[/eluser]
[quote author="ericsodt" date="1233017147"][quote author="RS71" date="1233015553"]Oh forgot to ask this:

I'm only 3/5 of the way into the completing the form code and its currently consuming:

MEMORY USAGE
2,266,544 bytes

I have no idea if this is too much or too little. Is it affected by the queries?[/quote]

You can do those queries with join statements, however you're going to have to parse the data to populate your user object, which would make little sense.

I don't know if you have worked with an ORM tool before or not, but I work with Hibernate and Hibernate does exactly what you're doing. One way to speed it up might be to add some index's on the fields that you're querying the most.

Goodluck[/quote]

Hello

Would it be wise to go with the JOIN approach performance wise? (I plan on having lots, but lots of page accesses a day) I don't think I've done queries before that use JOIN when dealing with single row results and multiple results from multiple tables. What would the structure of the results look like?

I don't believe I've worked with ORM tools before. Could you please elaborate?

Thank your for your assistance ericsodt

[eluser]ericsodt[/eluser]
[quote author="RS71" date="1233017760"][quote author="ericsodt" date="1233017147"][quote author="RS71" date="1233015553"]Oh forgot to ask this:

I'm only 3/5 of the way into the completing the form code and its currently consuming:

MEMORY USAGE
2,266,544 bytes

I have no idea if this is too much or too little. Is it affected by the queries?[/quote]

You can do those queries with join statements, however you're going to have to parse the data to populate your user object, which would make little sense.

I don't know if you have worked with an ORM tool before or not, but I work with Hibernate and Hibernate does exactly what you're doing. One way to speed it up might be to add some index's on the fields that you're querying the most.

Goodluck[/quote]

Hello

Would it be wise to go with the JOIN approach performance wise? (I plan on having lots, but lots of page accesses a day) I don't think I've done queries before that use JOIN when dealing with single row results and multiple results from multiple tables. What would the structure of the results look like?

I don't believe I've worked with ORM tools before. Could you please elaborate?

Thank your for your assistance ericsodt [/quote]

Check out:
1. http://www.doctrine-project.org/
2. http://www.hibernate.org/ (reference this, since its for java not PHP)
3. http://www.developertutorials.com/blog/p...n-php-331/

[eluser]RS71[/eluser]
Thank your for your reply, useful information.

I do have a question though, if I plan on having many people accessing this page, I should try to keep queries to a minimum yes? How would I combine a single row result from one table and 3 other multiple row results from 3 other tables into a single query? Can Active Record do this?

[eluser]ericsodt[/eluser]
[quote author="RS71" date="1233089809"]Thank your for your reply, useful information.

I do have a question though, if I plan on having many people accessing this page, I should try to keep queries to a minimum yes? How would I combine a single row result from one table and 3 other multiple row results from 3 other tables into a single query? Can Active Record do this?[/quote]

I am not too sure about active record. If you plan on having many people access this information then I would create indexes and cache query results.

Do some research on query optimization... you should see many results. For example read this


Goodluck

[eluser]RS71[/eluser]
I believe I already have indexes taken care of. About query caching, would that not be an option since my queries would be non identical (each user would equal a different user_ID)?

Could you give me an example on how I could join all those 4 queries?

[eluser]ericsodt[/eluser]
[quote author="RS71" date="1233090664"]I believe I already have indexes taken care of. About query caching, would that not be an option since my queries would be non identical (each user would equal a different user_ID)?

Could you give me an example on how I could join all those 4 queries?[/quote]

as I stated before you could do this, however you would get duplicate data which would require a lot of parsing. if your index's are in place and working as they should (the link I gave you gives you a good way to check and see) then you should be ok.