Call Stored Procedure to Update table with Parameters PHP MySQL

Question

Calling SELECT Statements with parameters is great and makes life coding so tidy. My problem comes to when I want to update data in the database using an UPDATE statement.

I have the Stored Proc with the UPDATE statement included, similar to this below

CREATE DEFINER = 'myuser'@'%'
PROCEDURE sp_upd_planning (
  IN prop_id int(11),
  IN planned_date varchar(15), 
  IN time_slot int(11),
  IN by_who int(11),
  IN cost decimal(15,2),
  IN complete_date varchar(15),
  IN lastupd_user varchar(100))
BEGIN
 UPDATE planning
   SET
     Status = CASE 
                  WHEN CompleteDate IS NOT NULL THEN 4 
                  WHEN PlannedDate IS NULL THEN 2 
                  WHEN PlannedDate IS NULL AND CompleteDate IS NULL THEN 3 
                END
    ,PlannedDate = planned_date
    ,BookingDate = NOW()
    ,TimeSlot = time_slot
    ,ByWho = by_who
    ,Cost = epc_cost
    ,Complete = CASE WHEN CompleteDate IS NOT NULL THEN 1 ELSE 0 END
    ,CompleteDate = complete_date
    ,LastUpdateDate = NOW()
    ,LastUpdateUser = lastupd_user
  WHERE PropID = prop_id;
END

The statement works as should when I run the CALL sp_upd_planning(paramters here); within the database.

I'm using as a submit from a form, I've posted the relevant fields into variables and in my connection I call the Stored Proc again and as before in the database I use the variables to match the parameters needed like this (yes I know it's using mysql_ but I wanted to test quickly so I used this)

mysql_query("CALL sp_upd_planning('$planned', '$timeslot', '$bywho', '$cost', '$completed', '$inputby', $propid)") or die(mysql_error());

When the code executes all looks good and no errors and the main form submits as should with the jquery I set up, but when I check the database nothing is updated.

Why would this be?

Debug with $sql = "call sp_upd_planning('$planned', ..." and by echoing $sql so that you can see if all parameters are set correct. — Ravinder Reddy
– Ravinder Reddy, Commented May 23, 2014 at 14:41
Thanks, I echoed the statement out and compared the output with what should be entered and that was correct, I tried to manually update using the output and this failed also. A question is, which order should the statement follow, should it be the order I declare the parameters or the order in the UPDATE? — Jez
– Jez, Commented May 23, 2014 at 15:12
Sorted, after changing the way I send the parameters to match how I defined them in the Stored Proc it works. Thanks for the help @Ravinder — Jez
– Jez, Commented May 23, 2014 at 15:58

CipherLock · Accepted Answer · 2014-05-23 15:49:59Z

1

It sounds like you aren't executing your statement. In PHP you still have to execute the statement after creation. Also if I remember correctly it is more secure to bind your parameters instead of pass them in the string. Try something like this:

$conn = new mysqli("mysql:host=$host;dbname=$dbname", $username, $password);
// execute the stored procedure
$sql = "CALL sp_upd_planning(:planned, :timeslot, :bywho, :cost, :completed, :inputby, :propid)";
$stmt = $conn->prepare($sql);

$stmt->bindParam('datatypes', $planned, $timeslot, $bywho, $cost, $completed, $inputby, $propid);
$stmt->execute();

Basically by the term datatypes lets assume the planned is a string, timeslot is a date/time, bywho is a string, cost is an int, completed is an int, inputby is a string, and propid is an int then it would say 'sdsiisi'

answered May 23, 2014 at 15:49

CipherLock

245 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jez Over a year ago

Thanks for the reply, I wasn't executing the Stored Proc correctly I was setting the variables as the SQL statement and not how the parameters are set out with the propid first and not last as in the SQL statement

Collectives™ on Stack Overflow

Call Stored Procedure to Update table with Parameters PHP MySQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related