<?php
$sql = mysql_query("SELECT c.countries_name, d.degree_name, COUNT(u.user_id), COUNT(u_u.degree_id)
FROM users as u, user_universities as u_u, countries as c, degrees as d WHERE u.user_id = u_u.user_id AND u.country_id = c.countries_id AND u_u.degree_id = d.degree_id
GROUP BY c.countries_name, u_u.degree_id");
$fetch = mysql_fetch_array($sql);
if(mysql_error()){
echo "There was an error.<br />MySQL said: ".mysql_error());
exit;
}
//there are two ways to do this...
//you can either pull each variable out by
//callin $user_id = $fetch['user_id'];
//or you can use the extract function
//i.e. extract($fetch);
extract($fetch);
echo "User ID: ".$user_id;
echo "<br />Degree ID: ".$degree_id;
echo "<br />Degree Name: ".$degree_name;
echo "<br />Country Name: ".$countries_name;
?>
By the way, if your user_id filed is a unique or primary key, than you can shorten your SQL query becuase only one value will be found for a user_id.
i.e.
$sql = mysql_query("SELECT c.countries_name, d.degree_name, COUNT(u.user_id), COUNT(u_u.degree_id)
FROM users as u, user_universities as u_u, countries as c, degrees as d WHERE u.user_id = u_u.user_id GROUP BY c.countries_name, u_u.degree_id");
One more thing...you can also shorten your SQL by using links...probably not what they are called but I will explain.
You have
SELECT countries.countries_name, degrees.degree_name, COUNT( users.user_id ) , COUNT( user_universities.degree_id )
FROM users, user_universities, countries, degrees
WHERE users.user_id = user_universities.user_id AND users.country_id = countries.countries_id AND user_universities.degree_id = degrees.degree_id
GROUP BY countries.countries_name, user_universities.degree_id;
You can shorten that by refering to your table as a letter or something else...
i.e. SELECT stuff FROM table as t, table2 as t2 etc...
Then you can call your stuff with less effort...
i.e.
SELECT d.degree, c.country FROM degrees as d, countries as c WHERE c.country='1' GROUP BY d.degrees;
Hope that helps.
🙂
