Problem adding row count to repeat region

maxwell_

I have a list of items (items.php) in a repeat region.
When an item is chosen it displays on its own page.
On that page is a comments form and a list of previous comments about that item.
I would like each item on the items.php page to state how many comments have been made about that item.
I'm sure my SELECT statement is ok because when I test in mysql it look as if the output is what I need

$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);
    $row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);

When I add the following to the repeat region I get this error

Warning: mysql_fetch_assoc() expects parameter 1 to be resource

if($row_Recordset1)
  {
  while($row = mysql_fetch_assoc($row_Recordset1))
    {
    echo $row['COUNT'];
    }
  }

Best regards Maxwell

leatherback

$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);
$totalRows_Recordset1 = mysql_num_rows($Recordset1); 

if($totalRows_Recordset1 > 0)
  {
  while($row = mysql_fetch_assoc($totalRows_Recordset1$Recordset1))
    {
    echo $row['COUNT'];
    }
  }

I think..

maxwell_

Many thanks for responding.

Did you mean

($totalRows_Recordset1 = $Recordset1)

I tried it,

the output was

The first item showed the following

Current comments:2311000000000000000000000000000

all others have Current comments:

the output coresponds exactly to the comments in the database table

one item has 2, next has 3, next has 1, next has 1, all others have 0

Best regards Maxwell

leatherback

Well.. The idea is that you add the rest of the info to your loop yourself; So where I placed echo $row['count'], you can create the full output with the other info from your database.

If for some reason [which I do not know] you do not want to produce all output for all fields in the same loop, then you could e.g. use an array to store the count values.

maxwell_

I already have the page content displaying.
All I need to do is add Current comments: to a table cell in the repeat region.
I'm not totally sure how to store the values in an array, and output the totals.

This is the code that makes up the page

if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  if (PHP_VERSION < 6) {
    $theValue = get_magic_quotes_gpc() ? $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_rs_comments = "-1";
if (isset($_GET['software_id'])) {
  $colname_rs_comments = $_GET['software_id'];
}


mysql_select_db($database_abe, $abe);
$query_rsTitles = "SELECT id, title, company, `description`, resources, location, url, image, keyword, class_id, level_id, copies FROM titles ORDER BY title ASC";
$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);

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);

if(!isset($_POST['softwareLevel'])){
if (!isset($_GET['class_id'])) {

$query_rsTitles = "SELECT id, title, company, `description`, resources, location, url, image, keyword, copies FROM titles ORDER BY id ASC";

}else{

$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{


$where = ''; 

{ 
      $types = array_pad(array(), count($_POST['softwareLevel']), 'int'); 

input data 
$_POST['softwareLevel'] = array_map('GetSQLValueString', $_POST['softwareLevel'], $types); 

$where = sprintf(' WHERE titles.level_id IN (%s) ', 
    implode(', ', $_POST['softwareLevel'])); 
} 

$query = sprintf('SELECT id, title, company, `description`, resources, location, url, image, keyword, copies FROM titles %s ORDER BY id ASC', $where);

$rsTitles = mysql_query($query_rsTitles, $abe) or die(mysql_error());
$row_rsTitles = mysql_fetch_assoc($rsTitles);
$totalRows_rsTitles = mysql_num_rows($rsTitles);
echo "<pre>get";

HTML part

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<?php $pagetitle="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" />
</head>
<body>
<?php include("../../includes/header.php"); ?>
    <table width="80%" border="1" align="center" cellpadding="3" cellspacing="0">
        <tr>
          <td width="37%" class="tableheading"><a href="../../index.php"><img src="../../images/Res_home.png" border="0" /></a></td>
          <td class="tableheading"><a href="software_detail.php"><img src="../../images/list.png" width="54" height="69" border="0" /></a></td>
          <td width="32%" class="tableheading"><a href="<?php echo $logoutAction ?>"><img src="../../images/log_out2.png" width="73" height="71" border="0" /></a></td>
      </tr>
        <tr>
          <td>&nbsp;</td>
          <td>&nbsp;</td>
          <td>&nbsp;</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>
    </table>
      <?php do { ?>
      <table width="820" border="0" align="center" cellpadding="3" cellspacing="2" rules="all">
        <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>


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

?> </td>
      </tr>
      </table>  

      <br />
      <?php } while ($row_rsTitles = mysql_fetch_assoc($rsTitles)); ?>
      <?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);
?>