SELECT and Count in the same Query

[premora]

Hello there,

 

Having a nightmare here.  It feels like what I need to do is really easy but I just can't get it to work.  I have a table called "groups2" that holds a unique id and the name of an activity.

 

I already have a query that finds the users selected groups using a while loop but I also want to show how many other members are in that group by counting the number of times the activity comes up or the id comes up.  I don't know whether I need 2 while loops nested or what but I get the error

 

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource on line 32 which is highlighted.

 

Can anyone help.  I am no expert at php and still learning so some advice or example code would be great.

 

 

$result = mysql_query("SELECT * FROM groups2 WHERE L_ID = ". $_SESSION['member_ID'] .";");

$data = mysql_query("SELECT COUNT('name') AS num FROM groups2 WHERE name = " . $row['name'] . "");

$count = mysql_fetch_assoc($data);

$numbers = $count['num'];

while ( $row = mysql_fetch_assoc($result) ) {

echo("<tr>

<td><font face='Arial, Helvetica, sans-serif' size='3'><strong>" . $row["name"] . "</strong></font></td>

</tr><tr>

<td><font face='Arial, Helvetica, sans-serif' size='1' color='#0000FF'><strong>Members (" . $numbers . ")</strong></font></td>

</tr><tr>

<td><hr width=95%><br></td>

</tr>");

}

[wigwambam]

$count = mysql_num_rows($data);

[premora]

Sorry,  I get the same error but this time it has mysql_num_rows() in it

 

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource

[wigwambam]

The $data query must be failing, try :-

 

$data = mysql_query("SELECT COUNT('name') AS num FROM groups2 WHERE name = " . $row['name'] . "") or die(mysql_error());

 

[premora]

It's a strange one.  If I put your code with the ... or die at the end I get this error

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

 

If I remove the ... or die I still get the previous error

 

What I don't understand from the error above is the first " mentioned is the "Select .... quote on line 32.  What is going on??

[wigwambam]

if name is a string, it needs single quotes:-

$data = mysql_query("SELECT COUNT('name') AS num FROM groups2 WHERE name = '" . $row['name'] . "'") or die(mysql_error());

 

Also, just before the $data line, type

echo "name=".$row['name'];

 

...just to make sure it's set.

[premora]

I get the same error.

 

Is there a completely different way I can do this?  I have never used a JOIN before but not sure if this can be used in the same way?  The only issue I see that is complicating things is because I want to get only the 1 users details I have to use WHERE L_ID = ". $_SESSION['member_ID'] .";" but the thing is this conflicts with me wanting to know the total number of members in that group as adding to the above SELECT Statement makes only the one users details come up and not everything.

 

Not sure if that was clear...

[wigwambam]

hmmm.. strange, query looks OK.

 

If the name = O'leary for example, then the single quote will break it. Try:

$data = mysql_query("SELECT COUNT('name') AS num FROM groups2 WHERE name = '" . mysql_real_escape_string($row['name']) . "'") or die(mysql_error());

 

Where is $row['name'] being set anyway?

[premora]

Right...

 

I get no error this time but the Members count is empty.

 

$row['name'] is one of the fields from groups2

 

 

[wigwambam]

But $row isn't set anywhere...

 

After $result line, add:-

$row = mysql_fetch_array($result);

[premora]

Sorry, $row is set in the while loop

 

while ( $row = mysql_fetch_assoc($result) ) {

 

but for the count i'm not using that anyway as $row is used by the name.

 

Instead I have

 

$count = mysql_num_rows($data);

$numbers = $count['num'];

[wigwambam]

$result = mysql_query("SELECT * FROM groups2 WHERE L_ID = ". $_SESSION['member_ID'] .";");
while ( $row = mysql_fetch_array($result) ) {
$data = mysql_query("SELECT COUNT('name') AS num FROM groups2 WHERE name = '" . mysql_real_escape_string($row['name']) . "'") or die(mysql_error()); 
$numbers = mysql_num_rows($data);
echo("<tr>
<td><font face='Arial, Helvetica, sans-serif' size='3'><strong>" . $row["name"] . "</strong></font></td>
</tr><tr>
<td><font face='Arial, Helvetica, sans-serif' size='1' color='#0000FF'><strong>Members (" . $numbers . ")</strong></font></td>
</tr><tr>
<td><hr width=95%><br></td>
</tr>");
}

[premora]

OK, we are definitely getting there I think.

 

It returned a (1) each for two activities but the issue now is that one of the activities has 2 members so the result should be

 

Activity 1

(2)

 

Activity 2

(1)

[wigwambam]

Change

 

$numbers = mysql_num_rows($data);

 

to

 

$row2=mysql_fetch_array($data);

$numbers=$row2['num'];

[premora]

Eureka!!!  It works.

 

Thank you so much wigwambam for sticking with this, it's appreciated.

 

Huge lesson learnt here so thanks for that and your time too 

[wigwambam]

You're welcome, I apologise for length of time it took - I misunderstood what you were trying to achieve part way through.