3 {{ upvoteCount | shortNum }}
3 {{ upvoteCount | shortNum }}

Mysqli call to mysql db routine not working

by alexwaston
3 replies
Hi, can someone help!

I'm new to php and mysql and making good progress. But I am stumped with a call to a mysql stored proc/routine.

It runs prefectly well in mysql reutrning just the number of rows in a table.

But I cannot get it to work in PHP. I have even stripped out all the IN to avoid any confusion. Here my routine SQL Code

PHP Code:
DROP PROCEDURE `TestUserWeeklyAppts`;
CREATE DEFINER=`xxxx`@`%` PROCEDURE `TestUserWeeklyAppts`(OUT `rows` INT) NOT DETERMINISTIC NO SQL SQL SECURITY DEFINER BEGIN
DECLARE StartDate DATE;
DECLARE EndDate DATE;
DECLARE StaffID INT;
SET StaffID = 4;
SET StartDate = '2016-09-11';
SET EndDate = DATE_ADD(StartDate ,INTERVAL 7 DAY);
SELECT COUNT(ID) INTO rows
FROM TblAppointments
WHERE StaffID = StaffID AND ApptDate BETWEEN StartDate AND EndDate;
END 
THis just returns the number 22 everytine!
Here my PHP code

PHP Code:
$mysqli = new mysqli($servername, $dbusername, $dbpassword);
mysqli_select_db($mysqli, $database);
if ($mysqli->connect_error) 
{
    die("Connection failed: " . $conn->connect_error);
} 
$stmt = $mysqli->prepare('CALL TestUserWeeklyAppts(@rows)');
$stmt->execute();
$select = $mysqli->query('SELECT @rows');
$result = $select->fetch_array();
print_r($result['Rows']);
if (!select) {
 echo "false";
 }
 else {
 echo "True"."<br>";
// output data of each row
while($row =  mysql_fetch_array($result)){
     print_r($row);
        }
 echo "Number of Select rows: ". $select->num_rows ."<br>";
 echo "Number of Result Rows: ". $result->num_rows ."<br>";
 }
 $stmt->close(); 
So if anyone can explain where Im going wrong it woulbe be appreciated...

Relax: watch latest fantasy movies | watch latest romance movies | watch bad moms movies. Good fun!
#call #mysql #mysqli #routine
  • Profile picture of the author oompaloompa
    ncalabroso any thoughts?
    {{ DiscussionBoard.errors[10867024].message }}
  • Profile picture of the author sunsetcoder
    Have you tried it using echo $row[0] in place of your code print_r($row); i think you should check it using it
    {{ DiscussionBoard.errors[10867831].message }}
  • Profile picture of the author David Beroff
    I keep looking at that WHERE StaffID = StaffID clause and wondering if that's the issue. I'm guessing that StaffID is the name of a column, and you're also using the same identifier as a variable to pass an intended value. That might also explain why it worked in isolation, since you likely just used the 4 directly then.

    You're probably using the procedure as a step for learning, in which case, that's great. But if this is part of a real-world solution, I'd gently suggest that you may be making things too complicated; you could simply invoke the one SELECT directly from the PHP.

    Also, please consider PDO; it's a bit easier and more standard moving forward.
    Signature
    Put MY voice on YOUR video: AwesomeAmericanAudio.com
    {{ DiscussionBoard.errors[10867882].message }}

Trending Topics