[SOLVED] How to simplify a complex query?

[sevun]

Table - column1, column2, column3, etc..:

event - eventID, eventLat, eventLng, profileID, categoryID

profile - profileID, profileUsername

category - categoryID, categoryName

 

The query:

 

$query = "SELECT 
`event`.`eventLat`,
`event`.`eventLng`,
`profile`.`profileUsername`,
`category`.`categoryName`,
    FROM event, profile, category
    WHERE (
        (`event`.`eventLat` <= ".$north.")
    AND (`event`.`eventLat` >= ".$south.")
    AND (`event`.`eventLng` <= ".$east.")
    AND (`event`.`eventLng` >= ".$west.")
    AND (`event`.`profileID` = `profile`.`profileID`)
    AND (`event`.`categoryID` = `category`.`categoryID`)
    )

 

The speed was okay until I added the lines:

 

AND (`event`.`profileID` = `profile`.`profileID`)
AND (`event`.`categoryID` = `category`.`categoryID`)

 

These two lines are not filtering anything, they are just replacing an ID number with a text.  Is there a query to filter the results to the set desired (e.g. just return the filtered primary key column of the 'event' database), then query the three databases for the information I want?

[pdkv2]

you should select those two columns also in your query

 

`event`.`profileID`

And

`event`.`categoryID`

[sevun]

I did originally, but I want to replace event.profile ID with profile.profileName & event.categoryID with category.categoryName.

 

event.profileID is a Foreign Key profile.profileID & event.categoryID is a Foreign Key for category.categoryID

[fenway]

Try rewriting this with proper join syntax first.

[sevun]

How would I do that in the context of a php query?

[sevun]

How would I do that in the context of a php query?  I figured it would require two steps, but I'm not familiar with how to process the query in php, then use a join statement on the results.

 

Try rewriting this with proper join syntax first.

[sevun]

Using the code in the LEFT JOIN clause

 

SELECT event.eventLat, event.eventLng, profile.profileUsername, category.categoryName
FROM event
LEFT JOIN profile ON event.profileID = profile.profileID`
LEFT JOIN category ON event.categoryID = category.categoryID
WHERE ... 

 

in place of using the AND clause

 

SELECT event.eventLat, event.eventLng, profile.profileUsername, category.categoryName
FROM event, profile, category
WHERE ((event.profileID = profile.profileID)
AND (event.categoryID = category.categoryID)
AND ... 

 

worked like a champ!  I'm guessing this is how I should of done it from the beginning.  Thanks for everyone's help.