premora Posted May 13, 2011 Share Posted May 13, 2011 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>"); } Quote Link to comment https://forums.phpfreaks.com/topic/236305-select-and-count-in-the-same-query/ Share on other sites More sharing options...
wigwambam Posted May 13, 2011 Share Posted May 13, 2011 $count = mysql_num_rows($data); Quote Link to comment https://forums.phpfreaks.com/topic/236305-select-and-count-in-the-same-query/#findComment-1214925 Share on other sites More sharing options...
premora Posted May 13, 2011 Author Share Posted May 13, 2011 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 Quote Link to comment https://forums.phpfreaks.com/topic/236305-select-and-count-in-the-same-query/#findComment-1214926 Share on other sites More sharing options...
wigwambam Posted May 13, 2011 Share Posted May 13, 2011 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()); Quote Link to comment https://forums.phpfreaks.com/topic/236305-select-and-count-in-the-same-query/#findComment-1214931 Share on other sites More sharing options...
premora Posted May 13, 2011 Author Share Posted May 13, 2011 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?? Quote Link to comment https://forums.phpfreaks.com/topic/236305-select-and-count-in-the-same-query/#findComment-1214932 Share on other sites More sharing options...
wigwambam Posted May 13, 2011 Share Posted May 13, 2011 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. Quote Link to comment https://forums.phpfreaks.com/topic/236305-select-and-count-in-the-same-query/#findComment-1214939 Share on other sites More sharing options...
premora Posted May 13, 2011 Author Share Posted May 13, 2011 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... Quote Link to comment https://forums.phpfreaks.com/topic/236305-select-and-count-in-the-same-query/#findComment-1214951 Share on other sites More sharing options...
wigwambam Posted May 13, 2011 Share Posted May 13, 2011 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? Quote Link to comment https://forums.phpfreaks.com/topic/236305-select-and-count-in-the-same-query/#findComment-1214966 Share on other sites More sharing options...
premora Posted May 13, 2011 Author Share Posted May 13, 2011 Right... I get no error this time but the Members count is empty. $row['name'] is one of the fields from groups2 Quote Link to comment https://forums.phpfreaks.com/topic/236305-select-and-count-in-the-same-query/#findComment-1214976 Share on other sites More sharing options...
wigwambam Posted May 13, 2011 Share Posted May 13, 2011 But $row isn't set anywhere... After $result line, add:- $row = mysql_fetch_array($result); Quote Link to comment https://forums.phpfreaks.com/topic/236305-select-and-count-in-the-same-query/#findComment-1214978 Share on other sites More sharing options...
premora Posted May 13, 2011 Author Share Posted May 13, 2011 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']; Quote Link to comment https://forums.phpfreaks.com/topic/236305-select-and-count-in-the-same-query/#findComment-1214982 Share on other sites More sharing options...
wigwambam Posted May 13, 2011 Share Posted May 13, 2011 $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>"); } Quote Link to comment https://forums.phpfreaks.com/topic/236305-select-and-count-in-the-same-query/#findComment-1214987 Share on other sites More sharing options...
premora Posted May 13, 2011 Author Share Posted May 13, 2011 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) Quote Link to comment https://forums.phpfreaks.com/topic/236305-select-and-count-in-the-same-query/#findComment-1214990 Share on other sites More sharing options...
wigwambam Posted May 13, 2011 Share Posted May 13, 2011 Change $numbers = mysql_num_rows($data); to $row2=mysql_fetch_array($data); $numbers=$row2['num']; Quote Link to comment https://forums.phpfreaks.com/topic/236305-select-and-count-in-the-same-query/#findComment-1214993 Share on other sites More sharing options...
premora Posted May 13, 2011 Author Share Posted May 13, 2011 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 Quote Link to comment https://forums.phpfreaks.com/topic/236305-select-and-count-in-the-same-query/#findComment-1214994 Share on other sites More sharing options...
wigwambam Posted May 13, 2011 Share Posted May 13, 2011 You're welcome, I apologise for length of time it took - I misunderstood what you were trying to achieve part way through. Quote Link to comment https://forums.phpfreaks.com/topic/236305-select-and-count-in-the-same-query/#findComment-1214995 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.