如何从PHP将变量传递到MySQL存储过程 [英] How to Pass Variable into a MySQL Stored Procedure from PHP

问题描述

我有以下存储过程:

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

虽然该功能很好地完成了工作-但目前仅允许通过AGTCOMMLVL降序进行排序.该存储过程的目的是将一个memberID与它们的parentID和关联的COMMLVL进行匹配.正确配对后，我将在第二个查询中使用memberID返回有关该特定成员的信息.

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:

1. 我似乎找不到一种将变量传递到存储过程中以更改其按字段排序的方法.

1. 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.

有什么想法吗?

我的php使用mysqli代码:

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 //

这篇关于如何从PHP将变量传递到MySQL存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！