[SOLVED] table joining help ....

[imarockstar]

I have 2 tables that I need to grab data from and show in a single output ...

 

I will try and explain this as best I can ...

 

 

Table 1 "users"

id,userid,username,email

(there are more rows then this, but these are all I need)

 

Table 2 "post"

id, userid, post

 

Basically I need to pull ALL the posts in DB,and then also list the USERID of each post.

 

The output looks like this:

http://franklinspirko.com/sites/isyatg/

 

or if you dont want to click the above link:

 

(post)

I was on the treadmill and I saw you running on the treadmill ahead of me !!!! I kept watching your ipod strapped to your shoulder. It kept going on and off ..

 

(user info)

mygym: La Fitness mylocation: 75023 postedby: benchgod @ 5:55pm

did you see me ?

 

post : the post the user posts (from table 2)

did you see me: this will be where I need to insert the USERID from table 1

 

Im not sure how to do this ... below is my current code Just pulling data from 1 table. How do I add another one and join them based on the USERID ?

 

<?php



// Retrieve data from database 
$sql="SELECT * FROM posts";
$result=mysql_query($sql);

// Start looping rows in mysql database.
while($rows=mysql_fetch_array($result)){
?>


<div class=postblock>


<div class=avatar>
<img class=avatar src="images/avatar.jpg" alt="image" width="90" >
</div> <!-- #avatar -->			

<div class=post>
<? echo $rows['post']; ?> 

<Br>

<div class=crumb><span class=crumb>mygym:</span> <span class=crumbdata>La Fitness</span></div>			

<div class=crumb><span class=crumb>mylocation:</span> <span class=crumbdata>75023</span></div>	

<div class=crumb>
<span class=crumb>postedby:</span> 
<span class=crumbdata>benchgod</span>
<span class=crumb>@</span> 
<span class=crumbdata>5:55pm</span>
</div>	

<br class=clear>		

<div class=seeme>did you see me ?</div>			

</div> <!-- #post -->

<br class=clear>

</div>	<!-- #postblock -->		




<? } mysql_close(); ?>

 

 

 

 

 

 

 

 

[Mchl]

Try this:

 

SELECT * FROM posts INNER JOIN users ON userid

[imarockstar]

thanks for the quick response ...

 

could you explain the code you gave me ? so i can understand what its doing ? if im going to use it i might as well understand it .. lol

 

thanks again

bobby

[Mchl]

OK. But maybe you'd like to read it at source?

 

I assume you have no problems with SELECT * thing

 

Next thing:

FROM posts INNER JOIN users ON userid

translates to something like this:

"take records from posts, and try to find matching records from users, using uderid field as a match"

[imarockstar]

ok that makes since .. thanks for your help ..

 

bobby

[Barand]

Try this:

 

SELECT * FROM posts INNER JOIN users ON userid

 

Either

SELECT * FROM posts INNER JOIN users ON posts.userid = users.userid

 

or

 

SELECT * FROM posts INNER JOIN users USING (userid)

 

and avoid SELECT * unless you really do want all the column from both tables. In this case you only want post and user

[Mchl]

Actually from what I've read in documentation, the optimiser will resolve both to:

SELECT * FROM posts, users WHERE posts.userid = users.userID

which will give same results of course.

 

And yes. Choosing only needed columns will make things easier and faster.

[imarockstar]

whats wrong with this ???

 

 

$result = mysql_query("SELECT * FROM users INNER JOIN usersinfo ON userid WHERE userid = ". $_SESSION['userid']);

 

[Mchl]

See Barand's post. I've made a mistake in mine.

[imarockstar]

 

OK it worked without the session data .... here is my code ..

 

 

// Retrieve data from database

$result = mysql_query("SELECT * FROM users INNER JOIN usersinfo USING '{$_SESSION['userid']}' ") or die(mysql_error());

 

 

here is the error

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2142747'' at line 1

 

[Mchl]

Not like this.

 

Like this

 

$result = mysql_query("SELECT * FROM users INNER JOIN usersinfo USING (userid) WHERE userid = '{$_SESSION['userid']}' ") or die(mysql_error());

[imarockstar]

 

this produced this error ... never seen this one b4 ..

 

 

Column 'userid' in where clause is ambiguous

 

[Mchl]

 

That's because you have 'userid' in both tables, and 'WHERE' doesn't know which one to use. Can you figure out how to amend it? (There's hint in Barand's post above)

[imarockstar]

thats "where" lol .. i get confused ..

 

if I dont have userid in both tables .... how will it know that the userid is attached to a post ?

 

oh wait ...

 

in the other table ... the post table ... or whatever table i am using ... should i change "userid" to somthing else .. like ... postid .... but the data will be the same as user id .. so userid and post id will have the same "number" .. so when is searched for matches .. looks for the data and not the row name ??

 

am i even close ?

 

b

[Mchl]

You said you do have userid in both tables....

 

Table 1 "users"

id,userid,username,email

(there are more rows then this, but these are all I need)

 

Table 2 "post"

id, userid, post

 

Forget it... was reading to fast.

 

 

[edit]

 

You can explicitly state which 'userid' to use this way

WHERE users.userid = '{$_SESSION['userid']}'

[fenway]

I'ts not a game... specify a table prefix.

[imarockstar]

ya i do now .. but you said it was because i had 2 userid rows .. i was saying if I changed one of those to somthing else .. would that solve the issue ?

[imarockstar]

 

omg this is giving me a headache .. i remember why inthe past I stayed away from joining tables ...

 

 

table 1 - users  (holds the original userid and username and pass) ---  uses userid to hold the member #

table 2 - usersinfo (hold personal info of the users) --- uses infoid to hold the member #

 

 

code:

 

 

$result = mysql_query("SELECT * FROM users INNER JOIN usersinfo USING (userid) WHERE usersinfo.infoid = '{$_SESSION['userid']}' ") or die(mysql_error());

 

 

 

error

 

Unknown column 'franklin_gym.usersinfo.userid' in 'on clause'

 

[fenway]

That error doesn't match that query.

[Mchl]

If the joining column have different names, you can't use 'USING ()' clause.

Instead use 'ON users.userID = usersinfo.infoid'

[imarockstar]

 

omg this is giving me a headache .. i remember why inthe past I stayed away from joining tables ...

 

 

table 1 - users  (holds the original userid and username and pass) ---  uses userid to hold the member #

table 2 - usersinfo (hold personal info of the users) --- uses infoid to hold the member #

 

 

code:

 

 

$result = mysql_query("SELECT * FROM users INNER JOIN usersinfo USING (userid) WHERE usersinfo.infoid = '{$_SESSION['userid']}' ") or die(mysql_error());

 

 

 

error

 

Unknown column 'franklin_gym.usersinfo.userid' in 'on clause'

 

 

 

 

 

 

 

[imarockstar]

holy crap .. that worked .. haha ..

 

i used ON instead of USING ...

 

 

thanks for the help guys !!!!! I actually learned somthing !!!

 

until next time ..

 

bobby

[Mchl]

I actually learned somthing !!!

 

That's great

 

Sorry for confusing you a little along the way