SitePoint Sponsor |
|
User Tag List
Results 1 to 3 of 3
Hybrid View
-
Feb 9, 2005, 13:11 #1
- Join Date
- Jan 2002
- Location
- U.S.A. *Blue State*
- Posts
- 289
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Help with JOIN SELECT from 3 Tables
Is there a way to do a JOIN type of SELECT query where you are pulling data from 3 tables, but all the data gets returned as one row or one array? Right now I am trying to do a SELECT from 3 tables and I am getting results like this:
(PART NUMBER, DESCRIPTION, PRICE, DISCOUNTED_PRICE, URL, IMAGE_URL)
TXX-101, Widget, 59.00, /index.php
TXX-101, Widget, 59.00, /image.jpg
TXX-101, Widget, 55.00, /index.php
TXX-101, Widget, 55.00, /image.jpg
When I really want to get is a single row/array like this:
TXX-101, Widget, 59.00, 55.00, /index.php, /image.jpg
The root of the problem is that in my primary table the id(primary key) has always one row. In the other tables the id(foreign key) has 2 rows. This problem may be a result of the database's stucture being wrong, but I am working off of a MySQL database I am allowed to change minimally if at all.
P.S. Running MySQL 4.0.17Last edited by frankiehots; Feb 9, 2005 at 14:22.
-
Feb 9, 2005, 18:35 #2
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
nope, not until 4.1 when you can use the GROUP_CONCAT function
you'll have to do that with script logic on the above result set
-
Feb 14, 2005, 10:22 #3
- Join Date
- Jan 2002
- Location
- U.S.A. *Blue State*
- Posts
- 289
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
I upgraded to MySQL 4.1.9 and the GROUP_CONCAT function does exactly what I need. That is ONE USEFUL function when you are joining multiple tables. I can't believe that it wasn't in previous versions. That feature alone makes the upgrade to 4.1 worth it.
With the values being concatenated by MySQL and separated by commas, I just use the PHP explode function to make them easily accessible:
$values = explode(',', $row['3']);
Bookmarks