SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Addict -Ice-php's Avatar
    Join Date
    May 2002
    Location
    UK
    Posts
    260
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL SELECT with Multiple Tables

    Hey
    i want to select some rows from my database from multiple tables

    i use this code:
    PHP Code:
    $sql mysql_query("SELECT * FROM $mulchtable$snipetable$conctable$rjumptable WHERE winner = '$name' ORDER BY date DESC LIMIT 5"); 
    Will this code select 5 rows from all 4 tables where the date is newest
    ( because i havent tested it, it may be ASC becasue i get confused sometimes ) or will it only select 5 from 1 table ?
    If it selects from just the one table is there an easier way of selecting
    from multiple tables ?
    Thanks
    -Ice

  2. #2
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You really need to read up on how joins work. I don't think you will be happy with the results of that query. The idea of a relational database is that there are relationships between the tables ("relations") in the table.

    You probably need to specify on which keys to join the tables together.

    SELECT *
    FROM table1, table2, table3
    WHERE table1.value = table2.foreignkey
    AND table2.value = table3.foreignkey

    Get the idea?

  3. #3
    SitePoint Addict -Ice-php's Avatar
    Join Date
    May 2002
    Location
    UK
    Posts
    260
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Nope
    im quite flouent in PHP but only know some VV basic MySQL
    i dont get the idea of a foreignkeys ?
    what are they ?
    Thanks
    -Ice

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    foreign keys are the values that relate rows in different tables

    here's a very simple example

    consider categories -- catid is the primary key

    catid catdesc
    12 animal
    13 vegetable
    14 mineral

    now here's another table -- thingid is the primary key, and catid is the foreign key

    thingid thingdesc catid
    104 quartz 14
    105 tomato 13
    106 cat 12
    107 cow 12
    108 granite 14
    109 naked molerat 12
    110 copper 14

    foreign keys reference primary keys

    in most databases (not mysql), you cannot enter a row with a value in the foreign key that does not exist in the primary key

    you could not, for example, enter

    111 sandwich 15

    becaase 15 isn't in the categories table

    you don't have to check it -- the database does it for you (again, not mysql)

    if you want more, check some of the links on my site --

    http://r937.com/links.cfm?links=sql
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •