我无法让我的存储过程或视图从数据库中检索内容。我不知道我的存储过程或视图是否工作,但我知道php不能处理这些工作。请,谢谢!
下面是我的视图和存储过程。CREATE VIEW viewActRole AS
SELECT `tblMovie`.`MovieName`, `tblActor`.`ActFirstName`, `tblActor`.`ActLastName`, `tblRole`.`Role`
FROM `tblRole`, `tblActor`, `tblMovie`
WHERE `tblRole`.`ActID` = `tblActor`.`ActID` AND `tblRole`.`MovieID`=`tblMovie`.`MovieID`;
DELIMITER $$
CREATE PROCEDURE `lookUpMovie`(IN `actorNameVAR` INT)
BEGIN
SELECT * FROM `viewActRole` WHERE `viewActRole`.`MovieID` = `actorNameVAR`;
END$$
DELIMITER ;
下面是我的php代码。在添加存储过程之前,我知道一切正常。
<?php
$mysqli = new mysqli("BLAH", "BLAH", "BLAH", "BLAH");
$dd = $_POST['displaydropdown'];
// used fixed-width font
echo "<pre>'n";
$sp = "CALL lookUpMovie(".$dd.")";
// Get person likes for
echo "$sp";
$r1 = $mysqli->query($sp);
$r2 = mysql_query("SELECT C.MovieName, B.ActFirstName, B.ActLastName, A.Role
FROM tblRole A, tblActor B, tblMovie C WHERE A.ActID = B.ActID AND A.MovieID=C.MovieID AND A.MovieID = '$dd'");
$result2 = mysql_fetch_array($r2);
echo "Albums of Artist: $result2[0]'n";
$fmt = "%-20s %-20s %-20s %-20s 'n";
printf($fmt, "Movie Name", "Actor First Name", "Actor Last Name", "Role");
// loop over courses, printing each one
while ($result = mysql_fetch_array($r1))
{
printf($fmt,$result[0], $result[1], $result[2], $result[3]);
}
// free result set
mysql_free_result($r1);
echo "</pre>'n";
?>
看起来你是混合和匹配mysqli面向对象的调用与已弃用的mysql_*函数。我不确定你能做到。
你正在做:
$r1 = $mysqli->query($sp)
然后传递给
mysql_fetch_assoc($r1);
循环你的结果,你需要做:
while ($result = $r1->fetch_array())
{
printf($fmt,$result[0], $result[1], $result[2], $result[3]);
}