September 11th, 2013, 01:06 AM
Question regarding JOIN in sql query...
I hope I can explain this correctly and hopefully get some help...
I have 2 tables:
Facts: (only showing relevant fields)
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):
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.
$userID = $_SESSION['userID'];
$sql = "SELECT facts.*, favorites.* from facts, favorites WHERE facts.ID = favorites.factID AND favorites.userID = $userID $limit";
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 ...
September 11th, 2013, 02:15 AM
While you can use commas to join multiple tables (as you have now), explicitly JOINing them is better.
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.
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
September 13th, 2013, 07:11 AM
Sorry for the double post, forgot about this one lol!
Originally Posted by requinix
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 :/
September 13th, 2013, 07:34 AM
And that's exactly why you not not use the friggin' * star to select columns.
Originally Posted by Jax2
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