Querying multiple tables to pull data to show to the user

Question

I have a function that I use to display a relatively large amount of data to the user:

 public function getAllProfileData() {
    $sql = "SELECT * FROM user_data WHERE user_id = :user_id";
    $query = $this->db->prepare($sql);
    $query->execute(array(':user_id' => $_SESSION['user_id']));
    $user_data = $query->fetchAll();
    foreach ($user_data as $key => $value) {
        $user_id = $value->user_id;
        $user_gender = $value->user_gender;
        $user_dob_day = $value->user_dob_day;
        $user_dob_month = $value->user_dob_month;
        $user_dob_year = $value->user_dob_year;
        $user_ethnicity = $value->user_ethnicity;
        $user_country = $value->user_country;
        $user_type = $value->user_type;
        $user_city = $value->user_city;
        $user_seeking = $value->user_seeking; //seeking  male/female 
        $user_lookingfor = $value->user_lookingfor; // long term
        $user_postalcode = $value->user_postalcode;
        $user_intent = $value->user_intent; // what the user want's out of the realtionship 
        $user_longestrelationship = $value->user_longestrelationship;
        $user_firstname = $value->user_firstname;
        $user_firstname_show = $value->user_firstname_show;
        $user_smoke = $value->user_smoke;
        $user_height = $value->user_height;
        $user_datesmoker = $value->user_datesmoker;  //bool
        $user_bodytype = $value->user_bodytype;
        $user_drugs = $value->user_drugs;
        $user_haircolor = $value->user_haircolor;
        $user_drink = $value->user_drink;
        $user_religion = $value->user_religion;
        $user_religionmatter = $value->user_religionmatter;
        $user_car = $value->user_car;               //bool
        $user_haschildren = $value->user_haschildren;
        $user_profession = $value->user_profession;
        $user_wantchildren = $value->user_wantchildren;
        $user_income = $value->user_income;
        $user_datekids = $value->user_datekids; //bool
        $user_education = $value->user_education;
        $user_pets = $value->user_pets;         //bool
        $user_siblings = $value->user_siblings; //bool
        $user_birthorder = $value->user_birthorder;
        $user_eyecolour = $value->user_eyecolour;
        $user_secondlanguage = $value->user_secondlanguage;
        $user_weight = $value->user_weight;
    }


    $sql = "SELECT * FROM gender WHERE id = $user_gender";
    $query = $this->db->prepare($sql);
    $query->execute();
    $user_gender_text = $query->fetch()->description;

    $sql = "SELECT * FROM ethnicity WHERE id = $user_ethnicity";
    $query = $this->db->prepare($sql);
    $query->execute();
    $user_ethnicity_text = $query->fetch()->description;


    $birthDate = $user_dob_month . "/" . $user_dob_day . "/" . $user_dob_year;
    //explode the date to get month, day and year
    $birthDate = explode("/", $birthDate);
    //get age from date or birthdate
    $age = (date("md", date("U", mktime(0, 0, 0, $birthDate[0], $birthDate[1], $birthDate[2]))) > date("md") ? ((date("Y") - $birthDate[2]) - 1) : (date("Y") - $birthDate[2]));
    $user_age = $age;

    $sql = "SELECT * FROM country WHERE id = $user_country";
    $query = $this->db->prepare($sql);
    $query->execute();
    $user_country_text = $query->fetch()->description;

    $sql = "SELECT * FROM searchtype WHERE id = $user_lookingfor";
    $query = $this->db->prepare($sql);
    $query->execute();
    $user_Lookingfor_text = $query->fetch()->description;

    $sql = "SELECT * FROM fishtype WHERE id = $user_type";
    $query = $this->db->prepare($sql);
    $query->execute();
    $user_typeofuser_text = $query->fetch()->description;

    $sql = "SELECT * FROM gender WHERE id = $user_seeking";
    $query = $this->db->prepare($sql);
    $query->execute();
    $user_seeking_text = $query->fetch()->description;

    $sql = "SELECT * FROM intent WHERE id = $user_intent";
    $query = $this->db->prepare($sql);
    $query->execute();
    $user_intent_text = $query->fetch()->description;

    $sql = "SELECT * FROM longestrelationship WHERE id = $user_longestrelationship";
    $query = $this->db->prepare($sql);
    $query->execute();
    $user_longestrelationship_text = $query->fetch()->description;




    $user_data_array['gender'] = $user_gender_text;
    $user_data_array['ethnicity'] = $user_ethnicity_text;
    $user_data_array['age'] = $user_age;
    $user_data_array['country'] = $user_country_text;
    $user_data_array['Lookingfor'] = $user_Lookingfor_text;
    $user_data_array['typeofuser'] = $user_typeofuser_text;
    $user_data_array['profession'] = $user_profession;
    $user_data_array['postcode'] = $user_postalcode;
    $user_data_array['city'] = $user_city;
    $user_data_array['firstname'] = $user_firstname;
    $user_data_array['firstname_show'] = $user_firstname_show;
    $user_data_array['longestrelationship'] = $user_longestrelationship_text;
    $user_data_array['datesmoker'] = $user_datesmoker;
    $user_data_array['datekids'] = $user_datekids;
    $user_data_array['havepets'] = $user_pets;
    $user_data_array['seeking'] = $user_seeking_text;
    $user_data_array['intent'] = $user_intent_text;
    $user_data_array['havecar'] = $user_car;
    $user_data_array['religionmatter'] = $user_religionmatter;
    $user_data_array['hassiblings'] = $user_siblings;
    return $user_data_array;
}

As you can see, it's querying a separate table for each bit of data. I was just wondering if this is the correct way to do this and what will the performance be like when a few users are viewing the page that call the function.

Answer 1

I'm not 100% sure, but I think this will do the same with half the code.. (Not able to test it) I'm not sure if you like to replace the newly fetched description data with the data allready stored in $user_data from the first fetch. if not, you need to remove the lines after the while-loop.

public function getAllProfileData() {
    $sql = "SELECT * FROM user_data WHERE user_id = :user_id";
    $query = $this->db->prepare($sql);
    $query->execute(array(':user_id' => $_SESSION['user_id']));
    $user_data = $query->fetchAll(FETCH_OBJ);

    $birthDate = $user_dob_month . "/" . $user_dob_day . "/" . $user_dob_year;
    //explode the date to get month, day and year
    $birthDate = explode("/", $birthDate);
    //get age from date or birthdate
    $age = (date("md", date("U", mktime(0, 0, 0, $birthDate[0], $birthDate[1], $birthDate[2]))) > date("md") ? ((date("Y") - $birthDate[2]) - 1) : (date("Y") - $birthDate[2]));
    $user_data['age'] = $age;


    $sql = "SELECT * FROM gender WHERE id = :gender;
            SELECT * FROM ethnicity WHERE id = :ethnicity;
            SELECT * FROM country WHERE id = :country;
            SELECT * FROM searchtype WHERE id = :lookingfor;
            SELECT * FROM fishtype WHERE id = :type;
            SELECT * FROM gender WHERE id = :seeking;
            SELECT * FROM intent WHERE id = :intent;
            SELECT * FROM longestrelationship WHERE id = :longestrelationship";

    $query = $this->db->prepare($sql);
    $query->execute(array(':gender' => $user_data['gender'],
                          ':ethnicity' => $user_data['ethnicity'],
                          ':country' => $user_data['country'],
                          ':lookingfor' => $user_data['lookingfor'],
                          ':type' => $user_data['type'],
                          ':seeking' => $user_data['seeking'],
                          ':intent' => $user_data['intent'],
                          ':longestrelationship' => $user_data['longestrelationship']));

    $check = explode("; ", $sql);
    $count = count($check);
    $x = 1;

    while($check > $x) {
        $userdata[$x] = $query->fetch()->description;
        $query->nextrowset();
        $x++;
    }

    $user_data['gender'] = $userdata[1];
    $user_data['ethnicity'] = $userdata[2];
    $user_data['country'] = $userdata[3];
    $user_data['lookingfor'] = $userdata[4];
    $user_data['type'] = $userdata[5];
    $user_data['seeking'] = $userdata[6];
    $user_data['intent'] = $userdata[7];
    $user_data['longestrelationship'] = $userdata[8];

    return $user_data;
}

Answer 2

Not a PHP pro, but looks to me like you need to write a stored proc with joins and pull all these values back in one go rather than individual queries?