#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2007
    Posts
    34
    Rep Power
    8

    Question regarding JOIN in sql query...


    Hi all,

    I hope I can explain this correctly and hopefully get some help...

    I have 2 tables:

    favorites:
    ID (int)
    factID (int)
    userID (int)

    Facts: (only showing relevant fields)
    ID (int)
    fact (text)


    When a user chooses to add an article as a favorite of his/hers, the article ID (factID) and the user ID (session['userID']) are inserted into the favorites table.

    I am trying to use both tables to figure out which articles have been favorited and to display only those articles.

    Here is what I tried (and failed at):

    PHP Code:
    $userID $_SESSION['userID'];
    $sql "SELECT facts.*, favorites.* from facts, favorites WHERE facts.ID = favorites.factID AND favorites.userID = $userID $limit";
    $result=mysql_query($sql$db); 
    The query does not produce an error, however, it does not find the correct articles to display. It will show 1 that is favorited, but then will show 4 of the same article that hasn't even been favorited by my test account.

    Any suggestions as to how I would rewrite the sql statement to actually work?

    Oh, and $limit is just for the pagination, in this case it would be limit 0,5 ...
  2. #2
  3. Transforming Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,119
    Rep Power
    9398
    While you can use commas to join multiple tables (as you have now), explicitly JOINing them is better.
    Code:
    SELECT facts.* -- as this is all the information you actually need
    FROM facts -- the "primary" table, as I think of it
    JOIN favorites ON facts.ID = favorites.factID -- tie in the favorites
    WHERE favorites.userID = $userID -- favorited by this user
    $limit
    Now that's basically what you have now so I'd expect both to produce the same results, in which case you should take a look at your variables and the data in the tables to be sure you've actually got the data you think you have.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2007
    Posts
    34
    Rep Power
    8
    Originally Posted by requinix
    While you can use commas to join multiple tables (as you have now), explicitly JOINing them is better.
    Code:
    SELECT facts.* -- as this is all the information you actually need
    FROM facts -- the "primary" table, as I think of it
    JOIN favorites ON facts.ID = favorites.factID -- tie in the favorites
    WHERE favorites.userID = $userID -- favorited by this user
    $limit
    Now that's basically what you have now so I'd expect both to produce the same results, in which case you should take a look at your variables and the data in the tables to be sure you've actually got the data you think you have.
    Sorry for the double post, forgot about this one lol!

    I had tried it all different ways and could not figure out why it wasn't working. I simply could not return $row['ID'] and have it give me the correct ID number of the "Fact" ... instead it was giving me the ID number of the "favorite" table.

    Rather than muck with this even more, I changed ID to favID in that table and problem solved, though I'm sure there was is a proper workaround to it. Googling joining tables with identical column names didn't help :/
  6. #4
  7. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Originally Posted by Jax2
    instead it was giving me the ID number of the "favorite" table.
    And that's exactly why you not not use the friggin' * star to select columns.

    I know that SELECT * (or variants thereof) is extremely popular amongst people who've just started with SQL. But if you think about it, it's just terrible:

    • It selects everything, regardless of how sensitive or big the data is, regardless of whether you need it or not. It simply dumps all rows into memory.
    • You can't even tell what you get. You get whatever the current table structure contains. Good luck debugging stuff like this. And good luck to the poor people who have to work with your code (if there are any).
    • You have no way of controlling the column names. If two of them happen to have the same name, well, you have a problem (as you just saw).

    Yeah, the * star may save you some seconds while writing the code. But it will cost you many hours or even days afterwards. And it's just poor design.

    The columns of a result set matter. Choose them explicitly, and treat them carefully. If you have name collisions, you need aliases. Never use the * star -- except maybe for quick tests.

    Comments on this post

    • recyan agrees
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".

IMN logo majestic logo threadwatch logo seochat tools logo