PHP MySQL Table INNER JOIN problem, help needed.?

Question

I have a table in MySQL database named "posts".
The data of "posts" is as follow.

id sender_id post_type text date

1 2 t hi Saturday, 4th August 2012, 7:43:18 pm
3 1 t hello Tuesday, 7th August 2012, 4:39:31 am
4 1 t how r u? Tuesday, 7th August 2012, 4:39:39 am
5 1 t thats cool Tuesday, 7th August 2012, 7:49:35 am
6 1 t good Tuesday, 7th August 2012, 7:49:45 am
7 2 t fine Tuesday, 7th August 2012, 7:50:05 am
8 2 t great Tuesday, 7th August 2012, 7:50:08 am



There is another table named "subscription".
The data of "subcription" is as follow.



id subscriber subscribed date
1 3 1 Friday, 6th July 2012, 7:25:41 pm
2 4 1 Saturday, 7th July 2012, 4:38:26 pm
3 4 2 Sunday, 8th July 2012, 6:46:52 pm



Now I want to fetch row from posts table if "sender_id" of "posts" table is in "subscribed" column
of "subscription" table WHERE "subscriber" = URL id.
I am using this code for it.



$post_query = mysql_query(" SELECT posts.*, subscription.* FROM posts
INNER JOIN subscription ON subscription.subscriber = $uid
ORDER BY posts.id DESC ") or mysql_error();
while($post_row = mysql_fetch_array($post_query))
{
echo $post_text = $post_row['text'].'

---

';

}



Now when URL id = 3
( http://localhost/userarea.php?id=3 )
then it shows the result like

hello
great
fine
good
thats cool
how r u?
hello
hi

( Remember that did not subscribe any other id and I want to show post by only id or ids who are
subscribed by id 3 )

And when URL id = 4
( http://localhost/userarea.php?id=4 )
then it shows the result like

hello
hello
great
great
fine
fine
good
good
thats cool
thats cool
how r u?
how r u?
hello
hello
hi
hi


It duplicate the results.
Remember that id 3 subscribed one user only and id 4 subscribed two users.
I know something is wrong with my INNER JOIN query.
Please tell me the correct way to write this.
Thanks.

Answer 1

It looks like it is because you are selecting subscription.* as well as the INNER JOIN on the subscription table. Try this instead:

mysql_query("SELECT * FROM posts INNER JOIN subscription ON subscription.subscriber = $uid

ORDER BY posts.id DESC")

Answer 2

Repying to dhvrm's comment. You must not use mysql_pconnect instead of mysql_connect. Mysql_pconnect motives apache to create a new and consistent connect to mysql the whole time the user is to your website. It will develop server and database load. You should hook up with the database and unencumber it as soon as you’re finished. Plus, when you've got restrained mysql to only 50 connects this means simplest 50 people would use your internet site at once. As an alternative of fifty connections to the database immediately.

Answer 3

extremely tough issue search in search engines like google that will might help