how to use mysql stored procedure with insert into table

Question

<?php

 $mysqli = new mysqli('localhost' 'root', "", 'scandi');

// check connection
if ($mysqli->connect_error) {
  trigger_error('Database connection failed: '.$mysqli->   connect_error , E_   USER_ERROR);
   }

$regid = mysqli_real_escape_string($mysqli, $_POST['regid']);
$startdate = mysqli_real_escape_string($mysqli, $_POST['startdate']);
$enddate = mysqli_real_escape_string($mysqli, $_POST['enddate']);

$query="DROP PROCEDURE IF EXISTS filldates ;

CREATE PROCEDURE filldates(dateStart DATE, dateEnd DATE)
BEGIN
 WHILE dateStart <= dateEnd
 DO
  INSERT INTO mydates (did,_date,regid) VALUES ('1',dateStart,'$regid');
 SET dateStart = date_add(dateStart, INTERVAL 7 DAY);
 END WHILE;
   END;
     CALL filldates('$startdate','$enddate')";

       if (mysqli_query($mysqli, $query)) {
    do {
     if ($result = mysqli_store_result($mysqli)) {
       mysqli_free_result ($result);
}
if (mysqli_more_results ($mysqli)) {

}
}while(mysqli_next_result ($mysqli));
}

  mysqli_close($mysqli)
?>

I want to store dates in the table (mydates), the $regid, $startdate, $enddate are provided by the user, the 'did' is auto_increament. the above code without mysqli works well in CLI and stores the dates in the table, but fails when i try to use it in php pages. Any one to help?

You can't put multiple statements into a single `mysqli_query()`. Either do them as separate queries, or use `mysqli_multi_query()`. — Barmar, May 26 '15 at 10:18
_"but fails when i try to use it in php pages"_ Please include the error messages you are getting. — Epodax, May 26 '15 at 10:18
Why don't you store the procedure in the database ahead of time, instead of defining it in the PHP script every time? — Barmar, May 26 '15 at 10:19
With `CREATE PROCEDURE`. A procedure stays in the database until you do `DROP PROCEDURE`. — Barmar, May 26 '15 at 10:53

score 1 · Answer 1 · answered May 26 '15 at 11:56

Instead of writing the procedure in the code, You can also store the procedure into the database and then call that procedure into your code like this

<?php 
  //connect to database
  $connection = mysqli_connect("hostname", "user", "password", "db", "port");

  //run the store proc
  $result = mysqli_query($connection, 
 "CALL ProcedureName()") or die("Query fail: " . mysqli_error());

  //loop the result set
  while ($row = mysqli_fetch_array($result)){   
  echo $row[0] . " - " . + $row[1]; 
  }
?>

Thanks guys,now everything is executing as planned, i just replaced the mysqli_query () with mysqli_multi_query() will try calling the procedure, thanks again. — Sylus, May 27 '15 at 10:03

how to use mysql stored procedure with insert into table

1 Answers1