how can I separate values returned from a query or is that even possible?

What I am trying to do is do is $query = "SELECT * FROM whitealbumreg.lps";

lps has a column called lp_origin. lp_origin is ENUM where values are either US or UK. I know how to pull them out of the database individually (like WHERE lp_origin = "us") and together (like SELECT * FROM colname"), but I am not sure how to pull them out together and then separate them.

My goal is to run numbers based on # of registered lps from US vs. UK.
I assume that once I know how to do that I can do all sorts of math like % of male to female members, or % registered UK mono to stereo copies, etc.

Unfortunately, this is how I achieved it. I have some enums with 6 choices. There has to be a way to pull them all out and seperate them later, rather than doing writing code to pull each one out at a time. There has to be a better (read: more efficient) way:

$query1 = "SELECT * FROM whitealbumreg.members";
 $result1 = mysqli_query($dbc, $query1)
   or die('Error querying database.');

$query2 = "SELECT * FROM whitealbumreg.lps WHERE lp_origin='uk'";
 $result2 = mysqli_query($dbc, $query2)
   or die('Error querying database.');

$query3 = "SELECT * FROM whitealbumreg.lps WHERE lp_origin='us'";
 $result3 = mysqli_query($dbc, $query3)
   or die('Error querying database.');

$query4 = "SELECT * FROM whitealbumreg.members WHERE member_gender='m'";
 $result4 = mysqli_query($dbc, $query4)
   or die('Error querying database.');

$query5 = "SELECT * FROM whitealbumreg.members WHERE member_gender='f'";
 $result5 = mysqli_query($dbc, $query5)
   or die('Error querying database.');

   $members = mysqli_num_rows($result1);
   $uk = mysqli_num_rows($result2);
   $us = mysqli_num_rows($result3);
   $m = mysqli_num_rows($result4);
   $f = mysqli_num_rows($result5);

echo '<br />';
echo '<table width="400" border="1" cellpadding="2">';
echo '<tr><td>Percent of registered albums from the UK:</td><td>';
echo ($uk / $members) * 100;
echo '</td></tr>';
echo '<tr><td>Percent of registered albums from the US:</td><td>';
echo ($us / $members) * 100;
echo '</td></tr>';

echo '<tr><td>Percent of male members:</td><td>';
echo ($m / $members) * 100;
echo '</td></tr>';

echo '<tr><td>Percent of female members:</td><td>';
echo ($f / $members) * 100;
echo '</td></tr>';

echo '</table>';

Maybe think about it this way:

Say you are throwing a wedding and in the expense table, you have the column dinner. Under dinner, guests can chose steak, chicken, or fish.

What I am trying to do is pull (determine) the total number of dinners and then, figure out what the percentage is for steak dinners vs. chicken dinners vs. fish dinners.

In very non-PHP language, I want to
1. Select * from Dinners
2. determine the total number of dinners,
3. then, (maybe) run a loop that says
4. "go through each entry in dinner and count the number steak dinners,
5. create a variable to represent that number.
6. take the steak dinners divided by total dinners,
7. output is the percent I want.
--repeat lines 4-7 for fish and chicken.

Does that make any sense? I have been working with PHP for about 2 months so there may be a function that does just that.

Read the mysql manual on 'COUNT'

SELECT lp_option, gender, COUNT(*) FROM lps GROUP BY lp_option, gender;
This article has been dead for over six months. Start a new discussion instead.