How to count and display the number of records in a table that have a certain value

[maxwell_]

I have a page with a repeat region that displays rows from a database.
Each row contains details about a particular item.
I have added a comment button that when clicked displays that item on its own page with a comments section displaying a comment form and previous comments about that item. Currently the comments are filtered by URL variable, so if URL variable says /software/fulltitle.php?software_id=21&id=21 only the comments with 21 in the software_id column of the comments table will display.

What I am trying to do is add to each row (item) in the repeat region page the total number of comments that have been made about that item.

This will allow the user to see before hand if it is worth while clicking on the View / Make comments button for that item if they only want to read previous comments.

There are 2 tables involved, one containing details of the items displayed on the repeat region page, the second containing all of the comments previously made about all of the items.
The comments table has a field called software_id which relates to the id column of the items table. So I know that if I count all comments in comments table with software_id = 5, the total will be the amount comments made about item 5.
As you can see filtering by url is no longer possible because each of the items have a different number of comments.

Regards Maxwell

[leatherback]

hm.. Just perform the query with a join to your comments field. If you then group the data by comment_id, add a field: count(comment_id) as count to the output and you can use that field to grab the number of comments.

[maxwell_]

Hi

My comments table is called guest my items table is called titles.
There are currently 7 comments in guest

The following statement outputs all of the rows with matching guest.software_id and titles.id fields

SELECT guest.software_id, titles.id FROM guest INNER JOIN titles ON (software_id = titles.id) WHERE guest.software_id = titles.id"

If I add the following to the repeat region

Current comments: <?php echo $row_Recordset1['id']; ?>

or

Current comments: <?php echo $row_Recordset1['software_id']; ?>

I get

Current comments: 3

3 is the first row of the 7 comments with the same software_id and id

is there a way to edit the SELECT statement so that it outputs only the
number of software_id and ids that match on each item?

[bradgrafelman]

As has already been suggested: COUNT().

[maxwell_]

Hi

I tried this

SELECT COUNT(*) FROM guest, titles WHERE guest.software_id = titles.id GROUP BY guest.software_id";

Current comments:<?php echo $row_Recordset1['COUNT(*)']; ?>

now total comments for each item all say 2

[leatherback]

Trey something along these lines:

$q = "
SELECT count(guest.software_id) as count, titles.id 
  FROM titles
  LEFT JOIN guest 
    ON titles.id = guest.software_id
  GROUPD BY guest.software_id";

$result = mysql_query($q) or die(mysql_error());

if($result)
  {
  while($row = mysql_fetch_array($result))
    {
    echo $row['count'];
    }
  }

[maxwell_]

Thanks for the help

I'm sure this is the correct mysql
The table guest currently has 7 comments 3 associated with one item 2 with another and another 2 each with 1 comment.

When I run the query in mysql

$Recordset1 = "SELECT COUNT(guest.software_id) as COUNT, titles.id FROM titles LEFT JOIN guest ON titles.id = guest.software_id GROUP BY guest.software_id";

the outoput is a list showing 2, 3, 1, 1
my problem is getting the totals into my repeat region

I tried running the following -

code in php section -

mysql_select_db($database_abe, $abe);
$query = "SELECT COUNT(guest.software_id) as COUNT, titles.id FROM titles LEFT JOIN guest ON titles.id = guest.software_id GROUP BY guest.software_id";
$Recordset = mysql_query($query, $abe) or die(mysql_error());
$row_Recordset = mysql_fetch_assoc($Recordset);
$totalRows_Recordset = mysql_num_rows($Recordset);

Code on html part of page -

<td>Current comments:<?php echo $row_Recordset['COUNT']; ?></td>

this showed all items with Current comments 0

I also tried

Code in php section -

$q = "
SELECT count(guest.software_id) as count, titles.id
FROM titles
LEFT JOIN guest
ON titles.id = guest.software_id
GROUPD BY guest.software_id";

$result = mysql_query($q) or die(mysql_error());

Code on html part of page -

<td>if($result)
{
while($row = mysql_fetch_array($result))
{
echo $row['count'];
}
} </t>

This resulted in an Undefined index error:

[leatherback]

I am not sure what you are ttrying to do. Based on the earlier provided code you should be able to add the HTML you need around the provided code. If you need other fields from the database too, you can just add those to the query, and then create the full output in one while() loop.

[maxwell_]

Hi

Please see full page bellow, and also previous posts to this thread.
I have highlighted in red bold the part that I am having difficulty with.
I'm sure the mysql part is correct as testing the query on its own produces
the proper results.

The page has a list of items and their details, every item has a button
Read / Make comments that loads the item in its own page displaying
a comments form and previous comments.
This is all working fine.

I would like to add to each item information stating how many comments have
been made about that item.
Allowing the user to see before hand if it is worth while clicking on the
Read / Make comments button for that item.

Ideally each item will have a different number of comments.

The only problem I have is outputing the number of comments associated with each item so far.

<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  if (PHP_VERSION < 6) {
    $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
  }

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    

    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}

$colname_rsTitles = "-1";
if (isset($_GET['id'])) {
  $colname_rsTitles = $_GET['id'];
}
mysql_select_db($database_abe, $abe);
$query_rsTitles = sprintf("SELECT title, company, `description`, resources, location, url, image, keyword, copies FROM titles WHERE id = %s ORDER BY id ASC", GetSQLValueString($colname_rsTitles, "int"));
$rsTitles = mysql_query($query_rsTitles, $abe) or die(mysql_error());
$row_rsTitles = mysql_fetch_assoc($rsTitles);
$totalRows_rsTitles = "-1";
if (isset($_GET['id'])) {
  $totalRows_rsTitles = $_GET['id'];
}
$colname_rsTitles = "-1";
mysql_select_db($database_abe, $abe);
$query_rsTitles = sprintf("SELECT title, company, `description`, resources, location, url, image, keyword, copies FROM titles WHERE id = %s ORDER BY id ASC", GetSQLValueString($colname_rsTitles, "int"));
$rsTitles = mysql_query($query_rsTitles, $abe) or die(mysql_error());
$row_rsTitles = mysql_fetch_assoc($rsTitles);
$totalRows_rsTitles = mysql_num_rows($rsTitles);

mysql_select_db($database_abe, $abe);
$query_rs_comments = "SELECT * FROM guest";
$rs_comments = mysql_query($query_rs_comments, $abe) or die(mysql_error());
$row_rs_comments = mysql_fetch_assoc($rs_comments);
$totalRows_rs_comments = mysql_num_rows($rs_comments);

mysql_select_db($database_abe, $abe);
$query_rs_users = "SELECT * FROM users";
$rs_users = mysql_query($query_rs_users, $abe) or die(mysql_error());
$row_rs_users = mysql_fetch_assoc($rs_users);
$totalRows_rs_users = mysql_num_rows($rs_users);

[COLOR="Red"][B]mysql_select_db($database_abe, $abe);
$query_Recordset1 = "SELECT COUNT(guest.software_id) as COUNT, titles.id FROM titles LEFT JOIN guest ON titles.id = guest.software_id GROUP BY guest.software_id";
$Recordset1 = mysql_query($query_Recordset1, $abe) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);[/B][/COLOR]


mysql_select_db($database_abe, $abe);

if(!isset($_POST['softwareLevel'])){
if (!isset($_GET['class_id'])) {
//show all software titles
$query_rsTitles = "SELECT id, title, company, `description`, resources, location, url, image, keyword, copies FROM titles ORDER BY id ASC";

}else{
//show software titles filtered by Literacy of Numeracy (using URL GET variable)
$query_rsTitles = "SELECT id, title, company, `description`, resources, location, url, image, keyword, copies FROM titles WHERE titles.class_id = ". GetSQLValueString($_GET['class_id'], "int") ." ORDER BY id ASC";
}
}else{
//show software titles filtered by Level (using Form POST variable)
$query_rsTitles = "SELECT id, title, company, `description`, resources, location, url, image, keyword, copies FROM titles WHERE titles.level_id = ". GetSQLValueString($_POST['softwareLevel'], "int") ." ORDER BY id ASC";
}

$rsTitles = mysql_query($query_rsTitles, $abe) or die(mysql_error());
$row_rsTitles = mysql_fetch_assoc($rsTitles);
$totalRows_rsTitles = mysql_num_rows($rsTitles);
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<?php $pagetitle="ABE Software Locator"?>
<html xmlns="http://www.w3.org/1999/xhtml" >
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title><?php echo $pagetitle ?></title>
<link rel="stylesheet" href="../../includes/styles.css" type="text/css" media="screen" />
<style type="text/css">
body {
	background-color: #FFF;
}
</style>
</head>
<body>
<?php include("../../includes/header.php"); ?>
    <div><table width="70%" border="0" align="center" cellpadding="3" cellspacing="0">
  <tr>
    <td width="29%" height="50" align="center"><a href="software_detail.php">Back to Locator</a></td>
    <td width="50%" align="center"><a href="../../index.php">Welcome Page</a></td>
    <td width="21%" align="center"><a href="../../logout.php">Log Out</a></td>
  </tr>
  <tr>
    <td colspan="3" align="center"><strong> There Are <span class="totalrecordsnumber"><?php echo $totalRows_rsTitles ?></span>  Software Titles Listed</strong></td>
  </tr>
  <tr>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
</table>
    <?php do { ?>
      <table width="820" border="0" align="center" cellpadding="3" cellspacing="2">
        <tr>
          <td width="206" height="200" rowspan="3" align="center" bgcolor="#FFFFFF"><img src="images/<?php echo $row_rsTitles['image']; ?>" alt="<?php echo $row_rsTitles['title']; ?>" /></td>
          <td colspan="3" align="center" bgcolor="#086b50"><h2><?php echo $row_rsTitles['title']; ?></h2></td>
        </tr>
        <tr>
          <td colspan="3" align="center" bgcolor="#f6b824"><strong>Made by:</strong> <?php echo $row_rsTitles['company']; ?></td>
        </tr>
        <tr>
          <td colspan="3" align="left" valign="top" bgcolor="#e5f8cb"><p class="ptaglineheight"><strong>Description: </strong><?php echo $row_rsTitles['description']; ?></p></td>
        </tr>
        <tr>
          <td colspan="2" align="left" valign="top" bgcolor="#e5f8cb"><span class="tabletext"><strong>Keywords</strong></span><strong>: </strong><?php echo $row_rsTitles['keyword']; ?></td>
          <td colspan="2" align="left" valign="top" bgcolor="#e5f8cb"><strong>Resources:</strong> <?php echo $row_rsTitles['resources']; ?></td>
        </tr>
        <tr>
          <td colspan="4" align="left" valign="top" bgcolor="#e5f8cb"><strong>Web Address:</strong> <a href="<?php echo $row_rsTitles['url']; ?>" target="_blank"><?php echo $row_rsTitles['url']; ?></a></td>
        </tr>
        <tr>
          <td colspan="3" align="left" valign="top" bgcolor="#e5f8cb"><strong>Is installed on:</strong> <?php echo $row_rsTitles['location']; ?></td>
          <td width="195" align="left" valign="top" bgcolor="#e5f8cb"><strong>Copies available:</strong><?php echo $row_rsTitles['copies']; ?></td>
        </tr>
        <tr>
          <td colspan="3" align="left" valign="top" bgcolor="#e5f8cb"><a href="fulltitle.php?software_id=<?php echo $row_rsTitles['id']; ?>&amp;id=<?php echo $row_rsTitles['id']; ?>">Read / Make Comments About This Software</a></td>
[COLOR="red"]        [B]  <td align="left" valign="top" bgcolor="#e5f8cb">Current comments:<?php echo $row_Recordset1['COUNT']; ?></td>[/B][/COLOR]
        </tr>
      </table>  

      <br />
      <?php } while ($row_rsTitles = mysql_fetch_assoc($rsTitles)); ?>
    </div>
    <?php include("../../includes/footer.php"); ?>
</body>
</html>
<?php
mysql_free_result($rsTitles);

mysql_free_result($rs_comments);

mysql_free_result($rs_users);

mysql_free_result($Recordset1);

[johanafm]

maxwell@;10985277 wrote:

mysql_select_db($database_abe, $abe);
$query = "SELECT COUNT(guest.software_id) as COUNT, titles.id FROM titles LEFT JOIN guest ON titles.id = guest.software_id GROUP BY guest.software_id";

You have an error in your SQL syntax (that MySQL doesn't care about). If you have a group by clause, each and every selected field has to be part of either the group clause or an aggregate function, but titles.id is part of neither. Moreover, guest.software_id is part of both the group by clause as well as the aggregate function COUNT, which is all syntactically correct, but also means that count cannot be more than 1 for any given software id. You most likely have either count(titles.id) in combination with group by software_id or the inverse.

maxwell@;10985277 wrote:

$Recordset = mysql_query($query, $abe) or die(mysql_error());
$row_Recordset = mysql_fetch_assoc($Recordset);
$totalRows_Recordset = mysql_num_rows($Recordset);

<td>Current comments:<?php echo $row_Recordset['COUNT']; ?></td>

Well, if the first row has a count of 0, and you output the same value more than once, of course it will say 0. Where's your while loop iterating over the data?

[maxwell_]

Hi

Thanks for the help!

If there is something wrong with the mysql syntax could you please
let me know what to change -

mysql_select_db($database_abe, $abe);
$query_Recordset1 = "SELECT COUNT(guest.software_id) as COUNT, titles.id FROM titles LEFT JOIN guest ON titles.id = guest.software_id GROUP BY guest.software_id";
$Recordset1 = mysql_query($query_Recordset1, $abe) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);

$row_Recordset1 = mysql_query($query_Recordset1) or die(mysql_error());

I tried adding a while loop which resulted in the first item displaying

Current comments:02311

All other items display

Current comments:

This is the while loop

<td>Current comments:<?php if(isset($row_Recordset1))
{
while($row = mysql_fetch_array($row_Recordset1))
{
echo $row['COUNT'];
}
} ?></td>

[maxwell_]

I changed the mysql to GROUP BY titles.id
and added a while loop to iterate over the data

mysql_select_db($database_abe, $abe);
$query_Recordset1 = "SELECT COUNT(guest.software_id) as COUNT, titles.id FROM titles LEFT JOIN guest ON titles.id = guest.software_id GROUP BY titles.id";
$Recordset1 = mysql_query($query_Recordset1, $abe) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);

$row_Recordset1 = mysql_query($query_Recordset1) or die(mysql_error());

<td>Current comments:<?php
if($row_Recordset1)
{
while($row = mysql_fetch_array($row_Recordset1))
{
echo $row['COUNT'];
}
} ?></td>

The first item now displays the following,

comments:2311000000000000000000000000000

all others

comments:

the number matched the database table exactly, 30 records 4 of which have 2, 3, 1, 1 comments.

It looks as if the problem is trying to get the repeat region to pick up on it!

[johanafm]

Well, since your 4 records have 2, 3, 1, 1 comments respectively, while the rest have 0 comments, and the output starts with 2311 followed by only 0s, I'd say you have what you need. Now you just need to fix the output. Hint: how do you create new table cells?

[maxwell_]

Can you help with the following error?

Current comments:
Warning: mysql_fetch_array() expects parameter 1 to be resource, array given in

This is the code -

mysql_select_db($database_abe, $abe);
$query_Recordset1 = "SELECT COUNT(guest.software_id) as COUNT, titles.id
 FROM titles LEFT JOIN guest ON titles.id = guest.software_id GROUP BY titles.id";
$Recordset1 = mysql_query($query_Recordset1) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);

<td> Current comments:
if($row_Recordset1)
  {
  while($row = mysql_fetch_array($row_Recordset1))
    {
    echo $row['COUNT'];
    }
  } 
 </td> 

[Derokorian]

$row_Recordset1 = mysql_fetch_assoc($Recordset1);

turns $row_Recordset1 into an array, representing an single result row given back by your query.

while($row = mysql_fetch_array($row_Recordset1))

then attempts to use this ARRAY as a result resource instead.

[maxwell_]

I tried changing

while($row = mysql_fetch_array($row_Recordset1))

to

while($row = mysql_fetch_array($row_Recordset1))

the error message now says

Current comments:
Warning: mysql_fetch_assoc() expects parameter 1 to be resource, array given in

[johanafm]

It sounds as if you completely missed the point of dreko's last post.

$recordset = mysql_query();
# $recordset is now either false (most likely due to a SQL syntax error) or a result set resource
# which can be used with mysql_fetch_* as documented at php.net


$recordset = mysql_fetch_assoc($recordset);
# $recordset is no longer a resulst set resource since mysql_fetch_* returns an array
# which you happily assigned to the variable previously holding the result set resource

$row = mysql_fetch_array($recordset);
# no longer makes any sense at all, since it's the same as writing
mysql_fetch_array( array() );

# and once again going to the documentation, the mysql_fetch_* functions do not take
# an array as parameter. They take a result set resource as parameter, but you no longer
# have any such resource to pass to them.

Also note that the only difference between assoc and array is that one creates a numerically indexed array while the other creates an associative array where the selected field names becomes keys of the array elements.