StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I've knocked togther a simple but feature rich example (excludes referential integrity) which you might find helpful...</p> <p>Full script can be found here : <a href="http://pastie.org/1303023" rel="nofollow">http://pastie.org/1303023</a></p> <h2>PHP</h2> <p>Calling a single stored procedure that returns multiple resultsets. You could of course just call 3 separate stored procedures.</p> <pre><code><?php $conn = new Mysqli("localhost", "foo_dbo", "pass", "foo_db"); $result = $conn->query(sprintf("call list_state_counties_cities(%d)", 1)); $row = $result->fetch_assoc(); echo $row["name"], "<br/>"; $conn->next_result(); $result = $conn->use_result(); while($row = $result->fetch_assoc()) echo $row["county_id"], "<br/>"; $conn->next_result(); $result = $conn->use_result(); while($row = $result->fetch_assoc()) echo $row["city_id"], "<br/>"; $result->close(); $conn->close(); ?> </code></pre> <h2>SQL Script</h2> <pre><code>-- TABLES drop table if exists states; create table states ( state_id tinyint unsigned not null auto_increment primary key, name varchar(255) unique not null, county_counter smallint unsigned not null default 0 ) engine=innodb; drop table if exists counties; create table counties ( county_id smallint unsigned not null auto_increment primary key, state_id tinyint unsigned not null, name varchar(255) not null, city_counter smallint unsigned not null default 0, key (state_id) ) engine=innodb; drop table if exists cities; create table cities ( city_id smallint unsigned not null auto_increment primary key, county_id smallint unsigned not null, state_id tinyint unsigned not null, -- denormalised shortcut join name varchar(255) not null, key (county_id), key (state_id) ) engine=innodb; -- TRIGGERS delimiter # create trigger counties_before_ins_trig before insert on counties for each row begin update states set county_counter = county_counter+1 where state_id = new.state_id; end# create trigger cities_before_ins_trig before insert on cities for each row begin declare v_state_id tinyint unsigned default 0; select state_id into v_state_id from counties where county_id = new.county_id; set new.state_id = v_state_id; update counties set city_counter = city_counter+1 where county_id = new.county_id; end# delimiter ; -- STORED PROCEDURES drop procedure if exists list_state_counties_cities; delimiter # create procedure list_state_counties_cities ( in p_state_id tinyint unsigned ) begin -- return multiple resultsets !! select * from states where state_id = p_state_id; select * from counties where state_id = p_state_id order by name; select * from cities where state_id = p_state_id order by name; end # delimiter ; -- TEST DATA insert into states (name) values ('state1'),('state2'); insert into counties (state_id, name) values (1,'county1'),(1,'county2'),(1,'county3'), (2,'county4'),(2,'county5'); insert into cities (county_id, name) values (1,'city1'),(1,'city2'),(1,'city3'), (2,'city4'),(2,'city5'), (3,'city6'),(3,'city7'),(3,'city8'), (4,'city9'),(4,'city10'), (5,'city11'); -- TESTING select * from states; select * from counties; select * from cities; call list_state_counties_cities(1); </code></pre> <p>Hope this helps point you in the right direction :)</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload