Help with enums

[bluetonic]

how can I separate values returned from a query?

 

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,  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. 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>';

[9three]

Not sure if I understand you correctly, but this should help you out a little

 

$sql = "SELECT * 
        FROM whitealbumreg.members, whitealbumreg.lps 
        WHERE lp_origin = 'uk' 
        AND lp_origin='us' 
        AND member_gender='m' 
        AND member_gender='f'";

$result = mysqli_query($dbc, $sql) or die('Error querying database.');
$row = mysqli_num_rows($result);

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

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

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

echo '</table>';

[zq29]

Without seeing your full database schema, you could reduce it to at least two queries, and a reduction in PHP, like so:

 

<?php
$r = mysql_query("SELECT `lp_origin`,COUNT(*) as `count` FROM `lps` GROUP BY `lp_origin`") or die(mysql_error());
while($rr = mysql_fetch_assoc($r)) echo "$rr[lp_origin]: $rr[count]<br />";

$members = 0;
$r = mysql_query("SELECT `member_gender`,COUNT(*) as `count` FROM `members` GROUP BY `member_gender`") or die(mysql_error());
while($rr = mysql_fetch_assoc($r)) {
    echo "$rr[member_gender]: $rr[count]<br />";
    $members += $rr['count'];
}
echo "Total members: $members";
?>

 

9three, your query wouldn't return anything - `member_gender` can't be both 'm' and 'f'.

[bluetonic]

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.

[zq29]

Well, expanding on my previous example, you could do this:

<?php
$x = array();
$r = mysql_query("SELECT `lp_origin`,COUNT(*) as `count` FROM `lps` GROUP BY `lp_origin`") or die(mysql_error());
while($rr = mysql_fetch_assoc($r)) $x[$rr['lp_origin']] = $rr['count'];
$t = array_sum($x);
foreach($x as $k => $v) echo "$k: ".(($v/$t)*100)."%<br />";
?>

[bluetonic]

Wow, that scary.

 

As I am new to PHP, and if its not too much trouble, do you think you could break this down for me? Why do you take each step? (I understand the 2nd step.)

 

$x = array();

 

$r = mysql_query("SELECT `lp_origin`,COUNT(*) as `count` FROM `lps` GROUP BY `lp_origin`") or die(mysql_error());

 

while($rr = mysql_fetch_assoc($r)) $x[$rr['lp_origin']] = $rr['count'];

 

$t = array_sum($x);

 

foreach($x as $k => $v) echo "$k: ".(($v/$t)*100)."%<br />";

[zq29]

Sure thing. Same code again, but with comments explaining.

 

<?php
//Create an array in preparation for storing the groups of origins and counts
$x = array();
//Fetch all origins and their counts
$r = mysql_query("SELECT `lp_origin`,COUNT(*) as `count` FROM `lps` GROUP BY `lp_origin`") or die(mysql_error());
//Loop through the query results, storing them in our previously created array, the origin name as the key the count as the value
while($rr = mysql_fetch_assoc($r)) $x[$rr['lp_origin']] = $rr['count'];
//Calculate the total of all origins, this is why we converted the SQL results to an array ($x)
$t = array_sum($x);
//Loop through our previously created array and display the origins (array key ($k)) and their percentage calculated from the count (array value ($v)) and sum ($t)
foreach($x as $k => $v) echo "$k: ".(($v/$t)*100)."%<br />";
?>

[bluetonic]

Well, I really appreciate your help. I will be putting the results of this "thing" in a table, so if I have any trouble with that, I will be back.

Thanks so much.

[bluetonic]

It looks like I need a little more help. I want to put the results into a table. I was was able to get all results into one column but don't know how to get them into separate rows. I also need to be able to change the enum value (which is generic like M or F) into Male or Female. Is the above solution the best way to achieve this?