SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Hybrid View

  1. #1
    SitePoint Addict Ghz's Avatar
    Join Date
    Jun 2002
    Posts
    284
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    multi selections in one query, how?

    Hello there,
    I'm building up my home page right now with php and I have alot of information that I want it to be in my homepage. I've made all of the requirements and it's working fine now but I feel it's very have to be loaded because of the queries in the homepage. What I'm thinking of is how to make multi selections in one query?!! That is to say, can I select different columns from different tables in one query???? And how???

    one more thing, doesn't that over load my server as many of users surfing in my site? And how many queries do you think are suitable to be in one page?

    Alot of questions need to be answered from my best friends
    Any help would be highly appreciated in advance

  2. #2
    if($awake){code();} PHP John's Avatar
    Join Date
    Jul 2002
    Location
    Along the Wasatch Fault line.
    Posts
    1,771
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The short answer is, Yes.

    The long answer is that you must (as far as I know) have a common field (foreign key) in the tables you want to select from. This is what makes a relational database "relational".

    For example, lets create 2 fictional tables:

    tblUsers
    • fldUserId (Primary Key)
    • fldUserName (Unique)
    • fldUserPassword
    • fldRegistrationDate
    tblPosts
    • fldUserId (Foreign Key, but not Unique)
    • fldTopic
    • fldDatePosted
    • fldText
    Now, to query and retrieve the fields from both tables based on the UserId we would write the query thusly [img]images/smilies/wink.gif[/img]:

    SELECT * FROM tblUsers as U, tblPosts as P WHERE U.fldUserId = P.UserId;

    HTH
    John

  3. #3
    SitePoint Addict Ghz's Avatar
    Join Date
    Jun 2002
    Posts
    284
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanx PHP John ;
    Well, I've seen a piece of code in a book and was looking like this:
    PHP Code:
     
    SELECT country
    .continent,country.countryname,city.cityname
    from country
    .city WHERE city.countryID country.ID 
    I really didn't understand what you meant very well. Couldn't it be more easier than this way??? If you or anyone else would guide me any useful article talking about this I'll be appreciated.

    What about the rest of my questions???

  4. #4
    SitePoint Wizard Chris82's Avatar
    Join Date
    Mar 2002
    Location
    Osnabrück
    Posts
    1,003
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Loosely based on the query you posted. Imagine you have two tables: country and city

    For example country holds following data:

    Code:
    ID | name
    1  | Europe
    2  | America
    3  | Africa
    And city holds following data: The citie's name, an internal number and the country's number it is located in.

    Code:
    cityid  | name     | continent
    1       | Berlin   | 1
    2       | London   | 1
    3       | Utah     | 2
    Here the column "continent" references the column ID in the table continent.

    If you use:

    Code:
    SELECT continent.name, city.name
    FROM continent, city 
    WHERE continent.ID = city.continen;
    you check where the numbers in the column "id" in table "continent" exactly match the same number in the column "continent" in the table "city".

    That query would return:

    Code:
    Berlin    Europe
    London    Europe
    Utah      America
    Because the table "city" does not contain a city which is located in Africa (i.e. no 3 in the column continent) no rows for "Africa" are returned.


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
  •