2
\$\begingroup\$

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.

\$\endgroup\$
  • \$\begingroup\$ How many users do you have in the system and how many would you query at any one time? \$\endgroup\$ – bumperbox Nov 10 '14 at 2:22
  • \$\begingroup\$ I don't think your code even works. Your first foreach loop overwrites the variables for every loop, maybe you could fix that up and update your question \$\endgroup\$ – bumperbox Nov 10 '14 at 2:24
  • \$\begingroup\$ Have a read up on table joins and fetch all this data in 1 select, or you could cache some of the reference data such as gender, country etc and lookup the cached data rather then referring to the database. It really comes down to how many user records you are displaying at a time to determine which method would be more efficient \$\endgroup\$ – bumperbox Nov 10 '14 at 2:27
2
\$\begingroup\$

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;
}
\$\endgroup\$
0
\$\begingroup\$

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?

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.