· 7 years ago · Sep 06, 2018, 08:36 PM
1Php Mysql Stored Procedure IN OUT
2DROP PROCEDURE IF EXISTS `XofferCommon`.`getCdpc`;<br>
3DELIMITER $$<br>
4CREATE 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>
5BEGIN<br>
6DECLARE city_ID INT DEFAULT in_city_ID;<br>
7SELECT t.Id, d.Id, p.Id, c.Id INTO out_country_id, out_district_id, out_provence_id, out_city_id FROM ((tblCity AS c INNER JOIN tblProvence AS p ON c.tblProvence_Id = p.ID) INNER JOIN tblDistrict AS d ON p.tblDistrict_Id = d.ID) INNER JOIN tblCountry AS t ON -d.tblCountry_Id = t.ID WHERE c.id = city_ID;<br>
8END$$<br>
9DELIMITER ;<br><br>
10<br>---------------------------------------------------
11Input: int referring to in_city_id<br>
12Output: 4 x int country district provence and city id's<br>
13---------------------------------------------------<br>
14
15if ($res = $mysqli->query( 'CALL getCdpc(1321,@co,@di,@pr,@ci);SELECT @co,@di,@pr,@ci'))<br>
16{<br>
17 while($row = $res->fetch_object())<br>
18 {<br>
19 print($row);<br>
20 }<br>
21 $res->close();<br>
22}<br>
23else<br>
24{<br>
25 print "<br> no result given <br>";<br>
26}<br>
27<br>
28Result:<br>
29-------<br>
30
31<br>
32<br>
33More info:<br>
34----------<br>
35in mysql sql the results get returned when I do:<br>
36->call xoffercommon.getCdpc(1321,@co,@di,@pr,@ci);<br>
37->select @co,@di,@pr,@ci;<br>
38=> @co @di @pr @ci<br>
39 1 2 3 4<br>
40<br>
41Question:<br>
42---------<br>
43
44drop procedure if exists XofferCommon.getCdpc;
45
46delimiter #
47
48create procedure XofferCommon.getCdpc
49(
50in p_city_id int unsigned
51)
52begin
53 select
54 t.Id as country_id,
55 d.Id as district_id,
56 p.Id as provence_id,
57 c.Id as city_id
58 from
59 tblCity c
60 inner join tblProvence p on c.tblProvence_Id = p.Id
61 inner join tblDistrict d on p.tblDistrict_Id = d.Id
62 inner join tblCountry t on d.tblCountry_Id = t.Id
63 where
64 c.Id = p_city_id;
65end#
66
67delimiter ;
68
69<?php
70
71ob_start();
72
73try
74{
75 $db = new mysqli("localhost", "foo_dbo", "pass", "foo_db", 3306);
76
77 if ($db->connect_errno)
78 throw new exception(sprintf("Could not connect: %s", $db->connect_error));
79
80 $sqlCmd = sprintf("call getCdpc(%d)", 1);
81
82 $result = $db->query($sqlCmd);
83
84 if(!$result) throw new exception(sprintf("Invalid query : %s", $sqlCmd));
85
86 if($result->num_rows <= 0){
87 echo "no records found !";
88 }
89 else{
90 $row = $result->fetch_assoc();
91 echo sprintf("country_id = %d district_id = %d, provence_id = %d, city_id = %d",
92 $row["country_id"],$row["district_id"],$row["provence_id"],$row["city_id"]);
93 }
94 $db->next_result();
95 $result->close();
96}
97catch(exception $ex)
98{
99 ob_clean();
100 echo sprintf("zomg borked - %s", $ex->getMessage());
101}
102
103if(!$db->connect_errno) $db->close();
104ob_end_flush();
105?>
106
107drop table if exists country;
108create table country
109(
110country_id tinyint unsigned not null auto_increment primary key -- 0 to 255 countries;
111)
112engine=innodb;
113
114drop table if exists district;
115create table district
116(
117district_id smallint unsigned not null auto_increment primary key, -- 0 to 65535 districts
118country_id tinyint unsigned not null
119)
120engine=innodb;
121
122drop table if exists provence;
123create table provence
124(
125provence_id smallint unsigned not null auto_increment primary key, - 0 to 65535 provences
126district_id smallint unsigned not null
127)
128engine=innodb;
129
130drop table if exists city;
131create table city
132(
133city_id mediumint unsigned not null auto_increment primary key, - 0 to 16777215 cities
134provence_id smallint unsigned not null
135)
136engine=innodb;
137
138drop procedure if exists getCdpc;
139
140delimiter #
141
142create procedure getCdpc
143(
144in p_city_id mediumint unsigned
145)
146begin
147 select
148 t.country_id,
149 d.district_id,
150 p.provence_id,
151 c.city_id
152 from
153 city c
154 inner join provence p on c.provence_id = p.provence_id
155 inner join district d on p.district_id = d.district_id
156 inner join country t on d.country_id = t.country_id
157 where
158 c.city_id = p_city_id;
159end#
160
161delimiter ;
162
163$mysqli->multi_query($query)