MySQL query problem

[pstylesss]

I need a little help pulling some info from a MySQL DB. I have 3 tables, accounts, subscriptionItems, & subscriptionList

accounts contain user information and accountID

These are the tables in my DB

subscriptionItems
subscriptionID
name
status

subscriptionList
subscriptionListID
accountID
subscriptionID



subscriptionItems hold the names of the things people can subscribe to. subscriptionList holds the actual list of subscribers. What I want to do is list all the names of items, then to the right of them, list the amount of subscribers. What I have kindof works, but not correctly. Below is what I am using.

<table width="450" border="0" cellspacing="0" cellpadding="0">
<tr>
<td width="210" align="center"><strong>Subscription Name </strong></td>
<td width="175" align="center"><strong>Options</strong></td>
<td width="65" align="center"><strong>Amount</strong></td>
</tr>
<?
$result = mysql_query("SELECT * FROM subscriptionItems ORDER BY name ASC",$connect);
while($row = mysql_fetch_assoc($result)){
$subscriptionID = $row['subscriptionID'];
$name = $row['name'];
$color1="#FFFFFF";
$color2="#EEEEEE";
$bgcolor=($bgcolor==$color1)?$color2:$color1;
?>

<tr bgcolor="<?=$bgcolor;?>">
<td><?=$name?></td>
<td align="center">Edit - Delete </td>
<td align="center"><?
if(isset($subscriptionID)){
$result2 = mysql_query("SELECT * FROM subscriptionList WHERE subscriptionID='$subscriptionID' ",$connect);
while($row2 = mysql_fetch_assoc($result2))
$amount = mysql_num_rows($result2);
echo $amount;
}else{
echo $amount;
}
?></td>
</tr>
<?
}
?>
</table>

Here is the output:
------Subscriptions---------------------------------Options-------------------Amount
Construction/Road Closures------------------ Edit - Delete-----------------------1
Pool Updates-------------------------------------Edit - Delete-----------------------2
Recreation Updates-----------------------------Edit - Delete-----------------------2
Senior Center------------------------------------Edit - Delete-----------------------2

in Pool Updates, Recreation Updates, and Senior Center there are 0 subscribers, so the result should be zero. I'm sure what is happening is when it is just sticking with the last $amount variable given... but I'm not sure how to fix it.

I tried joins, but they didn't seem to be working, at at this point, they seem a tad over my head.

Thanks for any help!

 

[aceO07]

I didn't look through your entire post, but I think this is what you're looking for. I think...

Select subscriptionID, count(subscriptionID) as amount from subscriptionList group by subscriptionID

 

[brandonb]

ace007 gave you a clue, however you want to join it.

SELECT subscriptionItems.name, COUNT(subscriptionList.SubscriptionId)
FROM subscriptionItems
LEFT JOIN subscriptionList ON subscriptionItems.subscriptionId = subscriptionList.subscriptionId
GROUP BY subscriptionItems.name

(I"m not really famililar with the MySQL syntax, and I ddin't test this in any sql tool and of the top of my head, so it could have syntax errors)

 

[pstylesss]

Thats great guys, thanks! I'll take a look at this in a minute and see how it works...

 

[pstylesss]

Originally posted by: aceO07
I didn't look through your entire post, but I think this is what you're looking for. I think...

Select subscriptionID, count(subscriptionID) as amount from subscriptionList group by subscriptionID

Almost had it with this one. Returned half of what I needed.

Originally posted by: brandonb
ace007 gave you a clue, however you want to join it.

SELECT subscriptionItems.name, COUNT(subscriptionList.SubscriptionId)
FROM subscriptionItems
LEFT JOIN subscriptionList ON subscriptionItems.subscriptionId = subscriptionList.subscriptionId
GROUP BY subscriptionItems.name

(I"m not really famililar with the MySQL syntax, and I ddin't test this in any sql tool and of the top of my head, so it could have syntax errors)

This worked PERFECTLY first try. Thanks a bundle, saved me a huge headache. I suppose I should learn MySQL syntax a bit more.

 

[aceO07]

Oops.. I forgot about the name lookup.

 

[imported_shader]

brandonb's solution should be correct (in theory at least, i can't verify mysql syntax right now).

and also to the OP, i don't want to sound insulting or anything here, but joins are a very low level/simple aspect of SQL. meaning that if you are using sql you should be familiar with how they work. i'm mentioning this because you may have other areas in your application which perform similar tasks, and it would probably be much more efficient to change the code to use joins (why is it more efficient?. not making multiple connections/requests to the database - like in a while loop, letting the dbms handle the query - dbms developers spend alot of time optimizing things like joins to put the least amount of stress on the dbms so you might as well use the product of their labor).

once again, my intention here is not be negative, but to help. learning about how joins work will help you, save you time, and allow you to create a working solution.

 

[pstylesss]

I could probably figure this out if I tried long enough, but since both of you are still online, I figure I might ask.

COUNT() worked just fine in when I ran the query in phpmyadmin, but I'm not figuring out how to get it into a php variable. How would I do that?

 

[pstylesss]

Originally posted by: shader
brandonb's solution should be correct (in theory at least, i can't verify mysql syntax right now).

and also to the OP, i don't want to sound insulting or anything here, but joins are a very low level/simple aspect of SQL. meaning that if you are using sql you should be familiar with how they work. i'm mentioning this because you may have other areas in your application which perform similar tasks, and it would probably be much more efficient to change the code to use joins (why is it more efficient?. not making multiple connections/requests to the database - like in a while loop, letting the dbms handle the query - dbms developers spend alot of time optimizing things like joins to put the least amount of stress on the dbms so you might as well use the product of their labor).

once again, my intention here is not be negative, but to help. learning about how joins work will help you, save you time, and allow you to create a working solution.

I'm not insulted in the least. I asked for help because I have hardly used them and I wanted to learn, and actually needed to learn. This DB will eventually get huge, and I knew joins took away a lot of stress, but up until now there wasn't a huge need for them in anything I've coded. I need and want to expand my sql knowledge.

 

[imported_shader]

you probably need to assign it a name in the sql syntax.

an example:

i have a table T with two columns, a and b. however when i want to select the b column i want it to be called c (this is just an example, dont ask me why you would want to do this)

here is how:

select a, b as c from T;

it might also work with:

select a, b c from T;

some sql vendors allow you to omit the as keyword. b as c is the part that is crucial.

so for your problem:

SELECT subscriptionItems.name, COUNT(subscriptionList.SubscriptionId) as cnt
FROM subscriptionItems
LEFT JOIN subscriptionList ON subscriptionItems.subscriptionId = subscriptionList.subscriptionId
GROUP BY subscriptionItems.name

and then when you pull the field from you will want to use the row name of 'cnt' to get those results

i think it will be:

$count = $row['cnt'];

 

[pstylesss]

wonderful shader. I should have figured that out by looking at aceO07's post. As soon as I saw "as" in your post, I remembered seeing it in Ace's.

Thanks everyone, great help!

 

[mugs]

Originally posted by: brandonb
ace007 gave you a clue, however you want to join it.

SELECT subscriptionItems.name, COUNT(subscriptionList.SubscriptionId)
FROM subscriptionItems
LEFT JOIN subscriptionList ON subscriptionItems.subscriptionId = subscriptionList.subscriptionId
GROUP BY subscriptionItems.name

(I"m not really famililar with the MySQL syntax, and I ddin't test this in any sql tool and of the top of my head, so it could have syntax errors)

This is fine as long as subscriptionItems.name is guaranteed to be unique. If not, you could do this:

SELECT subscriptionItems.id, subscriptionItems.name, COUNT(subscriptionList.SubscriptionId)
FROM subscriptionItems
LEFT JOIN subscriptionList ON subscriptionItems.subscriptionId = subscriptionList.subscriptionId
GROUP BY subscriptionItems.id, subscriptionItems.name

Or split it into two queries - aceO07's to get the ID and count, and another to fetch the name with the ID.