如何使用一个存储过程从单独的表中返回mysql列？

问题描述：

I sincerely apologize if repost, I can't find an answer. I am new to MySql and PHP and I am trying to write a stored procedure that returns content from two separate tables. The call is essentially this:

CREATE DEFINER=`root`@`localhost` PROCEDURE `grabPage`(t_user VARCHAR(32), 
                        t_page VARCHAR(128))
BEGIN
    SELECT formC FROM menuTable WHERE user=t_user;
    SELECT formC FROM siteTable WHERE user=t_user AND page=t_page ORDER BY contentID;
END

Is this the wrong way of looking at this? What is the best practice? The reason I want to do this with one stored procedure is so that I only have to make one call to the database in my php. That code looks like this, if I should handle it in the php how would I go about doing that?

$con = mysqli_connect("localhost", "dbusername", "dbpassword", "database");
$query = "CALL grabPage('username', 'pagename')";
if (mysqli_connect_errno()) {
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
$result = mysqli_query($con, $query);
if (!$result) {
    printf("Error: %s
", mysqli_error($con));
    exit();
}
$row = array();
while ($row[] = mysqli_fetch_array($result));
mysqli_close($con);
foreach ($row as $htmlOut)
    echo $htmlOut['formattedContent'];

Thanks for your help.

如果转发，我真诚地道歉，我找不到答案。我是MySql和PHP的新手，我正在尝试编写一个从两个单独的表中返回内容的存储过程。调用基本上是这样的： p>

  CREATE DEFINER =`root` @`localhost` PROCEDURE` grabPage`（t_user VARCHAR（32），
 t_page VARCHAR（128））\  nBEGIN 
 SELECT formC FROM menuTable WHERE user = t_user; 
 SELECT formC FROM siteTable WHERE user = t_user AND page = t_page ORDER BY contentID; 
END 
  code>  pre> 
 
 是 这是看错的方式吗？ 什么是最佳做法？ 我想用一个存储过程执行此操作的原因是我只需要在我的php中对数据库进行一次调用。 该代码看起来像这样，如果我应该在php中处理它我将如何去做呢？ p> 
 
 
  $ con = mysqli_connect（“localhost”，“dbusername”，“  dbpassword“，”“database”）; 
 $ query =“CALL grabPage（'username'，'pagename'）”; 
if（mysqli_connect_errno（））{
 echo“无法连接到MySQL：”。  mysqli_connect_error（）; 
} 
 $ result = mysqli_query（$ con，$ query）; 
if（！$ result）{
 printf（“错误：％s 
”，mysqli_error（$ con））; \  n exit（）; 
} 
 $ row = array（）; 
while（$ row [] = mysqli_fetch_array（$ result））; 
mysqli_close（$ con）; 
foreach（$ row as $ htmlOut）
  echo $ htmlOut ['formattedContent']; 
  code>  pre> 
 
 感谢您的帮助。 p> 
  div>

答

The key to what you're trying to do is twofold:

You need to define multiple OUT or INOUT vars to place your output results into.
You need to retrieve those output vars on the PHP side.

For 1 see the first example here:

CREATE PROCEDURE p (OUT ver_param VARCHAR(25), INOUT incr_param INT)
BEGIN
  # Set value of OUT parameter
  SELECT VERSION() INTO ver_param;
  # Increment value of INOUT parameter
  SET incr_param = incr_param + 1;
END;

You'll note the use of OUT and/or INOUT to indicate output, and INTO to assign SELECT results to the var(s).

For 2 you need to create a session variable on the DB server that you can assign the results to and query to get them back. Take a look at this example in the comments here. It gets pretty wordy so I won't copy and paste, but you should be able to adapt it to your needs. There are also some alternative approaches in the main body of the page.

如何使用一个存储过程从单独的表中返回mysql列？

相关推荐