[SOLVED] Advice on rewrite, artistId/songnameHash/ or artistHash/songnameHash/?

[limitphp]

To get to the comments page of a certain song, should I rewrite the url like:

website.com/artistID/songnameHash/

or

website.com/artistnameHash/songnameHash/

 

ex)

www.website.com/2/The-Weight/

or

 

www.website.com/The-Band/The-Weight/

 

The only reason I would do the first option is because, the artistID is stored in the song table, so I wouldn't have to make two trips to get the artistnameHash.

 

I figure most people will never type it in manually...they'll just follow the link.

 

What do you think?

 

 

[rhodesa]

you should  be able to join the tables and make it in 1 query. i would use the artist name vs the artist id to make it more SEO friendly

[limitphp]

you should  be able to join the tables and make it in 1 query. i would use the artist name vs the artist id to make it more SEO friendly

ok.  I'll use the artistNameHash.

 

I didn't even think of how I'm going to get the songID from the song table.

 

I've never used a JOIN before.  What it be a left join, right join or just a join?

 

In my songs table I have the columns:

songNameHash

songID

artistID

 

In my artist table I have the columns:

artistNameHash

artistID

 

with songnamehash and artistnamehash coming from the query, how would i get the correct songID?

 

thanks

 

 

[rhodesa]

SELECT * FROM songs s LEFT JOIN artists a ON s.artistID = a.artistID WHERE s.songNameHash = '$songHash' AND a.artistNameHash = '$artistHash'

 

but then again...is songNameHash unique across the entire song table? if so, then you can use just that in the where clause

[limitphp]

SELECT * FROM songs s LEFT JOIN artists a ON s.artistID = a.artistID WHERE s.songNameHash = '$songHash' AND a.artistNameHash = '$artistHash'

 

thank you so much!

 

why a left join and not a right join?  I'm really trying to understand this, but JOINS really confuse the heck out of me!

 

I'm reading about them,

    *  JOIN: Return rows when there is at least one match in both tables

    * LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table

    * RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table

    * FULL JOIN: Return rows when there is a match in one of the tables

 

 

but I can't seem to understand it fully....

 

 

 

[rhodesa]

so, a normal JOIN should work too...the only difference you would see is if there was a song with an artistID that wasn't in the artist table. the LEFT JOIN will return that song even if there is no matching artistID in the artist table

[limitphp]

so, a normal JOIN should work too...the only difference you would see is if there was a song with an artistID that wasn't in the artist table. the LEFT JOIN will return that song even if there is no matching artistID in the artist table

 

This might sound stupid, but how do you know which table is the left one and which one is the right one?

Does it come from this part?:

ON s.artistID = a.artistID

 

after the ON, the one on the left side of the equal sign is LEFT, and the one on the right side is the RIGHT one?

[rhodesa]

FROM songs s LEFT JOIN artists a

songs is on the left...artists is on the right

[limitphp]

thank you very much!

[limitphp]

so, a normal JOIN should work too...the only difference you would see is if there was a song with an artistID that wasn't in the artist table. the LEFT JOIN will return that song even if there is no matching artistID in the artist table

 

I want it to match where artistID is in both.  There will never be an occasion where its not in both....

so, would I use JOIN or INNER JOIN?

 

nevermind.....they are the same.....I just found out....

 

[rhodesa]

INNER JOIN