Page 1 of 1

PHP stored procedure insert with MYSqli

Posted: Sat Apr 09, 2011 12:07 pm
by ModusPonens
I don't have any trouble using the mysqli class to invoke a SP that executes a select query, but I cannot figure out how to invoke a SP that executes an insert/update.

The following works like a charm for a select SP to populate a DDL

Code: Select all

if (mysqli_connect_errno()){
       printf("Connect failed: %s\n", mysqli_connect_error());
       exit();
    } else {
      $result = mysqli_query($mysqli, "call sel_title;");
      if (!$result){
         echo "Query failed";
      } else {
        while ($arResult = mysqli_fetch_array($result, MYSQLI_ASSOC)){
              $title = $arResult['title'];
              echo $title."<BR>";
              }
        }
I've done several different searches on this board and just can't find what I'm looking for.

I need to insert data into about 5 or 6 different tables all at once, and I'm using commit/rollback within the SP itself so I don't have to deal with it in the PHP code itself.

Re: PHP stored procedure insert with MYSqli

Posted: Sat Apr 09, 2011 6:05 pm
by Weirdan
doesn't

Code: Select all

mysqli_query($mysqli, "call update_title(1,2);"); 
work?

Re: PHP stored procedure insert with MYSqli

Posted: Sun Apr 10, 2011 9:39 am
by ModusPonens
Yessir, that works.

There's a method in ADODB where you just put the arguments to be passed to the SP in parens that looks something like this:

Code: Select all

commandobj.SPmethod("SPname", var1, var2, var3....);
I got this bit to work:

Code: Select all

$result = mysqli_query($mysqli, "call ins_sal('".$var."');");
      if (!$result)
      {
         echo "Insert failed";
      }
      else
      {
        echo "insert succeeded";
      }
but the limitation is obvious because I'm going to be passing about 30 arguments to my stored procedure.

If I seem ungrateful, I apologize because that is still better than building a string literal for an insert.

I've only been using PHP for a week or so, so if I seem ignorant of it, it's because I am.

First program I ever wrote was in ForTran 77, and have used PL/1 and COBOL in the past as well. And I was an MCSD in VB6/MS Sql Server 7/2000, though that cert is retired now.

That ought to tell you how old I am. ;^)

Re: PHP stored procedure insert with MYSqli

Posted: Sun Apr 10, 2011 4:12 pm
by Weirdan
ModusPonens wrote:the limitation is obvious because I'm going to be passing about 30 arguments to my stored procedure.
Writing that by hand, entering and leaving string context for every argument would surely be daunting and repeating task - however isn't that something that programs excels at? :)

Code: Select all

    <?php

    function callSP($conn, $name) {
        $ret = 'call ' . $name;
        $args = func_get_args();
        array_shift($args); array_shift($args);

        if ($args) {
            foreach ($args as $i => $arg) {
                $args[$i] = '"' . mysqli_real_escape_string($conn, $arg) . '"';
            }
            $args = join(',', $args);
            $ret .= '(' . $args . ')';
        }

        $ret .= ';';
        return $ret;
    }


    $result = mysqli_query($conn, callSP($conn, "ins_qwe",
                                         "asd", 123, 0x22));