Is this a join? Somebody point me in the right direction plz

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Is this a join? Somebody point me in the right direction plz

Post by Luke »

I have a table called county_info that looks like:

id, name

and another table called county_boundaries that looks like

id, county_id, lat, lon, extra_info

There are 4 records in county info, and I need to select all from the second table where county_id is equal to any of the county_info ids. How would I do this?

EDIT: OK, I got this:

Code: Select all

SELECT * FROM `county_boundaries`, `county_info` WHERE county_info.id = county_boundaries.county_id
But I was hoping to return the information in this format:

Code: Select all

Array
(
    [Tehema] => Array
        (
        [0] => Array
            (
                [id] => 8
                [lat] => "-0.122220241240514E+03"
                [lon] => "0.401418265246914E+02"
            )
        [1] => Array
            (
                [id] => 8
                [lat] => "-0.122220241240514E+03"
                [lon] => "0.401418265246914E+02"
            )
        [2] => Array
            (
                [id] => 8
                [lat] => "-0.122220241240514E+03"
                [lon] => "0.401418265246914E+02"
            )
        )
    [Butte] => Array
        (
        [0] => Array
            (
                [id] => 10
                [lat] => "-0.122220241240514E+03"
                [lon] => "0.401418265246914E+02"
            )
        [1] => Array
            (
                [id] => 10
                [lat] => "-0.122220241240514E+03"
                [lon] => "0.401418265246914E+02"
            )
        [2] => Array
            (
                [id] => 10
                [lat] => "-0.122220241240514E+03"
                [lon] => "0.401418265246914E+02"
            )
        )
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

I don't think you are going to get the data that way straight from the mysql result. You might have to manipulate it with the array fetch or inside of a separate loop. Pulling from the database returns single dimensional arrays I believe. You can get the data to look like what you want, but you will have to work the array at some point.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

Pulling from the database returns single dimensional arrays I believe.
In fact the result set is two-dimensional. It's what the Relational databases is all about: playing with a bunch of two-dimensional 'arrays'.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

To return it in the format you need I would probably just use 1 query with another query inside of a while loop in the code. I wrote this join query for you... don't know if it will help or not.

Code: Select all

SELECT `county_info`.`id`, `county_info`.`name`, `county_boundaries`.`county_id`, `county_boundaries`.`lat`, `county_boundaries`.`lon`, `county_boundaries`.`extra_info`
FROM `county_info` 
JOIN `county_boundaries` 
ON (`county_info`.`id` = `county_boundaries`.`id`) 
WHERE ....`
GM
Forum Contributor
Posts: 365
Joined: Wed Apr 26, 2006 4:19 am
Location: Italy

Re: Is this a join? Somebody point me in the right direction

Post by GM »

The Ninja Space Goat wrote: But I was hoping to return the information in this format:

Code: Select all

Array
(
    [Tehema] => Array
        (
        [0] => Array
            (
                [id] => 8
                [lat] => "-0.122220241240514E+03"
                [lon] => "0.401418265246914E+02"
            )
        [1] => Array
            (
                [id] => 8
                [lat] => "-0.122220241240514E+03"
                [lon] => "0.401418265246914E+02"
            )
        [2] => Array
            (
                [id] => 8
                [lat] => "-0.122220241240514E+03"
                [lon] => "0.401418265246914E+02"
            )
        )
    [Butte] => Array
        (
        [0] => Array
            (
                [id] => 10
                [lat] => "-0.122220241240514E+03"
                [lon] => "0.401418265246914E+02"
            )
        [1] => Array
            (
                [id] => 10
                [lat] => "-0.122220241240514E+03"
                [lon] => "0.401418265246914E+02"
            )
        [2] => Array
            (
                [id] => 10
                [lat] => "-0.122220241240514E+03"
                [lon] => "0.401418265246914E+02"
            )
        )
That format is impossible with sql. Sql can return you a two dimensional array, and that's it.

The initial query that you posted is correct - you'll need to manipulate the resultant array with PHP to get it into the format you require.
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

Thank you... I realize I will need to manipulate it... i just wanted to make sure that the results would return all the information necessary to do so. thanks everybody.
Post Reply