?
Solved

Dreamweaver MX repeat region causing error with total row count

Posted on 2009-03-30
7
Medium Priority
?
689 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 1000 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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

Question has a verified solution.

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

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 …
Adobe Dreamweaver CS5 is a WYSIWYG web page editor that has advanced HTML, CSS, and Javascript rendering functionality and is probably the most well-known HTML editor available. Much of Dreamweaver's appeal centers around the Design View interfac…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

752 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