• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 709
  • Last Modified:

Dreamweaver MX repeat region causing error with total row count

I am selecting some data from a MySQL database to display in rows on a page using Dreamweaver's repeat region function. For some reason that I cannot fathom, the repeat region function is calculating that there are more rows than there actually are.

On visiting the page first off there doesn't appear to be a problem but if the database is updated by removing an item, the $totalRows count does not change.

I urgently need assistance with resolving this and a fresh pair of experienced eyes would be useful. I have uploaded the page, which can be found at http://cibse-epg.org/expertZone/expertZone_style1.php5. The DW head and body code for the repeat region element are as below.
<!-- head code -->
<?php
mysql_select_db($database_EPG_links, $EPG_links);
$query_rstCurrentThreads = "SELECT QandA_pk, QandA_question, QandA_answer, QandA_qName, Q_time, Q_date FROM expertZone_QandA WHERE topic_ID = 3 AND QandA_visible = 1 ORDER BY QandA_pk DESC";
$query_limit_rstCurrentThreads = sprintf("%s LIMIT %d, %d", $query_rstCurrentThreads, $startRow_rstCurrentThreads, $maxRows_rstCurrentThreads);
$rstCurrentThreads = mysql_query($query_limit_rstCurrentThreads, $EPG_links) or die(mysql_error());
$row_rstCurrentThreads = mysql_fetch_assoc($rstCurrentThreads);
 
if (isset($_GET['totalRows_rstCurrentThreads'])) {
  $totalRows_rstCurrentThreads = $_GET['totalRows_rstCurrentThreads'];
} else {
  $all_rstCurrentThreads = mysql_query($query_rstCurrentThreads);
  $totalRows_rstCurrentThreads = mysql_num_rows($all_rstCurrentThreads);
}
$totalPages_rstCurrentThreads = ceil($totalRows_rstCurrentThreads/$maxRows_rstCurrentThreads)-1;
?>
 
<!-- body code -->
<table class="couplet">
      <?php do { ?>
      <tr>
          <td class="question"><?php echo $row_rstCurrentThreads['QandA_question'].' ['.$row_rstCurrentThreads['QandA_qName'].', '.$row_rstCurrentThreads['Q_date'].']'; ?></td>
      </tr>
      <tr>
        <td class="answer"><?php echo $row_rstCurrentThreads['QandA_answer']; ?></td>
      </tr>
      <?php } while ($row_rstCurrentThreads = mysql_fetch_assoc($rstCurrentThreads)); ?>
</table>

Open in new window

0
kcalder
Asked:
kcalder
  • 4
  • 3
1 Solution
 
v2MediaCommented:
Code for $totalRows is missing. Code for many other vars is missing too. Why on earth would you get total rows from a GET value anyway? A user could chuck anything in the url and that would be the total rows. $totalRows should come from a database query, nowhere else.

It also looks like you're trying to paginate the query. If that's the case, the code for setting up pagination is missing too.
0
 
v2MediaCommented:
This is a standard paginated resultset that completes what you're trying to achieve in the code above: -
<?php
mysql_select_db($database_EPG_links, $EPG_links);
$sql_totalThreads = "SELECT QandA_pk FROM expertZone_QandA WHERE topic_ID = 3 AND QandA_visible = 1";
$qry_totalThreads = mysql_query($sql_totalThreads, $EPG_links) or die(mysql_error());
$resultset_total = mysql_num_rows($qry_totalThreads);
 
//PAGINATION
//user defined vars
 
//applicable db table for qry
$db_tbl = 'expertZone_QandA';
 
//default order
$order_by = 'ORDER BY QandA_pk DESC';
 
//where clauses here
$where = 'WHERE topic_ID = 3 AND QandA_visible = 1';
 
//max records per page
$p_max = 15;
 
//end user defined vars
 
//page get var & default value
$page = ( isset($_GET['page']) && is_numeric($_GET['page']) ) ? $_GET['page'] : 1;
 
//total pages
$p_total = ceil( $resultset_total / $p_max );
 
//page offset
$offset = ( $p_max * $page ) - $p_max;
 
//page links if required
$pagination = false;
 
if ($resultset_total > $p_max ) {
 
	$pagination = 'PAGES: ';
	
	//current url
	$page_url = $_SERVER['PHP_SELF'];
 
	for ($i=1;$i<=$p_total;$i++) {
	
		$arr_pagination[] .= "<a href=\"{$page_url}?page={$i}\">$i</a>";
	
	}
	
	$pagination .= implode(', ',$arr_pagination);
	
}
 
// page limited result set
$page_sql = "SELECT * FROM $db_tbl $where $order_by LIMIT $offset, $p_max";
$page_qry = qry ( $page_sql );
$page_row = row ( $page_qry );
 
 
?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Q_24278302</title>
</head>
<body>
<!-- body code -->
<table class="couplet">
      <?php do { ?>
      <tr>
          <td class="question"><?php echo $page_row['QandA_question'].' ['.$page_row['QandA_qName'].', '.$page_row['Q_date'].']'; ?></td>
      </tr>
      <tr>
        <td class="answer"><?php echo $page_row['QandA_answer']; ?></td>
      </tr>
      <?php } while ($page_row = row ( $page_qry )); ?>
      <?php
	  if($pagination) {
      ?>
      <tr>
      <td><?php echo $pagination ?></td>
      </tr>
      <?php } ?>
</table></body>
</html>

Open in new window

0
 
v2MediaCommented:
I was just informed that I left in user defined functions in the pagination snippet. The corrected code is below.
<?php
mysql_select_db($database_EPG_links, $EPG_links);
$sql_totalThreads = "SELECT QandA_pk FROM expertZone_QandA WHERE topic_ID = 3 AND QandA_visible = 1";
$qry_totalThreads = mysql_query($sql_totalThreads, $EPG_links) or die(mysql_error());
$resultset_total = mysql_num_rows($qry_totalThreads);
 
//PAGINATION
//user defined vars
 
//applicable db table for qry
$db_tbl = 'expertZone_QandA';
 
//default order
$order_by = 'ORDER BY QandA_pk DESC';
 
//where clauses here
$where = 'WHERE topic_ID = 3 AND QandA_visible = 1';
 
//max records per page
$p_max = 15;
 
//end user defined vars
 
//page get var & default value
$page = ( isset($_GET['page']) && is_numeric($_GET['page']) ) ? $_GET['page'] : 1;
 
//total pages
$p_total = ceil( $resultset_total / $p_max );
 
//page offset
$offset = ( $p_max * $page ) - $p_max;
 
//page links if required
$pagination = false;
 
if ($resultset_total > $p_max ) {
 
        $pagination = 'PAGES: ';
        
        //current url
        $page_url = $_SERVER['PHP_SELF'];
 
        for ($i=1;$i<=$p_total;$i++) {
        
                $arr_pagination[] .= "<a href=\"{$page_url}?page={$i}\">$i</a>";
        
        }
        
        $pagination .= implode(', ',$arr_pagination);
        
}
 
//page limited result set
$page_sql = "SELECT * FROM $db_tbl $where $order_by LIMIT $offset, $p_max";
$page_qry = my_sql_query( $page_sql );
$page_row = mysql_fetch_assoc( $page_qry,$EPG_links ) or die(mysql_error()); 
 
?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Q_24278302</title>
</head>
<body>
<!-- body code -->
<table class="couplet">
      <?php do { ?>
      <tr>
          <td class="question"><?php echo $page_row['QandA_question'].' ['.$page_row['QandA_qName'].', '.$page_row['Q_date'].']'; ?></td>
      </tr>
      <tr>
        <td class="answer"><?php echo $page_row['QandA_answer']; ?></td>
      </tr>
      <?php } while ($page_row = mysql_fetch_assoc( $page_qry )); ?>
      <?php
          if($pagination) {
      ?>
      <tr>
      <td><?php echo $pagination ?></td>
      </tr>
      <?php } ?>
</table></body>
</html>

Open in new window

0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
kcalderAuthor Commented:
Thanks very much for responding. I am using Dreamweaver MX and the code you see in the myquestion is that generated by the program as a result of my requiring a repeat region; it's not the whole of the code, just the relevant section for the repeat region. I'm paginating but changing the URL, it's only to display the database results in a list of 5 to the page and I have navigation in place for that.

It looks like it would be best for me to code this manually as you suggest. I don't like the use of GET lke that either; Dreamweaver is certainly screwing things up for sure. I'll redo the code with your routine and post back with the result once I have got it done.

Thanks again.
0
 
kcalderAuthor Commented:
sorry, the response above should say "I'm paginating but NOT changing the URL"
0
 
v2MediaCommented:
No probs, when you do use it - line 55 should read: $page_qry = mysql_query( $page_sql );
0
 
kcalderAuthor Commented:
In the end I decided to take apart the whole page and re-build it to see if it was Dreamweaver's code or mine that was causing the problem. In fact it is Dreamweaver, and for the same reason that you expressed surprise - the use of $_GET as a method for $totalRows. What is happening is that if I change the number of entries in the database and then refresh the page manually, the page refreshes but the value of $totalRows doesn't because it is using $_GET. What a daft idea. Thanks for your help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now