SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Zealot infoxicated's Avatar
    Join Date
    Jun 2001
    Location
    UK
    Posts
    140
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Are cross database selects possible in MySQL?

    I've come up against a bit of a hurdle, where I need to link the reviews in one database to the user who gave the review in another database.

    The review contains the user_id of the person who gave the review, so I could just do one query to get the review details and then another to get the user details... but that would suck!

    I've done a few searches and a bit of RTFM on this, but I cannot seem to find a straight answer, at least regarding MySQL at any rate.

    Anyone have any idea if it's possible?

  2. #2
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    sure can. just reference table names using this syntax

    database.table

    and column names like this

    database.table.column
    - Matt ** Ignore old signature for now... **
    Dr.BB - Highly optimized to be 2-3x faster than the "Big 3."
    "Do not enclose numeric values in quotes -- that is very non-standard and will only work on MySQL." - MattR

  3. #3
    SitePoint Zealot infoxicated's Avatar
    Join Date
    Jun 2001
    Location
    UK
    Posts
    140
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hell, I'm glad you answered!

    If I've already declared mysql_select_db('db1') as the active db, do I have to de-select that db somehow in order to do the query?

    I was assuming that was why I'm getting the errors thrown up.

  4. #4
    SitePoint Zealot infoxicated's Avatar
    Join Date
    Jun 2001
    Location
    UK
    Posts
    140
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Dr Larry.

    While you are quite clearly a genius, I am merely a f*ckwit!

    I had missed out the FROM clause all along - that was what was throwing back the errors... I had the other part of the syntax right.

    In my defense, the query was so long by this point that it was hard to notice... and, erm, Homesite makes it green on grey, so it was even harder to spot... and it was at after five in the evening and I'd been at it all day.... *sigh*

    *ahem*

    Sorry for wasting your time, Larry - thanks for the help

  5. #5
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    no prob.

    if you have selected a database with mysql_select_db('db1'), db1 will be the default, so you don't have to specify it in the query. e.g.

    Code:
    SELECT * FROM table1, db2.table2 ...
    table1 will come from db1 since that's what you selected as the default.

  6. #6
    SitePoint Zealot infoxicated's Avatar
    Join Date
    Jun 2001
    Location
    UK
    Posts
    140
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up

    Ah... now you tell me!

    Nice one, I'll keep that in mind... although with a deadline of going live Thursday morning I think I'll allow myself the luxury of not going back and re-writing the many queries I wrote today with my new found super-power.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •