How to Pass Variable into a MySQL Stored Procedure from PHP

[vicky]

I have the following stored procedure:\[code\]proc_main:begindeclare done tinyint unsigned default 0;declare dpth smallint unsigned default 0;create temporary table hier( AGTREFERRER int unsigned, AGTNO int unsigned, depth smallint unsigned default 0)engine = memory;insert into hier values (p_agent_id, p_agent_id, dpth);/* http://dev.mysql.com/doc/refman/5.0/en/temporary-table-problems.html */create temporary table tmp engine=memory select * from hier;while done <> 1 do if exists( select 1 from agents a inner join hier on a.AGTREFERRER = hier.AGTNO and hier.depth = dpth) then insert into hier select a.AGTREFERRER, a.AGTNO, dpth + 1 from agents a inner join tmp on a.AGTREFERRER = tmp.AGTNO and tmp.depth = dpth; set dpth = dpth + 1; truncate table tmp; insert into tmp select * from hier where depth = dpth; else set done = 1; end if;end while;select a.AGTNO, a.AGTLNAME as agent_name, if(a.AGTNO = b.AGTNO, null, b.AGTNO) as AGTREFERRER, if(a.AGTNO = b.AGTNO, null, b.AGTLNAME) as parent_agent_name, hier.depth, a.AGTCOMMLVLfrom hierinner join agents a on hier.AGTNO = a.AGTNOinner join agents b on hier.AGTREFERRER = b.AGTNOorder by -- dont want to sort by depth but by commission instead - i think ?? -- hier.depth, hier.agent_id; a.AGTCOMMLVL desc;drop temporary table if exists hier;drop temporary table if exists tmp;end proc_main\[/code\]While the function does its job well - it only currently allows sorting via AGTCOMMLVL descending order. The stored procedure's purpose is to match a memberID with their parentID and associated COMMLVL. Once paired appropriately,I use the memberID in a second query to return information about that particular member. I would like to be able to sort by any number of filters but have the following problems:[*]I can't seem to find a way to pass a variable into the stored procedure altering its sorting by field.[*]Even if I could - the sort may actually only contain data from the second query (such as first name, last name, etc)[*]Running a sort in the second query does nothing even though syntax is correct - it always falls back to the stored procedure's sort.any ideas?EDITMy php uses mysqli with code:\[code\]$sql = sprintf("call agent_hier2(%d)", $agtid);$resulta = $mysqli->query($sql, MYSQLI_STORE_RESULT) or exit(mysqli_error($mysqli));\[/code\]