Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    New Coder
    Join Date
    Oct 2011
    Posts
    81
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Selecting from two tables

    Okay so I pretty much know nothing of this, but what I'm trying to do is this:

    I want to select all columns from a table called "updates", and one column (named "image") from a table called "portfolio".

    I've been trying some things but I don't really have a clue and all I'm getting are errors.

    When I look up on how to select information from to tables, I find all things like joining them with inner join and left join etc. But I was wondering what I need to do in this case and if these things are necessary.

  • #2
    Senior Coder
    Join Date
    Jan 2011
    Location
    Missouri
    Posts
    4,029
    Thanks
    23
    Thanked 589 Times in 588 Posts
    I think you need to have something common with both tables in order to do this.

    Like "ON TableA.name = TableB.name". With out it I keep coming up with way too much information.

  • #3
    New Coder
    Join Date
    Oct 2011
    Posts
    81
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Mh but they have nothing in common, that's not my meaning because I just have this page, on which I want to display updates (just a date and the title of the update, which I have a table for). And on the same page I want to make a div in which random images from another table are displayed.

    Do I maybe just have to make a new query for that or something? Because I can't really see how to link those two.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,023
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    Well, I know one way to do it, but it would *NOT* guarantee that each record from your primary table would get a *UNIQUE* image from the second table.

    In other words, if you showed 10 dates and titles, you might find that you would get 7 different images, with 3 of the images repeated. etc.

    And I do see another more complex way to do this where each date/title would get a unique image *provided* there are at least as many image records as there are data/title records.

    But, really, it would probably be easier to just do this as two separate queries and let your PHP/ASP/JSP/whatever code get one record from each table.

    You could do both
    Code:
    SELECT * FROM dates_and_titles ORDER BY something;
    SELECT imageURL FROM images ORDER BY RAND();
    And just run a loop where you get one record from each query each time through the loop.

    But if you really need a MySQL solution we could make one.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    New Coder
    Join Date
    Oct 2011
    Posts
    81
    Thanks
    1
    Thanked 0 Times in 0 Posts
    The easy way that doesn't guarantee unique images is fine. But I'm a bit confused. To get my records from the updates table I use this

    Code:
    $query = "SELECT *FROM updates ORDER BY date DESC";
    So for that
    Code:
    SELECT images FROM portfolio ORDER BY RAND();
    part, where do I put it? In the same query? Or do I have to make a new query? I'm sorry I'm really completely new to this and when I try putting it together I'm getting errors.

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,023
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    Two separate queries.

    I don't use PHP, but it will be *SOMETHING* like this:
    Code:
    $r1 = mysql_query("SELECT * FROM updates ORDER BY date DESC") or die(mysql_error());
    $r2 = mysql_query("SELECT images FROM portfolio ORDER BY RAND()") or die(mysql_error());
    while ( $rows1 = mysql_fetch_array($r1) )
    {
        $rows2 = mysql_fetch_array($r2) or die(mysql_error());
        $image = $rows2["images"];
        ... now output info for one record from $rows1 ...
        ... including that image from $rows2 ...
    }
    CAUTION: If you don't have at least as many images as you have records in UPDATES table, you will run out of records from $rows2 before you get to the end of $row1.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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