Stored Procedures / Routines + general US startup questions

[mudmin]

Can you not just return all the results and do a foreach through them?

Apologies, I'm not sure where you are referring to returning all results? Did you mean from the database or in the result set with php?

I did forget to mention a few details about my use case. In my SP, I'm adding SQL generated data to two tables. x amount of rows for table A and 1 row for table B based on table C data. These are then retrieved, with two separate "SELECT"S, and returned to php for the web apps JS. A join on this result set wouldn't work as the table columns aren't similar.

I can't run pdo's nextRowset() on $result->_query as it's private so had to add the above code as a workaround. If I var_dump the $result it only shows the first result set, although it does show that there are 2 results with `private '_queryCount' => int 2`.

The only thing I can think of, to avoid breaking any upgrades to US, is to add the above function in an extension class of DB and only include/call it if I need to access a secondary+ result set, prior to calling the SP.

This is my caller FN:

Code:

private function my_fn($amount){
  global $user, $db;

  $result = $db->query("CALL MY_PROC(?,?,?)",array($user->data()->id,$amount,NULL));
  var_dump($result);

  $result_A = $result->results();
  $result_B = $result->nextRowset();

  var_dump($result_A, $result_B);

  exit();
}

We could DEFINITELY be speaking different languages and I'm going to have Brandin take a look at this.  

I see no problem at all with extending the db class. I wouldn't advise it for most people, but we have really only done 2 updates to the DB class in the last 3 years.  One to add error reporting and one that allows you to connect to multiple dbs.

If extending the class works, I would definitely say go for it because it won't cause any issues. If that doesn't work, modding it isn't the end of the world.  Once you mod it, you would just have to keep an eye out for changes to the class.  I usually would do something like copy the modded db class to DB.mine just in case an update rolls over it, you can do a quick diff.


I'll talk to Brandin and see if there's anything I'm missing.