Solved

Dreamweaver MX repeat region causing error with total row count

Posted on 2009-03-30
7
685 Views
Last Modified: 2012-05-06
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
Comment
Question by:kcalder
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 19

Expert Comment

by:v2Media
ID: 24023617
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
 
LVL 19

Expert Comment

by:v2Media
ID: 24023845
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
 
LVL 19

Accepted Solution

by:
v2Media earned 250 total points
ID: 24024364
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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:kcalder
ID: 24026568
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
 

Author Comment

by:kcalder
ID: 24026572
sorry, the response above should say "I'm paginating but NOT changing the URL"
0
 
LVL 19

Expert Comment

by:v2Media
ID: 24026805
No probs, when you do use it - line 55 should read: $page_qry = mysql_query( $page_sql );
0
 

Author Closing Comment

by:kcalder
ID: 31564456
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

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I still run into .cgi files every now and then. In some instances, I actually prefer the simplicity of a .cgi script to other options. Since I use DreamWeaver extensively, what I needed was a way to open .cgi scripts in Dreamweaver. And I wanted to …
This article is very specific and is only intended to help if you are installing Dreamweaver 8 in a Windows 7 environment with Office 2007 installed.   I'm not sure why Microsoft tends to release OS' that should not be released but they do.  Windows…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question