Suggestions for best practices with arrays returned from MySQL queries

skygremlin · January 7, 2014

I have a set of tables for job listing. Right now I have a job table with id that is linked to multiple other tables that contain requirements for this job.

For example:

Job Responsiblities

Job Duties

Job Desired Skills

Job Required Skills

…..and so on. Lets say 10 different tables.

The way my code is set up now I’m getting a couple levels of arrays returned. I’m wondering if there is a way to get rid of one of the levels. OR if this is how it should look. The returned data is displayed on a PHP Page. Some of the information returned is going to a table (Job Title, city, state, country). The rest - tables listed above will be put to a simple list in sections for each job. So an example of that will be:

Job 1

Job Title

Job Responsiblities list

Job Duties list

Job Desired Skills list

Job Required Skills list

Job 2

Job Title

Job Responsiblities list

Job Duties list

Job Desired Skills list

Job Required Skills list

Here is what my return looks like using the <pre> </pre> tags

I'm wondering if I can / should remove the third [0]..

[0][0][0][resp]

[0][0][0][duties]

Code:

 function jb_return(){       
        
        try{
            global $db;
            //Prepare query - Limit 2 now for testing
            $jb_return_set = $db->prepare("SELECT * from job order by job_id desc LIMIT 2");

            //execute
            $jb_return_set->execute();

            //Get results from returned object
            $jb_return_array = $jb_return_set->fetchAll(PDO::FETCH_ASSOC);
            
            //Transfer the returned array - Not sure if I need this, but I wanted to keep the original $jb_return_array as is
            $jb_full_array_return = $jb_return_array;
            
            /*
            * Get Job Specific Details
            */
            $count = 0;
            foreach($jb_return_array as $cur_id){
                //echo '<script type="text/javascript">alert("Job ID Returned is:  '. $cur_id['job_id'] .'")</script>';

                
                //Get Job Responsibilities and add the array to the job array
                $jb_resp= array();
                $jb_resp = get_jb_resp($cur_id['job_id']);
                array_push($jb_full_array_return[$count], array($jb_resp));
                
                //Get Job Duties and add the array to the job array
                $jb_duty= array();
                $jb_duty = get_jb_duties($cur_id['job_id']);
                array_push($jb_full_array_return[$count], array($jb_duty));
               
               
                //Get Job Skills and add the array to the job array

                //Get Job Desired Skills and add the array to the job array
                // and so on with the other tables

                ++$count;
            }
           
            //Return result
            return $jb_full_array_return;

            
            }
        
        catch(PDOException $e){
            echo 'ERROR: Inside jb_return Function' . $e->getMessage();
            }
    }
  
    function get_jb_resp($jb_id){       
        //echo '<script type="text/javascript">alert("Inside the get_jb_resp - Job ID is:  '.$jb_id.'")</script>';
        
        
        try{
            global $db;
            //Prepare query
            $jb_resp_set = $db->prepare("SELECT resp from job_resp where job_id = :jb_id and resp !=\"\"");
            
            //Bind the Value
             $jb_resp_set->bindValue(':jb_id', $jb_id, PDO::PARAM_STR);

            //execute
            $jb_resp_set->execute();

            //Get results from returned object
            $jb_resp_array = $jb_resp_set->fetchAll(PDO::FETCH_ASSOC);
            
            foreach ($jb_resp_array as $row){
                $return_jb_resp_array['resp'][] = $row['resp'];
            }

            //return $jb_resp_array;
            return $return_jb_resp_array;
        
            }
        
        catch(PDOException $e){
            echo 'ERROR: Inside get_jb_resp Function' . $e->getMessage();
            }
    }
    
    function get_jb_duties($jb_id){       

        try{
            global $db;
            //Prepare query
            $get_jb_duty_set = $db->prepare("SELECT duties from job_resp where job_id = :jb_id and duties !=\"\"");
            
            //Bind the Value
             $get_jb_duty_set->bindValue(':jb_id', $jb_id, PDO::PARAM_STR);

            //execute
            $get_jb_duty_set->execute();

            //Get results from returned object
            $get_jb_duty_array = $get_jb_duty_set->fetchAll(PDO::FETCH_ASSOC);
            
            foreach ($get_jb_duty_array as $row){
                $return_jb_duty_array['duties'][] = $row['duties'];
            }
            
            //return $jb_resp_array;
            return $return_jb_duty_array;
        
            }
        
        catch(PDOException $e){
            echo 'ERROR: Inside get_jb_duties Function' . $e->getMessage();
            }
    }

As of now the total job responses are under 15, with multiple (random) items in the other tables..

thanx all

ginerjm · January 7, 2014

Sorry - can't make sense out of all that data, nor do I get what your topic subject is talking about.

I don't know how an sql query can return an array in its result. Since MySQL tables don't contain arrays, where would one obtain an array in a query result?

Maybe you can re-word your question and try to describe your dilemma with less 'example' and more 'content'?

GetFreaky · January 7, 2014

Sorry - can't make sense out of all that data, nor do I get what your topic subject is talking about.

I don't know how an sql query can return an array in its result. Since MySQL tables don't contain arrays, where would one obtain an array in a query result?

Maybe you can re-word your question and try to describe your dilemma with less 'example' and more 'content'?

You can serialize it, similar to a java class which implements serializable

PHP has a similar function

@OP, please clean up your code a bit.

skygremlin · January 7, 2014

Yeah sure - that balance between not enough info and to much... And in all fairness this may not be an issue at all.. My question is how I'm returning my ending result set from the function. My first query returns an array, but before that will be sent back, I'm using a value from that returned array to get more information (more arrays) and adding that to the end...

So my initial array, now contains array elements inside of it. I'm concerned I'm doing a little to much work because my ending return has arrays inside of arrays inside of arrays...

hope that helps a bit... if not I'll try again..

ginerjm · January 7, 2014

Queries don't return arrays.

Why do you need so many queries? Is your db not normalized properly? From the sound of your project, You s/b able to pull up the info you need with a properly written query and then be able to process that resource and display it all in one pass.

skygremlin · January 7, 2014

The number of queries could very well be my the issue..

A brief example:

Table1:

PK - tb1_id

col_info1

col_info2

col_info3

Table2:

PK - tb2_id

Foreign Key tb1_id

col_info1

col_info2

col_info3

Table3:

PK - tb3_id

Foreign Key tb1_id

col_info1

col_info2

col_info3

So what I'm doing is taking the PK tbl1_id and getting all the items from table2 and table3 that have that id..

ginerjm · January 7, 2014

Since those tables all have the exact same symbolic structure, I'm wondering why you don't have their data all in one table. That would be normalization. What makes it necessary to store those records in 3 separate tables?

skygremlin · January 7, 2014

Sorry let me restate these from initial example...

Yeah I guess I could have them all in......2 tables... One for the actual job listing, the the other have columns for each of the requirements for the job...... I guess I wanted to keep things separate and not have a row with 15 columns with only 1 entry and a number of rows per job... But might be better the other way... Is that more the common approach? These are things I'm finding out as I go, and here...

thanx for the help and advice..

job:

PK - job_id

job_info1

job_info2

job_info3

job_resp:

PK - job_resp_id

Foreign Key job_id

job_resp_info

job_duties:

PK - job_duties_id

Foreign Key job_id

job_duties_info

ginerjm · January 7, 2014

If you are tracking job openings I would think you need the following:

jobs table - job id,employer_id, cols for specific items related to each job, cols to track the status of the job (closed, open, etc.)

employer table - info about the companies with job openings with an id to tie it to the jobs they give you. May have multiple records for an employer if they have diff addresses, or contact people.

client table - to track your potential clients and link them to jobs they have seen to avoid duplicating any emailing or stuff you do with all this info. Or if you don't care and this is a one-way system you don't need this table.

skygremlin · January 7, 2014

thank you for your help..

Sign In

Suggestions for best practices with arrays returned from MySQL queries

Recommended Posts

skygremlin

Link to comment

Share on other sites

ginerjm

Link to comment

Share on other sites

GetFreaky

Link to comment

Share on other sites

skygremlin

Link to comment

Share on other sites

ginerjm

Link to comment

Share on other sites

skygremlin

Link to comment

Share on other sites

ginerjm

Link to comment

Share on other sites

skygremlin

Link to comment

Share on other sites

ginerjm

Link to comment

Share on other sites

skygremlin

Link to comment

Share on other sites

Archived

Browse

Activity

Important Information