How to transfer a lot of values beteen tables in a variable or array?

Question

I have two SQL tables. The first one structure is simple.

ID    Name
----------

The second one is simple too.

CommentID     Comment    CreatorID
----------------------------------

I want to show in my site all the comments that correspond with the "ID of user"

The problem is we have here a lot of ID and two different tables.

Something like this:

$1= $bdd->query("SELECT * FROM comments WHERE id_user=220281");
$2=$1->fetch();

But its impossible because id user is not on the comments table.

You said you have "ID of the creator of the comment" on the comments table... Why not using it? — Itay, Sep 01 '13 at 05:50
Imagine the database will do anything you want if you'll just ask it nicely — Itay, Sep 01 '13 at 05:53

score 1 · Answer 1 · edited May 23 '17 at 11:49

1

The most simple way to do this is to join the tables like this:

select
    users.name,
    comms.commentID,
    comms.comment
from
    userTable users
        join commentTable comms
            on users.ID=comms.ownersID
where
    users.id=1

This will return the users name in each row of data, but you don't need to use it in the output more than once.

It also sounds like you could use a few pointers on SQL queries. Do yourself a favour and have a read of this article I put together a while back.

edited May 23 '17 at 11:49

Community

1
1

answered Sep 01 '13 at 05:52

Fluffeh

33,228
16
67
80

I'm not sure that's what he's asking – Itay Sep 01 '13 at 05:53
@BorisDetry I linked a lengthy document in my answer. Go have a read of it. It will explain a LOT of the things you probably want to know. – Fluffeh Sep 01 '13 at 05:55

Ashish Gaur · Answer 2 · 2013-09-01T06:01:42.367

0

A simple join will do the trick like this :

SELECT c.comment, u.user_name FROM 
    Users u 
JOIN
    Comments c ON 
    c.creator_id = u.user_id
WHERE 
    u.user_id=220281

fiddle:http://sqlfiddle.com/#!6/3b28a/1

edited Sep 01 '13 at 06:01

answered Sep 01 '13 at 05:54

Ashish Gaur

2,030
2
18
32

score 0 · Answer 3 · answered Sep 01 '13 at 05:57

0

SELECT c.* 
  FROM comments c
 INNER JOIN users u ON c.id_creator = u.id_user AND 
                       u.id_user = 220281

answered Sep 01 '13 at 05:57

Fabien TheSolution

5,055
1
18
30

How to transfer a lot of values beteen tables in a variable or array?

3 Answers3