how to select multiple table in single query?

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
bug_aonz
Forum Newbie
Posts: 1
Joined: Sun Jun 22, 2008 2:47 am

how to select multiple table in single query?

Post by bug_aonz »

i need php to call stored procedure which is returned 3 table.
this is my code for store procedure
--------------------------------------------------------------

Code: Select all

CREATE PROCEDURE 'sp_TGCourseDetail'(IN courseID INT)
BEGIN
 
--- table 1
SELECT mast.*
FROM TG_COURSE_MAST mast
WHERE mast.COURSE_ID = courseID;
 
--- table 2
SELECT pho.PHONE_NO
FROM TG_COURSE_PHONE pho
WHERE pho.COURSE_ID = courseID;
 
--- table3
SELECT pri.PRICE,pri.DAY_TYPE
FROM TG_COURSE_PRICE pri
WHERE pri.COURSE_ID = courseID;
 
END
--------------------------------------------------------------
***NOTE! i don't need to join these 3 table into 1 because of my own reason.
and this is sample php that calls the stored procedure above
--------------------------------------------------------------

Code: Select all

<?
$db = new mysqli("localhost","root","root","golfcourse");
$res_CourseDetail =$db->query("CALL sp_TGCourseDetail(1)");
$db->close();
 
//How to fetch data
 
?>
---------------------------------------------------------------
- i try using $res_CourseDetail->fetch_row() to fetch data from those 3 output table but i gave only the data of the first table.
How to fetch data from 2nd and 3rd table?

Thank for help
^^
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: how to select multiple table in single query?

Post by onion2k »

You can only return one result set from a query. You need to rethink your approach to do one of three things:

1. Use a join. You say you don't want to, but you don't give a reason why not. If the things can be joined then join them.

2. Use a UNION to merge the results into one set. This won't work on the queries you've got in your example but maybe you can tweak them so it will.

3. Use 3 separate queries. This is the most likely approach, if you're getting 3 different bits of unrelated data then 3 queries is the right way to do it.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: how to select multiple table in single query?

Post by Weirdan »

onion2k wrote:You can only return one result set from a query.
This is not true. Read: mysqli_next_result()
bug_aonz wrote: i need php to call stored procedure which is returned 3 table
You need to use mysqli_multi_query()
Post Reply