Can Any One Simplify This PHP Code

[cathalbahn]

I was wondering was there any way to simplify this PHP code, it go up 1 through 7 in several places. I was trying to do it with a while loop but I wasn't to sure how to go about this.

 

// Build Sub Navigation menu and gather page data here ----------------------------------------------------- 

$sqlCommand = "SELECT id, name, alias, sublevel, parent, link FROM menu WHERE published='1' AND sublevel='1' AND parent='1' ORDER BY  sublevel, parent, ordering ASC"; 
$query = mysqli_query($myConnection, $sqlCommand) or die (mysqli_error()); 

$submenuDisplay1 = '';
$submenuDisplay1 .= '<ul id="vbUL_button1" class="bar" style="visibility: hidden;">';
while ($row = mysqli_fetch_array($query)) { 
    $parent = $row["parent"];
    $pid = $row["id"];
   	$link = $row["link"];
   	$name = $row["name"];

   	

$submenuDisplay1 .= '<li><a href="' . $link . '" title="' . $name . '">' . $name . '</a></li>';


} 
$submenuDisplay1 .= '</ul>';
mysqli_free_result($query); 




//----------------------------------------------------------------------------------------------------------
$sqlCommand = "SELECT id, name, alias, sublevel, parent, link FROM menu WHERE published='1' AND sublevel='1' AND parent='2' ORDER BY  sublevel, parent, ordering ASC"; 
$query = mysqli_query($myConnection, $sqlCommand) or die (mysqli_error()); 

$submenuDisplay2 = '';
$submenuDisplay2 .= '<ul id="vbUL_button2" class="bar" style="visibility: hidden;">';
while ($row = mysqli_fetch_array($query)) { 
    $parent = $row["parent"];
    $pid = $row["id"];
   	$link = $row["link"];
   	$name = $row["name"];

   	

$submenuDisplay2 .= '<li><a href="' . $link . '" title="' . $name . '">' . $name . '</a></li>';


} 
$submenuDisplay2 .= '</ul>';
mysqli_free_result($query); 

//----------------------------------------------------------------------------------------------------------

$sqlCommand = "SELECT id, name, alias, sublevel, parent, link FROM menu WHERE published='1' AND sublevel='1' AND parent='3' ORDER BY  sublevel, parent, ordering ASC"; 
$query = mysqli_query($myConnection, $sqlCommand) or die (mysqli_error()); 

$submenuDisplay3 = '';
$submenuDisplay3 .= '<ul id="vbUL_button3" class="bar" style="visibility: hidden;">';
while ($row = mysqli_fetch_array($query)) { 
    $parent = $row["parent"];
    $pid = $row["id"];
   	$link = $row["link"];
   	$name = $row["name"];

   	

$submenuDisplay3 .= '<li><a href="' . $link . '" title="' . $name . '">' . $name . '</a></li>';


} 
$submenuDisplay3 .= '</ul>';
mysqli_free_result($query); 

//----------------------------------------------------------------------------------------------------------

$sqlCommand = "SELECT id, name, alias, sublevel, parent, link FROM menu WHERE published='1' AND sublevel='1' AND parent='4' ORDER BY  sublevel, parent, ordering ASC"; 
$query = mysqli_query($myConnection, $sqlCommand) or die (mysqli_error()); 

$submenuDisplay4 = '';
$submenuDisplay4 .= '<ul id="vbUL_button4" class="bar" style="visibility: hidden;">';
while ($row = mysqli_fetch_array($query)) { 
    $parent = $row["parent"];
    $pid = $row["id"];
   	$link = $row["link"];
   	$name = $row["name"];

   	

$submenuDisplay4 .= '<li><a href="' . $link . '" title="' . $name . '">' . $name . '</a></li>';


} 
$submenuDisplay4 .= '</ul>';
mysqli_free_result($query); 

//----------------------------------------------------------------------------------------------------------

$sqlCommand = "SELECT id, name, alias, sublevel, parent, link FROM menu WHERE published='1' AND sublevel='1' AND parent='5' ORDER BY  sublevel, parent, ordering ASC"; 
$query = mysqli_query($myConnection, $sqlCommand) or die (mysqli_error()); 

$submenuDisplay5 = '';
$submenuDisplay5 .= '<ul id="vbUL_button5" class="bar" style="visibility: hidden;">';
while ($row = mysqli_fetch_array($query)) { 
    $parent = $row["parent"];
    $pid = $row["id"];
   	$link = $row["link"];
   	$name = $row["name"];

   	

$submenuDisplay5 .= '<li><a href="' . $link . '" title="' . $name . '">' . $name . '</a></li>';


} 
$submenuDisplay5 .= '</ul>';
mysqli_free_result($query); 

//----------------------------------------------------------------------------------------------------------

$sqlCommand = "SELECT id, name, alias, sublevel, parent, link FROM menu WHERE published='1' AND sublevel='1' AND parent='6' ORDER BY  sublevel, parent, ordering ASC"; 
$query = mysqli_query($myConnection, $sqlCommand) or die (mysqli_error()); 

$submenuDisplay6 = '';
$submenuDisplay6 .= '<ul id="vbUL_button6" class="bar" style="visibility: hidden;">';
while ($row = mysqli_fetch_array($query)) { 
    $parent = $row["parent"];
    $pid = $row["id"];
   	$link = $row["link"];
   	$name = $row["name"];

   	

$submenuDisplay6 .= '<li><a href="' . $link . '" title="' . $name . '">' . $name . '</a></li>';


} 
$submenuDisplay6 .= '</ul>';
mysqli_free_result($query); 

//----------------------------------------------------------------------------------------------------------

$sqlCommand = "SELECT id, name, alias, sublevel, parent, link FROM menu WHERE published='1' AND sublevel='1' AND parent='7' ORDER BY  sublevel, parent, ordering ASC"; 
$query = mysqli_query($myConnection, $sqlCommand) or die (mysqli_error()); 

$submenuDisplay7 = '';
$submenuDisplay7 .= '<ul id="vbUL_button7" class="bar" style="visibility: hidden;">';
while ($row = mysqli_fetch_array($query)) { 
    $parent = $row["parent"];
    $pid = $row["id"];
   	$link = $row["link"];
   	$name = $row["name"];

   	

$submenuDisplay7 .= '<li><a href="' . $link . '" title="' . $name . '">' . $name . '</a></li>';


} 
$submenuDisplay7 .= '</ul>';
mysqli_free_result($query); 

//----------------------------------------------------------------------------------------------------------

 

And to render it out on the page where I want it I use this bit of code.

 

<?php echo $submenuDisplay1; echo $submenuDisplay2; echo $submenuDisplay3; echo $submenuDisplay4; echo $submenuDisplay5; echo $submenuDisplay6;echo $submenuDisplay7; ?>

 

 

Thanks for your help in advance.

 

[andrewgauger]

I did not read the whole source code to get this result but I think I see what you mean.  I'm going to use a for loop because we KNOW 1 is the beginning and 7 is the end:

 

for ($iterative=1;$iterative<8;$iterative++){
$sqlCommand = "SELECT id, name, alias, sublevel, parent, link FROM menu WHERE published='1' AND sublevel='1' AND parent='$iterative' ORDER BY  sublevel, parent, ordering ASC"; 
$query = mysqli_query($myConnection, $sqlCommand) or die (mysqli_error()); 

${"submenuDisplay$iterative"} = '';
${"submenuDisplay$iterative"} .= "<ul id=\"vbUL_button".$iterative."\" class=\"bar\" style=\"visibility: hidden;\">";
while ($row = mysqli_fetch_array($query)) { 
    $parent = $row["parent"];
    $pid = $row["id"];
   	$link = $row["link"];
   	$name = $row["name"];

   	

${"submenuDisplay$iterative"} .= '<li><a href="' . $link . '" title="' . $name . '">' . $name . '</a></li>';


} 
${"submenuDisplay$iterative"} .= '</ul>';
mysqli_free_result($query); 
}

 

That should get you going in the right direction, even if it doesn't give you exactly the results you were looking for.

 

[cathalbahn]

Thanks very much for the help that has seemed to have done the trick.

[andrewgauger]

Wow, it worked on the first try?  Sweet.

[PFMaBiSmAd]

The following should be the equivalent to the above (assuming no typo's and that I did not misinterpret what your data is) -

 

// assumptions 	- parent 1-7 are all the values and you always select all of them
//				- you always display the whole menu (i.e. you can build a single string holding the whole menu)
// if these assumptions are incorrect, some of the code reduction would need to be undone
$sqlCommand = "SELECT name, parent, link FROM menu WHERE published='1' AND sublevel='1' ORDER BY parent, ordering";
$query = mysqli_query($myConnection, $sqlCommand) or die (mysqli_error());
$submenuDisplay = '';
$last_parent = NULL;
while ($row = mysqli_fetch_array($query)){
if($row['parent'] != $last_parent){
	// the parent changed
	$last_parent = $row['parent']; // remember the new parent value
	if($last_parent != NULL){
		// finish the previous section
		$submenuDisplay .= '</ul>';
	}
	// start a new section
	$submenuDisplay .= "<ul id=\"vbUL_button{$row['parent']}\" class=\"bar\" style=\"visibility: hidden;\">";
}
$submenuDisplay .= "<li><a href=\"{$row['link']}\" title=\"{$row['name']}\">{$row['name']}</a></li>";
}
$submenuDisplay .= '</ul>'; // finish the last section
mysqli_free_result($query);

 

One of the great points to strive for in using a database is to retrieve the data you want in the order that you want it. Then simply let your presentation code iterate over the data. If you find yourself repeating a block of code or putting a query inside of a loop, where the only difference are the value being operated on, you can almost always simplify the code.

[andrewgauger]

Yeah I expected someone to balk at the "query inside a loop" method, but I am glad to see an alternative example.