0

I'm new at Programming, so I have a question about selecting a data in MySQL. All the answer is about merging and joining that I'm not really looking for. returns

First table containing all the user data, and second table showing all posts by users.

I've tried doing this:

$sql="select * from wall, maintable where postverification="yes" AND userid='".$_SESSION['userid']."'";

$result=mysql_query($sql);

$row=mysql_fetch_array($result);

The reason I want to get these different data is so i can post to php a hidden field in the form that containing "who post what" in the same page called wall.php (Like a twitter concept, but the timeline showing all user post)

The code that i've tried above only selecting from 1 table whether "wall" or "maintable".

Why is this happening?

BenMorel
  • 34,448
  • 50
  • 182
  • 322
user3665072
  • 3
  • 1
  • What's your tables structure? Also mysql_* functions are (being?) depreciated, you should switch to using mysqli_* or PDO. – TMH May 22 '14 at 12:59
  • 1
    Based on the information you provide about the two tables, they almost MUST have a common column - containing the user ID or similar. Join the tables on that column and then use a normal join. You might [get some benefit from reading this Q&A](http://stackoverflow.com/questions/12475850/how-can-an-sql-query-return-data-from-multiple-tables) that I put together to help with questions like this. – Fluffeh May 22 '14 at 13:01
  • Hello @TomHart Thankyou for your response. I'm trying to understand your question, my maintable contain userid, fullname, nickname. and my wall table contain postid postverification fullpost and title. is it you asking? – user3665072 May 22 '14 at 13:04
  • You need to change the structure of your table.Your `wall table` must have an `userid` which meaningfully connects it with `main table`. – krishna May 22 '14 at 13:10
  • @user3665072: TomHart is not asking, he is telling you. You say that the second table contains posts from *users*. So users and posts *are* related. Only your wall table doesn't show that relation. How do you know who posted? Is there maybe a third table holding that relation? – Thorsten Kettner May 22 '14 at 13:11
  • BTW: maintable is not a good name for a table. A table name should tell what the table contains. – Thorsten Kettner May 22 '14 at 13:13
  • @ThorstenKettner krishna Yep I've just realized that they're related. But from what I've Known before (from just 2 days learning), if i put things like "Select * from maintable, wall where userid" it will selecting only the post that posted by the userid isn't it? Sorry, beginner here :D – user3665072 May 22 '14 at 13:18
  • @user3665072 - it would select all the rows from the table with a column called userid which has the value you specify, but combined with EVERY row from the other table. So it maintable contained 10 posts for (say) userid = 5, and wall had 100 rows, you would bring back 500 rows. This is why you specify a column to join the 2 tables on (ie, a column where the values match when one tables row relates to the other tables row). – Kickstart May 22 '14 at 13:24
  • @ThorstenKettner okay i've changed my maintable name with userprofile, thankyou thorsten :D – user3665072 May 22 '14 at 13:26
  • @user3665072: Can you explain exactly what you want to see in your result list? Which rows? All user-wall combinations? Or one record per user? Or one record per wall? And what columns do you want to show? – Thorsten Kettner May 22 '14 at 13:27
  • @ThorstenKettner sure thorsten, why not haha. My purpose is to show in the wall.php containing : All posts and the nickname who post it each and the form to post under it posting to "postprocess.php". The problem here is, i have to select the nickname that indexed by the userid. and the wall i want to be shown is all post (the post, indexed by postid) – user3665072 May 22 '14 at 13:39
  • Guys, thankyou for helping me. you just help a 12 years old who get influenced by learntocode campaign and start to learn code using an used old book that using an indonesian language, so using a very advanced term is kinda hard for me. this is what i did: select * from userprofile, wall where userid='".$_SESSION['userid']."' (i've changed the maintable to userprofile based on thorsten advice. thankyou thorsten!) But thankyou for helping me. Goodluck GBU – user3665072 May 22 '14 at 14:09

2 Answers2

1

Perhaps you look for something like this:

SELECT a.field1, b.field2 FROM table1 AS a, table2 AS b WHERE a.field3=b.field3

Or using LEFT JOIN,the same query becomes:

SELECT a.field1, b.field2 
FROM table1 AS a
LEFT JOIN table2 AS b ON a.field3=b.field3

You need to look at sql aliases.

DrColossos
  • 12,656
  • 3
  • 46
  • 67
andrew
  • 2,058
  • 2
  • 25
  • 33
0

You have to use a join statement:

select * from wall inner join nmaintable on wall.'samefield'=postverification.'samefield'
 where postverification="yes" AND userid='".$_SESSION['userid']."'";

Also consider SQL injection, try using PDO.

apomene
  • 14,282
  • 9
  • 46
  • 72