Created
Updated
Viewed
4k times

I am trying to put an SQL query inside a function and not sure if it's possible to do this in PHP. I know I can do it in ColdFusion.

Basically, I want to call a recursive query that is selectable via some id.

function getmenu($menu_id){
    /* get the top most level menu */
    mysql_select_db($database_eggbox, $eggbox);
    $query_top_menu = "select * from menu where menu_parent_menu_id = $menu_id";
    $top_menu = mysql_query($query_top_menu, $eggbox) or die(mysql_error());
    $row_top_menu = mysql_fetch_assoc($top_menu);
    $totalRows_top_menu = mysql_num_rows($top_menu);

    /* loop and echo */
}

getmenu(0);

It's telling me that I can't do this inside a function. Here is the error I get:

Warning: mysql_select_db(): supplied argument is not a valid MySQL-Link resource in Library

How would I be able to achieve this? Maybe in a class?

add a comment
0

5 Replies

  • Votes
  • Oldest
  • Latest
JO
144 4
Replied
Updated

The scope is the issue here.

Pass the relevant variables as arguments in the function.

function getmenu($menu_id, $database_eggbox, $eggbox){}

Or alternatively (not recommended), is to use the "global" keyword to make the $database_eggbox and other relevant variables visible inside the function.

function getmenu($menu_id)
{
  global $database_eggbox, $eggbox;
  // ...
}
add a comment
0
Replied

Of course now.... Got it working...

	mysql_select_db($database_eggbox, $eggbox);

	function getmenu($menu_id){

		/* get the top most level menu */
		global $database_eggbox, $eggbox;
		$query_top_menu = "select * from menu where menu_parent_menu_id = $menu_id";
		$top_menu = mysql_query($query_top_menu, $eggbox) or die(mysql_error());
		$row_top_menu = mysql_fetch_assoc($top_menu);
		$totalRows_top_menu = mysql_num_rows($top_menu);

		do { 
		   echo $row_top_menu['menu_text'];
		} while ($row_top_menu = mysql_fetch_assoc($top_menu));

	}

Just need a bit more code to get it working recursively now ;o)

Thanks for your help

add a comment
0
JO
144 4
Replied

Now that the question is answered, I should probably mention that using a query recursively isn't usually a good thing to do.

It looks like you're building a hierarchy, which can be done with one query and two loops, if you use an item_id for your array keys and references to turn a single-dimensional array into a virtual multi-dimensional array.

This is taken from another site so it's not going to match up with your code, but hopefully the concept here is easy to see. 🙂

$category_lineage = array();

$result = $db->query('SELECT category_id, parent_id, label FROM ' . CATEGORIES_TABLE, MYSQLI_USE_RESULT);
while($row = $result->fetch_object())
{
	$row->sub_categories = array();
	$category_lineage[$row->category_id] = $row;
}
$result->close();

foreach($category_lineage as $key => $val)
{
	if($val->parent_id)
	{
		$category_lineage[$val->parent_id]->sub_categories[$key] =& $category_lineage[$key];
	}
}
add a comment
0
Replied
Updated

Hi There when I meant recursive I meant like this.... ;o)

top level
sub level
sub sub level
sub level
top level

This works although I do not know if it is the most efficient way of doing it.

function getmenu($menu_level){

		/* get the top most level menu */
		global $database_eggbox, $eggbox;
		$query_top_menu = "select * from menu where menu_parent_menu_id = 0 order by menu_display_order";
		$top_menu = mysql_query($query_top_menu, $eggbox) or die(mysql_error());
		$row_top_menu = mysql_fetch_assoc($top_menu);
		$totalRows_top_menu = mysql_num_rows($top_menu);

		if ($menu_level <> 0) {

			echo "<ul>";

			do { 

			   echo "<li><a href='index.php?' title='opens in same window' target='_self' >" . $row_top_menu['menu_text'] . "</a>";
				$menuid = $row_top_menu['menu_id'];
				$query_has_children = "select * from menu where menu_parent_menu_id = $menuid order by menu_display_order";
				$has_children = mysql_query($query_has_children, $eggbox) or die(mysql_error());
				$row_has_children = mysql_fetch_assoc($has_children);
				$totalRows_has_children = mysql_num_rows($has_children);
				if($totalRows_has_children > 0){

					getchildren($menuid,0);	   

			   	} else {
					echo "</li>";
				}
			} while ($row_top_menu = mysql_fetch_assoc($top_menu));

			echo "</ul>";

		} elseif ($menu_level == 0) {

			echo "<ul>";

			do { 

			   echo "<li><a href='index.php?' title='opens in same window' target='_self' >" . $row_top_menu['menu_text'] . "</a></li>";

			} while ($row_top_menu = mysql_fetch_assoc($top_menu));

			echo "</ul>";
		}

	} // End Function

	function getchildren ($menuid,$count) {

		/* get the next level menu */
		global $database_eggbox, $eggbox;
		$query_child_menu = "select * from menu where menu_parent_menu_id = $menuid order by menu_display_order";
		$child_menu = mysql_query($query_child_menu, $eggbox) or die(mysql_error());
		$row_child_menu = mysql_fetch_assoc($child_menu);
		$totalRows_child_menu = mysql_num_rows($child_menu);   	

		do {

			if($count == 0) {
				echo "<ul>";
			}

			echo "<li><a href='index.php?' title='opens in same window' target='_self' >" . $row_child_menu['menu_text'] . "</a>";
			$menuidd = $row_child_menu['menu_id'];
			$query_has_childrens = "select * from menu where menu_parent_menu_id = $menuidd order by menu_display_order";
			$has_childrens = mysql_query($query_has_childrens, $eggbox) or die(mysql_error());
			$row_has_childrens = mysql_fetch_assoc($has_childrens);
			$totalRows_has_childrens = mysql_num_rows($has_childrens);

			if($totalRows_has_childrens > 0){

					getchildren($menuidd,0);

			} else {
				"</li>";
			}			

			if($count+1 == $totalRows_child_menu) {
				echo "</ul>";
			}

			$count += 1;		
		} while ($row_child_menu = mysql_fetch_assoc($child_menu));		

	} // end function
add a comment
0
Replied
Updated

Here is everything finally. Fully recursive collapsable menu system. If you think I can improve on it then please let me know as I still think there is a better way to do it. My PHP skills are still pretty ropey as I come from a CF background... but ho hum here it is...

mysql_select_db($database_eggbox, $eggbox);

/* get the home page at start*/
$query_default_url = "select * from menu where menu_text = 'Home' order by menu_display_order";
$default_url = mysql_query($query_default_url, $eggbox) or die(mysql_error());
$row_default_url = mysql_fetch_assoc($default_url);
$totalRows_default_url = mysql_num_rows($default_url);

if (isset($_GET['menu_id'])) {
	$urlmenu_id = $_GET['menu_id'];
} else {
	$urlmenu_id = $row_default_url['menu_id'];
}

///////////////////////////////////////////////////////////////
/////////////////////* functions */////////////////////////////
///////////////////////////////////////////////////////////////

if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  $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;
}
}

/* get breadcrumb function */
function getbreadcrumb($arraycount,$parentmenuid){

	/* get the current level menu */
	global $database_eggbox, $eggbox, $urlmenu_id, $array_menu;
	$query_last_breadcrumb = "select * from menu where menu_id = $parentmenuid order by menu_display_order";
	$last_breadcrumb = mysql_query($query_last_breadcrumb, $eggbox) or die(mysql_error());
	$row_last_breadcrumb = mysql_fetch_assoc($last_breadcrumb);
	$totalRows_last_breadcrumb = mysql_num_rows($last_breadcrumb);

	$array_menu[$arraycount][0] = $row_last_breadcrumb['menu_id'];
	$array_menu[$arraycount][1] = $row_last_breadcrumb['page_id'];
	$array_menu[$arraycount][2] = $row_last_breadcrumb['menu_text'];
	$array_menu[$arraycount][3] = $row_last_breadcrumb['menu_parent_menu_id'];

	if($array_menu[$arraycount][0] != "") {
		getbreadcrumb($arraycount+1,$array_menu[$arraycount][3]);
	} 

	if($arraycount == count){
		echo $array_menu[$arraycount][2];
	} else {
		$menuid = $array_menu[$arraycount][0];
		echo "<li><a href='index.php?menu_id=$menuid' title='opens in same window' target='_self' >" . $array_menu[$arraycount][2] . "</a> : </li>";
	}
	mysql_free_result($last_breadcrumb);
} // end function

/* get menu functions */
function getmenu($menu_level,$menuid){

	/* get the top most level menu */
	global $database_eggbox, $eggbox, $urlmenu_id, $array_menu;
	$query_top_menu = "select * from menu where menu_parent_menu_id = $menuid and menu_display = 1 order by menu_display_order";
	$top_menu = mysql_query($query_top_menu, $eggbox) or die(mysql_error());
	$row_top_menu = mysql_fetch_assoc($top_menu);
	$totalRows_top_menu = mysql_num_rows($top_menu);

	if ($menu_level == 0) { // Top menu for nav bar only

		echo "<ul>";

		do { 
		   	$menuid = $row_top_menu['menu_id'];
			if($urlmenu_id == $menuid) { 
				$selectedclass = "class='selected'";
			} else {
				$selectedclass = "";
			}

		   	if($row_top_menu['menu_alt_location'] != "") {
				// Alternative location like google
		   		if(substr($row_top_menu['menu_alt_location'],0,4) == "http") { $menuurl = $row_top_menu['menu_alt_location']; } else { $menuurl = "http://" . $row_top_menu['menu_alt_location'];}
		   		$title = "opens in new window";
				$target = "_blank";				
		   	} else {
		   		// site menu 
				$menuurl = "index.php?menu_id=$menuid";
		   		$title = "opens in same window";
				$target = "_self";
			} // End if

			echo "<li><a href='$menuurl' title='$target' target='$target' $selectedclass >" . $row_top_menu['menu_text'] . "</a></li>";

		} while ($row_top_menu = mysql_fetch_assoc($top_menu));

		echo "</ul>";

	} elseif ($menu_level == 1) {

			if($totalRows_top_menu > 0) { // full side menu and you don't want a top menu

				echo "<ul>";
				$count = 0;	

				do { 
				   $menuid = $row_top_menu['menu_id'];
				   if($urlmenu_id == $menuid) { 
						$selectedclass = "class='selected'";
					} else {
						$selectedclass = "";
					}

				   if($row_top_menu['menu_alt_location'] != "") {
						// Alternative location like google
						if(substr($row_top_menu['menu_alt_location'],0,4) == "http") { $menuurl = $row_top_menu['menu_alt_location']; } else { $menuurl = "http://" . $row_top_menu['menu_alt_location'];}
						$title = "opens in new window";
						$target = "_blank";				
					} else {
						// Site menu
						$menuurl = "index.php?menu_id=$menuid";
						$title = "opens in same window";
						$target = "_self";
					} // End if

				   echo "<li><a href='$menuurl' title='$title' target='$target' >" . $row_top_menu['menu_text'] . "</a>";

					$query_has_children = "select * from menu where menu_parent_menu_id = $menuid and menu_display = 1 order by menu_display_order";
					$has_children = mysql_query($query_has_children, $eggbox) or die(mysql_error());
					$row_has_children = mysql_fetch_assoc($has_children);
					$totalRows_has_children = mysql_num_rows($has_children);

					if($totalRows_has_children > 0 && $array_menu[sizeof($array_menu)-2][0] == $menuid){
						getchildren($menuid,0,3);
						echo "</li>"; 	   
					} else {
						echo "</li>";
					}

					$count += 1;		
				} while ($row_top_menu = mysql_fetch_assoc($top_menu));

				echo "</ul>";

			} // End if

			mysql_free_result($has_children);
	} elseif ($menu_level == 2) { // Second level menu based on top level menu

		if($totalRows_top_menu > 0) {

				$count = 0;	

				do { 
				   	$menuid = $row_top_menu['menu_id'];
				   	$query_has_children = "select * from menu where menu_parent_menu_id = $menuid and menu_display = 1 order by menu_display_order";
					$has_children = mysql_query($query_has_children, $eggbox) or die(mysql_error());
					$row_has_children = mysql_fetch_assoc($has_children);
					$totalRows_has_children = mysql_num_rows($has_children);

					if($totalRows_has_children > 0 && $array_menu[sizeof($array_menu)-2][0] == $menuid){
						getchildren($menuid,0,3);

					} 

					$count += 1;		
				} while ($row_top_menu = mysql_fetch_assoc($top_menu));

		} // End if
		mysql_free_result($has_children);
	} // End if function for menu levels		

	mysql_free_result($top_menu);
} // End Function

function getchildren ($menuid,$count,$count_level) {

	/* get the next level menu */
	global $database_eggbox, $eggbox, $urlmenu_id, $array_menu;
	$query_child_menu = "select * from menu where menu_parent_menu_id = $menuid and menu_display = 1 order by menu_display_order";
	$child_menu = mysql_query($query_child_menu, $eggbox) or die(mysql_error());
	$row_child_menu = mysql_fetch_assoc($child_menu);
	$totalRows_child_menu = mysql_num_rows($child_menu);   	

	do {

		if($count == 0) {
			echo "<ul>";
		}

		$menuidd = $row_child_menu['menu_id'];
		if($urlmenu_id == $menuidd) { 
			$selectedclass = "class='selected'";
		} else {
			$selectedclass = "";
		}

		if($row_child_menu['menu_alt_location'] != "") {
			// Alternative location like google
			if(substr($row_child_menu['menu_alt_location'],0,4) == "http") { $menuurl = $row_child_menu['menu_alt_location']; } else { $menuurl = "http://" . $row_child_menu['menu_alt_location'];}
			$title = "opens in new window";
			$target = "_blank";				
		} else {
			// site menu
			$menuurl = "index.php?menu_id=$menuidd";
			$title = "opens in same window";
			$target = "_self";
		} // End if

		echo "<li><a href='$menuurl' title='$title' target='$target' $selectedclass >" . $row_child_menu['menu_text'] . "</a>";

		$query_has_childrens = "select * from menu where menu_parent_menu_id = $menuidd and menu_display = 1 order by menu_display_order";
		$has_childrens = mysql_query($query_has_childrens, $eggbox) or die(mysql_error());
		$row_has_childrens = mysql_fetch_assoc($has_childrens);
		$totalRows_has_childrens = mysql_num_rows($has_childrens);

		if($totalRows_has_childrens > 0 && $array_menu[sizeof($array_menu)-$count_level][0] == $menuidd){

				getchildren($menuidd,0,$count_level+1);
				echo "</li>";
		} else {
				echo "</li>";
		}

		if($count+1 == $totalRows_child_menu) {
			echo "</ul>";
		}

		$count += 1;

	} while ($row_child_menu = mysql_fetch_assoc($child_menu));
	mysql_free_result($has_childrens);
	mysql_free_result($child_menu);
} // end function

/* get content */
$query_page_info = "select menu_id, pages.* from pages inner join menu on pages.page_id = menu.page_id where menu.menu_id = $urlmenu_id";
$page_info = mysql_query($query_page_info, $eggbox) or die(mysql_error());
$row_page_info = mysql_fetch_assoc($page_info);
$totalRows_page_info = mysql_num_rows($page_info);

function getcontent($content){
	global $row_page_info;
	$content = $row_page_info[$content];
	return $content;
} // End function

function getcolumns() {
	global $database_eggbox, $eggbox, $row_page_info, $urlmenu_id;

	$query_top_top_menu = "select * from menu where menu_parent_menu_id = $urlmenu_id and menu_display = 1 order by menu_display_order";
	$top_top_menu = mysql_query($query_top_top_menu, $eggbox) or die(mysql_error());
	$row_top_top_menu = mysql_fetch_assoc($top_top_menu);
	$totalRows_top_top_menu = mysql_num_rows($top_top_menu);

	if($row_page_info['page_lhs_content'] == "" && $row_page_info['page_rhs_content'] == "" && $totalRows_top_top_menu == 0) {
		$collhsrhs = "-noLHSRHS";
	} elseif($row_page_info['page_rhs_content'] == "" ) {
		$collhsrhs = "-noRHS";
	} elseif ($row_page_info['page_lhs_content'] == "" || $row_top_top_menu == 0) {
		$collhsrhs = "-noLHS";
	} else {
		$collhsrhs = "";
	}

	return $collhsrhs;
}

mysql_free_result($page_info);
add a comment
0