Page 1 of 1

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

Posted: Thu Jul 13, 2006 12:31 pm
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"
            )
        )

Posted: Thu Jul 13, 2006 4:07 pm
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.

Posted: Thu Jul 13, 2006 6:21 pm
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'.

Posted: Thu Jul 13, 2006 6:49 pm
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 ....`

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

Posted: Fri Jul 14, 2006 2:46 am
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.

Posted: Fri Jul 14, 2006 10:18 am
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.