Join

TlcAndres · 02-12-2008, 09:08 PM

I was wondering if anyone could show me a data dump and a valid example of using JOIN

I wanted to see if I could use it to retrieve the sakt and use CONCAT and MD5 to hash up my passwords for loggin in.

CoryMathews · 02-12-2008, 10:50 PM

i didnt understand that say it again.

TlcAndres · 02-12-2008, 11:07 PM

Lets us say for example you've the following

Table members
`id`
`username`
`password`

Table info
`info1`
`info2`
`user_id`

What would be a JOIN statement between the two? (I've looked at the examples on mysql and can't understand)

Ultimately I'd like to in the query fetch the hash from the row and concat it with the password and then md5 it.

SOCK · 02-13-2008, 03:55 AM

So we're assuming the relation between the two tables is members.id ==> info.user_id? You might consider using `member_id` as the column name on both columns, it looks a bit ambiguous. I usually use naming conventions like members.memberID ==> info.memberID_FK to relate the two tables and make it clear the info table stores the Foreign Key.

Code:

SELECT
 m.username
, m.password
, i.info1
, i.info2
FROM members AS m
INNER JOIN info AS i
ON ( m.id = i.user_id )
WHERE m.id = 2;

I'd be happy to provide a 'real' JOIN with the CONCAT expression if you'd provide more detail on the columns and what output you expect.

Please post any questions you have as to the JOIN statement itself.

TlcAndres · 02-17-2008, 04:24 AM

I can't seem to wrap my head around it

SELECT `banned` , `password`
FROM `mrf_users`
INNER JOIN `mrf_uhash` AS `hash`
WHERE `user_idFK` =3 ON ( MD5( CONCAT( 'trick', `hash`.`hash` ) ) = `password` )

In this query I'm trying to retrieve the hash and then md5 the concated string and match it with the retrieved password, I'm aware it's syntactically so any pointers would be great.

SOCK · 02-17-2008, 06:32 PM

You've got a couple of elements out of place, namely the ON conditional for the JOIN statement needs to immediately follow the JOIN. Then you can use a WHERE clause to establish more matches for the join statement to match columns on.

It needs to be something more like

Code:

SELECT `banned` , `password`
FROM `mrf_users`
INNER JOIN `mrf_uhash` AS `hash`
ON ( `mrf_users`.`password` = MD5( CONCAT('trick',`hash`.`hash`) ) )
WHERE `user_idFK` =3;

I don't quite understand the application logic, but I guess that's ok.

TlcAndres · 02-17-2008, 07:04 PM

Well the query is used for a login and simultaneous ban check, the final query is something supposed to fetch the hash from the table in accordance with the proper username, the concate it and md5 is and check whether it's the same as the password in the table mrf_users where the username is mrferos

SOCK · 02-17-2008, 07:15 PM

Right, I was just not clear on why there were two hash values, i.e. a hash within an MD5 hash.

Did altering that query do it?

TlcAndres · 02-17-2008, 07:32 PM

I'm terrible with naming convention, that hash within the md5 is actually a salt.

SOCK · 02-17-2008, 08:05 PM

Aha. That makes sense.