Php Mysql Stored Procedure IN OUT -

case: stored procedure looking like:

drop procedure if exists `xoffercommon`.`getcdpc`;<br> delimiter $$<br> create procedure `xoffercommon`.`getcdpc` (in in_city_id int, out out_country_id int, out out_district_id int, out out_provence_id int, out out_city_id int)<br> begin<br> declare city_id int default in_city_id;<br> select t.id, d.id, p.id, c.id out_country_id, out_district_id, out_provence_id, out_city_id ((tblcity c inner join tblprovence p on c.tblprovence_id = p.id) inner join tbldistrict d on p.tbldistrict_id = d.id) inner join tblcountry t on -d.tblcountry_id = t.id c.id = city_id;<br> end$$<br> delimiter ;<br><br> <br>--------------------------------------------------- input: int referring in_city_id<br> output: 4 x int country district provence , city id's<br> ---------------------------------------------------<br> 

php: want tot call procedure , store 4 returned objects 4variables

if ($res = $mysqli->query( 'call getcdpc(1321,@co,@di,@pr,@ci);select @co,@di,@pr,@ci'))<br> {<br>     while($row = $res->fetch_object())<br>     {<br>         print($row);<br>     }<br>     $res->close();<br> }<br> else<br> {<br>     print "<br> no result given <br>";<br> }<br> <br> result:<br> -------<br> 

i 0 rows back

<br> <br> more info:<br> ----------<br> in mysql sql results returned when do:<br> ->call xoffercommon.getcdpc(1321,@co,@di,@pr,@ci);<br> ->select @co,@di,@pr,@ci;<br> => @co @di @pr @ci<br>    1   2   3   4<br> <br> question:<br> ---------<br> 

do have run 2 queries, call procedure , select on result?
there way run in 1 line?
... please i've found solution problem, tried return in procedure, allowed in functions. 2weeks gone... learned lot reading/trial , error ;-), nothing cracked case....

not sure why want passing out 4 variables when return resultset. avoid having change sproc interface everytime want extend data output.

mysql

drop procedure if exists xoffercommon.getcdpc;  delimiter #  create procedure xoffercommon.getcdpc ( in p_city_id int unsigned ) begin     select      t.id country_id,       d.id district_id,       p.id provence_id,       c.id city_id           tblcity c      inner join tblprovence p on c.tblprovence_id = p.id       inner join tbldistrict d on p.tbldistrict_id = d.id       inner join tblcountry t on d.tblcountry_id = t.id           c.id = p_city_id; end#  delimiter ; 

php

<?php  ob_start();   try {     $db = new mysqli("localhost", "foo_dbo", "pass", "foo_db", 3306);      if ($db->connect_errno)          throw new exception(sprintf("could not connect: %s", $db->connect_error));      $sqlcmd = sprintf("call getcdpc(%d)", 1);      $result = $db->query($sqlcmd);      if(!$result) throw new exception(sprintf("invalid query : %s", $sqlcmd));      if($result->num_rows <= 0){         echo "no records found !";     }     else{         $row = $result->fetch_assoc();         echo sprintf("country_id = %d district_id = %d, provence_id = %d, city_id = %d",             $row["country_id"],$row["district_id"],$row["provence_id"],$row["city_id"]);     }     $db->next_result();      $result->close(); } catch(exception $ex) {     ob_clean();      echo sprintf("zomg borked - %s", $ex->getmessage()); }  if(!$db->connect_errno) $db->close(); ob_end_flush(); ?> 

it worth while cleaning schema have more appropriate integer datatypes keys (not signed integer 4 bytes) , avoid mix of fieldnames seem have chosen same thing i.e. id, id, city_id, city_id etc. avoid unnecessary aliasing !

http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html

mysql revised schema

• removed tbl tablename prefix
• chosen more appropriate integer datatypes keys
• renamed key fields consistent i.e city_id

tables

drop table if exists country; create table country ( country_id tinyint unsigned not null auto_increment primary key -- 0 255 countries; ) engine=innodb;  drop table if exists district; create table district ( district_id smallint unsigned not null auto_increment primary key, -- 0 65535 districts country_id tinyint unsigned not null ) engine=innodb;  drop table if exists provence; create table provence ( provence_id smallint unsigned not null auto_increment primary key, - 0 65535 provences district_id smallint unsigned not null ) engine=innodb;  drop table if exists city; create table city ( city_id mediumint unsigned not null auto_increment primary key, - 0 16777215 cities provence_id smallint unsigned not null ) engine=innodb; 

stored procedure

drop procedure if exists getcdpc;  delimiter #  create procedure getcdpc ( in p_city_id mediumint unsigned ) begin     select      t.country_id,       d.district_id,       p.provence_id,       c.city_id           city c      inner join provence p on c.provence_id = p.provence_id      inner join district d on p.district_id = d.district_id      inner join country t on d.country_id = t.country_id          c.city_id = p_city_id; end#  delimiter ; 

hope helps :)