[SOLVED] PHP CASE WHEN HELP

[Skipjackrick]

The following code is counting the species_id for every single entry in my DB.  I only want it to count the entries for each specific angler.

 

Why won't the following code work like I want?

 

 

<?php
$query_anglertotals = "SELECT
                         species_id,
                         COUNT(CASE WHEN angler = 'Oz' THEN species_id ELSE 0 END ) AS anglerA,
                         COUNT(CASE WHEN angler = 'Skipjack' THEN species_id ELSE 0 END ) AS anglerB,
                         COUNT(CASE WHEN angler = 'Curmit' THEN species_id ELSE 0 END ) AS anglerC,
                         COUNT(CASE WHEN angler = 'Old Salt' THEN species_id ELSE 0 END ) AS anglerD,
                         COUNT(CASE WHEN angler = 'Kip' THEN species_id ELSE 0 END ) AS anglerE
                         FROM submit
                         GROUP BY species_id
                         ORDER BY species_id";

$anglertotals = mysql_query($query_anglertotals) or die(mysql_error());
?>

[soycharliente]

<?php
$sql = "SELECT COUNT(*) AS total FROM submit WHERE angler='Oz'";
?>

 

Is that what you're looking for?

[Skipjackrick]

Nope, I tried the following and I can't get it to work.

 

Basically I am trying to arrange my data in columns rather than rows.

 

<?php
$query_anglertotals = "SELECT
                         species_id,
                         COUNT(species_id) AS anglerA FROM submit WHERE angler='Oz'
                         GROUP BY species_id
                         ORDER BY species_id
                         COUNT(species_id) AS anglerB FROM submit WHERE angler='Skipjack'
                         GROUP BY species_id
                         ORDER BY species_id
                         COUNT(species_id) AS anglerC FROM submit WHERE angler='Curmit'
                         GROUP BY species_id
                         ORDER BY species_id
                         COUNT(species_id) AS anglerD FROM submit WHERE angler='Old Salt'
                         GROUP BY species_id
                         ORDER BY species_id
                         COUNT(species_id) AS anglerE FROM submit WHERE angler='Kip'
                         GROUP BY species_id
                         ORDER BY species_id";

$anglertotals = mysql_query($query_anglertotals) or die(mysql_error());

while($row = mysql_fetch_array($anglertotals))
{
$anglerA = $row['anglerA'];
$anglerB = $row['anglerB'];
$anglerC = $row['anglerC'];
$anglerD = $row['anglerD'];
$anglerE = $row['anglerE'];
$species_id = $row['species_id'];

get_species($species_id);

$kayakwars_totals2 .=<<<EOD
<tr>
	<td align='center'>$speciesname</td>
	<td align='center'>$anglerA</td>
	<td align='center'>$anglerB</td>
	<td align='center'>$anglerC</td>
	<td align='center'>$anglerD</td>
	<td align='center'>$anglerE</td>
</tr>
EOD;
}
?>

[soycharliente]

Ok. Give me your database layout and how you want your data to be formatted. You're trying to do way too much.

[Skipjackrick]

Ok. Give me your database layout and how you want your data to be formatted. You're trying to do way too much.

 

LOL...

 

Thanks

 

I'll PM it to you.

[fenway]

Ok. Give me your database layout and how you want your data to be formatted. You're trying to do way too much.

 

LOL...

 

Thanks

 

I'll PM it to you.

Not helpful the rest of us... post on this thread, please.  Besides, what output did you get from the first query you posted?

[Skipjackrick]

Not helpful the rest of us... post on this thread, please.  Besides, what output did you get from the first query you posted?

 

 

 

Basically I am trying to arrange data across columns with a different "Group By" for each column.

 

Is it possible to Fetch more than one array yet, put it in the same exact table????

 

 

Case #1

 

<?php
$query_anglertotals = "SELECT
                         species_id,
                         COUNT(CASE WHEN angler = 'Oz' THEN species_id ELSE 0 END ) AS anglerA,
                         COUNT(CASE WHEN angler = 'Skipjack' THEN species_id ELSE 0 END ) AS anglerB,
                         COUNT(CASE WHEN angler = 'Curmit' THEN species_id ELSE 0 END ) AS anglerC,
                         COUNT(CASE WHEN angler = 'Old Salt' THEN species_id ELSE 0 END ) AS anglerD,
                         COUNT(CASE WHEN angler = 'Kip' THEN species_id ELSE 0 END ) AS anglerE
                         FROM submit
                         GROUP BY species_id
                         ORDER BY species_id";

$anglertotals = mysql_query($query_anglertotals) or die(mysql_error());
?>

 

The first Case I posted gives me this..............Look at the Total catches by anglers table

 

It gives me the total for each species of all of the entires.  Well, Oz did not catch 108 redfish and neither did Skipjack.

 

Click Here to See Case #1

 

 

 

Case #2

 

When I did this Oz is catching the correct amount of redfish but nobody else is catching anything.

 

 

Click Here to See Case #2

 

<?php
$query_anglertotals = "SELECT
                         species_id,
                         COUNT(species_id) AS anglerA
                         FROM submit
                         WHERE angler='Oz'
                         GROUP BY species_id
                         ORDER BY species_id";

$anglertotals = mysql_query($query_anglertotals) or die(mysql_error());
?>

 

[Skipjackrick]

Just so you can see how I am fetching my array I thought I'd include my thinking process behind it.

 

 

<?php
while($row = mysql_fetch_array($anglertotals))
{
$anglerA = $row['anglerA'];
$anglerB = $row['anglerB'];
$anglerC = $row['anglerC'];
$anglerD = $row['anglerD'];
$anglerE = $row['anglerE'];
$species_id = $row['species_id'];

get_species($species_id);

$kayakwars_totals2 .=<<<EOD
<tr>
	<td align='center'>$speciesname</td>
	<td align='center'>$anglerA</td>
	<td align='center'>$anglerB</td>
	<td align='center'>$anglerC</td>
	<td align='center'>$anglerD</td>
	<td align='center'>$anglerE</td>
</tr>
EOD;
}
?>

[soycharliente]

Question... what about making a column in your table to hold this data. It could be a tally of how many you have. And then every time a new one is added, just increment it. It could make for a much easier query.

[Skipjackrick]

Question... what about making a column in your table to hold this data. It could be a tally of how many you have. And then every time a new one is added, just increment it. It could make for a much easier query.

 

Well, I should be able to figure out some matter of accomplishing what I want to do without editing the database.

 

I just haven't quite figured it out.  I am certain that someone else has tried to arrange data in columns rather than rows and MySQL probably addressed that at some point.

 

My problem is that I don't know the functions?????????  Ahhh!!!

[fenway]

I want to see the output from mysql, NOT the output from php...

[fenway]

You want

 

SUM(IF angler = 'Oz', 1, 0 ) AS anglerA

 

not

 

COUNT(CASE WHEN angler = 'Oz' THEN species_id ELSE 0 END ) AS anglerA

[Skipjackrick]

You want

 

SUM(IF angler = 'Oz', 1, 0 ) AS anglerA

 

not

 

COUNT(CASE WHEN angler = 'Oz' THEN species_id ELSE 0 END ) AS anglerA

 

 

 

HOLY CRAP!!!!!!!!!  What a way to use SUM and IF together!!!!!!!!!  You are amazing!!!!!!!!!!!!!!!!!!!

 

I had figured out how to do it but I used about 100 different queries to fill each row and column separately.  This simplifies it into 1 query!!!!!!!!!!!!!!

 

 

THANKS A BILLION TIMES OVER!!!!!!!!!!!!!!!

[fenway]

No problem... often times, simplicity is synonymous with efficiency.