SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Zealot
    Join Date
    Mar 2004
    Location
    united states
    Posts
    178
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    how to combine two queries into one?

    I have two tables:

    settings:
    Image_ID INT UNSIGNED NOT NULL

    Image
    ID INT UNSIGNED NOT NULL,
    Width INT UNSIGNED NOT NULL,
    Height INT UNSIGNED NOT NULL,
    Thumbnail_ID INT UNSIGNED NOT NULL

    The Image table stores information about all the images in the database.
    If a certain image has a thumbnail, the ID of the thumbnail is stored in the Thumbnail_ID column (otherwise 0 is stored)

    The Image_ID column in the settings table defines the ID for an image (which has a thumbnail) which is used a logo in the system.

    I would like to extract the following in a single query:
    The width & height of the thumbnail image for the image whose ID is stored in the settings table.

    I can do this in two queries:
    $id = select Thumbnail_ID from image where settings.Image_ID = Image.ID;
    select Width, Height from Image where ID = $id;


    is it possible to do it in a single query?
    maybe the design of the tables is wrong?
    what would be a better design?

    thanks

  2. #2
    Now with customized title Jump's Avatar
    Join Date
    Sep 2002
    Location
    The Restaurant at The End of The Universe
    Posts
    1,423
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You need to use a "JOIN". But why do you have a table with just an ID?

  3. #3
    Now with customized title Jump's Avatar
    Join Date
    Sep 2002
    Location
    The Restaurant at The End of The Universe
    Posts
    1,423
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Something like this.
    PHP Code:
            $img "SELECT
                        settings.Image_ID,
                        Image.Thumbnail_ID,
                        Image.Width,
                        Image.Height
                    FROM
                        settings
                    LEFT JOIN
                        Image
                    ON
                        Image.ID         = settings.Image_ID"



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
  •