PHP stored procedure insert with MYSqli

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
ModusPonens
Forum Newbie
Posts: 13
Joined: Sat Apr 09, 2011 11:57 am

PHP stored procedure insert with MYSqli

Post 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.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: PHP stored procedure insert with MYSqli

Post by Weirdan »

doesn't

Code: Select all

mysqli_query($mysqli, "call update_title(1,2);"); 
work?
ModusPonens
Forum Newbie
Posts: 13
Joined: Sat Apr 09, 2011 11:57 am

Re: PHP stored procedure insert with MYSqli

Post 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. ;^)
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: PHP stored procedure insert with MYSqli

Post 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));
Post Reply