PHP变量传人MySQL,如何从PHP将变量传递到MySQL存储过程

I have the following stored procedure:

proc_main:begin

declare 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.AGTCOMMLVL

from

hier

inner join agents a on hier.AGTNO = a.AGTNO

inner join agents b on hier.AGTREFERRER = b.AGTNO

order 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

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?

EDIT

My php uses mysqli with code:

$sql = sprintf("call agent_hier2(%d)", $agtid);

$resulta = $mysqli->query($sql, MYSQLI_STORE_RESULT) or exit(mysqli_error($mysqli));

解决方案

If you want to sort by input parameter of the stored procedure, you need to use Prepared staments

For example,

DELIMITER //

CREATE PROCEDURE `test1`(IN field_name VARCHAR(40) )

BEGIN

SET @qr = CONCAT ("SELECT * FROM table_name ORDER BY ", field_name);

PREPARE stmt FROM @qr;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

END //