Solved

Dreamweaver MX repeat region causing error with total row count

Posted on 2009-03-30
7
679 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
  • 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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

For those who don't know, Adobe Dreamweaver is a popular commercial web editor that enables you to design, build and manage complex websites. The editor is a WYSIWYG (What You See Is What You Get) web editor, which means that you can create your web…
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 …
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

706 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now