Jump to content

Nested Repeat Region using a Recordset Filter

letournp

By letournp
in PHP Coding Help

 Share

Recommended Posts

letournp Newbie

letournp

Posted
Posted (edited)

I am trying to build a php page that uses 2 Repreat Regions.  I have a table called users that contains all the users info uncluding a region, and am picture file name and a title.

 

I have a recordset that gets distinct regions from the users table and a recordset that has all the users

 

What I need to do is show the picture with the name and title for every user that is in a region. for each region in the region recordset.

 

I have been at this for days trying to figure this out,  I know if must be something simple, I think I am close, but going  in circles now.

 

Here  is my code

<?php require_once('Connections/CQBLDATAB.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  if (PHP_VERSION < 6) {
    $theValue = get_magic_quotes_gpc() ? stripslashes($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;
}
}

mysql_select_db($database_CQBLDATAB, $CQBLDATAB);
$query_rsChefsAss = "SELECT * FROM Users WHERE `Chefs ASS` = 'Y'";
$rsChefsAss = mysql_query($query_rsChefsAss, $CQBLDATAB) or die(mysql_error());
$row_rsChefsAss = mysql_fetch_assoc($rsChefsAss);
$totalRows_rsChefsAss = mysql_num_rows($rsChefsAss);

mysql_select_db($database_CQBLDATAB, $CQBLDATAB);
$query_rsSecteurs = "SELECT DISTINCT Secteurs FROM Users WHERE ASS = 'Y' ORDER BY Secteurs ASC";
$rsSecteurs = mysql_query($query_rsSecteurs, $CQBLDATAB) or die(mysql_error());
$row_rsSecteurs = mysql_fetch_assoc($rsSecteurs);
$totalRows_rsSecteurs = mysql_num_rows($rsSecteurs);

$maxRows_rsAss = 15;
$pageNum_rsAss = 0;
if (isset($_GET['pageNum_rsAss'])) {
  $pageNum_rsAss = $_GET['pageNum_rsAss'];
}
$startRow_rsAss = $pageNum_rsAss * $maxRows_rsAss;

$secname_rsAss = "-1";
if (isset($_POST['CurSecteurs'])) {
  $secname_rsAss = $_POST['CurSecteurs'];
}
mysql_select_db($database_CQBLDATAB, $CQBLDATAB);
$query_rsAss = sprintf("SELECT * FROM Users WHERE ASS = 'Y' AND Users.Secteurs = %s ORDER BY TitreAss ASC", GetSQLValueString($secname_rsAss, "text"));
$query_limit_rsAss = sprintf("%s LIMIT %d, %d", $query_rsAss, $startRow_rsAss, $maxRows_rsAss);
$rsAss = mysql_query($query_limit_rsAss, $CQBLDATAB) or die(mysql_error());
$row_rsAss = mysql_fetch_assoc($rsAss);

if (isset($_GET['totalRows_rsAss'])) {
  $totalRows_rsAss = $_GET['totalRows_rsAss'];
} else {
  $all_rsAss = mysql_query($query_rsAss);
  $totalRows_rsAss = mysql_num_rows($all_rsAss);
}
$totalPages_rsAss = ceil($totalRows_rsAss/$maxRows_rsAss)-1;

?>
<!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=iso-8859-1" />
<meta name="robots" content="index, follow" />
<meta property="og:image" content="http://clubquadbasseslaurentides.com/CQBL/Images/CQBL_logo3.jpg" />
<meta name="keywords" content="Club Quad Basses Laurentides' Club, Quad, Basses, Laurentides, Club Quad Basses Laurentides, CQBL," />
<meta property="og:type" content="website" />
<meta name="title" content="Les agents de sentiers" />
<meta property="og:title" content="Les agents de sentiers" />
<meta property="og:site_name" content="CQBL" />
<meta property="og:description" content="CQBL - Club Quad Basses Laurentides - Site officiel du club de VTT des Basses Laurentides" />
<meta name="description" content="CQBL - Club Quad Basses Laurentides - Site officiel du club de VTT des Basses Laurentides" />

<title>Les agents de sentiers</title>
<script type="text/javascript">
  function resizeIframe(obj){
     {obj.style.height = 0;};
     {obj.style.height = obj.contentWindow.document.body.scrollHeight + 'px';}
  }
</script>
<style type="text/css">
.LesActivitesHeader {}
.ReunionTxtFldHeader {
	font-weight: bold;
}
.XXlargetxt {
	font-size: xx-large;
}
.BoldTxt {
	font-weight: bold;
}
</style>
</head>

<body>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<table width="100%" border="0" cellspacing="0" cellpadding="0">
  <tr>
    <td><table width="100%" border="0" cellspacing="0" cellpadding="1">
      <tr>
        <td align="center"><p class="XXlargetxt"><strong>Superviseur(s) en chef</strong></p>
          <table cellpadding="0" cellspacing="0" >
            <tr>
              <?php
$rsChefsAss_endRow = 0;
$rsChefsAss_columns = 3; // number of columns
$rsChefsAss_hloopRow1 = 0; // first row flag
do {
    if($rsChefsAss_endRow == 0  && $rsChefsAss_hloopRow1++ != 0) echo "<tr>";
   ?>
              <td><table border="0" cellspacing="3" cellpadding="3">
                <tr>
                  <td align="center"><img src="<?php echo $row_rsChefsAss['Photo']; ?>" alt="" name="ChefAS" width="80" height="92" id="ChefAS" /></td>
                </tr>
                <tr>
                  <td align="center" class="BoldTxt"><?php echo $row_rsChefsAss['Name']; ?></td>
                </tr>
                <tr>
                  <td align="center" class="BoldTxt"><?php echo $row_rsChefsAss['TitreAss']; ?></td>
                </tr>
              </table></td>
              <?php  $rsChefsAss_endRow++;
if($rsChefsAss_endRow >= $rsChefsAss_columns) {
  ?>
            </tr>
            <?php
 $rsChefsAss_endRow = 0;
  }
} while ($row_rsChefsAss = mysql_fetch_assoc($rsChefsAss));
if($rsChefsAss_endRow != 0) {
while ($rsChefsAss_endRow < $rsChefsAss_columns) {
    echo("<td> </td>");
    $rsChefsAss_endRow++;
}
echo("</tr>");
}?>
          </table></td>
      </tr>
    </table>
      <p> </p>
        <?php do { ?>
          <table width="100%" border="0" cellspacing="0" cellpadding="1">
            <tr>
              <td class="BoldTxt"><form id="form1" name="form1" method="post" action="">
                <input name="CurSecteurs" type="hidden" id="CurSecteurs" value="<?php echo $row_rsSecteurs['Secteurs']; ?>" />
                Secteurs : <?php echo $row_rsSecteurs['Secteurs']; ?>
              sql:  <?php echo $query_rsAss ?>
              </form></td>
            </tr>
            <tr>
              <td align="center"><table >
                <tr>
                  <?php
$rsAss_endRow = 0;
$rsAss_columns = 3; // number of columns
$rsAss_hloopRow1 = 0; // first row flag
do {
    if($rsAss_endRow == 0  && $rsAss_hloopRow1++ != 0) echo "<tr>";
   ?>
                  <td><table border="0" cellspacing="3" cellpadding="3">
                    <tr>
                      <td align="center"><img src="<?php echo $row_rsAss['Photo']; ?>" alt="" name="PhotoAss" width="80" height="92" id="PhotoAss" /></td>
                    </tr>
                    <tr>
                      <td align="center" class="BoldTxt"><?php echo $row_rsAss['Name']; ?></td>
                    </tr>
                    <tr>
                      <td align="center" class="BoldTxt"><?php echo $row_rsAss['TitreAss']; ?></td>
                    </tr>
                  </table></td>
                  <?php  $rsAss_endRow++;
if($rsAss_endRow >= $rsAss_columns) {
  ?>
                </tr>
                <?php
 $rsAss_endRow = 0;
  }
} while ($row_rsAss = mysql_fetch_assoc($rsAss));
if($rsAss_endRow != 0) {
while ($rsAss_endRow < $rsAss_columns) {
    echo("<td> </td>");
    $rsAss_endRow++;
}
echo("</tr>");
}?>
              </table>
              </tr>
          </table>
          <?php } 
		  while ($row_rsSecteurs = mysql_fetch_assoc($rsSecteurs)); 
				mysql_data_seek($rsSecteurs, 0); //resets recordset pointer
				$row_rsSecteurs = mysql_fetch_assoc($rsSecteurs);  //selects the first row of recordset  
		  
		  ?></td>
  </tr>
  <tr>
    <td> </td>
  </tr>
</table>
<tr>
  <td><p> </p>
  </body>
</html>
<?php
mysql_free_result($rsChefsAss);

mysql_free_result($rsSecteurs);

mysql_free_result($rsAss);
?>

So the table should look something like this

 

Region = Est

 

User1 Picture     User 2 Picture        User 3 Picture

User 1 name      User 2 name          User 2 name

User 1 Title         User 2 Title            User 3 Title

 

Region = West

 

 

User1 Picture     User 2 Picture        User 3 Picture

User 1 name      User 2 name          User 2 name

User 1 Title         User 2 Title            User 3 Title

 

next Region etc....

 

I hope this makes sense.

 

Thanks in advance

Edited by letournp
Link to comment
Share on other sites
Ch0cu3r Newbie

Ch0cu3r

Posted
Posted

You are not going to get very far using Dreamweaver's built in code wizards, (which to to be honest are complete garbage. By the looks of thing Adobe has not updated it since the Macromedia days).

 

You are far better of coding PHP by hand.

 

If the data is coming from the same table then you only need to use 1 query. You will have the query return all records in the users table, but have the results ordered by region name first and then secondly order by the users id( or name).  Example query (Substitute user user_picure_column and Username_column to be your columns names)

SELECT Secteurs, user_picure_column FROM Users WHERE ASS = 'Y' ORDER BY Secteurs ASC, Username_column ASC

For displaying the results you'd do something like this

$prevRegion = '';

// loop over results of query
while($row = mysql_fetch_assoc($result))
{
   // if the region has changed from previous row
   if($prevRegion != $row['Secteurs'])
   {
        // then output a new region heading
        echo '<h1>' . $row['Secteurs'] . '</h1>';
        $prevRegion = $row['Secteurs'];
   }

   // output the users picture
   echo '<img src="'.$row['user_picture'].'" />';
}

The result should be each user picture is displayed under the correct region heading.

Link to comment
Share on other sites
letournp Newbie

letournp

Posted
  • Author
Posted

I understand that Dreamweaver might not be the best, but  it has suited my needs util now.  I am not a professional web developer.  i just do it for fun and to learn.

 

Your codes works great thanks, but now how do I get the user name and the user titles to ligne up centered underneath the user's picture.

Link to comment
Share on other sites
Ch0cu3r Newbie

Ch0cu3r

Posted
Posted

You'll need to wrap the username, title  and image in a containing element and then apply css styling as necessary

   // output the users name, title and picture
   echo '
   <div class="user">
     <img src="'.$row['user_picture'].'" />
     <h4>' . $row['username'] . '<span class="title">' . $row['title'] . '</span></h4>'
   </div>';

Then apply a little css to style it.

.user {
    display: inline-block;
    text-align: center;
    border: 1px solid #CCC;
    padding: 5px;
    margin: 5px;
}

.user img {
    display: block;
}

.user h4 {
    margin: 0;
    padding: 0;
    font-weight: normal;
}
.user .title {
    display: block;
    font-weight: bold;
}
Link to comment
Share on other sites
letournp Newbie

letournp

Posted
  • Author
Posted

That is great thank you so much,

 

One more thing,  these pages are loaded into an Iframe.  For some reason since I have made the changes the Iframe no longer re-sizes to fit the content that is loaded.

 

Here is my code again.  I do have a script in there, but for some reason it does not want to work.  It works fine on all of my other pages but not on this one.

<?php require_once('Connections/CQBLDATAB.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  if (PHP_VERSION < 6) {
    $theValue = get_magic_quotes_gpc() ? stripslashes($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;
}
}

mysql_select_db($database_CQBLDATAB, $CQBLDATAB);
$query_rsChefsAss = "SELECT * FROM Users WHERE `Chefs ASS` = 'Y'";
$rsChefsAss = mysql_query($query_rsChefsAss, $CQBLDATAB) or die(mysql_error());
$row_rsChefsAss = mysql_fetch_assoc($rsChefsAss);
$totalRows_rsChefsAss = mysql_num_rows($rsChefsAss);

$maxRows_rsAss = 15;
$pageNum_rsAss = 0;
if (isset($_GET['pageNum_rsAss'])) {
  $pageNum_rsAss = $_GET['pageNum_rsAss'];
}
$startRow_rsAss = $pageNum_rsAss * $maxRows_rsAss;

$secname_rsAss = "-1";
if (isset($_POST['CurSecteurs'])) {
  $secname_rsAss = $_POST['CurSecteurs'];
}
mysql_select_db($database_CQBLDATAB, $CQBLDATAB);
$query_rsAss = sprintf("SELECT Name, Photo, TitreAss, Secteurs FROM Users WHERE ASS = 'Y' ORDER BY Secteurs ASC, Users.OrdreTriAss ASC");
$query_limit_rsAss = sprintf("%s LIMIT %d, %d", $query_rsAss, $startRow_rsAss, $maxRows_rsAss);
$rsAss = mysql_query($query_limit_rsAss, $CQBLDATAB) or die(mysql_error());
$row_rsAss = mysql_fetch_assoc($rsAss);

if (isset($_GET['totalRows_rsAss'])) {
  $totalRows_rsAss = $_GET['totalRows_rsAss'];
} else {
  $all_rsAss = mysql_query($query_rsAss);
  $totalRows_rsAss = mysql_num_rows($all_rsAss);
}
$totalPages_rsAss = ceil($totalRows_rsAss/$maxRows_rsAss)-1;
?>
<!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=iso-8859-1" />
<meta name="robots" content="index, follow" />
<meta property="og:image" content="http://clubquadbasseslaurentides.com/CQBL/Images/CQBL_logo3.jpg" />
<meta name="keywords" content="Club Quad Basses Laurentides' Club, Quad, Basses, Laurentides, Club Quad Basses Laurentides, CQBL," />
<meta property="og:type" content="website" />
<meta name="title" content="L'administration" />
<meta property="og:title" content="L'administration" />
<meta property="og:site_name" content="CQBL" />
<meta property="og:description" content="CQBL - Club Quad Basses Laurentides - Site officiel du club de VTT des Basses Laurentides" />
<meta name="description" content="CQBL - Club Quad Basses Laurentides - Site officiel du club de VTT des Basses Laurentides" />
<title>L'administration</title>
<script type="text/javascript">
  function resizeIframe(obj){
     {obj.style.height = 0;};
     {obj.style.height = obj.contentWindow.document.body.scrollHeight + 'px';}
  }
</script>
<style type="text/css">
.LesActivitesHeader {}
.ReunionTxtFldHeader {
	font-weight: bold;
}
.user {
    display: inline-block;
    text-align: center;
    border: 0px solid #CCC;
    padding: 5px;
    margin: 5px;
}

.user img {
    display: block;
	margin-left:auto;
	margin-right:auto;
	
}

.user h4 {
    margin: 0;
    padding: 0;
    font-weight: normal;
}
.user .title {
    display: block;
    font-weight: normal;
}
.MainHeadTxt {
	font-size: xx-large;
	font-weight: bold;
}
</style>
</head>
<body>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
  <tr>
    <td align="center"><table width="100%" border="0" cellspacing="0" cellpadding="1">
      <tr>
        <td align="center"><p class="MainHeadTxt">Superviseur(s) en chef </p>
          <table cellpadding="0" cellspacing="0" >
            <tr>
              <?php
$rsChefsAss_endRow = 0;
$rsChefsAss_columns = 3; // number of columns
$rsChefsAss_hloopRow1 = 0; // first row flag
do {
    if($rsChefsAss_endRow == 0  && $rsChefsAss_hloopRow1++ != 0) echo "<tr>";
   ?>
              <td><table border="0" cellspacing="3" cellpadding="3">
                <tr>
                  <td align="center"><img src="<?php echo $row_rsChefsAss['Photo']; ?>" alt="" name="ChefAS" width="80" height="92" id="ChefAS" /></td>
                </tr>
                <tr>
                  <td align="center" class="BoldTxt"><?php echo $row_rsChefsAss['Name']; ?></td>
                </tr>
                <tr>
                  <td align="center" class="BoldTxt"><?php echo $row_rsChefsAss['TitreAss']; ?></td>
                </tr>
              </table></td>
              <?php  $rsChefsAss_endRow++;
if($rsChefsAss_endRow >= $rsChefsAss_columns) {
  ?>
            </tr>
            <?php
 $rsChefsAss_endRow = 0;
  }
} while ($row_rsChefsAss = mysql_fetch_assoc($rsChefsAss));
if($rsChefsAss_endRow != 0) {
while ($rsChefsAss_endRow < $rsChefsAss_columns) {
    echo("<td> </td>");
    $rsChefsAss_endRow++;
}
echo("</tr>");
}?>
    </table></td>
      </tr>
    </table>
    <?php 
      $prevRegion = '';
// loop over results of query
while($row = mysql_fetch_assoc($rsAss))
{
   // if the region has changed from previous row
   if($prevRegion != $row['Secteurs'])
   {
        // then output a new region heading
        echo '<h1>' . $row['Secteurs'] . '</h1>';
        $prevRegion = $row['Secteurs'];
   }
   // output the users picture?
   echo '
	<div class="user">
    <img src="'.$row['Photo']. '" height="92" width="80"/>
	<h4>' . $row['Name'] . '<span class="title">' . $row['TitreAss'] . '</span></h4>'
    ?> </div>

<?php }?>
  </tr>
  <tr>
    <td align="center">  
  </tr>
</table>
</body>
</html>
<?php
mysql_free_result($rsChefsAss);
mysql_free_result($rsAss);
?>
Link to comment
Share on other sites
letournp Newbie

letournp

Posted
  • Author
Posted (edited)

Found it.  The following row in my code was causing the issue.  Not quite qure why it is there and what it does (no time right now to analyse) but when I change the number to 555 instead of 15 it wolved the issue and the page resizes properly.

$maxRows_rsAss = 555;<--- this is the colprit.

I really want to thank you for all your assistance.  You solved my issue and i learned something new.

 

Thank you soo much.

Edited by letournp
Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.