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.

• bumperbox: How many users do you have in the system and how many would you query at any one time?
• bumperbox: 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
• bumperbox: 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

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?