[SOLVED] query then requery problem

[samoht]

hello again,

 

I am having difficulties with my db queries I think.

 

I created a top level navigation for the admin back end of my site. It connects to my db and populates the sub level navigation based on the user.

all this is in mainnav.php which is included in the header of all back end pages.

 

Then I have the actual content back end pages - one of which is a control page that allows/disallows user access to pages (same stuff that gets populated in my drop down list)

 

Do I need to kill or quit a connection before trying to requery the same stuff??

[Psycho]

No, but if you need to utilize the same data twice you don't have to run the query twice. Just run the query once and create the two sets of content once.

 

<?php

while ($record = mysql_fetch_assoc($result) {

  $content1 .= //HTML and record data
  $content2 .= //HTML and record data

}

echo $content1;

echo //Other HTML content

echo $content2;

?>

[samoht]

alright then I am not sure what to do?

(please be patient with me)

my mainnav.php looks like:

<?php	$page = basename($_SERVER['SCRIPT_NAME']);
		$NavBarsExist = false;
		mysql_select_db($database_connection1, $connection1);
		$query_rsSANavBars = "SELECT * FROM sanavbars WHERE Type = 'SA' AND FlagStatus = 'A' ORDER BY Name";
		$rsSANavBars = mysql_query($query_rsSANavBars, $connection1) or die(mysql_error());
		$totalRows_rsSANavBars = mysql_num_rows($rsSANavBars);

			if ($totalRows_rsSANavBars > 0) {
			while ($row = mysql_fetch_assoc($rsSANavBars)) {
				$linkurls[] = $row['LinkURL']; // add new array element to $linkurls, an array
				if ($row['LinkURL'] == $page) {
				 $currentType = $row['Type'];
				 }

			}
 		// Now, if $currentType is set, then there was a match with $page
		if($currentType == 'SA'){$c = 'current';}else{$c = 'adminnav';}        
				echo "\n\t".'<ul class="' .$c . ' one">'."\n\t";
    		  echo   " <li>\n\t  ";
    			echo '<a href="'.$page.'?current=one&sub=none"><b>Secure Access</b><!--[if IE 7]><!--></a><!--<![endif]-->'."\n\t ";
				echo " <!--[if lte IE 6]><table><tr><td><![endif]-->\n\t   ";
          echo '<ul class="sub">'."\t ";
					mysql_data_seek($rsSANavBars,0);
					while ($row_rsSANavBars = mysql_fetch_assoc($rsSANavBars)) {

						$SANavBarId = $row_rsSANavBars['NavBarId'];
						mysql_select_db($database_connection1, $connection1);
						$query_rsSAUserNavBars = "SELECT * FROM sausernavbars WHERE SAUserId = '$SAUserId' AND SANavBarId = '$SANavBarId' AND FlagStatus = 'A'";
						$rsSAUserNavBars = mysql_query($query_rsSAUserNavBars, $connection1) or die(mysql_error());
						$row_rsSAUserNavBars = mysql_fetch_assoc($rsSAUserNavBars);
						$totalRows_rsSAUserNavBars = mysql_num_rows($rsSAUserNavBars);	

						// show navbar if user has access to it
						if ($totalRows_rsSAUserNavBars != 0 || ($totalRows_rsCheckEmpty == 0 && $row_rsSANavBars['NavBarId'] == '112')) {
						$NavBarsExist = true; ?>	
               <li<?php if($row_rsSANavBars['LinkURL'] == $page){echo ' class="current_sub"';} ?>><a href="<?php echo $row_rsSANavBars['LinkURL']; ?>"class="blackRed"><?php echo $row_rsSANavBars['Name']; ?></a></li><?php 
												 }
											}
										} 
										if ($totalRows_rsSANavBars == 0 || !$NavBarsExist) echo "\t".'<li><a href="#">not available</a></li>'."\n";						
										echo "\n";
										?>
           </ul>
          <!--[if lte IE 6]></td></tr></table></a><![endif]-->
         </li>
        </ul><?php
	  $page = basename($_SERVER['SCRIPT_NAME']);
		$NavBarsExist = false;
		mysql_select_db($database_connection1, $connection1);
		$query_rsSANavBars = "SELECT * FROM sanavbars WHERE Type = 'AM' AND FlagStatus = 'A' ORDER BY Name";
		$rsSANavBars = mysql_query($query_rsSANavBars, $connection1) or die(mysql_error());
		$totalRows_rsSANavBars = mysql_num_rows($rsSANavBars);

			if ($totalRows_rsSANavBars > 0) {
			while ($row = mysql_fetch_assoc($rsSANavBars)) {
				$linkurls[] = $row['LinkURL']; // add new array element to $linkurls, an array
				if ($row['LinkURL'] == $page) {
				 $currentType = $row['Type'];
				 }

			}
 		// Now, if $currentType is set, then there was a match with $page
		if($currentType == 'AM'){$c = 'current';}else{$c = 'adminnav';}        
				echo "\n\t".'<ul class="' .$c . ' two">'."\n\t";
    		  echo   " <li>\n\t  ";
    			echo '<a href="'.$page.'?current=one&sub=none"><b>Account Management</b><!--[if IE 7]><!--></a><!--<![endif]-->'."\n\t ";
				echo " <!--[if lte IE 6]><table><tr><td><![endif]-->\n\t   ";
          echo '<ul class="sub">'."\t ";
					mysql_data_seek($rsSANavBars,0);
					while ($row_rsSANavBars = mysql_fetch_assoc($rsSANavBars)) {

						$SANavBarId = $row_rsSANavBars['NavBarId'];
						mysql_select_db($database_connection1, $connection1);
						$query_rsSAUserNavBars = "SELECT * FROM sausernavbars WHERE SAUserId = '$SAUserId' AND SANavBarId = '$SANavBarId' AND FlagStatus = 'A'";
						$rsSAUserNavBars = mysql_query($query_rsSAUserNavBars, $connection1) or die(mysql_error());
						$row_rsSAUserNavBars = mysql_fetch_assoc($rsSAUserNavBars);
						$totalRows_rsSAUserNavBars = mysql_num_rows($rsSAUserNavBars);	

						// show navbar if user has access to it
						if ($totalRows_rsSAUserNavBars != 0) {
						$NavBarsExist = true; ?>	
                <li<?php if($row_rsSANavBars['LinkURL'] == $page){echo ' class="current_sub"';} ?>><a href="<?php echo $row_rsSANavBars['LinkURL']; ?>"class="blackRed"><?php echo $row_rsSANavBars['Name']; ?></a></li><?php 
												 }
											}
										} 
										if ($totalRows_rsSANavBars == 0 || !$NavBarsExist)echo "\n\t\t".'<li><a href="#">not available</a></li>';						
										echo "\n"; ?>
           </ul>
          <!--[if lte IE 6]></td></tr></table></a><![endif]-->
         </li>
        </ul>

 

and my sub page above the html

$query_rsSANavBars = "SELECT * FROM sanavbars WHERE FlagStatus = 'A' ORDER BY NavBarId";
$rsSANavBars = mysql_query($query_rsSANavBars, $connection1) or die(mysql_error());
$row_rsSANavBars = mysql_fetch_assoc($rsSANavBars);
$totalRows_rsSANavBars = mysql_num_rows($rsSANavBars);

 

then in the html i have a dbl loop:

<?php
			$Rights = '';	// initialize to empty because its used below
			do {
				mysql_select_db($database_connection1, $connection1);
				$query_rsSAUserNavBars = "SELECT * FROM sausernavbars WHERE SAUserId = $SAUserId ORDER BY SANavBarId";
				$rsSAUserNavBars = mysql_query($query_rsSAUserNavBars, $connection1) or die(mysql_error());
				$row_rsSAUserNavBars = mysql_fetch_assoc($rsSAUserNavBars);
				$totalRows_rsSAUserNavBars = mysql_num_rows($rsSAUserNavBars);
				$debug->save("useridcheck",22,"sausersedit",$row_rsSANavBars['NavBarId'],$connection1); ?>
                        <tr>
                          <td><input name="Checkbox[]" type="checkbox" id="Checkbox<?php echo $row_rsSANavBars['NavBarId']; ?>" onChange="select(this);" value="<?php echo $row_rsSANavBars['NavBarId']; ?>" <?php
					do {

						if ($row_rsSAUserNavBars['SANavBarId'] == $row_rsSANavBars['NavBarId']) {
							$debug->save("navbarid",2,"sausersedit",$row_rsSANavBars['NavBarId'],$connection1);
							echo 'checked';
							$Rights = $row_rsSAUserNavBars['Rights'];
						}
					} while($row_rsSAUserNavBars = mysql_fetch_assoc($rsSAUserNavBars)); ?> tabindex="17"></td>
                          <td nowrap><label for="Checkbox<?php echo $row_rsSANavBars['NavBarId'];  $debug->save("navbarid",3,"sausersedit",$row_rsSANavBars['NavBarId'],$connection1); ?>"><?php echo $row_rsSANavBars['Name']; ?></label></td>
                          <td nowrap><input name="<?php echo $row_rsSANavBars['NavBarId']; ?>" id="Read<?php echo $row_rsSANavBars['NavBarId']; ?>" type="radio" value="read" <?php if (isset($Rights) && $Rights == 'R'){echo 'checked'; $Rights = '';}?>>
                            <label for="Read<?php echo $row_rsSANavBars['NavBarId']; ?>">Read</label>
                            <input name="<?php echo $row_rsSANavBars['NavBarId']; ?>" id="Write<?php echo $row_rsSANavBars['NavBarId']; ?>" type="radio" value="write" <?php if (isset($Rights) && $Rights == 'W'){echo 'checked'; $Rights = '';}?>>
                            <label for="Write<?php echo $row_rsSANavBars['NavBarId']; ?>">Write</label></td>
                        </tr><?php
			} while ($row_rsSANavBars = mysql_fetch_assoc($rsSANavBars)); ?>

 

I assume my problem is in my do while loops - but I am not sure how to fix them so they do not conflict with the code in mainnav.php

 

Any ideas??

[Psycho]

your last post seems to indicate you are having an issue that you have some details about (otherwise you wouldn't have an idea of where the problem is):

I assume my problem is in my do while loops...

 

Well, I interpreted the question in your first post as a generic question:

Do I need to kill or quit a connection before trying to requery the same stuff??

 

So, I answered genereically. I'm sorry, but I don't have the time at present to read through all of the code you presented above to try and guess what problem you are experiencing and what you can do to fix it.

 

If you have "a problem" you need to provide information as to the nature of the problem. Are you getting errors? What are they? If you are not getting errors what is happening with your current code that is not intended and what do you expect to happen?

 

With that information I might be inclined to take a look at the code above as I will have some sort of reference to identify a possible problem.

[samoht]

sorry about the confusion - but I was not sure where my problem was.

I found the problem now. I was using the same variable name in several queries - so I was not getting the out put I hoped for when looping through the final recorset. silly me.